1 PACKAGE AHL_UMP_ProcessUnit_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVUMUS.pls 120.4.12020000.2 2012/12/07 16:24:45 sareepar ship $ */
3
4
5 ------------------------------------------------------------
6 -- Define record structures used by Process Unit Procedure
7 -- and procedures in ahl_ump_processunit_extn_pvt.
8 ------------------------------------------------------------
9 -- To hold the instance relationship details.
10 TYPE config_node_rec_type IS RECORD (
11 CSI_ITEM_INSTANCE_ID NUMBER,
12 OBJECT_ID NUMBER,
13 POSITION_REFERENCE CSI_II_RELATIONSHIPS.POSITION_REFERENCE%TYPE);
14
15 -- To hold the counter values.
16 TYPE counter_values_rec_type IS RECORD(
17 COUNTER_ID NUMBER,
18 COUNTER_NAME CS_COUNTERS_V.NAME%TYPE,
19 UOM_CODE VARCHAR2(3),
20 COUNTER_VALUE NUMBER );
21
22 ----------------------------------------------
23 -- Define Table Types for record structures --
24 ----------------------------------------------
25 TYPE counter_values_tbl_type IS TABLE OF counter_values_rec_type INDEX BY BINARY_INTEGER;
26 TYPE config_node_tbl_type IS TABLE OF config_node_rec_type INDEX BY BINARY_INTEGER;
27
28 ------------------------
29 -- Declare Procedures --
30 ------------------------
31
32 -- Start of Comments --
33 -- Procedure name : Process_Unit
34 -- Type : Private
35 -- Function : Manages Create/Modify/Delete operations of applicable maintenance
36 -- requirements on a unit.
37 -- Pre-reqs :
38 -- Parameters :
39 --
40 -- Standard OUT Parameters :
44 -- Standard IN parameters :
41 -- x_return_status OUT VARCHAR2 Required
42 -- x_msg_count OUT NUMBER Required
43 -- x_msg_data OUT VARCHAR2 Required
45 -- p_commit IN VARCHAR2 := FND_API.G_FALSE
46 --
47 -- Process_Unit Parameters :
48 -- p_csi_item_instance_id IN NUMBER Required.
49 -- Effectivity will be built for the input item instance ID.
50 -- p_concurrent_flag IN VARCHAR2
51 -- This flag will be 'Y' if called from a concurrent program. Based on this flag, the error
52 -- and informational messages will be logged into the log file.
53
54 PROCEDURE Process_Unit (
55 p_commit IN VARCHAR2 := FND_API.G_FALSE,
56 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
57 x_msg_count OUT NOCOPY NUMBER,
58 x_msg_data OUT NOCOPY VARCHAR2,
59 x_return_status OUT NOCOPY VARCHAR2,
60 p_csi_item_instance_id IN NUMBER,
61 p_concurrent_flag IN VARCHAR2 := 'N',
62 -- JKJain, NR Analysis and Forecasting
63 p_simulation_plan_id IN NUMBER := NULL
64 );
65
66 -- Process units affected by an MR.
67 -- Start of Comments --
68 -- Procedure name : Process_MR_Affected_Units
69 -- Type : Private
70 -- Function : Processes effectivity on all Units impacted by an MR.
71 -- Pre-reqs :
72 -- Parameters :
73 --
74 -- Standard OUT Parameters :
75 -- x_return_status OUT VARCHAR2 Required
76 -- x_msg_count OUT NUMBER Required
77 -- x_msg_data OUT VARCHAR2 Required
78 --
79 -- Process_MR_Affected_Units Parameters :
80 -- Effectivity will be built for all units having p_mr_id as a maintenance requirement.
81 -- This procedure will also be called from terminate_MRs; in which case p_old_mr_header_id
82 -- will also be passed.p_old_mr_header_id is the MR that was terminated. Effectivity will
83 -- be re-build for all units that had p_old_mr_header_id as applicability.
84 --
85 -- p_concurrent_flag IN VARCHAR2
86 -- This flag will be 'Y' if called from a concurrent program. Based on this flag, the error
87 -- and informational messages will be logged into the log file.
88 --
89
90 PROCEDURE Process_MRAffected_Units (
91 p_commit IN VARCHAR2 := FND_API.G_FALSE,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94 x_return_status OUT NOCOPY VARCHAR2,
95 p_mr_header_id IN NUMBER,
96 p_old_mr_header_id IN NUMBER := NULL,
97 p_concurrent_flag IN VARCHAR2 := 'N',
98 -- added to fix performance bug# 6893404
99 p_num_of_workers IN NUMBER := 1,
100 p_mtl_category_id IN NUMBER := NULL,
101 p_process_option IN VARCHAR2 := NULL
102 );
103
104
105 -- Process effectivity for all Units.
106 -- Start of Comments --
107 -- Procedure name : Process_All_Units
108 -- Type : Private
109 -- Function : Processes effectivity on all Units/instances from Intalled Base for which
110 -- maintenance requirements have been defined in FMP.
111 -- Pre-reqs :
112 -- Parameters :
113 --
114 -- Standard OUT Parameters :
115 -- x_return_status OUT VARCHAR2 Required
116 -- x_msg_count OUT NUMBER Required
117 -- x_msg_data OUT VARCHAR2 Required
118 --
119 -- Process_All_Units Parameters :
120 --
121 -- p_concurrent_flag IN VARCHAR2
122 -- This flag will be 'Y' if called from a concurrent program. Based on this flag, the error
123 -- and informational messages will be logged into the log file.
124 --
125
126 PROCEDURE Process_All_Units (
127 p_commit IN VARCHAR2 := FND_API.G_FALSE,
128 x_msg_count OUT NOCOPY NUMBER,
129 x_msg_data OUT NOCOPY VARCHAR2,
130 x_return_status OUT NOCOPY VARCHAR2,
131 p_concurrent_flag IN VARCHAR2 := 'N',
132 -- added to fix performance bug# 6893404
133 p_num_of_workers IN NUMBER := 1,
134 p_mtl_category_id IN NUMBER := NULL,
135 p_process_option IN VARCHAR2 := NULL,
136 -- JKJain, NR Analysis and Forecasting
137 p_simulation_plan_id IN NUMBER := NULL
138 );
139
140 -- Tamal: Bug #4207212, #4114368: Begin
141 -- Process units for contract number, modifier.
142 -- Start of Comments --
143 -- Procedure name : Process_PM_Contracts
144 -- Type : Private
145 -- Function : Retrieves all instances for a contract and calls Process_Unit for each unit.
146 -- Pre-reqs :
147 -- Parameters :
148 --
149 -- Standard OUT Parameters :
150 -- x_return_status OUT VARCHAR2 Required
151 -- x_msg_count OUT NUMBER Required
152 -- x_msg_data OUT VARCHAR2 Required
153 --
154 -- Process_PM_Contracts Parameters :
155 --
156 -- p_concurrent_flag IN VARCHAR2
157 -- This flag will be 'Y' if called from a concurrent program. Based on this flag, the error
158 -- and informational messages will be logged into the log file.
159 -- p_contract_number IN VARCHAR2
160 -- The contract number for which want to process csi_item_instances entitlement
161 -- p_contract_number IN VARCHAR2
162 -- The contract number modifier for above contract number
163 --
164 PROCEDURE Process_PM_Contracts
165 (
166 p_commit IN VARCHAR2 := FND_API.G_FALSE,
167 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
168 x_msg_count OUT NOCOPY NUMBER,
169 x_msg_data OUT NOCOPY VARCHAR2,
170 x_return_status OUT NOCOPY VARCHAR2,
171 p_contract_number IN VARCHAR2 := NULL,
172 p_contract_modifier IN VARCHAR2 := NULL,
173 p_concurrent_flag IN VARCHAR2 := 'N'
174 );
175 -- Tamal: Bug #4207212, #4114368: End
176
177 -- Procedure to calculate due date based on deferral details.
178 PROCEDURE Get_Deferred_Due_Date (p_unit_effectivity_id IN NUMBER,
179 p_deferral_threshold_tbl IN counter_values_tbl_type,
180 x_due_date OUT NOCOPY DATE,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_msg_data OUT NOCOPY VARCHAR2,
183 x_msg_count OUT NOCOPY NUMBER);
184
185 -------------------------------------------------------------
186 -- Following procedure has been added for R12 Enhancements --
187 -------------------------------------------------------------
188 -- Procedure to calculate counter values at a given forecasted date for Reliability Fwk use.
189 PROCEDURE Get_Forecasted_Counter_Values(
190 x_return_status OUT NOCOPY VARCHAR2,
191 x_msg_data OUT NOCOPY VARCHAR2,
192 x_msg_count OUT NOCOPY NUMBER,
193 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
194 p_csi_item_instance_id IN NUMBER, -- Instance Number
195 p_forecasted_date IN DATE,
196 x_counter_values_tbl OUT NOCOPY counter_values_tbl_type); -- Forecasted Counter Vals.
197
198 -- Added for BUE parallel run(Perf bug# 6893404.
199 ------------------------------------------------
200 -- worker concurrent programs for Building Unit Effectivities.
201 -- used for Preventive Maint. processing.
202 PROCEDURE Process_Unit_Range (
203 errbuf OUT NOCOPY VARCHAR2,
204 retcode OUT NOCOPY NUMBER,
205 p_mr_header_id IN NUMBER,
206 p_start_instance_id IN NUMBER,
207 p_end_instance_id IN NUMBER);
208
209 -- this proccedure is used when user selects to run BUE for all units.
210 PROCEDURE Process_Unit_Range (
211 errbuf OUT NOCOPY VARCHAR2,
212 retcode OUT NOCOPY NUMBER,
213 p_parent_conc_pgm_id IN NUMBER);
214
215
216 -- this procedure is launched when the MR is revised from FMP Complete Revision API..
217 PROCEDURE Process_Unit_Range (errbuf OUT NOCOPY VARCHAR2,
218 retcode OUT NOCOPY NUMBER,
219 p_old_mr_header_id IN NUMBER,
220 p_new_mr_header_id IN NUMBER);
221
222 -- function to get net reading to fix performance issue.
223 -- for use by procedure get_current_usage in SQL query.
224 -- Added for performance bug# 6893404.
225 FUNCTION get_latest_ctr_reading(p_counter_id IN NUMBER) RETURN NUMBER;
226
227
228 TYPE warranty_counter_rec_type IS RECORD(
229 WARRANTY_CNTRT_COUNTER_ID NUMBER,
230 COUNTER_ID NUMBER,
231 OBJECT_VERSION_NUMBER NUMBER,
232 UOM_CODE VARCHAR2(3),
233 MAX_COUNTER_VALUE NUMBER,
234 COUNTER_EXPIRATION_DATE DATE);
235
236 TYPE warranty_counter_tbl_type IS TABLE OF warranty_counter_rec_type INDEX BY BINARY_INTEGER;
237
238 -- Calculate Expiration Dates based on Counter Values
239 -- Get_Contract_Counter_Expiration_Date
240 -- Start of Comments --
241 -- Procedure name : Get_Cont_Ctr_Expiration_Date
242 -- Type : Private
243 -- Function : Calculates and updated Expiration date of all counters in an Contract given an Item Instace and Counter values
244 -- Pre-reqs :
245 -- Parameters :
246 --
247 -- Standard OUT Parameters :
248 -- x_return_status OUT VARCHAR2 Required
249 -- x_msg_count OUT NUMBER Required
250 -- x_msg_data OUT VARCHAR2 Required
251 --
252 -- Process_PM_Contracts Parameters :
253 --
254 -- p_item_instance_id IN NUMBER
255 -- Item Instance ID for which contract is instantiated.
256 -- p_x_warranty_counter_tbl IN OUT warranty_counter_tbl_type
257 -- Table of Counter Values associated in the Warranty Contract
258 -- x_expiration_date OUT NOCOPY VARCHAR2
259 -- Least Expiration Date among all the Counters.
260 --
261 PROCEDURE Get_Cont_Ctr_Expiration_Date (p_item_instance_id IN NUMBER,
262 p_x_warranty_counter_tbl IN OUT NOCOPY warranty_counter_tbl_type,
263 x_expiration_date OUT NOCOPY DATE,
264 x_return_status OUT NOCOPY VARCHAR2,
265 x_msg_data OUT NOCOPY VARCHAR2,
266 x_msg_count OUT NOCOPY NUMBER);
267
268
269
270 -- JKJain, NR Analysis and Forecasting
271 -- UC, date for a plan. If plan is null, primary plan will be considered.
272 FUNCTION get_fleet_from_unit_asso(p_uc_header_id IN NUMBER,
273 p_date IN DATE,
274 p_plan_id IN NUMBER) RETURN NUMBER;
275 FUNCTION get_primary_plan_id RETURN NUMBER;
276
277 --------------------------------------------------------------------
278 -- Following procedure has been added for USAF Complex Assembly Enh
279 --------------------------------------------------------------------
280 -- Procedure to calculate NR due date for init-due definition
281 -- due date details will be updated in ahl_unit_effectivities_b table.
282 PROCEDURE Calculate_Init_DueDate (
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_msg_data OUT NOCOPY VARCHAR2,
285 x_msg_count OUT NOCOPY NUMBER,
286 x_due_date OUT NOCOPY DATE,
287 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
288 p_unit_effectivity_id IN NUMBER);
289
290 END AHL_UMP_ProcessUnit_PVT;