1 PACKAGE AHL_CMP_UTIL_PKG AUTHID CURRENT_USER AS
2 /* $Header: AHLUCMPS.pls 120.0.12020000.2 2012/12/10 16:46:04 prakkum noship $ */
3
4 ------------------------------------------------------------------------------------
5 -- Start of Comments
6 -- Function name : Get_Rpr_Batch_For_Inst
7 -- Type : Public
8 -- Function : Function to get the repair batch name where a given instance resides in the given org.
9 -- Returns NULL if no open repair batch can be found.
10 --
11 -- Pre-reqs :
12 -- Parameters :
13 --
14 -- Get_Rpr_Batch_For_Inst Parameters:
15 -- p_instance_id IN Given instance id. Required
16 -- p_org_id IN Given org id. Required
17 --
18 -- End of Comments
19
20 FUNCTION Get_Rpr_Batch_For_Inst (
21 p_instance_id IN NUMBER,
22 p_org_id IN NUMBER
23 ) RETURN VARCHAR2;
24 ------------------------------------------------------------------------------------
25
26 ------------------------------------------------------------------------------------
27 -- Start of Comments
28 -- Function name : Get_Rpr_Status_For_Inst
29 -- Type : Public
30 -- Function : Function to get the repair status for a given instance and its inventory org id.
31 -- It returns:
32 -- - UNPLANNED : If instance is in sub-inventory, not in any repair batch.
33 -- - PLANNING : If instance is:
34 -- - In a repair batch not havig any RTS workorder
35 -- - Issued to a non-RTS workorder
36 -- - PRODUCTION: If instance is:
37 -- - In a repair batch havig an RTS workorder
38 -- - Issued to an RTS workorder
39 -- - NULL : If instance is not in WIP or INVENTORY
40 --
41 -- Status lookup_type is AHL_CMP_REPAIR_STATUS.
42 --
43 -- Pre-reqs :
44 -- Parameters :
45 --
46 -- Get_Rpr_Status_For_Inst Parameters:
47 -- p_instance_id IN Given instance id. Required
48 -- p_org_id IN Given org id. Required
49 --
50 -- End of Comments
51
52 FUNCTION Get_Rpr_Status_For_Inst (
53 p_instance_id IN NUMBER,
54 p_org_id IN NUMBER
55 ) RETURN VARCHAR2;
56 ------------------------------------------------------------------------------------
57
58 ------------------------------------------------------------------------------------
59 -- Start of Comments
60 -- Function name : Get_Comp_Visit_For_Org
61 -- Type : Public
62 -- Function : Function to get the valid Component Visit id for the given Org id.
63 -- Returns NULL if no valid component visit exists.
64 --
65 -- A valid component visit is one that satisfies the following criteria:
66 -- 1) Visit Type attribute has an associated Visit Type where the
67 -- Component Visit indicator = Yes
68 -- 2) Visit Start date <= SYSDATE and Planned End date > SYSDATE
69 -- 3) Visit Locked indicator = No
70 -- 4) Mandatory attributes for Component Visit populated
71 --
72 -- Pre-reqs :
73 -- Parameters :
74 --
75 -- Get_Comp_Visit_For_Org Parameters:
76 -- p_org_id IN Given org id. Required
77 --
78 -- End of Comments
79
80 FUNCTION Get_Comp_Visit_For_Org (
81 p_org_id IN NUMBER
82 ) RETURN NUMBER;
83 ------------------------------------------------------------------------------------
84
85 ------------------------------------------------------------------------------------
86 -- Start of Comments
87 -- Function name : Is_Comp_Visit
88 -- Type : Public
89 -- Function : Function to check whether the given Visit is a Component Visit.
90 -- Returns 'Y' if it's a Component Visit, 'N' otherwise.
91 --
92 -- Pre-reqs :
93 -- Parameters :
94 --
95 -- Is_Comp_Visit Parameters:
96 -- p_visit_id IN Given visit id. Required
97 --
98 -- End of Comments
99
100 FUNCTION Is_Comp_Visit (
101 p_visit_id IN NUMBER
102 ) RETURN VARCHAR2;
103 -----------------------------------------------------------------------------------------------
104
105 ------------------------------------------------------------------------------------------------
106 -- Start of Comments
107 -- Function name : Check_Rts_Workorder_Exists
108 -- Type : Public
109 -- Function : Function to check for a given Repair Batch and for a given instance if there exists atleast one Open Workorder with RTS flag as Yes.
110 -- Returns 'Y' if it has atleast one Open RTS Workorder, Oherwise 'N'.
111 -- If no value is passed for instance, then we look at all the instances of that Repair Batch.
112 --
113 -- Pre-reqs :
114 -- Parameters :
115 --
116 -- Check_Rts_Workorder_Exists Parameters:
117 -- p_repair_batch IN Given Repair Batch. Required
118 -- p_instance_id IN Given Instance Id Optional
119 --
120 -- End of Comments
121 ------------------------------------------------------------------------------------------------
122 FUNCTION Check_Rts_Workorder_Exists (
123 p_repair_batch IN VARCHAR2,
124 p_instance_id IN NUMBER
125 ) RETURN VARCHAR2;
126 -------------------------------------------------------------------------------------------------
127
128
129
130 -------------------------------------------------------------------------------------------------
131 -- Start of Comments
132 -- Function name : Get_Batch_Qty
133 -- Type : Public
134 -- Function : Function to retrieve the instance quantity located at repair batch Planning and In-Repair locators of a given Repair Batch
135 --
136 -- Pre-reqs :
137 -- Parameters :
138 --
139 -- Get_Batch_Qty Parameters:
140 -- p_repair_batch IN Given Repair Batch. Required
141 --
142 -- End of Comments
143 -------------------------------------------------------------------------------------
144 FUNCTION Get_Batch_Qty (
145 p_repair_batch IN VARCHAR2
146 ) RETURN NUMBER;
147 -------------------------------------------------------------------------------------
148
149
150
151
152 ------------------------------------------------------------------------------------
153 -- Start of Comments
154 -- Function name : Get_Workorder_Qty
155 -- Type : Public
156 -- Function : Function to retrieve the instance quantity that has been issued to a workorder that is associated with this Batch.
157 --
158 -- Pre-reqs :
159 -- Parameters :
160 --
161 -- Get_Workorder_Qty Parameters:
162 -- p_repair_batch IN Given Repair Batch. Required
163 --
164 -- End of Comments
165 -------------------------------------------------------------------------------------
166 FUNCTION Get_Workorder_Qty (
167 p_repair_batch IN VARCHAR2
168 ) RETURN NUMBER;
169 -------------------------------------------------------------------------------------
170
171
172 -----------------------------------------------------------------------------------------
173 -- Start of Comments
174 -- Function name : Get_Locator_Segments
175 -- Type : Public
176 -- Function : Function to retrieve the Concatenated Locator Segments for a given Locator.
177 --
178 -- Pre-reqs :
179 -- Parameters :
180 --
181 -- Get_Locator_Segments Parameters:
182 -- p_locator_id IN Given Locator. Required
183 --
184 -- End of Comments
185 --------------------------------------------------------------------------------------------
186 FUNCTION Get_Locator_Segments (
187 P_LOCATOR_ID IN NUMBER
188 ) RETURN VARCHAR2;
189 ---------------------------------------------------------------------------------------------
190
191 ----------------------------------------------------------------------------------------------
192 -- Start of Comments
193 -- Function name : Get_Repair_Batch
194 -- Type : Public
195 -- Function : Function to retrieve the Repair Batch for a given child task.
196 --
197 -- Pre-reqs :
198 -- Parameters :
199 --
200 -- Get_Repair_Batch Parameters:
201 -- p_child_task_id IN Child Task ID Required
202 --
203 -- End of Comments
204 -----------------------------------------------------------------------------------------------
205 FUNCTION Get_Repair_Batch (
206 p_child_task_id IN NUMBER
207 ) RETURN VARCHAR2;
208 ------------------------------------------------------------------------------------------------
209
210
211 ------------------------------------------------------------------------------------
212 -- Start of Comments
213 -- Function name : Get_Planned_Quantity
214 -- Type : Public
215 -- Function : Function to retrieve the planned quantity for a given item,
216 -- organization and Workorder criteria
217 -- Planned qty : the # of unique workorder qty of item instances in an Org and Visit(s)
218 -- that does not have a Return to supply flag = YES
219 -- Pre-reqs :
220 -- Parameters :
221 --
222 -- Get_Planned_Quantity Parameters:
223 -- p_item_id IN NUMBER,
224 -- p_org_id IN NUMBER,
225 -- p_start_date IN DATE,
226 -- p_end_date IN DATE,
227 -- p_comp_visit_flag VARCHAR2
228 --
229 -- End of Comments
230 -------------------------------------------------------------------------------------
231 FUNCTION Get_Planned_Quantity (
232 p_item_id IN NUMBER,
233 p_org_id IN NUMBER,
234 p_start_date IN DATE,
235 p_end_date IN DATE,
236 p_comp_visit_flag VARCHAR2
237 ) RETURN NUMBER;
238 -------------------------------------------------------------------------------------
239
240 ------------------------------------------------------------------------------------
241 -- Start of Comments
242 -- Function name : Get_Scheduled_Quantity
243 -- Type : Public
244 -- Function : Function to retrieve the planned quantity for a given item, organization and Workorder criteria
245 -- Scheduled qty : the # of unique workorder qty of item instances in an Org and Visit(s)
246 -- that have a Return to supply flag = YES
247 -- Pre-reqs :
248 -- Parameters :
249 --
250 -- Get_Scheduled_Quantity Parameters:
251 -- p_item_id IN NUMBER,
252 -- p_org_id IN NUMBER,
253 -- p_start_date IN DATE,
254 -- p_end_date IN DATE,
255 -- p_comp_visit_flag VARCHAR2
256 --
257 -- End of Comments
258 -------------------------------------------------------------------------------------
259 FUNCTION Get_Scheduled_Quantity (
260 p_item_id IN NUMBER,
261 p_org_id IN NUMBER,
262 p_start_date IN DATE,
263 p_end_date IN DATE,
264 p_comp_visit_flag VARCHAR2
265 ) RETURN NUMBER;
266 -----------------------------------------------------------------------------------------------
267
268 ------------------------------------------------------------------------------------------------
269 -- Start of Comments
270 -- Function name : Are_All_Workorders_Complete
271 -- Type : Public
272 -- Function : Function to check whether all the Workorders associated to a given Repair Batch and for a given instance are Completed.
273 -- If no value is passed for instance, then we look at all the instances of that Repair Batch.
274 -- If no value is passed for repair batch, then we retrieve the repair batch from the instance given.
275 -- Pre-reqs :
276 -- Parameters :
277 --
278 -- Are_All_Workorders_Complete Parameters:
279 -- p_repair_batch IN Given Repair Batch. Optional.
280 -- p_instance_id IN Given Instance Optional.
281 -- p_org_id IN Given Org Required.
282 --
283 -- End of Comments
284 -----------------------------------------------------------------------------------------------
285 FUNCTION Are_All_Workorders_Complete (
286 p_repair_batch IN VARCHAR2,
287 p_instance_id IN NUMBER,
288 p_org_id IN NUMBER
289 ) RETURN VARCHAR2;
290 ------------------------------------------------------------------------------------------------
291
292 -----------------------------------------------------------------------------------------------------
293 -- Start of Comments
294 -- Function name : Get_Rpr_Qty
295 -- Type : Public
296 -- Function : Function to retrieve the repair quantity for a given Repair Batch
297 --
298 -- Pre-reqs :
299 -- Parameters :
300 --
301 -- Get_Rpr_Qty Parameters:
302 -- p_repair_batch IN Given Repair Batch. Required
303 --
304 -- End of Comments
305 -------------------------------------------------------------------------------------
309 -------------------------------------------------------------------------------------
306 FUNCTION Get_Rpr_Qty (
307 p_repair_batch IN VARCHAR2
308 ) RETURN NUMBER;
310
311 END AHL_CMP_UTIL_PKG;
[Home] [Help]