[Home] [Help]
PACKAGE BODY: APPS.INV_INVIRITD_XMLP_PKG
Source
1 PACKAGE BODY INV_INVIRITD_XMLP_PKG AS
2 /* $Header: INVIRITDB.pls 120.2 2008/01/08 06:53:22 dwkrishn noship $ */
3 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
4 BEGIN
5 BEGIN
6 IF P_BREAK_ID = 1 THEN
7 P_ORDERBY := 'ORDER BY C_CAT_FLEX, C_ITEM_FLEX';
8 ELSE
9 P_ORDERBY := 'ORDER BY C_ITEM_FLEX, C_CAT_FLEX2';
10 END IF;
11 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
12 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
13 EXCEPTION
14 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
15 /*SRW.MESSAGE(1
16 ,'Error Initializing SRW :SRWINIT')*/NULL;
17 RAISE;
18 END;
19 BEGIN
20 NULL;
21 EXCEPTION
22 WHEN /*SRW.USER_ EXIT_FAILURE*/OTHERS THEN
23 /*SRW.MESSAGE(1
24 ,'Invalid Item Flexfield :MSTK')*/NULL;
25 RAISE;
26 END;
27 BEGIN
28 SELECT
29 DISTINCT
30 MEANING
31 INTO P_YES_VALUE
32 FROM
33 FND_LOOKUP_VALUES
34 WHERE LOOKUP_CODE = 'Y'
35 AND LOOKUP_TYPE = 'YES_NO'
36 AND LANGUAGE = USERENV('LANG')
37 AND VIEW_APPLICATION_ID = 0;
38 SELECT
39 DISTINCT
40 MEANING
41 INTO P_NO_VALUE
42 FROM
43 FND_LOOKUP_VALUES
44 WHERE LOOKUP_CODE = 'N'
45 AND LOOKUP_TYPE = 'YES_NO'
46 AND LANGUAGE = USERENV('LANG')
47 AND VIEW_APPLICATION_ID = 0;
48 EXCEPTION
49 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
50 /*SRW.MESSAGE(2
51 ,'Invalid Value for Lookup_Type')*/NULL;
52 RAISE;
53 END;
54 BEGIN
55 IF P_ITEM_HI IS NULL AND P_ITEM_LO IS NULL THEN
56 NULL;
57 ELSIF P_ITEM_HI = P_ITEM_LO THEN
58 NULL;
59 ELSE
60 NULL;
61 END IF;
62 EXCEPTION
63 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
64 /*SRW.MESSAGE(2
65 ,'Invalid Item Flexfield :where:MSTK')*/NULL;
66 RAISE;
67 END;
68 BEGIN
69 BEGIN
70 NULL;
71 EXCEPTION
72 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
73 /*SRW.MESSAGE(3
74 ,'Invalid Category Flexfield:where:MCAT')*/NULL;
75 RAISE;
76 END;
77 BEGIN
78 NULL;
79 EXCEPTION
80 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
81 /*SRW.MESSAGE(1
82 ,'Invalid Category Flexfield:MCAT')*/NULL;
83 RAISE;
84 END;
85 END;
86 BEGIN
87 IF P_BOM_INFO = '1' OR P_ALL_INFO = '1' THEN
88 BEGIN
89 NULL;
90 EXCEPTION
91 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
92 /*SRW.MESSAGE(30
93 ,'Invalid Item Flexfield :BOM:MSTK')*/NULL;
94 RAISE;
95 END;
96 BEGIN
97 NULL;
98 EXCEPTION
99 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
100 /*SRW.MESSAGE(30
101 ,'Invalid Item Flexfield :ENG:MSTK')*/NULL;
102 RAISE;
103 END;
104 ELSE
105 NULL;
106 END IF;
107 END;
108 BEGIN
109 IF P_CST_INFO = '1' OR P_ALL_INFO = '1' THEN
110 BEGIN
111 NULL;
112 EXCEPTION
113 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
114 /*SRW.MESSAGE(31
115 ,'Invalid Accounting Flexfield :CST:GL#')*/NULL;
116 RAISE;
117 END;
118 ELSE
119 NULL;
120 END IF;
121 END;
122 BEGIN
123 IF P_PO_INFO = '1' OR P_ALL_INFO = '1' THEN
124 BEGIN
125 NULL;
126 EXCEPTION
127 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
128 /*SRW.MESSAGE(40
129 ,'Invalid Accounting Flexfield :ENC_ACCT:GL#')*/NULL;
130 RAISE;
131 END;
132 BEGIN
133 NULL;
134 EXCEPTION
135 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
136 /*SRW.MESSAGE(40
137 ,'Invalid Accounting Flexfield :EXP_ACCT:GL#')*/NULL;
138 RAISE;
139 END;
140 /*SRW.MESSAGE(99
141 ,'Debug Mode 1: PO Attributes')*/NULL;
142 SELECT
143 count(*),
144 MAX(CATEGORY_FLEX_STRUCTURE)
145 INTO P_FA_INSTALLED,P_ACAT_STRUCT_NUM
149 ,'Debug Mode 2: ' || TO_CHAR(P_FA_INSTALLED))*/NULL;
146 FROM
147 FA_SYSTEM_CONTROLS;
148 /*SRW.MESSAGE(99
150 /*SRW.MESSAGE(99
151 ,'Debug Mode 3: ' || TO_CHAR(P_ACAT_STRUCT_NUM))*/NULL;
152 IF P_FA_INSTALLED <> 0 THEN
153 BEGIN
154 /*SRW.MESSAGE(99
155 ,'Debug Mode [3-1]: ' || TO_CHAR(P_ACAT_STRUCT_NUM))*/NULL;
156 /*SRW.MESSAGE(99
157 ,'Debug Mode [3-2]: ' || TO_CHAR(P_ACAT_STRUCT_NUM))*/NULL;
158 EXCEPTION
159 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
160 /*SRW.MESSAGE(40
161 ,'Invalid Category Flexfield :CAT#')*/NULL;
162 RAISE;
163 END;
164 ELSE
165 P_PO_ACAT_FLEX := '''x''';
166 END IF;
167 /*SRW.MESSAGE(99
168 ,'Debug Mode 4: ' || TO_CHAR(P_ACAT_STRUCT_NUM))*/NULL;
169 ELSE
170 NULL;
171 END IF;
172 END;
173 BEGIN
174 IF P_WIP_INFO = '1' OR P_ALL_INFO = '1' THEN
175 BEGIN
176 NULL;
177 EXCEPTION
178 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
179 /*SRW.MESSAGE(70
180 ,'Invalid Locator Flexfield :MTLL')*/NULL;
181 RAISE;
182 END;
183 ELSE
184 NULL;
185 END IF;
186 END;
187 BEGIN
188 IF P_INVC_INFO = '1' OR P_ALL_INFO = '1' THEN
189 BEGIN
190 NULL;
191 EXCEPTION
192 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
193 /*SRW.MESSAGE(90
194 ,'Invalid Accounting Flexfield :INVC_ACCT:GL#')*/NULL;
195 RAISE;
196 END;
197 ELSE
198 NULL;
199 END IF;
200 END;
201 RETURN (TRUE);
202 END BEFOREREPORT;
203
204 FUNCTION AFTERREPORT RETURN BOOLEAN IS
205 BEGIN
206 BEGIN
207 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
208 EXCEPTION
209 WHEN /*SRW.USER_EXIT_FAILURE*/OTHERS THEN
210 /*SRW.MESSAGE(1
211 ,'SRWEXIT failed')*/NULL;
212 END;
213 RETURN (TRUE);
214 END AFTERREPORT;
215
216 FUNCTION C_GEN_INFO_SELFORMULA RETURN VARCHAR2 IS
217 BEGIN
218 IF P_GEN_INFO = 1 OR P_ALL_INFO = 1 THEN
219 RETURN (',INV_MEANING_SEL.c_mfg_lookup(' || 'msi.ALLOWED_UNITS_LOOKUP_CODE' || ',''MTL_CONVERSION_TYPE'') Conversions,
220 /*MSI.inventory_item_status_code Item_status, */
224 decode(MSI.DUAL_UOM_CONTROL, 1, ''Non-Dual'', 2, ''Fixed'', 3, ''Default'', 4, ''No Default'', NULL) DUAL_UOM_CONTROL,
221 INV_MEANING_SEL.C_ITEM_STATUS(msi.INVENTORY_ITEM_STATUS_CODE) Item_Status,
222 INV_MEANING_SEL.c_fnd_lookup(msi.ITEM_TYPE,''ITEM_TYPE'') Item_type,
223 MSI.primary_unit_of_measure Primary_uom,
225 INV_MEANING_SEL.C_UNITMEASURE(msi.SECONDARY_UOM_CODE) SECONDARY_UOM_CODE,
226 msi.DUAL_UOM_DEVIATION_HIGH DUAL_UOM_DEVIATION_HIGH,
227 msi.DUAL_UOM_DEVIATION_LOW DUAL_UOM_DEVIATION_LOW,
228 INV_MEANING_SEL.c_fnd_lookup_vl(msi.TRACKING_QUANTITY_IND,''INV_TRACKING_UOM_TYPE'') Tracking_Quantity_Ind,
229 INV_MEANING_SEL.c_fnd_lookup_vl(msi.ONT_PRICING_QTY_SOURCE,''INV_PRICING_UOM_TYPE'') Ont_Pricing_Qty_Source,
230 INV_MEANING_SEL.c_fnd_lookup_vl(msi.SECONDARY_DEFAULT_IND,''INV_DEFAULTING_UOM_TYPE'') Secondary_Default_Ind');
231 ELSE
232 RETURN (' ,''z'' Conversions,
233 ''x'' Item_status,
234 ''x'' Item_type,
235 ''x'' Primary_uom,
236 ''x'' DUAL_UOM_CONTROL,
237 ''x'' SECONDARY_UOM_CODE,
238 0 DUAL_UOM_DEVIATION_HIGH,
239 0 DUAL_UOM_DEVIATION_LOW,
240 ''x'' TRACKING_QUANTITY_IND,
241 ''x'' ONT_PRICING_QTY_SOURCE,
242 ''x'' SECONDARY_DEFAULT_IND');
243 END IF;
244 RETURN NULL;
245 END C_GEN_INFO_SELFORMULA;
246
247 FUNCTION C_GEN_INFO_FROMFORMULA RETURN VARCHAR2 IS
248 BEGIN
249 IF P_GEN_INFO = 1 OR P_ALL_INFO = 1 THEN
250 RETURN (' ');
251 ELSE
252 RETURN ('/* Do not select general information */');
253 END IF;
254 RETURN NULL;
255 END C_GEN_INFO_FROMFORMULA;
256
257 FUNCTION C_GEN_INFO_WHEREFORMULA RETURN VARCHAR2 IS
258 BEGIN
259 IF P_GEN_INFO = 1 OR P_ALL_INFO = 1 THEN
260 RETURN (' ');
261 ELSE
262 RETURN (' ');
263 END IF;
264 RETURN NULL;
265 END C_GEN_INFO_WHEREFORMULA;
266
267 FUNCTION AFTERPFORM RETURN BOOLEAN IS
268 BEGIN
269 RETURN (TRUE);
270 END AFTERPFORM;
271
272 FUNCTION C_CAT_FROMFORMULA RETURN VARCHAR2 IS
273 BEGIN
274 RETURN ('mtl_item_categories mic, mtl_categories_b mc, ');
275 END C_CAT_FROMFORMULA;
276
277 FUNCTION C_CAT_WHEREFORMULA RETURN VARCHAR2 IS
278 BEGIN
279 RETURN ('and msi.inventory_item_id = mic.inventory_item_id
280 and mic.category_id = mc.category_id
281 and mic.category_set_id = ' || TO_CHAR(P_CAT_SET_ID) || '
282 and mic.organization_id = msi.organization_id');
283 END C_CAT_WHEREFORMULA;
284
285 FUNCTION C_CAT_PADFORMULA(C_CAT_FIELD IN VARCHAR2
286 ,C_CAT_PAD IN VARCHAR2) RETURN VARCHAR2 IS
287 BEGIN
288 /*SRW.REFERENCE(C_CAT_FIELD)*/NULL;
289 RETURN (C_CAT_PAD);
290 END C_CAT_PADFORMULA;
291
292 FUNCTION C_BOM_SELFORMULA RETURN VARCHAR2 IS
293 BEGIN
294 IF P_BOM_INFO = 1 OR P_ALL_INFO = 1 THEN
295 RETURN (',' || P_BOM_ITEM_FLEX || ' C_bom_item_flex,
296 INV_MEANING_SEL.c_mfg_lookup(' || 'msi.bom_item_type' || ',''BOM_ITEM_TYPE'') BOM_item_type,
297 decode(msi.BOM_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) BOM_allowed,
298 MSI.ENGINEERING_ECN_CODE Engineering_Change_Order,
299 decode(msi.ENG_ITEM_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Engineering_item,
300 ' || P_ENG_ITEM_FLEX || ' C_eng_item_flex ,
301 decode(msi.Effectivity_Control, 1,''Date'',2,''Model/Unit Number'',NULL) Effectivity_Control,
302 INV_MEANING_SEL.c_fnd_lookup_vl(' || 'msi.CONFIG_MODEL_TYPE ' || ',''CZ_CONFIG_MODEL_TYPE'') Config_model_type ,
303 decode(msi.Auto_Created_Config_Flag,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Auto_Created_Config_Flag,
304 INV_MEANING_SEL.c_fnd_lookup_vl(' || 'msi.CONFIG_ORGS ' || ',''INV_CONFIG_ORGS_TYPE'') Config_orgs ,
305 INV_MEANING_SEL.c_fnd_lookup_vl(' || 'msi.CONFIG_MATCH ' || ',''INV_CONFIG_MATCH_TYPE'') Config_match
306 ');
307 ELSE
308 RETURN (',''x'' C_bom_item_flex,
309 ''x'' BOM_item_type,
310 ''x'' BOM_allowed,
311 ''x'' Engineering_Change_Order,
312 ''x'' Engineering_item,
313 ''x'' C_eng_item_flex ,
314 ''x'' Effectivity_Control,
315 ''x'' Config_model_type,
316 ''x'' Auto_Created_Config_Flag,
317 ''x'' Config_orgs,
318 ''x'' Config_match
319 ');
320 END IF;
321 RETURN NULL;
322 END C_BOM_SELFORMULA;
323
324 FUNCTION C_BOM_FROMFORMULA RETURN VARCHAR2 IS
325 BEGIN
326 IF P_BOM_INFO = 1 OR P_ALL_INFO = 1 THEN
327 RETURN (' mtl_system_items_b BIF,
328 mtl_system_items_b EIF, ');
329 ELSE
330 RETURN (' /* Do not select BOM info */ ');
331 END IF;
332 RETURN NULL;
333 END C_BOM_FROMFORMULA;
334
335 FUNCTION C_BOM_WHEREFORMULA RETURN VARCHAR2 IS
336 BEGIN
337 IF P_BOM_INFO = 1 OR P_ALL_INFO = 1 THEN
338 RETURN ('and msi.BASE_ITEM_ID = BIF.inventory_item_id(+)
339 and BIF.organization_id(+) = ' || TO_CHAR(P_ORG_ID) || '
340 and msi.ENGINEERING_ITEM_ID = EIF.inventory_item_id(+)
341 and EIF.organization_id(+) = ' || TO_CHAR(P_ORG_ID) || ' ');
342 ELSE
343 RETURN (' ');
344 END IF;
345 RETURN NULL;
346 END C_BOM_WHEREFORMULA;
347
348 FUNCTION C_CST_SELFORMULA RETURN VARCHAR2 IS
349 BEGIN
350 IF P_CST_INFO = 1 OR P_ALL_INFO = 1 THEN
351 RETURN (', decode(msi.COSTING_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Costing_enabled
352 , ' || P_CST_ACCT_FLEX || ' C_cst_acct_flex
353 , decode(msi.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Include_in_rollup
354 , decode(msi.INVENTORY_ASSET_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Inventory_asset_value
355 , MSI.std_lot_size Standard_lot_size ');
356 ELSE
357 RETURN (', ''z'' Costing_enabled
358 , ''x'' C_cst_acct_flex
359 , ''x'' Include_in_rollup
360 , ''x'' Inventory_asset_value
361 , 0 Standard_lot_size ');
362 END IF;
363 RETURN NULL;
364 END C_CST_SELFORMULA;
365
366 FUNCTION C_CST_FROMFORMULA RETURN VARCHAR2 IS
367 BEGIN
368 IF P_CST_INFO = 1 OR P_ALL_INFO = 1 THEN
369 RETURN (' gl_code_combinations CST, ');
370 ELSE
371 RETURN ('/* Do not select Costing Options */');
372 END IF;
373 RETURN NULL;
374 END C_CST_FROMFORMULA;
375
376 FUNCTION C_CST_WHEREFORMULA RETURN VARCHAR2 IS
377 BEGIN
378 IF P_CST_INFO = 1 OR P_ALL_INFO = 1 THEN
379 RETURN ('and msi.COST_OF_SALES_ACCOUNT = CST.code_combination_id(+) ');
380 ELSE
381 RETURN (' ');
382 END IF;
383 RETURN NULL;
384 END C_CST_WHEREFORMULA;
385
386 FUNCTION C_PO_SELFORMULA RETURN VARCHAR2 IS
387 BEGIN
388 IF P_PO_INFO = 1 OR P_ALL_INFO = 1 THEN
389 RETURN (',MSI.asset_category_id Asset_category_id
390 ,MSI.RECEIVE_CLOSE_TOLERANCE Receive_Close_Tolerance
391 ,MSI.INVOICE_CLOSE_TOLERANCE Invoice_Close_Tolerance
392 ,decode(msi.RECEIPT_REQUIRED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Receipt_Required
393 ,decode(msi.INSPECTION_REQUIRED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Inspection_Required
394 ,decode(msi.PURCHASING_ITEM_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Purchasing_item
395 ,decode(msi.MUST_USE_APPROVED_VENDOR_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Use_approved_vendor
396 ,decode(msi.TAXABLE_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Taxable_item
397 ,decode(msi.ALLOW_ITEM_DESC_UPDATE_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Allow_description_update
398 ,MSI.market_price Market_price
399 ,INV_MEANING_SEL.c_po_un_numb(msi.UN_NUMBER_ID) UN_number
400 ,INV_MEANING_SEL.C_PO_HAZARD_CLASS(msi.HAZARD_CLASS_ID) Hazard_Class
401 ,decode(msi.RFQ_REQUIRED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) RFQ_Required
402 ,MSI.list_price_per_unit List_price
403 ,MSI.PRICE_TOLERANCE_PERCENT Price_tolerance_percent
404 ,' || P_PO_ACAT_FLEX || ' C_acat_flex
405 ,decode(msi.OUTSIDE_OPERATION_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Outside_Operation
406 ,MSI.OUTSIDE_OPERATION_UOM_TYPE Outside_Operation_Unit_Type
407 ,MSI.ROUNDING_FACTOR Rounding_Factor
408 ,MSI.UNIT_OF_ISSUE Unit_of_Issue
409 ,' || P_PO_EXP_ACCT_FLEX || ' Expense_Account
410 ,' || P_PO_ENC_ACCT_FLEX || ' Encumbrance_Account
411 ,INV_MEANING_SEL.C_PER_PEOPLE(msi.buyer_id) Default_Buyer
412 ,decode(msi.PURCHASING_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Purchasable
413 ,msi.purchasing_tax_code Purchasing_tax_code
414 ');
415 ELSE
416 RETURN (', 0 Asset_category_id
417 , 0 Receive_Close_Tolerance
418 , 0 Invoice_Close_Tolerance
419 , ''x'' Receipt_Required
420 , ''x'' Inspection_Required
421 ,''x'' Purchasing_item
422 ,''x'' Use_approved_vendor
423 ,''x'' Taxable_item
424 ,''x'' Allow_description_update
425 , 0 Market_price
426 ,''x'' UN_number
427 ,''x'' Hazard_Class
428 ,''x'' RFQ_Required
429 , 0 List_price
430 , 0 Price_tolerance_percent
431 ,''x'' C_acat_flex
432 ,''x'' Outside_Operation
433 ,''x'' Outside_Operation_Unit_Type
434 , 0 Rounding_Factor
435 ,''x'' Unit_of_Issue
436 ,''x'' Expense_Account
437 ,''x'' Encumbrance_Account
438 ,''x'' Default_Buyer
439 ,''x'' Purchasable
440 ,''x'' Purchasing_tax_code
441 ');
442 END IF;
443 RETURN NULL;
444 END C_PO_SELFORMULA;
445
446 FUNCTION C_PO_FROMFORMULA RETURN VARCHAR2 IS
447 BEGIN
448 IF P_PO_INFO = 1 OR P_ALL_INFO = 1 THEN
449 RETURN (' fa_categories_b PACF
450 ,gl_code_combinations PXAF,gl_code_combinations PEAF,');
451 ELSE
452 RETURN ('/* Do not select Purchasing Options */');
453 END IF;
454 RETURN NULL;
455 END C_PO_FROMFORMULA;
456
457 FUNCTION C_PO_WHEREFORMULA RETURN VARCHAR2 IS
458 BEGIN
459 IF P_PO_INFO = 1 OR P_ALL_INFO = 1 THEN
460 RETURN ('and msi.ASSET_CATEGORY_ID = PACF.CATEGORY_ID(+)
461 and msi.EXPENSE_ACCOUNT = PEAF.code_combination_id(+)
462 and msi.ENCUMBRANCE_ACCOUNT = PXAF.code_combination_id(+)');
463 ELSE
464 RETURN (' ');
465 END IF;
466 RETURN NULL;
467 END C_PO_WHEREFORMULA;
468
469 FUNCTION C_RCV_SELFORMULA RETURN VARCHAR2 IS
470 BEGIN
471 IF P_RCV_INFO = 1 OR P_ALL_INFO = 1 THEN
472 RETURN (',INV_MEANING_SEL.c_po_lookup(msi.QTY_RCV_EXCEPTION_CODE,''RECEIVING CONTROL LEVEL'') Over_tolerance_exception
473 ,RRH.ROUTING_NAME Receiving_Routing
474 ,MSI.QTY_RCV_TOLERANCE Quantity_Received_Tolerance
475 ,MSI.ENFORCE_SHIP_TO_LOCATION_CODE Enforce_Ship_To_Location
476 ,decode(msi.ALLOW_SUBSTITUTE_RECEIPTS_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Allow_Substitute_Receipts
477 ,decode(msi.ALLOW_UNORDERED_RECEIPTS_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Allow_Unordered_Receipts
478 ,decode(msi.ALLOW_EXPRESS_DELIVERY_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Allow_Express_Delivery
479 ,MSI.DAYS_EARLY_RECEIPT_ALLOWED Days_Early_Receipt_Allowed
480 ,MSI.DAYS_LATE_RECEIPT_ALLOWED Days_Late_Receipt_Allowed
481 ,INV_MEANING_SEL.c_po_lookup(msi.RECEIPT_DAYS_EXCEPTION_CODE,''RECEIVING CONTROL LEVEL'') Receipt_Date_Exception
482 ');
483 ELSE
484 RETURN (',''x'' Over_tolerance_exception
485 ,''x'' Receiving_Routing
486 , 0 Quantity_Received_Tolerance
487 ,''x'' Enforce_Ship_To_Location
488 ,''x'' Allow_Substitute_Receipts
489 ,''x'' Allow_Unordered_Receipts
490 ,''x'' Allow_Express_Delivery
491 , 0 Days_Early_Receipt_Allowed
492 , 0 Days_Late_Receipt_Allowed
493 ,''x'' Receipt_Date_Exception');
494 END IF;
495 RETURN NULL;
496 END C_RCV_SELFORMULA;
497
498 FUNCTION C_RCV_FROMFORMULA RETURN VARCHAR2 IS
499 BEGIN
500 IF P_RCV_INFO = 1 OR P_ALL_INFO = 1 THEN
501 RETURN (' rcv_routing_headers RRH, ');
502 ELSE
503 RETURN ('/* Do not select Receiving Group Options*/');
504 END IF;
505 RETURN NULL;
506 END C_RCV_FROMFORMULA;
507
508 FUNCTION C_RCV_WHEREFORMULA RETURN VARCHAR2 IS
509 BEGIN
510 IF P_RCV_INFO = 1 OR P_ALL_INFO = 1 THEN
511 RETURN ('and MSI.RECEIVING_ROUTING_ID = RRH.ROUTING_HEADER_ID(+) ');
512 ELSE
513 RETURN (' ');
514 END IF;
515 RETURN NULL;
516 END C_RCV_WHEREFORMULA;
517
518 FUNCTION C_PHYS_SELFORMULA RETURN VARCHAR2 IS
519 BEGIN
520 IF P_ALL_INFO = 1 OR P_PHYS_INFO = 1 THEN
521 RETURN (',MSI.UNIT_VOLUME Unit_Volume
522 ,MSI.UNIT_WEIGHT Unit_Weight
523 ,decode(msi.container_item_flag,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Container_item_flag
524 ,decode(msi.vehicle_item_flag,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Vehicle_Item_Flag
525 ,MSI.Maximum_Load_Weight Maximum_Load_Weight
526 ,MSI.Minimum_Fill_Percent Minimum_Fill_Percent
527 ,MSI.Internal_Volume Internal_Volume
528 ,INV_MEANING_SEL.C_FNDCOMMON(' || 'msi.Container_Type_CODE' || ',''CONTAINER_TYPE'') Container_Type_Code
529 ,INV_MEANING_SEL.c_unit_measure(msi.volume_uom_code) Volume_Unit_of_Measure
530 ,INV_MEANING_SEL.c_unit_measure(msi.weight_uom_code) Weight_Unit_of_Measure
531 ,INV_MEANING_SEL.C_UNITMEASURE(msi.DIMENSION_UOM_CODE) DIMENSION_UOM_CODE
532 ,msi.UNIT_LENGTH UNIT_LENGTH
533 ,msi.UNIT_WIDTH UNIT_WIDTH
534 ,msi.UNIT_HEIGHT UNIT_HEIGHT
535 ,decode(msi.COLLATERAL_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) COLLATERAL_FLAG
536 ,decode(msi.ELECTRONIC_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) ELECTRONIC_FLAG
537 ,decode(msi.EVENT_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) EVENT_FLAG
538 ,decode(msi.DOWNLOADABLE_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) DOWNLOADABLE_FLAG
539 ,decode(msi.EQUIPMENT_TYPE,1,' || '''' || P_YES_VALUE || '''' || ',2,' || '''' || P_NO_VALUE || '''' || ',NULL) EQUIPMENT_TYPE
540 ,decode(msi.INDIVISIBLE_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) INDIVISIBLE_FLAG ');
541 ELSE
542 RETURN (', 0 Unit_Volume
543 , 0 Unit_Weight
544 ,''x'' Container_item_flag
545 ,''x'' Vehicle_item_flag
546 , 0 Maximum_load_weight
547 , 0 Minimum_Fill_Percent
548 , 0 Internal_volume
549 ,''x'' container_type_code
550 ,''x'' Volume_Unit_of_Measure
551 ,''x'' Weight_Unit_of_Measure,
552 ''x'' DIMENSION_UOM_CODE,
553 0 UNIT_LENGTH,
554 0 UNIT_WIDTH,
555 0 UNIT_HEIGHT,
556 ''x'' COLLATERAL_FLAG,
557 ''x'' ELECTRONIC_FLAG,
558 ''x'' EVENT_FLAG,
559 ''x'' DOWNLOADABLE_FLAG,
560 ''x'' EQUIPMENT_TYPE,
561 ''x'' INDIVISIBLE_FLAG');
562 END IF;
563 RETURN NULL;
564 END C_PHYS_SELFORMULA;
565
566 FUNCTION C_GP_SELFORMULA RETURN VARCHAR2 IS
567 BEGIN
568 IF P_ALL_INFO = 1 OR P_GP_INFO = 1 THEN
569 RETURN (',INV_MEANING_SEL.c_mfg_lookup(' || 'msi.INVENTORY_PLANNING_CODE' || ',''MTL_MATERIAL_PLANNING'') Inventory_Planning_Method
570 ,MSI.MAX_MINMAX_QUANTITY Minmax_Maximum_quantity
571 ,MSI.MIN_MINMAX_QUANTITY Minmax_Minimum_quantity
572 ,MSI.SAFETY_STOCK_BUCKET_DAYS Safety_Stock_Bucket_Days
573 ,MSI.CARRYING_COST Carrying_cost_percent
574 ,MSI.ORDER_COST Order_Cost
575 ,MSI.MRP_SAFETY_STOCK_PERCENT Safety_Stock_Percent
576 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.MRP_SAFETY_STOCK_CODE' || ',''MTL_SAFETY_STOCK_TYPE'') Safety_Stock
577 ,MSI.FIXED_ORDER_QUANTITY Fixed_order_quantity
578 ,MSI.FIXED_DAYS_SUPPLY Fixed_days_supply
579
580 ,MSI.MINIMUM_ORDER_QUANTITY Minimum_order_quantity
581 ,MSI.MAXIMUM_ORDER_QUANTITY Maximum_order_quantity
582 ,MSI.FIXED_LOT_MULTIPLIER Fixed_lot_size_multiplier
583 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.SOURCE_TYPE' || ',''MTL_SOURCE_TYPES'') Replenishment_Source_Type
584 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.PLANNING_MAKE_BUY_CODE' || ',''MTL_PLANNING_MAKE_BUY'') Make_or_Buy
585 ,MSI.planner_code Planner
586 ,INV_MEANING_SEL.c_org_name(msi.SOURCE_ORGANIZATION_ID) Source_Organization
587 ,MSI.SOURCE_SUBINVENTORY Source_Subinventory
588 ,msi.SUBCONTRACTING_COMPONENT Subcontracting_Component ');
589 ELSE
590 RETURN (',''x'' Inventory_Planning_Method
591 , 0 Minmax_Maximum_quantity
592 , 0 Minmax_Minimum_quantity
593 , 0 Safety_Stock_Bucket_Days
594 , 0 Carrying_cost_percent
595 , 0 Order_Cost
596 , 0 Safety_Stock_Percent
597 ,''x'' Safety_Stock
598 , 0 Fixed_order_quantity
599 , 0 Fixed_days_supply
600
601 , 0 Minimum_order_quantity
602 , 0 Maximum_order_quantity
603 , 0 Fixed_lot_size_multiplier
604 ,''x'' Replenishment_Source_Type
605 ,''x'' Make_or_Buy
609 , 0 Subcontracting_Component ');
606 ,''x'' Planner
607 ,''x'' Source_Organization
608 ,''x'' Source_Subinventory
610 END IF;
611 RETURN NULL;
612 END C_GP_SELFORMULA;
613
614 FUNCTION C_GP_FROMFORMULA RETURN VARCHAR2 IS
615 BEGIN
616 IF P_ALL_INFO = 1 OR P_GP_INFO = 1 THEN
617 RETURN (' ');
618 ELSE
619 RETURN ('/* Do not select General Planning Group Option */');
620 END IF;
621 RETURN NULL;
622 END C_GP_FROMFORMULA;
623
624 FUNCTION C_GP_WHEREFORMULA RETURN VARCHAR2 IS
625 BEGIN
626 IF P_ALL_INFO = 1 OR P_GP_INFO = 1 THEN
627 RETURN (' ');
628 ELSE
629 RETURN (' ');
630 END IF;
631 RETURN NULL;
632 END C_GP_WHEREFORMULA;
633
634 FUNCTION C_MPS_SELFORMULA RETURN VARCHAR2 IS
635 BEGIN
636 IF P_ALL_INFO = 1 OR P_MPS_INFO = 1 THEN
637 RETURN (',INV_MEANING_SEL.c_fnd_lookup_vl(msi.END_ASSEMBLY_PEGGING_FLAG,''ASSEMBLY_PEGGING_CODE'') End_Assembly_Pegging
638 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.RELEASE_TIME_FENCE_CODE' || ',''MTL_RELEASE_TIME_FENCE'') release_time_fence
639 ,MSI.Release_time_fence_days Release_Time_Fence_Days
640 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.ROUNDING_CONTROL_TYPE' || ',''MTL_ROUNDING'') Rounding_control
641 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.AUTO_REDUCE_MPS' || ',''MRP_AUTO_REDUCE_MPS'') Reduce_MPS
642 ,MSI.SHRINKAGE_RATE Shrinkage_Rate
643 ,MSI.ACCEPTABLE_EARLY_DAYS Acceptable_Early_Days
644 ,decode(msi.MRP_CALCULATE_ATP_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Calculate_ATP
645 ,MSI.OVERRUN_PERCENTAGE Overrun_Percentage
646 ,MSI.ACCEPTABLE_RATE_DECREASE Acceptable_Rate_Decrease
647 ,MSI.ACCEPTABLE_RATE_INCREASE Acceptable_Rate_Increase
648 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.PLANNING_TIME_FENCE_CODE' || ',''MTL_TIME_FENCE'') Planning_Time_Fence
649 ,MSI.PLANNING_TIME_FENCE_DAYS Planning_Time_Fence_Days
650 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.DEMAND_TIME_FENCE_CODE' || ',''MTL_TIME_FENCE'') Demand_Time_Fence
651 ,MSI.DEMAND_TIME_FENCE_DAYS Demand_Time_Fence_Days
652 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.MRP_PLANNING_CODE' || ',''MRP_PLANNING_CODE'') MRP_Planning_Method
653 ,MSI.PLANNING_EXCEPTION_SET Planning_Exception_Set
654 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.ato_forecast_control' || ',''MRP_ATO_FORECAST_CONTROL'') MRP_Forecast_Control
655 ,decode(msi.REPETITIVE_PLANNING_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Repetitive_Planning
656 ,decode(msi.PLANNED_INV_POINT_FLAG,''Y'',''Yes'',''N'',''No'',NULL) PLANNED_INV_POINT_FLAG
657 ,INV_MEANING_SEL.c_mfg_lookup(msi.SUBSTITUTION_WINDOW_CODE,''MTL_TIME_FENCE'') SUBSTITUTION_WINDOW_CODE
658 ,msi.SUBSTITUTION_WINDOW_DAYS SUBSTITUTION_WINDOW_DAYS
659 ,decode(msi.CREATE_SUPPLY_FLAG,''Y'',''Yes'',''N'',''No'',NULL) CREATE_SUPPLY_FLAG
660 ,msi.REPAIR_LEADTIME Repair_Lead_time
661 ,msi.REPAIR_YIELD Repair_Yield
662 ,decode(msi.PREPOSITION_POINT ,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Pre_position_Point
663 ,msi.REPAIR_PROGRAM Repair_Program ');
664 ELSE
665 RETURN (',''z'' End_Assembly_Pegging
666 , 0 Release_Time_Fence
667 , 0 Release_Time_Fence_Days
668 ,''x'' Rounding_control
669 ,''x'' Reduce_MPS
670 , 0 Shrinkage_Rate
671 , 0 Acceptable_Early_Days
672 ,''x'' Calculate_ATP
673 , 0 Overrun_Percentage
674 , 0 Acceptable_Rate_Decrease
675 , 0 Acceptable_Rate_Increase
676 ,''x'' Planning_Time_Fence
677 , 0 Planning_Time_Fence_Days
678 ,''x'' Demand_Time_Fence
679 , 0 Demand_Time_Fence_Days
680 ,''x'' MRP_Planning_Method
681 ,''x'' Planning_Exception_Set
682 ,''x'' MRP_Forecast_Control
683 ,''x'' Repetitive_Planning
684 ,''x'' PLANNED_INV_POINT_FLAG
685 ,''0'' SUBSTITUTION_WINDOW_CODE
686 , 0 SUBSTITUTION_WINDOW_DAYS
687 ,''x'' CREATE_SUPPLY_FLAG
688 , 0 Repair_Lead_time
689 , 0 Repair_Yield
690 ,''x'' Pre_position_Point
691 , 0 Repair_Program ');
692 END IF;
693 RETURN NULL;
694 END C_MPS_SELFORMULA;
695
696 FUNCTION C_MPS_WHEREFORMULA RETURN VARCHAR2 IS
697 BEGIN
698 IF P_ALL_INFO = 1 OR P_MPS_INFO = 1 THEN
699 RETURN (' ');
700 ELSE
701 RETURN (' ');
702 END IF;
703 RETURN NULL;
704 END C_MPS_WHEREFORMULA;
705
706 FUNCTION C_MPS_FROMFORMULA RETURN VARCHAR2 IS
707 BEGIN
708 IF P_ALL_INFO = 1 OR P_MPS_INFO = 1 THEN
709 RETURN (' ');
710 ELSE
711 RETURN ('/*Do not select MPS/MRP Planning Group Options */');
712 END IF;
713 RETURN NULL;
714 END C_MPS_FROMFORMULA;
715
716 FUNCTION C_LEAD_SELFORMULA RETURN VARCHAR2 IS
717 BEGIN
718 RETURN (',msi.CUMULATIVE_TOTAL_LEAD_TIME Cumulative_Total_Lead_Time
719 ,msi.CUM_MANUFACTURING_LEAD_TIME Cum_Manufacturing_Lead_Time
720 ,msi.FIXED_LEAD_TIME Fixed_Lead_Time
721 ,msi.FULL_LEAD_TIME Processing_Lead_Time
722 ,msi.LEAD_TIME_LOT_SIZE Lead_Time_Lot_Size
723 ,msi.POSTPROCESSING_LEAD_TIME Postprocessing_lead_time
724 ,msi.PREPROCESSING_LEAD_TIME Preprocessing_lead_time
725 ,msi.VARIABLE_LEAD_TIME Variable_Lead_Time ');
726 END C_LEAD_SELFORMULA;
727
728 FUNCTION C_WIP_SELFORMULA RETURN VARCHAR2 IS
729 BEGIN
730 IF P_ALL_INFO = 1 OR P_WIP_INFO = 1 THEN
731 RETURN (',decode(msi.BUILD_IN_WIP_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Build_in_WIP
732 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.WIP_SUPPLY_TYPE' || ',''WIP_SUPPLY'') WIP_Supply_Type
733 ,' || P_WIP_LOC_FLEX || ' WIP_Supply_Locator
734 ,MSI.WIP_SUPPLY_SUBINVENTORY WIP_Supply_Subinventory
738 ,msi.OVERCOMPLETION_TOLERANCE_VALUE OVERCOMPLETION_TOLERANCE_VALUE');
735 ,msi.INVENTORY_CARRY_PENALTY INVENTORY_CARRY_PENALTY
736 ,msi.OPERATION_SLACK_PENALTY OPERATION_SLACK_PENALTY
737 ,decode(msi.OVERCOMPLETION_TOLERANCE_TYPE,1,''Percent'',2,''Amount'',NULL) OVERCOMPLETION_TOLERANCE_TYPE
739 ELSE
740 RETURN (',''x'' Build_in_WIP
741 ,''x'' WIP_Supply_Type
742 ,''x'' WIP_Supply_Locator
743 ,''x'' WIP_Supply_Subinventory
744 ,0 INVENTORY_CARRY_PENALTY
745 ,0 OPERATION_SLACK_PENALTY
746 ,''x'' OVERCOMPLETION_TOLERANCE_TYPE
747 ,0 OVERCOMPLETION_TOLERANCE_VALUE');
748 END IF;
749 RETURN NULL;
750 END C_WIP_SELFORMULA;
751
752 FUNCTION C_WIP_FROMFORMULA RETURN VARCHAR2 IS
753 BEGIN
754 IF P_ALL_INFO = 1 OR P_WIP_INFO = 1 THEN
755 RETURN ('mtl_item_locations LOC, ');
756 ELSE
757 RETURN ('/* Do not select WIP Group Options */');
758 END IF;
759 RETURN NULL;
760 END C_WIP_FROMFORMULA;
761
762 FUNCTION C_WIP_WHEREFORMULA RETURN VARCHAR2 IS
763 BEGIN
764 IF P_ALL_INFO = 1 OR P_WIP_INFO = 1 THEN
765 RETURN ('and msi.WIP_SUPPLY_LOCATOR_ID =
766 LOC.INVENTORY_LOCATION_ID(+)
767 and LOC.ORGANIZATION_ID(+) = ' || TO_CHAR(P_ORG_ID));
768 ELSE
769 RETURN (' ');
770 END IF;
771 RETURN NULL;
772 END C_WIP_WHEREFORMULA;
773
774 FUNCTION C_OE_SELFORMULA RETURN VARCHAR2 IS
775 BEGIN
776 IF P_ALL_INFO = 1 OR P_OE_INFO = 1 THEN
777 RETURN (',decode(msi.CUSTOMER_ORDER_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Customer_Ordered_Item
778 ,decode(msi.CUSTOMER_ORDER_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Customer_Orders_Enabled
779 ,decode(msi.INTERNAL_ORDER_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Internal_Ordered_Item
780 , decode( msi.INTERNAL_ORDER_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Internal_Orders_Enabled
781 ,decode( msi.SHIPPABLE_ITEM_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Shippable_Item
782 , decode( msi.SO_TRANSACTIONS_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) OE_transactable
783 ,INV_MEANING_SEL.c_org_name(msi.DEFAULT_SHIPPING_ORG) Default_Shipping_Organization
784 ,INV_MEANING_SEL.C_PICK_RULES(msi.PICKING_RULE_ID) Picking_Rule
785 ,decode( msi.PICK_COMPONENTS_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Pick_Components
786 ,decode( msi.SHIP_MODEL_COMPLETE_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Ship_Model_Complete
787 , decode( msi.REPLENISH_TO_ORDER_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Assemble_to_Order
788 -- , decode( msi.ATP_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Check_ATP
789 ,INV_MEANING_SEL.C_FND_LOOKUP(msi.ATP_FLAG,''ATP_FLAG'') CHECK_ATP
790 ,INV_MEANING_SEL.C_ATP_RULES( msi.ATP_RULE_ID) ATP_Rule
791 ,INV_MEANING_SEL.C_FND_LOOKUP(msi.ATP_COMPONENTS_FLAG, ''ATP_FLAG'') ATP_Components
792 , decode( nvl(msi.RETURNABLE_FLAG,''Y''),''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Returnable
793 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.RETURN_INSPECTION_REQUIREMENT' || ',''MTL_RETURN_INSPECTION'') RMA_Inspection_Status
794 ,msi.OVER_SHIPMENT_TOLERANCE OVER_SHIPMENT_TOLERANCE
795 ,msi.UNDER_SHIPMENT_TOLERANCE UNDER_SHIPMENT_TOLERANCE
796 ,msi.OVER_RETURN_TOLERANCE OVER_RETURN_TOLERANCE
797 ,msi.UNDER_RETURN_TOLERANCE UNDER_RETURN_TOLERANCE
798 ,INV_MEANING_SEL.C_OE_LOOKUP(msi.DEFAULT_SO_SOURCE_TYPE,''SOURCE_TYPE'') DEFAULT_SO_SOURCE_TYPE
799 ,msi.CHARGE_PERIODICITY_code Charge_Periodicity ');
800 ELSE
801 RETURN (',''x'' Customer_Ordered_Item
802 ,''x'' Customer_Orders_Enabled
803 ,''x'' Internal_Ordered_Item
804 ,''x'' Internal_Orders_Enabled
805 ,''x'' Shippable_Item
806 ,''x'' OE_transactable
807 ,''x'' Default_Shipping_Organization
808 ,''x'' Picking_Rule
809 ,''x'' Pick_Components
810 ,''x'' Ship_Model_Complete
811 ,''x'' Assemble_to_Order
812 ,''x'' Check_ATP
813 ,''x'' ATP_Rule
814 ,''x'' ATP_Components
815 ,''x'' Returnable
816 ,''x'' RMA_Inspection_Status
817 ,0 OVER_SHIPMENT_TOLERANCE
818 ,0 UNDER_SHIPMENT_TOLERANCE
819 ,0 OVER_RETURN_TOLERANCE
820 ,0 UNDER_RETURN_TOLERANCE
821 ,''x'' DEFAULT_SO_SOURCE_TYPE
822 , 0 Charge_Periodicity ');
823 END IF;
824 RETURN NULL;
825 END C_OE_SELFORMULA;
826
827 FUNCTION C_OE_FROMFORMULA RETURN VARCHAR2 IS
828 BEGIN
829 IF P_ALL_INFO = 1 OR P_OE_INFO = 1 THEN
830 RETURN (' ');
831 ELSE
832 RETURN ('/* Do not select OE Group Option */');
833 END IF;
834 RETURN NULL;
835 END C_OE_FROMFORMULA;
836
837 FUNCTION C_OE_WHEREFORMULA RETURN VARCHAR2 IS
838 BEGIN
839 IF P_ALL_INFO = 1 OR P_OE_INFO = 1 THEN
840 RETURN (' ');
841 ELSE
842 RETURN (' ');
843 END IF;
844 RETURN NULL;
845 END C_OE_WHEREFORMULA;
846
847 FUNCTION C_INVC_SELFORMULA RETURN VARCHAR2 IS
848 BEGIN
849 IF P_ALL_INFO = 1 OR P_INVC_INFO = 1 THEN
850 RETURN (',decode(msi.INVOICEABLE_ITEM_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Invoiceable_Item
851 ,decode(msi.INVOICE_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Invoice_enabled
852 ,MSI.tax_code Tax_Code
853 ,' || P_INVC_ACCT_FLEX || ' Sales_Account
854 ,INV_MEANING_SEL.c_ra_terms(msi.PAYMENT_TERMS_ID) Payment_terms
855 ,INV_MEANING_SEL.c_ra_rules(msi.ACCOUNTING_RULE_ID) Accounting_Rule
856 ,INV_MEANING_SEL.c_ra_rules(msi.INVOICING_RULE_ID) Invoicing_Rule ');
857 ELSE
858 RETURN (',''x'' Invoiceable_Item
859 ,''x'' Invoice_enabled
860 ,''x'' Tax_Code
861 ,''x'' Sales_Account
862 ,''x'' Payment_terms
863 ,''x'' Accounting_Rule
864 ,''x'' Invoicing_Rule ');
865 END IF;
866 RETURN NULL;
867 END C_INVC_SELFORMULA;
868
869 FUNCTION C_INVC_FROMFORMULA RETURN VARCHAR2 IS
870 BEGIN
871 IF P_ALL_INFO = 1 OR P_INVC_INFO = 1 THEN
872 RETURN (' gl_code_combinations IAF, ');
873 ELSE
874 RETURN ('/* Do not select Invoice Group Options */');
875 END IF;
876 RETURN NULL;
877 END C_INVC_FROMFORMULA;
878
879 FUNCTION C_INVC_WHEREFORMULA RETURN VARCHAR2 IS
880 BEGIN
881 IF P_ALL_INFO = 1 OR P_INVC_INFO = 1 THEN
882 RETURN ('and msi.SALES_ACCOUNT = IAF.code_combination_id(+) ');
883 ELSE
884 RETURN (' ');
885 END IF;
886 RETURN NULL;
887 END C_INVC_WHEREFORMULA;
888
889 FUNCTION C_CAT_SET_NAMEFORMULA RETURN VARCHAR2 IS
890 BEGIN
891 DECLARE
892 NAME VARCHAR2(30);
893 SET_ID NUMBER;
894 BEGIN
895 IF P_CAT_SET_ID IS NULL THEN
896 RETURN ('');
897 ELSE
898 SET_ID := P_CAT_SET_ID;
899 SELECT
900 CATEGORY_SET_NAME
901 INTO NAME
902 FROM
903 MTL_CATEGORY_SETS_VL
904 WHERE CATEGORY_SET_ID = SET_ID;
905 RETURN (NAME);
906 END IF;
907 EXCEPTION
908 WHEN NO_DATA_FOUND THEN
909 RETURN ('');
910 END;
911 RETURN NULL;
912 END C_CAT_SET_NAMEFORMULA;
913
914 FUNCTION C_ITEM_PADFORMULA(C_ITEM_FIELD IN VARCHAR2
915 ,C_ITEM_PAD IN VARCHAR2) RETURN VARCHAR2 IS
916 BEGIN
917 /*SRW.REFERENCE(C_ITEM_FIELD)*/NULL;
918 RETURN (C_ITEM_PAD);
919 END C_ITEM_PADFORMULA;
920
921 FUNCTION C_PHYS_FROMFORMULA RETURN VARCHAR2 IS
922 BEGIN
923 IF P_ALL_INFO = 1 OR P_PHYS_INFO = 1 THEN
924 RETURN (' ');
925 ELSE
926 RETURN ('/* Do not select OE Group Option */');
927 END IF;
928 RETURN NULL;
929 END C_PHYS_FROMFORMULA;
930
931 FUNCTION C_PHYS_WHEREFORMULA RETURN VARCHAR2 IS
932 BEGIN
933 IF P_ALL_INFO = 1 OR P_PHYS_INFO = 1 THEN
934 RETURN (' ');
935 ELSE
936 RETURN (' ');
937 END IF;
938 RETURN NULL;
939 END C_PHYS_WHEREFORMULA;
940
941 FUNCTION C_CAT_PAD2FORMULA(C_CAT_FIELD2 IN VARCHAR2
942 ,C_CAT_PAD2 IN VARCHAR2) RETURN VARCHAR2 IS
943 BEGIN
944 /*SRW.REFERENCE(C_CAT_FIELD2)*/NULL;
945 RETURN (C_CAT_PAD2);
946 END C_CAT_PAD2FORMULA;
947
948 FUNCTION C_SERVICE_INFO_SELFORMULA RETURN VARCHAR2 IS
949 BEGIN
950 IF P_SERVICE_INFO = 1 OR P_ALL_INFO = 1 THEN
951 RETURN (',INV_MEANING_SEL.C_COVERAGE_SCHEDULE(MSI.COVERAGE_SCHEDULE_ID) Coverage_schedule_id
952 ,MSI.vendor_warranty_flag vendor_warranty_flag
953 ,MSI.service_starting_delay service_starting_delay
954 ,MSI.service_duration_period_code service_duration_period_code
955 ,MSI.service_duration service_duration
956 ,MSI.material_billable_flag Material_billable_flag
957 ,MSI.service_item_flag service_item_flag
958 ,decode(msi.serviceable_product_flag,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) serviceable_product_flag
959 ,decode(msi.Asset_Creation_Code ,''1'',' || '''' || P_YES_VALUE || '''' || ',''0'',' || '''' || P_NO_VALUE || '''' || ',NULL) Asset_Creation_Code
960 ,decode(msi.COMMS_NL_TRACKABLE_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) COMMS_NL_TRACKABLE_FLAG
961 ,decode(msi.serv_billing_enabled_flag,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) SERV_BILLING_ENABLED_FLAG
962 ,msi.serv_importance_level serv_importance_level
966 ,INV_MEANING_SEL.c_fnd_lookup_vl(' || 'msi.CONTRACT_ITEM_TYPE_CODE' || ',''OKB_CONTRACT_ITEM_TYPE'') CONTRACT_ITEM_TYPE_CODE
963 ,decode(msi.serv_req_enabled_code,''E'',''Enabled'',''D'',''Disabled'',''I'',''Inactive'',NULL) serv_req_enabled_code
964 ,INV_MEANING_SEL.c_fnd_lookup_vl(' || 'msi.IB_ITEM_INSTANCE_CLASS' || ',''CSI_ITEM_CLASS'') Ib_Item_Instance_Class
965 ,decode(msi.COMMS_ACTIVATION_REQD_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',' || '''' || P_NO_VALUE || '''' || ') COMMS_ACTIVATION_REQD_FLAG
967 ,INV_MEANING_SEL.c_fnd_lookup_vl(' || 'msi.RECOVERED_PART_DISP_CODE' || ',''CSP_RECOVERED_PART_DISP_CODE'') RECOVERED_PART_DISP_CODE
968 ,decode(msi.DEFECT_TRACKING_ON_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',' || '''' || P_NO_VALUE || '''' || ') DEFECT_TRACKING_ON_FLAG');
969 ELSE
970 RETURN (' ,''x'' Coverage_Schedule_id,
971 ''x'' Vendor_Warranty_Flag,
972 0 Service_Starting_Delay,
973 ''x'' Service_Duration_Period_Code,
974 0 Service_Duration,
975 ''x'' Material_billable_flag,
976 ''x'' Service_Item_Flag,
977 ''x'' Serviceable_Product_Flag,
978 ''x'' Asset_Creation_Code,
979 ''x'' COMMS_NL_TRACKABLE_FLAG,
980 ''x'' serv_billing_enabled_flag,
981 0 serv_importance_level,
982 ''x'' serv_req_enabled_code ,
983 ''x'' Ib_Item_Instance_class ,
984 ''x'' COMMS_ACTIVATION_REQD_FLAG,
985 ''x'' CONTRACT_ITEM_TYPE_CODE,
986 ''x'' RECOVERED_PART_DISP_CODE,
987 ''x'' DEFECT_TRACKING_ON_FLAG ');
988 END IF;
989 RETURN NULL;
990 END C_SERVICE_INFO_SELFORMULA;
991
992 FUNCTION C_SERVICE_INFO_FROMFORMULA RETURN NUMBER IS
993 BEGIN
994 IF P_SERVICE_INFO = 1 OR P_ALL_INFO = 1 THEN
995 RETURN (NULL);
996 ELSE
997 RETURN (NULL);
998 END IF;
999 RETURN NULL;
1000 END C_SERVICE_INFO_FROMFORMULA;
1001
1002 FUNCTION C_SERVICE_INFO_WHEREFORMULA RETURN NUMBER IS
1003 BEGIN
1004 IF P_SERVICE_INFO = 1 OR P_ALL_INFO = 1 THEN
1005 RETURN (NULL);
1006 ELSE
1007 RETURN (NULL);
1008 END IF;
1009 RETURN NULL;
1010 END C_SERVICE_INFO_WHEREFORMULA;
1011
1012 FUNCTION RESOLVE_VENDOR_WARRANTY(VENDOR_WARRANTY_FLAG IN VARCHAR2) RETURN VARCHAR2 IS
1013 TEMP VARCHAR2(80);
1014 BEGIN
1015 IF VENDOR_WARRANTY_FLAG = 'Y' THEN
1016 SELECT
1017 FLU.MEANING
1018 INTO TEMP
1019 FROM
1020 FND_LOOKUPS FLU
1021 WHERE LOOKUP_TYPE = 'YES_NO'
1022 AND LOOKUP_CODE = 'Y';
1023 RETURN (TEMP);
1024 ELSIF VENDOR_WARRANTY_FLAG = 'N' THEN
1025 SELECT
1026 FLU.MEANING
1027 INTO TEMP
1028 FROM
1029 FND_LOOKUPS FLU
1030 WHERE LOOKUP_TYPE = 'YES_NO'
1031 AND LOOKUP_CODE = 'N';
1032 RETURN (TEMP);
1033 ELSE
1034 RETURN (' ');
1035 END IF;
1036 RETURN NULL;
1037 END RESOLVE_VENDOR_WARRANTY;
1038
1039 FUNCTION RESOLVE_SERVICE_ITEM_FLAG(SERVICE_ITEM_FLAG IN VARCHAR2) RETURN VARCHAR2 IS
1040 TEMP VARCHAR2(80);
1041 BEGIN
1042 IF SERVICE_ITEM_FLAG = 'Y' THEN
1043 SELECT
1044 FLU.MEANING
1045 INTO TEMP
1046 FROM
1047 FND_LOOKUPS FLU
1048 WHERE LOOKUP_TYPE = 'YES_NO'
1049 AND LOOKUP_CODE = 'Y';
1050 RETURN (TEMP);
1051 ELSIF SERVICE_ITEM_FLAG = 'N' THEN
1052 SELECT
1053 FLU.MEANING
1054 INTO TEMP
1055 FROM
1056 FND_LOOKUPS FLU
1057 WHERE LOOKUP_TYPE = 'YES_NO'
1058 AND LOOKUP_CODE = 'N';
1059 RETURN (TEMP);
1060 ELSE
1061 RETURN (' ');
1062 END IF;
1063 RETURN NULL;
1064 END RESOLVE_SERVICE_ITEM_FLAG;
1065
1066 FUNCTION RESOLVE_SERVICEABLE_PRODUCT(SERVICEABLE_PRODUCT_FLAG IN VARCHAR2) RETURN VARCHAR2 IS
1067 TEMP VARCHAR2(80);
1068 BEGIN
1069 IF SERVICEABLE_PRODUCT_FLAG = 'Y' THEN
1070 SELECT
1071 FLU.MEANING
1072 INTO TEMP
1073 FROM
1074 FND_LOOKUPS FLU
1075 WHERE LOOKUP_TYPE = 'YES_NO'
1076 AND LOOKUP_CODE = 'Y';
1077 RETURN (TEMP);
1078 ELSIF SERVICEABLE_PRODUCT_FLAG = 'N' THEN
1079 SELECT
1080 FLU.MEANING
1081 INTO TEMP
1082 FROM
1083 FND_LOOKUPS FLU
1084 WHERE LOOKUP_TYPE = 'YES_NO'
1085 AND LOOKUP_CODE = 'N';
1086 RETURN (TEMP);
1087 ELSE
1088 RETURN (' ');
1089 END IF;
1090 RETURN NULL;
1091 END RESOLVE_SERVICEABLE_PRODUCT;
1092
1093 FUNCTION RESOLVE_MATERIAL_BILLABLE(MATERIAL_BILLABLE_FLAG IN VARCHAR2) RETURN VARCHAR2 IS
1094 TEMP VARCHAR2(80);
1095 BEGIN
1096 IF MATERIAL_BILLABLE_FLAG = 'E' THEN
1097 SELECT
1098 CLU.MEANING
1099 INTO TEMP
1100 FROM
1101 CS_LOOKUPS CLU
1102 WHERE LOOKUP_TYPE = 'MTL_SERVICE_BILLABLE_FLAG'
1103 AND LOOKUP_CODE = 'E';
1104 RETURN (TEMP);
1105 ELSIF MATERIAL_BILLABLE_FLAG = 'L' THEN
1106 SELECT
1107 CLU.MEANING
1108 INTO TEMP
1109 FROM
1110 CS_LOOKUPS CLU
1111 WHERE LOOKUP_TYPE = 'MTL_SERVICE_BILLABLE_FLAG'
1112 AND LOOKUP_CODE = 'L';
1113 RETURN (TEMP);
1114 ELSIF MATERIAL_BILLABLE_FLAG = 'M' THEN
1115 SELECT
1116 CLU.MEANING
1117 INTO TEMP
1118 FROM
1119 CS_LOOKUPS CLU
1123 ELSE
1120 WHERE LOOKUP_TYPE = 'MTL_SERVICE_BILLABLE_FLAG'
1121 AND LOOKUP_CODE = 'M';
1122 RETURN (TEMP);
1124 RETURN (' ');
1125 END IF;
1126 RETURN NULL;
1127 END RESOLVE_MATERIAL_BILLABLE;
1128
1129 FUNCTION C_WEB_SELFORMULA RETURN VARCHAR2 IS
1130 BEGIN
1131 IF P_ALL_INFO = 1 OR P_WEB_INFO = 1 THEN
1132 RETURN (',MSI.WEB_STATUS Web_Status
1133 ,decode(msi.BACK_ORDERABLE_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Back_Orderable
1134 ,decode(msi.ORDERABLE_ON_WEB_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Web_Orderable
1135 ,msi.Minimum_License_Quantity Minimum_License_Quantity ');
1136 END IF;
1137 RETURN (',msi.web_status web_status ,msi.segment2 Back_Orderable,msi.segment2 Web_Orderable, msi.segment2 Minimum_License_quantity');
1138 END C_WEB_SELFORMULA;
1139
1140 FUNCTION C_EAM_FROMFORMULA RETURN CHAR IS
1141 BEGIN
1142 IF P_EAM_INFO = 1 OR P_ALL_INFO = 1 THEN
1143 RETURN (' ');
1144 ELSE
1145 RETURN ('/* Do not select Asset Group Options */');
1146 END IF;
1147 RETURN NULL;
1148 END C_EAM_FROMFORMULA;
1149
1150 FUNCTION C_EAM_SELFORMULA RETURN CHAR IS
1151 BEGIN
1152 IF P_EAM_INFO = 1 OR P_ALL_INFO = 1 THEN
1153 RETURN (',INV_MEANING_SEL.c_mfg_lookup(' || 'msi.EAM_ITEM_TYPE' || ',''MTL_EAM_ITEM_TYPE'') Eam_Item_Type
1154 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.EAM_ACTIVITY_TYPE_CODE' || ',''MTL_EAM_ACTIVITY_TYPE'') Eam_Activity_Type_Code
1155 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.EAM_ACTIVITY_CAUSE_CODE' || ',''MTL_EAM_ACTIVITY_CAUSE'') Eam_Activity_Cause_Code
1156 ,decode(msi.EAM_ACT_NOTIFICATION_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Eam_Act_Notification_Flag
1157 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.EAM_ACT_SHUTDOWN_STATUS' || ',''BOM_EAM_SHUTDOWN_TYPE'') Eam_Act_Shutdown_Status
1158 ,INV_MEANING_SEL.c_fnd_lookup_vl(' || 'msi.EAM_ACTIVITY_SOURCE_CODE' || ',''MTL_EAM_ACTIVITY_SOURCE'') Eam_Activity_Source_Code
1159 ');
1160 ELSE
1161 RETURN (', ''x'' Eam_Item_Type
1162 , ''x'' Eam_Activity_Type_Code
1163 , ''x'' Eam_Activity_Cause_Code
1164 , ''x'' Eam_Act_Notification_Flag
1165 , ''x'' Eam_Act_Shutdown_Status
1166 , ''x'' Eam_Activity_Source_Code');
1167 END IF;
1168 RETURN NULL;
1169 END C_EAM_SELFORMULA;
1170
1171 FUNCTION C_EAM_WHEREFORMULA RETURN CHAR IS
1172 BEGIN
1173 IF P_EAM_INFO = 1 OR P_ALL_INFO = 1 THEN
1174 RETURN (' ');
1175 ELSE
1176 RETURN (' ');
1177 END IF;
1178 RETURN NULL;
1179 END C_EAM_WHEREFORMULA;
1180
1181 FUNCTION C_OPM_FROMFORMULA RETURN VARCHAR2 IS
1182 BEGIN
1183 IF P_ALL_INFO = 1 OR P_OPM_INFO = 1 THEN
1184 RETURN (' ');
1185 ELSE
1186 RETURN ('/* Do not select OE Group Option */');
1187 END IF;
1188 END C_OPM_FROMFORMULA;
1189
1190 FUNCTION C_OPM_SELFORMULA RETURN VARCHAR2 IS
1191 BEGIN
1192 IF P_ALL_INFO = 1 OR P_OPM_INFO = 1 THEN
1193 RETURN ('
1194 ,decode(msi.PROCESS_QUALITY_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Quality_Enabled
1195 ,decode(msi.PROCESS_COSTING_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Process_Costing_Enabled
1196 ,decode(msi.RECIPE_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Recipe_Enabled
1197 ,decode(msi.HAZARDOUS_MATERIAL_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Hazardious_Material
1198 ,msi.CAS_NUMBER CAS_Number
1199 ,decode(msi.PROCESS_EXECUTION_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Process_Execution_Enabled
1200 ,msi.PROCESS_SUPPLY_SUBINVENTORY Supply_Subinventory
1201 ,msi.PROCESS_SUPPLY_LOCATOR_ID Supply_Locator
1202 ,msi.PROCESS_YIELD_SUBINVENTORY Yield_Subinventory
1203 ,msi.PROCESS_YIELD_LOCATOR_ID Yield_Locator');
1204 ELSE
1205 RETURN (',''x'' Quality_Enabled
1206 ,''x'' Process_Costing_Enabled
1207 ,''x'' Recipe_Enabled
1208 ,''x'' Hazardious_Material
1209 ,''x'' CAS_Number
1210 ,''x'' Process_Execution_Enabled
1211 ,''x'' Supply_Subinventory
1212 , 0 Supply_Locator
1213 ,''x'' Yield_Subinventory
1214 , 0 Yield_Locator ');
1215 END IF;
1216 RETURN NULL;
1217 END C_OPM_SELFORMULA;
1218
1219 FUNCTION C_OPM_WHEREFORMULA RETURN VARCHAR2 IS
1220 BEGIN
1221 IF P_ALL_INFO = 1 OR P_OPM_INFO = 1 THEN
1222 RETURN (' ');
1223 ELSE
1224 RETURN (' ');
1225 END IF;
1226 END C_OPM_WHEREFORMULA;
1227
1228 FUNCTION C_INV_WHEREFORMULA RETURN VARCHAR2 IS
1229 BEGIN
1230 IF P_INV_INFO = 1 OR P_ALL_INFO = 1 THEN
1231 RETURN (' ');
1232 ELSE
1233 RETURN (' ');
1234 END IF;
1235 RETURN NULL;
1236 END C_INV_WHEREFORMULA;
1237
1238 FUNCTION C_INV_SELFORMULA RETURN VARCHAR2 IS
1239 BEGIN
1240 IF P_INV_INFO = 1 OR P_ALL_INFO = 1 THEN
1241 RETURN (',decode(msi.INVENTORY_ITEM_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Inventory_Item
1245 ,MSI.AUTO_LOT_ALPHA_PREFIX Starting_Lot_Prefix
1242 ,decode(msi.STOCK_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Stockable
1243 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.LOT_CONTROL_CODE' || ',''MTL_LOT_CONTROL'') Lot_Control
1244 ,MSI.START_AUTO_LOT_NUMBER Starting_Lot_Number
1246 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.SERIAL_NUMBER_CONTROL_CODE' || ',''MTL_SERIAL_NUMBER'') Serial_Number_Control
1247 ,MSI.START_AUTO_SERIAL_NUMBER Starting_Serial_Number
1248 ,MSI.AUTO_SERIAL_ALPHA_PREFIX Starting_Serial_Prefix
1249 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.SHELF_LIFE_CODE' || ',''MTL_SHELF_LIFE'') Shelf_Life_Control
1250 ,MSI.SHELF_LIFE_DAYS Shelf_Life_Days
1251 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.RESTRICT_SUBINVENTORIES_CODE' || ',''MTL_SUBINVENTORY_RESTRICTIONS'') Subinventory_Restrictions
1252 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.LOCATION_CONTROL_CODE' || ',''MTL_LOCATION_CONTROL'') Stock_Locator_Control
1253 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.RESTRICT_LOCATORS_CODE' || ',''MTL_LOCATOR_RESTRICTIONS'') Locator_Restrictions
1254 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.RESERVABLE_TYPE' || ',''MTL_RESERVATION_CONTROL'') Reservation_Control
1255 ,decode(msi.CYCLE_COUNT_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Cycle_Count_Enabled
1256 ,MSI.NEGATIVE_MEASUREMENT_ERROR Neg_Measurement_Error
1257 ,MSI.POSITIVE_MEASUREMENT_ERROR Pos_Measurement_Error
1258 ,decode(msi.MTL_TRANSACTIONS_ENABLED_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Transactable
1259 ,INV_MEANING_SEL.c_mfg_lookup(' || 'msi.REVISION_QTY_CONTROL_CODE' || ',''MTL_ENG_QUANTITY'') Revision_Control
1260 ,decode(msi.CHECK_SHORTAGES_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Check_Shortages
1261 ,decode(msi.Lot_Status_Enabled,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Lot_Status_Enabled
1262 ,INV_MEANING_SEL.C_LOT_LOOKUP(msi.DEFAULT_LOT_STATUS_ID) DEFAULT_LOT_STATUS_ID
1263 ,decode(msi.Serial_Status_Enabled,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Serial_Status_Enabled
1264 ,INV_MEANING_SEL.C_SERIAL_LOOKUP(msi.DEFAULT_SERIAL_STATUS_ID) DEFAULT_SERIAL_STATUS_ID
1265 ,decode(msi.Lot_Split_Enabled,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Lot_Split_Enabled
1266 ,decode(msi.Lot_Merge_Enabled,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Lot_Merge_Enabled
1267 ,decode(msi.Bulk_Picked_Flag,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Bulk_Picked
1268 ,decode(msi.lot_translate_enabled,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) lot_translate_enabled
1269 ,decode(msi.lot_substitution_enabled,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) lot_substitution_enabled
1270 ,msi.MATURITY_DAYS Maturity_Days
1271 ,msi.HOLD_DAYS Hold_Days
1272 ,msi.RETEST_INTERVAL Retest_Interval
1273 ,msi.EXPIRATION_ACTION_CODE Expiration_Action_Code
1274 ,decode(msi.GRADE_CONTROL_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Grade_Control_Flag
1275 ,msi.DEFAULT_GRADE Default_Grade
1276 ,decode(msi.LOT_DIVISIBLE_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Lot_Divisible_Flag
1277 ,decode(msi.CHILD_LOT_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Child_Lot_Flag
1278 ,INV_MEANING_SEL.C_LOOKUPS(' || 'msi.PARENT_CHILD_GENERATION_FLAG' || ',''INV_PARENT_CHILD_GENERATION'') Parent_Child_Generation_Flag
1279 ,msi.CHILD_LOT_PREFIX Child_Lot_Prefix
1280 ,msi.CHILD_LOT_STARTING_NUMBER Child_Lot_Starting_Number
1281 ,decode(msi.CHILD_LOT_VALIDATION_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Child_Lot_Validation_Flag
1282 ,decode(msi.COPY_LOT_ATTRIBUTE_FLAG,''Y'',' || '''' || P_YES_VALUE || '''' || ',''N'',' || '''' || P_NO_VALUE || '''' || ',NULL) Copy_Lot_Attribute_Flag
1283 ,msi.EXPIRATION_ACTION_INTERVAL Expiration_Action_Interval
1284 ');
1285 ELSE
1286 RETURN (',''z'' Inventory_Item
1287 ,''x'' Stockable
1288 ,''x'' Lot_Control
1292 ,''x'' Starting_Serial_Number
1289 ,''x'' Starting_Lot_Number
1290 ,''x'' Starting_Lot_Prefix
1291 ,''x'' Serial_Number_Control
1293 ,''x'' Starting_Serial_Prefix
1294 ,''x'' Shelf_Life_Control
1295 , 0 Shelf_Life_Days
1296 ,''x'' Subinventory_Restrictions
1297 ,''x'' Stock_Locator_Control
1298 ,''x'' Locator_Restrictions
1299 ,''x'' Reservation_Control
1300 ,''x'' Cycle_Count_Enabled
1301 , 0 Neg_Measurement_Error
1302 , 0 Pos_Measurement_Error
1303 ,''x'' Transactable
1304 ,''x'' Revision_Control
1305 ,''x'' Check_Shortages
1306 ,''x'' Lot_Status_Enabled
1307 ,''x'' DEFAULT_LOT_STATUS_ID
1308 ,''x'' Serial_Status_Enabled
1309 ,''x'' DEFAULT_SERIAL_STATUS_ID
1310 ,''x'' Lot_Split_Enabled
1311 ,''x'' Lot_Merge_Enabled
1312 ,''x'' Bulk_Picked
1313 ,''x'' lot_translate_enabled
1314 ,''x'' lot_substitution_enabled
1315 , 0 Maturity_Days
1316 , 0 Hold_Days
1317 , 0 Retest_Interval
1318 ,''x'' Expiration_Action_Code
1319 ,''x'' Grade_Control_Flag
1320 ,''x'' Default_Grade
1321 ,''x'' Lot_Divisible_Flag
1322 ,''x'' Child_Lot_Flag
1323 ,''x'' Parent_Child_Generation_Flag
1324 ,''x'' Child_Lot_Prefix
1325 , 0 Child_Lot_Starting_Number
1326 ,''x'' Child_Lot_Validation_Flag
1327 ,''x'' Copy_Lot_Attribute_Flag
1328 , 0 Expiration_Action_Interval
1329
1330 ');
1331 END IF;
1332 RETURN NULL;
1333 END C_INV_SELFORMULA;
1334
1335 FUNCTION C_INV_FROMFORMULA RETURN VARCHAR2 IS
1336 BEGIN
1337 IF P_INV_INFO = 1 OR P_ALL_INFO = 1 THEN
1338 RETURN (' ');
1339 ELSE
1340 RETURN ('/* Do not select Inventory Group Options */');
1341 END IF;
1342 RETURN NULL;
1343 END C_INV_FROMFORMULA;
1344
1345 FUNCTION C_INVC_SELECT(C_INVC_ACCT_FIELD VARCHAR2) RETURN VARCHAR2
1346 IS
1347 BEGIN
1348 IF P_BREAK_ID = 1 THEN
1349 RETURN(C_INVC_ACCT_FIELD);
1350 ELSE
1351 RETURN(NULL);
1352 END IF;
1353 END C_INVC_SELECT;
1354
1355 FUNCTION C_WIP_SELECT(C_WIP_LOC_FIELD VARCHAR2) RETURN VARCHAR2
1356 IS
1357 BEGIN
1358 IF (P_ALL_INFO = 1 OR P_WIP_INFO = 1 ) THEN
1359 RETURN( C_WIP_LOC_FIELD);
1360 ELSE
1361 RETURN(NULL);
1362 END IF;
1363 END C_WIP_SELECT;
1364
1365 FUNCTION C_ENC_SELECT(C_ENC_ACCT_FIELD VARCHAR2) RETURN VARCHAR2
1366 IS
1367 BEGIN
1368 IF (P_PO_INFO = 1 OR P_ALL_INFO = 1 ) THEN
1369 RETURN( C_ENC_ACCT_FIELD);
1370 ELSE
1371 RETURN(NULL);
1372 END IF;
1373 END C_ENC_SELECT;
1374
1375 FUNCTION C_EXP_SELECT(C_EXP_ACCT_FIELD VARCHAR2) RETURN VARCHAR2
1376 IS
1377 BEGIN
1378 IF (P_PO_INFO = 1 OR P_ALL_INFO = 1 ) THEN
1379 RETURN( C_EXP_ACCT_FIELD);
1380 ELSE
1381 RETURN(NULL);
1382 END IF;
1383 END C_EXP_SELECT;
1384
1385 FUNCTION C_ACAT_SELECT(C_ACAT_FIELD VARCHAR2) RETURN VARCHAR2
1386 IS
1387 BEGIN
1388 IF (P_PO_INFO = 1 OR P_ALL_INFO = 1 ) THEN
1389 RETURN( C_ACAT_FIELD);
1390 ELSE
1391 RETURN(NULL);
1392 END IF;
1393 END C_ACAT_SELECT;
1394
1395 FUNCTION C_CST_SELECT(C_CST_ACCT_FIELD VARCHAR2) RETURN VARCHAR2
1396 IS
1397 BEGIN
1398 IF (P_CST_INFO = 1 OR P_ALL_INFO = 1 ) THEN
1399 RETURN( C_CST_ACCT_FIELD);
1400 ELSE
1401 RETURN(NULL);
1402 END IF;
1403 END C_CST_SELECT;
1404
1405 END INV_INVIRITD_XMLP_PKG;
1406