[Home] [Help]
PACKAGE: APPS.AHL_UMP_SMRINSTANCE_PVT
Source
1 PACKAGE AHL_UMP_SMRINSTANCE_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVSMRS.pls 120.4.12020000.2 2012/12/07 14:41:51 sareepar ship $ */
3
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 --------------------------------------------------------------------
7 TYPE Search_MRInstance_Rec_Type IS RECORD (
8 UNIT_NAME VARCHAR2(80),
9 PART_NUMBER VARCHAR2(40),
10 SERIAL_NUMBER VARCHAR2(30),
11 SORT_BY VARCHAR2(30),
12 MR_STATUS VARCHAR2(30),
13 MR_TITLE VARCHAR2(80),
14 PROGRAM_TYPE VARCHAR2(80),
15 DUE_FROM DATE,
16 DUE_TO DATE,
17 SHOW_TOLERANCE VARCHAR2(1),
18 COMPONENTS_FLAG VARCHAR2(1),
19 REPETITIVE_FLAG VARCHAR2(1),
20 CONTRACT_NUMBER VARCHAR2(120),
21 CONTRACT_MODIFIER VARCHAR2(120),
22 SERVICE_LINE_ID NUMBER,
23 SERVICE_LINE_NUM VARCHAR2(150),
24 PROGRAM_ID NUMBER,
25 PROGRAM_TITLE VARCHAR2(80),
26 SHOW_GROUPMR VARCHAR2(1),
27 OBJECT_TYPE VARCHAR2(3),
28 SEARCH_FOR_TYPE VARCHAR2(30),
29 --amsriniv ER 6116245
30 VISIT_NUMBER VARCHAR2(30),
31 VISIT_ORG_NAME VARCHAR2(240),
32 VISIT_DEPT_NAME VARCHAR2(240),
33 --amsriniv ER 6116245
34 --start changes for bug# 7562008
35 INCIDENT_TYPE_ID NUMBER,
36 SERVICE_REQ_NUM cs_incidents_all_b.incident_number%TYPE,
37 --end changes for bug# 7562008
38 --apattark added for VWP enahncement
39 ITEM_GRP_NAME VARCHAR2(80),
40 ON_WING VARCHAR2(30),
41 -- Sthilak CAM changes
42 MR_IMPLEMENT_STATUS VARCHAR2(30),
43 SERVICE_CATEGORY VARCHAR2(30),
44 IS_VWP_NAVG VARCHAR2(1),
45 VWP_VISIT_ID NUMBER
46 );
47
48 TYPE Results_MRInstance_Rec_Type IS RECORD (
49 PROGRAM_TYPE_MEANING VARCHAR2(80),
53 UOM_REMAIN NUMBER,
50 MR_TITLE VARCHAR2(80),
51 PART_NUMBER VARCHAR2(40),
52 SERIAL_NUMBER VARCHAR2(30),
54 COUNTER_NAME VARCHAR2(30),
55 EARLIEST_DUE_DATE DATE,
56 DUE_DATE DATE,
57 LATEST_DUE_DATE DATE,
58 TOLERANCE_FLAG VARCHAR2(30),
59 UMR_STATUS_CODE VARCHAR2(30),
60 UMR_STATUS_MEANING VARCHAR2(80),
61 SCHEDULED_DATE DATE,
62 VISIT_NUMBER VARCHAR2(80),
63 VISIT_STATUS VARCHAR2(80),
64 ASSIGN_STATUS VARCHAR2(80),
65 SERVICE_REQ_ID NUMBER,
66 SERVICE_REQ_NUM VARCHAR2(64),
67 SERVICE_REQ_DATE DATE,
68 ORIGINATOR_TITLE VARCHAR2(80),
69 DEPENDANT_TITLE VARCHAR2(80),
70 UNIT_EFFECTIVITY_ID NUMBER,
71 MR_ID NUMBER,
72 CSI_ITEM_INSTANCE_ID NUMBER,
73 INSTANCE_NUMBER VARCHAR2(30),
74 MR_INTERVAL_ID NUMBER,
75 UNIT_NAME VARCHAR2(4000),
76 PROGRAM_TITLE VARCHAR2(80),
77 CONTRACT_NUMBER VARCHAR2(120),
78 DEFER_FROM_UE_ID NUMBER,
79 DEFER_TO_UE_ID NUMBER,
80 UNIT_EFFECTIVITY_TYPE VARCHAR2(30),
81 OBJECT_TYPE VARCHAR2(3),
82 MANUALLY_PLANNED_FLAG VARCHAR2(30),
83 MANUALLY_PLANNED_DESC VARCHAR2(80),
84 VISIT_ID NUMBER, --PDOKI Added for ER# 6333770
85 VERSION_NUMBER VARCHAR2(50), -- Bug 9410754 added for adding version number to results table
86 ACC_TRIG_RELN_TYPE VARCHAR2(30), -- Changes for SBE - dependent details
87 BUILD_GOAL_PERCENT NUMBER -- Sthilak CAM changes
88 );
89
90 TYPE Search_Ump_Rec_Type IS RECORD (
91 UNIT_NAME VARCHAR2(80),
92 FLEET VARCHAR2(80),
93 ITEM VARCHAR2(40),
94 ITEM_SERIAL_NUMBER VARCHAR2(30),
95 MR_TITLE VARCHAR2(80),
96 MR_SCHEDULED_STATUS VARCHAR2(30),
97 DUE_FROM DATE,
98 DUE_TO DATE,
99 SHOW_TOLERANCE VARCHAR2(1),
100 PROGRAM_TYPE VARCHAR2(80),
101 PROGRAM_SUB_TYPE VARCHAR2(80),
102 DUE_WITHIN NUMBER,
103 DUE_WITHIN_UOM VARCHAR2(30),
104 MR_IMPLEMENT_STATUS VARCHAR2(30),
105 ARRIVAL_ORG VARCHAR2(240),
106 NR_TYPE VARCHAR2(90),
107 SHOW_DEPENDENT_REQUIREMENTS VARCHAR2(1),
108 OPERATING_ORG VARCHAR2(240),
109 ENGINEERING_ORG VARCHAR2(240),
110 PRIMARY_VISIT_TYPE VARCHAR2(80),
111 SCHEDULED_VISIT_TYPE VARCHAR2(80),
112 VISIT_NUMBER VARCHAR2(30),
113 VISIT_ORG_NAME VARCHAR2(240),
114 MAX_DURATION NUMBER,
115 MAX_DURATION_UOM VARCHAR2(30),
116 SERVICE_CATEGORY NUMBER,
117 SERVICE_YIELD NUMBER,
118 SHOW_ONLY_NR_REQ_FLAG VARCHAR2(1),
119 SHOW_VISIT_DATE_VIOLATIONS VARCHAR2(1),
120 MR_SERVICE_TYPE VARCHAR2(30) -- Sthilak CAM change
121 );
122
123 ----------------------------------------------
124 -- Define Table Type for records structures --
125 ----------------------------------------------
126 TYPE Results_MRInstance_Tbl_Type IS TABLE OF Results_MRInstance_Rec_Type INDEX BY BINARY_INTEGER;
127
128 ------------------------
129 -- Declare Procedures --
130 ------------------------
131 --------------------------------------------------------------------------------------------
132 -- Start of Comments --
133 -- Procedure name : Search_MR_Instances
134 -- Type : Private
135 -- Function : This procedure fetches all the MR Instances based both at the instance level
136 -- and the item level for the given search criteria.
137 -- Pre-reqs :
138 -- Parameters :
139 --
140 -- Standard IN Parameters :
141 -- p_api_version IN NUMBER Required
142 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
143 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
144 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
145 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
146 -- Based on this flag, the API will set the default attributes.
147 -- p_module_type IN VARCHAR2 Default NULL
148 -- This will be null.
149 -- Standard OUT Parameters :
150 -- x_return_status OUT VARCHAR2 Required
154 -- Search_MR_Instances Parameters :
151 -- x_msg_count OUT NUMBER Required
152 -- x_msg_data OUT VARCHAR2 Required
153 --
155 -- p_start_row IN NUMBER Required
156 -- The row from which the search results table should be displayed.
157 -- p_rows_per_page IN NUMBER Required
158 -- The number of rows to be displayed per page.
159 -- p_search_mr_instance_rec IN Search_MRInstance_Rec_Type Required
160 -- The search criteria based on which the query needs to be run to
161 -- return the MR Instances.
162 -- x_results_mr_instance_tbl OUT Results_MRInstance_Tbl_Type Required
163 -- List of all the MR Instances which match the search criteria entered.
164 -- x_results_count OUT NUMBER Required
165 -- The total count of the results returned from the entered search criteria.
166 --
167 --
168 --
169 -- Version :
170 -- Initial Version 1.0
171 --
172 -- End of Comments.
173 --------------------------------------------------------------------------------------------
174
175 PROCEDURE Search_MR_Instances
176 (
177 p_api_version IN NUMBER,
178 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
179 p_commit IN VARCHAR2 := FND_API.G_FALSE,
180 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
181 p_default IN VARCHAR2 := FND_API.G_TRUE,
182 p_module_type IN VARCHAR2 := NULL,
183 p_start_row IN NUMBER,
184 p_rows_per_page IN NUMBER,
185 p_search_mr_instance_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_MRInstance_Rec_Type,
186 x_results_mr_instance_tbl OUT NOCOPY AHL_UMP_SMRINSTANCE_PVT.Results_MRInstance_Tbl_Type,
187 x_results_count OUT NOCOPY NUMBER,
188 x_return_status OUT NOCOPY VARCHAR2,
189 x_msg_count OUT NOCOPY NUMBER,
190 x_msg_data OUT NOCOPY VARCHAR2
191 );
192
193 --------------------------------------------------------------------------------------------
194 -- Procedure name : Build_Ump_Search_query
195 -- Type : Private
196 -- Function : This procedure builds search query for planning workbench for the given search criterion.
197 -- Pre-reqs :
198 -- Parameters :
199 --
200 -- Standard IN Parameters :
201 -- p_api_version IN NUMBER Required
202 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
203 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
204 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
205 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
206 -- Based on this flag, the API will set the default attributes.
207 -- p_module_type IN VARCHAR2 Default NULL
208 -- This will be null.
209 -- Standard OUT Parameters :
210 -- x_return_status OUT VARCHAR2 Required
211 -- x_msg_count OUT NUMBER Required
212 -- x_msg_data OUT VARCHAR2 Required
213 --
214 -- Build_Ump_Search_query Parameters :
215 -- p_search_ump_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_Ump_Rec_Type Required
216 -- The search criteria based on which the query needs to be built.
217 -- x_ump_search_query OUT VARCHAR2 Required
218 -- The query built based upon the search criterion passed.
219 -- Version :
220 -- Initial Version 1.0
221 --
222 -- End of Comments.
223 --------------------------------------------------------------------------------------------
224 type ump_search_query_type is record (
225 ump_search_query_str VARCHAR2(31000)
226 );
227
228 type ump_bind_params_type is record (
229 bind_param VARCHAR2(400)
230 );
231 ----------------------------------------------
232 -- Define array type for Ump bind params --
233 ----------------------------------------------
234 type ump_bind_param_tbl_type is table of ump_bind_params_type index by binary_integer;
235
236 PROCEDURE BUILD_UMP_SEARCH_QUERY
237 (
238 P_API_VERSION IN NUMBER,
239 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
240 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
241 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
242 P_DEFAULT IN VARCHAR2 := FND_API.G_TRUE,
243 P_MODULE_TYPE IN VARCHAR2 := NULL,
244 P_SEARCH_UMP_REC IN AHL_UMP_SMRINSTANCE_PVT.SEARCH_UMP_REC_TYPE,
245 X_UMP_SEARCH_QUERY OUT NOCOPY AHL_UMP_SMRINSTANCE_PVT.UMP_SEARCH_QUERY_TYPE,
246 X_UMP_BIND_PARAMS OUT NOCOPY AHL_UMP_SMRINSTANCE_PVT.UMP_BIND_PARAM_TBL_TYPE,
247 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
248 X_MSG_COUNT OUT NOCOPY NUMBER,
249 X_MSG_DATA OUT NOCOPY VARCHAR2 );
250
251
252 FUNCTION GET_UOM_REMAIN(P_UNIT_EFFECTIVITY_ID IN NUMBER) RETURN NUMBER;
253
254 END AHL_UMP_SMRINSTANCE_PVT; -- Package spec