1 PACKAGE AHL_PRD_DISP_UTIL_PVT AS
2 /* $Header: AHLVDIUS.pls 120.2 2008/01/29 14:05:24 sathapli ship $ */
3
4 ---------------------------------
5 -- Define Record Type for Node --
6 ---------------------------------
7 TYPE Disp_Type_Rec_Type IS RECORD (
8 CODE VARCHAR2(30),
9 MEANING VARCHAR2(80));
10
11 TYPE Disp_Type_Tbl_Type IS TABLE OF Disp_Type_Rec_Type
12 INDEX BY BINARY_INTEGER;
13
14 -- Start of Comments --
15 -- Define Record Type for the filter structure in disposition overview page --
16 TYPE disp_filter_rec_type IS RECORD (
17 path_position_id NUMBER,
18 path_position_ref VARCHAR2(80),
19 item_group_id NUMBER,
20 item_group_name VARCHAR2(80),
21 inv_item_id NUMBER,
22 item_number VARCHAR2(40),
23 condition_id NUMBER,
24 condition_code VARCHAR2(80), --Actually not code, but meaning
25 item_type_code VARCHAR2(30),
26 item_type VARCHAR2(80), --May not be used
27 immediate_disp_code VARCHAR2(30),
28 immediate_disp VARCHAR2(80), --May not be used
29 secondary_disp_code VARCHAR2(30),
30 secondary_disp VARCHAR2(80), --May not be used
31 disp_status_code VARCHAR2(30),
32 disp_status VARCHAR2(80)); --May not be used
33 -- Define Record Type for the disposition list in disposition overview page --
34 TYPE disp_list_rec_type IS RECORD (
35 disposition_id NUMBER,
36 part_change_id NUMBER,
37 path_position_id NUMBER,
38 path_position_ref VARCHAR2(80),
39 item_group_id NUMBER,
40 item_group_name VARCHAR2(80),
41 immediate_disp_code VARCHAR2(30),
42 immediate_disp VARCHAR2(80),
43 secondary_disp_code VARCHAR2(30),
44 secondary_disp VARCHAR2(80),
45 disp_status_code VARCHAR2(30),
46 disp_status VARCHAR2(80),
47 condition_id NUMBER,
48 condition_code VARCHAR2(80),
49 off_inv_item_id NUMBER,
50 off_item_number VARCHAR2(120), --very rarely it will contains item_group_name
51 off_instance_id NUMBER,
52 off_instance_number VARCHAR2(30),
53 off_serial_number VARCHAR2(30),
54 off_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE,
55 off_quantity NUMBER,
56 off_uom VARCHAR2(3),
57 on_inv_item_id NUMBER,
58 on_item_number VARCHAR2(40),
59 on_instance_id NUMBER,
60 on_instance_number VARCHAR2(30),
61 on_serial_number VARCHAR2(30),
62 on_lot_number MTL_LOT_NUMBERS.LOT_NUMBER%TYPE,
63 on_quantity NUMBER,
64 on_uom VARCHAR2(3));
65
66 -- Define Table Type for the disposition list in disposition overview page --
67 TYPE disp_list_tbl_type IS TABLE OF disp_list_rec_type INDEX BY BINARY_INTEGER;
68
69 -- Define procedure get_disposition_list
70 -- Procedure name: get_disposition_list
71 -- Type: Private
72 -- Function: API to get all dispositions for a job. This API is used to replace the
73 -- disposition view which is too complicated to build
74 -- Pre-reqs:
75 --
76 -- Parameters:
77 -- p_workorder_id IN NUMBER Required, to identify the job
78 -- p_start_row IN NUMBER specify the start row to populate into search result table
79 -- p_rows_per_page IN NUMBER specify the number of row to be populated in the search result table
80 -- p_disp_filter_rec IN disp_filter_rec_type, to store the record structure with which
81 -- to restrict the disposition list result
82 -- x_results_count OUT NUMBER, row count from the query, this number can be more than the
83 -- number of row in search result table
84 -- x_disp_list_tbl OUT disp_list_tbl_type, to store the disposition list result
85 -- Version: Initial Version 1.0
86 --
87 -- End of Comments --
88
89 PROCEDURE get_disposition_list(
90 p_api_version IN NUMBER := 1.0,
91 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
92 --p_commit IN VARCHAR2 := FND_API.G_FALSE,
93 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2,
97 p_workorder_id IN NUMBER,
98 p_start_row IN NUMBER,
99 p_rows_per_page IN NUMBER,
100 p_disp_filter_rec IN disp_filter_rec_type,
101 x_results_count OUT NOCOPY NUMBER,
102 x_disp_list_tbl OUT NOCOPY disp_list_tbl_type);
103
104 ------------------------
105 -- Declare Procedures --
106 ------------------------
107 -- Start of Comments --
108 -- Procedure name : Get_Part_Change_Disposition
109 -- Type : Private
110 -- Function : Fetch the disposition for part change UI
111 -- Pre-reqs :
112 -- Parameters : p_parent_instance_id: parent csi item instance_id
113 -- p_workorder_id: workorder_id
114 -- p_unit_config_header_id: top unit header id
115 -- p_relationship_id: position for installation/removal
116 -- x_disposition_rec: returning disposition record
117 -- x_imm_disp_type_tbl: returning immediate disposition type
118 -- x_sec_disp_type_tbl: returning secondary dispositions
119 --
120 -- SATHAPLI::FP OGMA Issue# 105 - Non-Serialized Item Maintenance, 17-Dec-2007
121 -- p_instance_id: Added to support IB Trees. Pass the instance id to get the disposition for the given instance.
122 --
123 --
124 -- End of Comments.
125
126 PROCEDURE Get_Part_Change_Disposition (
127 p_api_version IN NUMBER,
128 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
129 p_commit IN VARCHAR2 := FND_API.G_FALSE,
130 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
131 x_return_status OUT NOCOPY VARCHAR2,
132 x_msg_count OUT NOCOPY NUMBER,
133 x_msg_data OUT NOCOPY VARCHAR2,
134 p_workorder_id IN NUMBER,
135 --p_unit_config_header_id IN NUMBER, replaced by p_workorder_id by Jerry on 09/20/04
136 p_parent_instance_id IN NUMBER,
137 p_relationship_id IN NUMBER,
138 p_instance_id IN NUMBER,
139 x_disposition_rec OUT NOCOPY AHL_PRD_DISPOSITION_PVT.disposition_rec_type,
140 x_imm_disp_type_tbl OUT NOCOPY Disp_Type_Tbl_Type,
141 x_sec_disp_type_tbl OUT NOCOPY Disp_Type_Tbl_Type);
142 -- Start of Comments --
143 -- Procedure name : Create_Disp_Mtl_Requirement
144 -- Type : Public
145 -- Function : Public API to create a Material requirements for a Disposition.
146 -- If the disposition has neither an item nor a Position Path, an
147 -- exception is raised. If the disposition is for a position that is
148 -- empty, this API gets the item group for the position and picks one
149 -- item from the item group and creates a material requirement for that item.
150 -- If the requirement was created successfully, a message is returned
151 -- via x_msg_data indicating the item, the quantity and the UOM of the
152 -- requirement created.
153 -- Pre-reqs :
154 -- Parameters :
155 --
156 -- Standard IN Parameters :
157 -- p_api_version IN NUMBER Required
158 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
159 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
160 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
161 --
162 -- Standard OUT Parameters :
163 -- x_return_status OUT VARCHAR2 Required
164 -- x_msg_count OUT NUMBER Required
165 -- x_msg_data OUT VARCHAR2 Required
166 --
167 -- Create_Disp_Mtl_Requirement Parameters:
168 -- p_disposition_id IN NUMBER Required
169 -- The Id of disposition for which to create the material requirement.
170 --
171 -- Version :
172 -- Initial Version 1.0
173 --
174 -- End of Comments.
175
176 PROCEDURE Create_Disp_Mtl_Requirement (
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 x_return_status OUT NOCOPY VARCHAR2,
182 x_msg_count OUT NOCOPY NUMBER,
183 x_msg_data OUT NOCOPY VARCHAR2,
184 p_disposition_id IN NUMBER
185 );
186
187 ------------------------
188 -- Start of Comments --
189 -- Procedure name : Get_Available_Disp_Types
190 -- Type : Private
191 -- Function : Fetch the available disposition types for given disposition
192 -- Pre-reqs :
193 -- Parameters : p_disposition_id: The disposition id to fetch against
194 -- x_imm_disp_type_tbl: returning immediate disposition type
195 -- x_sec_disp_type_tbl: returning secondary dispositions
196 --
197 --
198 -- End of Comments.
199
200 PROCEDURE Get_Available_Disp_Types (
201 p_api_version IN NUMBER,
202 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
203 p_commit IN VARCHAR2 := FND_API.G_FALSE,
204 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
205 x_return_status OUT NOCOPY VARCHAR2,
206 x_msg_count OUT NOCOPY NUMBER,
207 x_msg_data OUT NOCOPY VARCHAR2,
208 p_disposition_id IN NUMBER,
209 x_imm_disp_type_tbl OUT NOCOPY Disp_Type_Tbl_Type,
210 x_sec_disp_type_tbl OUT NOCOPY Disp_Type_Tbl_Type);
211
212
213 -- Start of Comments --
214 -- Procedure name : Create_SR_Disp_Link
215 -- Type : Private
216 -- Function : Private API to create a SR Link between the Disposition
217 -- and the new SR object
218 -- Pre-reqs :
219 -- Parameters :
220 --
221 -- Standard IN Parameters :
222 -- p_api_version IN NUMBER Required
223 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
224 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
225 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
226 --
227 -- Standard OUT Parameters :
228 -- x_return_status OUT VARCHAR2 Required
229 -- x_msg_count OUT NUMBER Required
230 -- x_msg_data OUT VARCHAR2 Required
231 --
232 -- Create_SR_Disp_Link Parameters:
233 -- p_disposition_id IN NUMBER Required
234 -- p_service_request_id IN Number Required
235 -- The Id of disposition for which to create the material requirement.
236 --
237 -- Version :
238 -- Initial Version 1.0
239 --
240 -- End of Comments.
241
242 PROCEDURE Create_SR_Disp_Link (
243 p_api_version IN NUMBER,
244 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
245 p_commit IN VARCHAR2 := FND_API.G_FALSE,
246 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2,
250 p_service_request_id IN NUMBER,
251 p_disposition_id IN NUMBER,
252 x_link_id OUT NOCOPY NUMBER
253 );
254
255 -- Function to determine if the incident specified is the
256 -- primary Non Conformance for the disposition specified.
257 -- If it is the primary NC, 'Y' is returned.
258 -- If not, 'N' is returned.
259 -- 'N' is returned in case of any invalid inputs also.
260 FUNCTION Get_Primary_SR_Flag(p_disposition_id IN NUMBER,
261 p_incident_id IN NUMBER)
262 RETURN VARCHAR2;
263
264 -- Function to get the Unit Config Header Id from the workorder Id
265 -- Tries to get the instance from the Workorder's Visit Task First.
266 -- If not possible, gets the instance from the Visit.
267 -- This instance is matched against top nodes of UCs and the matching
268 -- UC's header id is returned.
269 -- If no match is found, null is returned.
270 FUNCTION Get_WO_Unit_Id(p_workorder_id IN NUMBER)
271 RETURN NUMBER;
272
273 -- Function added by Jerry on 01/05/2005 for fixing bug 4093642
274 -- If the installation part change occurrs after the disposition was termindated,
275 -- then even if the removal part change against which the disposition was created,
276 -- is linked with this installation part change id, then this kind of link doesn't
277 -- make sense and we should break it
278 FUNCTION install_part_change_valid(p_disposition_id IN NUMBER)
279 RETURN VARCHAR2;
280
281 End AHL_PRD_DISP_UTIL_PVT;
[Home] [Help]