4 -- Define Record Type for Affected Item Instance Record --
1 PACKAGE AHL_FMP_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVFMPS.pls 120.6.12020000.3 2013/04/01 01:45:17 sracha ship $ */
3
5 TYPE MR_ITEM_INSTANCE_REC_TYPE IS RECORD (
6 ITEM_NUMBER VARCHAR2(40),
7 SERIAL_NUMBER VARCHAR2(30),
8 LOCATION VARCHAR2(4000),
9 STATUS VARCHAR2(80),
10 OWNER VARCHAR2(360),
11 CONDITION VARCHAR2(240),
12 UNIT_NAME VARCHAR2(80),
13 ITEM_INSTANCE_ID NUMBER,
14 INVENTORY_ITEM_ID NUMBER,
15 MR_EFFECTIVITY_ID NUMBER,
16 UC_HEADER_ID NUMBER,
17 -- Added terminate_flag for SB Effect enhancements.
18 TERMINATE_FLAG VARCHAR2(1)
19 );
20
21 -- Define Table Type for Affected Item Instances --
22 TYPE MR_ITEM_INSTANCE_TBL_TYPE IS TABLE OF MR_ITEM_INSTANCE_REC_TYPE INDEX BY BINARY_INTEGER;
23
24 -- Define Record Type for Applicable MR Record --
25 TYPE APPLICABLE_MR_REC_TYPE IS RECORD (
26 MR_HEADER_ID NUMBER,
27 MR_EFFECTIVITY_ID NUMBER,
28 ITEM_INSTANCE_ID NUMBER,
29 -- PARENT_ITEM_INSTANCE_ID NUMBER, --may be deleted
30 REPETITIVE_FLAG VARCHAR2(1),
31 SHOW_REPETITIVE_CODE VARCHAR2(4),
32 PRECEDING_MR_HEADER_ID NUMBER,
33 COPY_ACCOMPLISHMENT_FLAG VARCHAR2(1),
34 IMPLEMENT_STATUS_CODE VARCHAR2(30),
35 DESCENDENT_COUNT NUMBER,
36 -- added for SB Effect enhancements
37 RELATIONSHIP_CODE VARCHAR2(30),
38 START_MR_HEADER_ID NUMBER,
39 SEQUENCE_NUMBER NUMBER
40 );
41
42 -- Define Table Type for Applicable MRs --
43 TYPE APPLICABLE_MR_TBL_TYPE IS TABLE OF APPLICABLE_MR_REC_TYPE INDEX BY BINARY_INTEGER;
44
45 -- Define Record Type for Applicable Activities for PM --
46 TYPE APPLICABLE_ACTIVITIES_REC_TYPE IS RECORD
47 (
48 MR_HEADER_ID NUMBER,
49 PROGRAM_MR_HEADER_ID NUMBER,
50 SERVICE_LINE_ID NUMBER,
51 MR_EFFECTIVITY_ID NUMBER,
52 ITEM_INSTANCE_ID NUMBER,
53 REPETITIVE_FLAG VARCHAR2(1),
54 WHICHEVER_FIRST_CODE VARCHAR2(30),
55 SHOW_REPETITIVE_CODE VARCHAR2(4),
56 IMPLEMENT_STATUS_CODE VARCHAR2(30),
57 ACT_SCHEDULE_EXISTS VARCHAR2(1)
58 );
59
60 -- Define Table Type for Applicable Activities for PM --
61 TYPE APPLICABLE_ACTIVITIES_TBL_TYPE IS TABLE OF APPLICABLE_ACTIVITIES_REC_TYPE
62 INDEX BY BINARY_INTEGER;
63
64 -- Define Record Type for Applicable Programs for PM --
65 -- R12: replaced okc_k_headers_b with okc_k_headers_all_b for MOAC (ref bug# 4337173).
66 TYPE applicable_programs_rec_type IS RECORD
67 (
68 contract_id NUMBER,
69 contract_number OKC_K_HEADERS_ALL_B.contract_number%TYPE,
70 contract_number_modifier OKC_K_HEADERS_ALL_B.contract_number_modifier%TYPE,
71 sts_code OKC_K_HEADERS_ALL_B.sts_code%TYPE,
72 service_line_id NUMBER,
73 service_name VARCHAR2(300), --OKX_SYSTEM_ITEMS_V.NAME%TYPE
77 coverage_term_description OKC_K_LINES_V.item_description%TYPE,
74 service_description VARCHAR2(300), --OKX_SYSTEM_ITEMS_V.DESCRIPTION%TYPE
75 coverage_term_line_id NUMBER,
76 coverage_term_name OKC_K_LINES_V.name%TYPE,
78 coverage_type_code Oks_Cov_Types_B.code%TYPE,
79 coverage_type_meaning Oks_Cov_Types_TL.meaning%TYPE,
80 coverage_type_imp_level Oks_Cov_Types_B.importance_level%TYPE,
81 service_start_date DATE,
82 service_end_date DATE,
83 warranty_flag VARCHAR2(1),
84 eligible_for_entitlement VARCHAR2(1),
85 exp_reaction_time DATE,
86 exp_resolution_time DATE,
87 status_code VARCHAR2(1),
88 status_text VARCHAR2(1995),
89 date_terminated DATE,
90 pm_program_id VARCHAR2(40),
91 pm_schedule_exists VARCHAR2(450),
92 mr_effectivity_id NUMBER
93 );
94
95 TYPE applicable_programs_tbl_type IS TABLE OF applicable_programs_rec_type
96 INDEX BY BINARY_INTEGER;
97
98 -- Define Procedure GET_MR_AFFECTED_ITEMS --
99 PROCEDURE GET_MR_AFFECTED_ITEMS (
100 p_api_version IN NUMBER,
101 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
102 p_commit IN VARCHAR2 := FND_API.G_FALSE,
103 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_msg_count OUT NOCOPY NUMBER,
106 x_msg_data OUT NOCOPY VARCHAR2,
107 p_mr_header_id IN NUMBER,
108 p_mr_effectivity_id IN NUMBER := NULL,
109 p_top_node_flag IN VARCHAR2 := 'N',
110 p_unique_inst_flag IN VARCHAR2 := 'N',
111 p_sort_flag IN VARCHAR2 := 'N',
112 -- JKJain, NR Analysis and Forecasting
113 p_consider_fleet_flag IN VARCHAR2 := 'Y',
114 p_flt_range_calc_date IN DATE := sysdate ,
115 x_mr_item_inst_tbl OUT NOCOPY MR_ITEM_INSTANCE_TBL_TYPE
116 );
117 -- Start of Comments --
118 --
119 -- Procedure name : GET_MR_AFFECTED_ITEMS
120 -- Type : Public
121 -- Function : Get all of the affected item instances for a given MR_id.
122 -- Pre-reqs :
123 --
124 -- GET_MR_AFFECTED_ITEMS Parameters :
125 -- p_mr_id IN NUMBER Required
126 -- Primary key of table AHL_MR_HEADERS_B
127 -- p_mr_effectivity_id IN NUMBER Required
128 -- Primary key of table AHL_MR_EFFECTIVITIES
129 -- p_top_node_flag IN VARCHAR2 Required
130 -- If 'Y' only return top node item instances, else
131 -- return all matching ones
132 -- p_unique_inst_flag IN VARCHAR2 Required
133 -- If 'Y' only return unique item instances, else
134 -- return all matching ones
135 -- x_mr_item_inst_tbl OUT NOCOPY MR_ITEM_INSTANCE_TBL_TYPE Required
136 -- Stores the returned item instance ID and its other
137 -- attributes
138 -- Version :
139 -- Initial Version 1.0
140 --
141 -- End of Comments --
142
143 -- Declare Procedures GET_APPLICABLE_MRS --
144 PROCEDURE GET_APPLICABLE_MRS (
145 p_api_version IN NUMBER,
146 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
147 p_commit IN VARCHAR2 := FND_API.G_FALSE,
148 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
149 x_return_status OUT NOCOPY VARCHAR2,
150 x_msg_count OUT NOCOPY NUMBER,
151 x_msg_data OUT NOCOPY VARCHAR2,
152 p_item_instance_id IN NUMBER,
153 p_mr_header_id IN NUMBER := NULL,
154 p_components_flag IN VARCHAR2 := 'Y',
155 p_include_doNotImplmt IN VARCHAR2 := 'Y',
156 p_visit_type_code IN VARCHAR2 := NULL,
157 -- JKJain, NR Analysis and Forecasting
158 p_consider_fleet_flag IN VARCHAR2 := 'N',
159 p_flt_range_calc_date IN DATE := sysdate ,
160 -- added parameter p_insert_into_tmp_tbl to fix perf bug# 13629335
161 p_insert_into_tmp_tbl IN VARCHAR2 := 'N',
162 x_applicable_mr_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE
163 );
164 -- Start of Comments --
165 --
166 -- Procedure name : GET_APPLICABLE_MRS
167 -- Type : Public
168 -- Function : Get all of its applicable Maintenance Requirements for
169 -- a given item_instance_id.
170 -- Pre-reqs :
171 --
175 -- p_components_flag IN VARCHAR2 Required
172 -- GET_APPLICABLE_MRS Parameters :
173 -- p_item_instance_id IN NUMBER Required
174 -- Item Instance ID
176 -- If 'Y' also return the applicable MRs of all its
177 -- child item instances as well
178 -- x_applicable_mr_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE Required
179 -- Stores the returned MR ID and its other
180 -- attributes
181 -- Version :
182 -- Initial Version 1.0
183 --
184 -- End of Comments --
185
186 -- Declare FUNCTION COUNT_MR_DESCENDENTS --
187 FUNCTION count_mr_descendents(
188 p_mr_header_id NUMBER
189 ) RETURN NUMBER;
190 PRAGMA RESTRICT_REFERENCES (count_mr_descendents, WNDS);
191 -- Start of Comments --
192 --
193 -- Function name : COUNT_MR_DESCENDENTS
194 -- Type : Public
195 -- Function : Get the number of all the given MR's descendents. This
196 -- function is used in the column list of a select statement.
197 -- and that is why it appears in this specification.
198 -- Pre-reqs :
199 -- Parameters :
200 -- p_mr_header_id IN NUMBER Required
201 -- MR Header ID
202 -- Version :
203 -- Initial Version 1.0
204 --
205 -- End of Comments --
206
207 FUNCTION Instance_Matches_Path_Pos(
208 p_instance_id IN NUMBER,
209 p_path_position_id IN NUMBER
210 ) RETURN VARCHAR2;
211
212 -- Declare Procedures GET_PM_APPLICABLE_MRS --
213 PROCEDURE GET_PM_APPLICABLE_MRS (
214 p_api_version IN NUMBER,
215 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
216 p_commit IN VARCHAR2 := FND_API.G_FALSE,
217 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2,
221 p_item_instance_id IN NUMBER,
222 x_applicable_activities_tbl OUT NOCOPY applicable_activities_tbl_type,
223 x_applicable_programs_tbl OUT NOCOPY applicable_programs_tbl_type
224 );
225 -- Start of Comments --
226 --
227 -- Procedure name : GET_PM_APPLICABLE_MRS
228 -- Type : Public
229 -- Function : Get all of its applicable Maintenance Requirements for
230 -- a given item_instance_id.
231 -- Pre-reqs :
232 --
233 -- GET_APPLICABLE_MRS Parameters :
234 -- p_item_instance_id IN NUMBER Required
235 -- Item Instance ID
236 -- p_components_flag IN VARCHAR2 Required
237 -- If 'Y' also return the applicable MRs of all its
238 -- child item instances as well
239 -- x_applicable_programs_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE Required
240 -- Stores the returned MR ID (programs) and its other
241 -- attributes
242 -- x_applicable_activities_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE Required
243 -- Stores the returned MR ID (activities)and its other
244 -- attributes
245 -- Version :
246 -- Initial Version 1.0
247 --
248 -- End of Comments --
249
250
251 -- Declare Procedures GET_VISIT_APPLICABLE_MRS --
252 PROCEDURE get_visit_applicable_mrs (
253 p_api_version IN NUMBER,
254 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
255 p_commit IN VARCHAR2 := FND_API.G_FALSE,
256 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
257 x_return_status OUT NOCOPY VARCHAR2,
258 x_msg_count OUT NOCOPY NUMBER,
259 x_msg_data OUT NOCOPY VARCHAR2,
260 p_item_instance_id IN NUMBER,
261 p_visit_type_code IN VARCHAR2
262 );
263 -- Start of Comments --
264 --
265 -- Procedure name : GET_VISIT_APPLICABLE_MRS
266 -- Type : Public
267 -- Function : Get all of its applicable Maintenance Requirements for
268 -- a given item_instance_id.
269 -- returns all the op mr's satisfying p_visit_type_code in temp table AHL_APPLICABLE_RELNS
270 -- Pre-reqs :
271 --
272 -- GET_APPLICABLE_MRS Parameters :
273 -- p_item_instance_id IN NUMBER Required
274 -- Item Instance ID
275 -- x_applicable_mr_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE Required
276 -- Stores the returned MR ID and its other
277 -- attributes
278 -- Version :
279 -- Initial Version 1.0
280 --
281 -- End of Comments --
282 --
283 -- Declare Procedures GET_VISIT+APPLICABLE_MRS --
284
285 FUNCTION is_pc_assoc_valid(p_item_instance_id IN NUMBER,p_pc_node_id IN NUMBER) RETURN VARCHAR2;
286
287 -- Added to fix bug# 9263774
288 -- Given a MR Header ID and Instance, this procedure returns effectivities
289 -- applicable even if MR has expired.
290 PROCEDURE GET_MR_APPLICABILITY(
291 p_api_version IN NUMBER,
292 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
293 p_commit IN VARCHAR2 := FND_API.G_FALSE,
294 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
295 x_return_status OUT NOCOPY VARCHAR2,
296 x_msg_count OUT NOCOPY NUMBER,
297 x_msg_data OUT NOCOPY VARCHAR2,
298 p_item_instance_id IN NUMBER,
299 p_mr_header_id IN NUMBER,
300 p_components_flag IN VARCHAR2 := 'N',
301 p_include_doNotImplmt IN VARCHAR2 := 'Y',
302 x_applicable_mr_tbl OUT NOCOPY APPLICABLE_MR_TBL_TYPE);
303
304 END AHL_FMP_PVT; -- Package spec
[Home] [Help]