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