1 PACKAGE MSD_DEM_COMMON_UTILITIES AS
2 /* $Header: msddemcus.pls 120.8 2008/01/02 15:09:46 syenamar noship $ */
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
54 /*** PUBLIC PROCEDURES ***
55 * LOG_MESSAGE
56 * LOG_DEBUG
57 * GET_DBLINK
58 * GET_INSTANCE_INFO
59 */
60
61 /*
62 * This procedure logs a given message text in the concurrent request log file.
63 * param: p_buff - message text to be logged.
64 */
65 PROCEDURE LOG_MESSAGE ( p_buff IN VARCHAR2);
66
67 /*
68 * This procedure logs a given debug message text in the concurrent request log file
69 * only if the profile MSD_DEM_DEBUG is set to 'Yes'.
70 * param: p_buff - debug message text to be logged.
71 */
72 PROCEDURE LOG_DEBUG ( p_buff IN VARCHAR2);
73
74 /*
75 * This procedure gets the db link to the given source instance
76 */
77 PROCEDURE GET_DBLINK (
78 errbuf OUT NOCOPY VARCHAR2,
79 retcode OUT NOCOPY VARCHAR2,
80 p_sr_instance_id IN NUMBER,
81 p_dblink OUT NOCOPY VARCHAR2);
82
83 /*
84 * This procedure gets the instance info given the source instance id
85 */
86 PROCEDURE GET_INSTANCE_INFO (
87 errbuf OUT NOCOPY VARCHAR2,
88 retcode OUT NOCOPY VARCHAR2,
89 p_instance_code OUT NOCOPY VARCHAR2,
90 p_apps_ver OUT NOCOPY NUMBER,
91 p_dgmt OUT NOCOPY NUMBER,
92 p_instance_type OUT NOCOPY NUMBER,
93 p_sr_instance_id IN NUMBER);
94
95 /*
96 * This procedure will refresh Purge Series Data data profile to its defualt value
97 * i.e. it will set the data profile option to No Load and No Purge for all series
98 * included in the profile.
99 */
100
101 PROCEDURE REFRESH_PURGE_SERIES (
102 errbuf OUT NOCOPY VARCHAR2,
103 retcode OUT NOCOPY VARCHAR2,
104 p_profile_id IN NUMBER,
105 p_schema IN VARCHAR2);
106
107 /*
108 * Update the synonyms MSD_DEM_TRANSFER_LIST and MSD_DEM_TRANSFER_QUERY
109 * to point to the Demantra's tables TRANSFER_LIST and TRANSFER_QUERY
110 * if Demantra is installed.
111 * Sets the profile MSD_DEM_SCHEMA to the Demantra Schema Name
112 * The checks if the table MDP_MATRIX exists in the Demantra Schema
113 */
114
115 PROCEDURE UPDATE_SYNONYMS (
116 errbuf OUT NOCOPY VARCHAR2,
117 retcode OUT NOCOPY VARCHAR2);
118
119 /* Deletes the msd_dem_entities_inuse table if the new demantra schema is intstalled
120 this will ensure that there will be no mapping between the seeded units in APPS and
121 the (display uints,exchange rate,indexes) in Demantra */
122
123 PROCEDURE CLEANUP_ENTITIES_INUSE(
124 errbuf out nocopy varchar2,
125 retcode out nocopy varchar2);
126
127
128 /*** FUNCTIONS ***
129 * GET_ALL_ORGS
130 * DM_TIME_LEVEL
131 * GET_PARAMETER_VALUE
132 * GET_LOOKUP_VALUE
133 * GET_UOM_CODE
134 * GET_SR_INSTANCE_ID_FOR_ZONE
135 * UOM_CONVERT
136 * IS_PF_FCSTABLE_FOR_ITEM
137 * IS_PRODUCT_FAMILY_FORECASTABLE
138 * GET_SUPPLIER_CALENDAR
139 * GET_SAFETY_STOCK_ENDDATE
140 * GET_PERIOD_DATE_FOR_DUMMY
141 * GET_SITE_FOR_CSF
142 * IS_LAST_DATE_IN_BUCKET
143 * GET_SNO_PLAN_CUTOFF_DATE
144 * IS_SUPPLIER_CALENDAR_PRESENT
145 */
146
147
148
149 /*
150 * This function returns the comma(,) separated list of demand management enabled orgs
151 * belonging to the given org group.
152 */
153 FUNCTION GET_ALL_ORGS (
154 p_org_group IN VARCHAR2,
155 p_sr_instance_id IN NUMBER)
156 RETURN VARCHAR2;
157
158 /* This function returns the Active Demantra Data Model time level (Day/Month/week) */
159 FUNCTION DM_TIME_LEVEL RETURN VARCHAR2;
160
161 /* This function returns the parameter_value in msd_dem_setup_parameters given the parameter_name */
162 FUNCTION GET_PARAMETER_VALUE (
163 p_sr_instance_id NUMBER,
164 p_parameter_name VARCHAR2)
165 RETURN VARCHAR2;
166
167 /* This function returns the lookup_value given the lookup_type and lookup_code */
168 FUNCTION GET_LOOKUP_VALUE (
169 p_lookup_type IN VARCHAR2,
170 p_lookup_code IN VARCHAR2)
171 RETURN VARCHAR2;
172
173 /* This function returns the UOM code given the display unit id */
174 FUNCTION GET_UOM_CODE (
175 p_unit_id IN NUMBER)
176 RETURN VARCHAR2;
177
178 /* This function returns a sr_instance_id in which the zone is defined */
179 FUNCTION GET_SR_INSTANCE_ID_FOR_ZONE (
180 p_zone IN VARCHAR2)
181 RETURN NUMBER;
182
183 /* This function returns the conversion rate for the given item, From UOM and To UOM */
184 FUNCTION UOM_CONVERT (
185 p_inventory_item_id IN NUMBER,
186 p_precision IN NUMBER,
187 p_from_unit IN VARCHAR2,
188 p_to_unit IN VARCHAR2)
189 RETURN NUMBER;
190
191 /* This function returns 1 if the product family's forecast control is set for the given
192 item in the master org, else returns 2 */
193 FUNCTION IS_PF_FCSTABLE_FOR_ITEM (
194 p_sr_inventory_item_id IN NUMBER,
195 p_sr_instance_id IN NUMBER,
196 p_master_org_id IN NUMBER)
197 RETURN NUMBER;
198
199 /* This function returns 1 if the product family forecast control flag is set,
200 * else returns 2
201 */
202 FUNCTION IS_PRODUCT_FAMILY_FORECASTABLE (
203 p_inventory_item_id IN NUMBER,
204 p_sr_instance_id IN NUMBER)
205 RETURN NUMBER;
206
207 /*
208 * This function gets the calendar code
209 */
210 FUNCTION GET_SUPPLIER_CALENDAR (
211 p_plan_id IN NUMBER,
212 p_sr_instance_id IN NUMBER,
213 p_organization_id IN NUMBER,
214 p_inventory_item_id IN NUMBER,
215 p_supplier_id IN NUMBER,
216 p_supplier_site_id IN NUMBER,
217 p_using_organization_id IN NUMBER)
218 RETURN VARCHAR2;
219
220 /*
221 * This function gets the period end date
222 */
223 FUNCTION GET_SAFETY_STOCK_ENDDATE (
224 p_plan_id IN NUMBER,
225 p_sr_instance_id IN NUMBER,
226 p_organization_id IN NUMBER,
227 p_inventory_item_id IN NUMBER,
228 p_period_start_date IN DATE)
229 RETURN DATE;
230
231 /*
232 * Returns a valid date from the table INPUTS in Demantra
233 */
234 FUNCTION GET_PERIOD_DATE_FOR_DUMMY
235 RETURN DATE;
236
237 /*
238 * Given, the instance, customer and/or site, this function returns
239 * the site level member name. If only the customer is specified then
240 * then any arbit site belonging to the customer is returned.
241 */
242 FUNCTION GET_SITE_FOR_CSF (
243 p_sr_instance_id IN NUMBER,
244 p_customer_id IN NUMBER,
245 p_customer_site_id IN NUMBER)
246 RETURN VARCHAR2;
247
248 /*
249 * Given, the instance, calendar_code, calendar_date, this function
250 * returns 1 if the date is the last date in its demantra bucket,
251 * else returns 2.
252 * Note: This function requires the table msd_dem_dates to be
253 * populated.
254 */
255 FUNCTION IS_LAST_DATE_IN_BUCKET (
256 p_sr_instance_id IN NUMBER,
257 p_calendar_code IN VARCHAR2,
258 p_calendar_date IN DATE)
259 RETURN NUMBER;
260
261 /*
262 * Given the plan id of a SNO plan, this function returns
263 * the cutoff date for the plan.
264 */
265 FUNCTION GET_SNO_PLAN_CUTOFF_DATE (
266 p_plan_id IN NUMBER)
267 RETURN DATE;
268
269 /*
270 * This function returns 1 if a supplier calendar is present else returns 2.
271 */
272 FUNCTION IS_SUPPLIER_CALENDAR_PRESENT (
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 NUMBER;
281
282 END MSD_DEM_COMMON_UTILITIES;
[Home] [Help]