1 PACKAGE BODY AHL_RA_ANALYSER_PVT AS
2 /* $Header: AHLVRAAB.pls 120.29.12000000.3 2007/05/09 10:37:38 mpothuku ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_RA_ANALYSER_PVT';
5
6 -- To log error messages into a log file if called from concurrent process.
7 PROCEDURE log_error_messages;
8
9 -- Start of Comments --
10 --
11 -- Procedure name : PROCESS_RA_DATA
12 -- Type : Private
13 -- Function : This API would create the setup data for Reliability Framework in AHL_RA_DEFINITION_HDR
14 -- Pre-reqs :
15 --
16 -- Standard IN Parameters :
17 -- p_api_version IN NUMBER Required
18 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
19 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
20 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
21 --
22 -- Standard OUT Parameters :
23 -- x_return_status OUT VARCHAR2 Required
24 -- x_msg_count OUT NUMBER Required
25 -- x_msg_data OUT VARCHAR2 Required
26 --
27 -- PROCESS_RA_DATA Parameters :
28 -- p_start_date IN DATE Required
29 -- p_end_date IN DATE Required
30 --
31 -- Version :
32 -- Initial Version 1.0
33 --
34 -- End of Comments --
35 PROCEDURE PROCESS_RA_DATA (
36 p_api_version IN NUMBER,
37 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
38 p_commit IN VARCHAR2 := FND_API.G_FALSE,
39 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
40 p_module_type IN VARCHAR2,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2,
44 p_start_date IN DATE,
45 p_end_date IN DATE,
46 p_concurrent_flag IN VARCHAR2,
47 x_xml_data OUT NOCOPY CLOB) IS
48
49 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_RA_DATA';
50 l_api_version CONSTANT NUMBER := 1.0;
51 L_FULL_NAME CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME || '.' || L_API_NAME;
52
53 CURSOR c_setup_counter_data IS
54 SELECT NEW.COUNTER_ID, -- l_tmp_new_ctr_id_tbl
55 NEW.START_DATE_ACTIVE, -- l_tmp_new_start_date_tbl
56 NEW.END_DATE_ACTIVE, -- l_tmp_new_end_date_tbl
57 OVER.COUNTER_ID, -- l_tmp_over_ctr_id_tbl
58 OVER.START_DATE_ACTIVE, -- l_tmp_over_start_date_tbl
59 OVER.END_DATE_ACTIVE -- l_tmp_over_end_date_tbl
60 FROM csi_counter_template_vl NEW,
61 csi_counter_template_vl OVER,
62 AHL_RA_CTR_ASSOCIATIONS CTR
63 WHERE CTR.SINCE_NEW_COUNTER_ID = NEW.COUNTER_ID
64 AND CTR.SINCE_OVERHAUL_COUNTER_ID = OVER.COUNTER_ID(+);
65
66 /*
67 -- Comment Here Starts
68 -- This Cursor Usage is replaced by UA API Call below
69 CURSOR c_flight_schedule_data(c_start_date DATE, c_end_date DATE) IS
70 SELECT US.UNIT_SCHEDULE_ID, -- l_fs_unit_sch_id_tbl
71 US.ARRIVAL_ORG_ID, -- l_fs_arr_org_id_tbl
72 US.UNIT_CONFIG_HEADER_ID, -- l_fs_uc_header_id_tbl
73 US.CSI_ITEM_INSTANCE_ID, -- l_fs_csi_instance_id_tbl
74 ORG.organization_code, -- l_fs_org_code_tbl
75 TRUNC(NVL(US.ACTUAL_ARRIVAL_TIME,US.EST_ARRIVAL_TIME)) -- l_fs_arrival_date_tbl
76 FROM AHL_UNIT_SCHEDULES_V US,org_organization_definitions org
77 WHERE TRUNC(NVL(US.ACTUAL_ARRIVAL_TIME,US.EST_ARRIVAL_TIME)) BETWEEN C_START_DATE AND C_END_DATE
78 AND ORG.ORGANIZATION_ID = US.ARRIVAL_ORG_ID
79 AND UC_STATUS_CODE IN ('COMPLETE', 'INCOMPLETE');
80 -- Comment Here Ends
81 */
82
83 /* -- Bug 4777658 : Perf Fix : Rewriting cursor c_uc_node_details below.
84 -- Perf Fix -- MTL_SYSTEM_ITEMS need not be used here as DESCRIPTION can be fetched from MTL_SYSTEM_ITEMS_KFV
85 CURSOR c_uc_node_details(c_csi_instance_id csi_ii_relationships.object_id%TYPE) IS
86 SELECT CIIR.OBJECT_ID, -- l_dtls_object_id_tbl
87 CIIR.SUBJECT_ID, -- l_dtls_subject_id_tbl
88 DECODE(UC.CSI_ITEM_INSTANCE_ID,
89 NULL,CIIR.POSITION_REFERENCE,
90 (SELECT RELATIONSHIP_ID
91 FROM AHL_MC_RELATIONSHIPS MCR,
92 AHL_UNIT_CONFIG_HEADERS UCI
93 WHERE MCR.MC_HEADER_ID = UCI.MASTER_CONFIG_ID
94 AND MCR.PARENT_RELATIONSHIP_ID IS NULL
95 AND UCI.UNIT_CONFIG_HEADER_ID = AHL_UTIL_UC_PKG.GET_SUB_UC_HEADER_ID(UC.CSI_ITEM_INSTANCE_ID))), -- l_dtls_pos_ref_tbl
96 CII.INVENTORY_ITEM_ID, -- l_dtls_inv_item_id_tbl
97 CII.INV_MASTER_ORGANIZATION_ID, -- l_dtls_inv_master_org_id_tbl
98 CII.INVENTORY_REVISION, -- l_dtls_inv_revision_tbl
99 CII.QUANTITY, -- l_dtls_quantity_tbl
100 CII.UNIT_OF_MEASURE, -- l_dtls_uom_tbl
101 CII.SERIAL_NUMBER, -- l_dtls_srl_no_tbl
102 KFV.CONCATENATED_SEGMENTS, -- l_dtls_item_name_tbl
103 KFV.DESCRIPTION -- l_dtls_item_desc_tbl
104 FROM CSI_II_RELATIONSHIPS CIIR,
105 CSI_ITEM_INSTANCES CII,
106 --MTL_SYSTEM_ITEMS MSI,
107 MTL_SYSTEM_ITEMS_KFV KFV,
108 AHL_UNIT_CONFIG_HEADERS UC
109 WHERE CII.INSTANCE_ID = CIIR.SUBJECT_ID
110 AND CII.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
111 AND CII.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
112 --AND KFV.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
113 --AND KFV.ORGANIZATION_ID = MSI.ORGANIZATION_ID
114 AND UC.CSI_ITEM_INSTANCE_ID(+) = CIIR.SUBJECT_ID
115 START WITH CIIR.OBJECT_ID = c_csi_instance_id
116 AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
117 AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
118 AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
119 CONNECT BY PRIOR CIIR.SUBJECT_ID = CIIR.OBJECT_ID
120 AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
121 AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
122 AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1));
123 */
124
125 -- Bug 4777658 : Perf Fix : Modified Cursor
126
127 CURSOR c_uc_node_details(c_csi_instance_id csi_ii_relationships.object_id%TYPE) IS
128 Select A.OBJECT_ID, -- l_dtls_object_id_tbl
129 A.SUBJECT_ID, -- l_dtls_subject_id_tbl
130 DECODE(A.CSI_ITEM_INSTANCE_ID,
131 NULL,A.POSITION_REFERENCE,
132 MCR.RELATIONSHIP_ID) X, -- l_dtls_pos_ref_tbl
133 A.INVENTORY_ITEM_ID, -- l_dtls_inv_item_id_tbl
134 A.INV_MASTER_ORGANIZATION_ID, -- l_dtls_inv_master_org_id_tbl
135 A.INVENTORY_REVISION, -- l_dtls_inv_revision_tbl
136 A.QUANTITY, -- l_dtls_quantity_tbl
137 A.UNIT_OF_MEASURE, -- l_dtls_uom_tbl
138 A.SERIAL_NUMBER, -- l_dtls_srl_no_tbl
139 A.CONCATENATED_SEGMENTS, -- l_dtls_item_name_tbl
140 A.DESCRIPTION, -- l_dtls_item_desc_tbl
141 --Added by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
142 /* This is a point of contention. For MTBF flow, the header node for the Sub-config is considered for
143 MTBF definition. But for part changes, the position_key correspnding to the node where the sub-config is considered
144 So we need to consider both */
145 A.POSITION_REFERENCE --l_dtls_pos_ref_his_tbl
146 FROM (SELECT CIIR.OBJECT_ID, -- l_dtls_object_id_tbl
147 CIIR.SUBJECT_ID, -- l_dtls_subject_id_tbl
148 CII.INVENTORY_ITEM_ID, -- l_dtls_inv_item_id_tbl
149 CII.INV_MASTER_ORGANIZATION_ID, -- l_dtls_inv_master_org_id_tbl
150 CII.INVENTORY_REVISION, -- l_dtls_inv_revision_tbl
151 CII.QUANTITY, -- l_dtls_quantity_tbl
152 CII.UNIT_OF_MEASURE, -- l_dtls_uom_tbl
153 CII.SERIAL_NUMBER, -- l_dtls_srl_no_tbl
154 KFV.CONCATENATED_SEGMENTS, -- l_dtls_item_name_tbl
155 KFV.DESCRIPTION, -- l_dtls_item_desc_tbl
156 UC.CSI_ITEM_INSTANCE_ID,
157 CIIR.POSITION_REFERENCE,
158 UCI.MASTER_CONFIG_ID
159 FROM ( Select CIIRI.SUBJECT_ID ,
160 CIIRI.OBJECT_ID ,
161 CIIRI.POSITION_REFERENCE
162 from CSI_II_RELATIONSHIPS CIIRI
163 START WITH CIIRI.OBJECT_ID = c_csi_instance_id
164 AND CIIRI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
165 AND TRUNC(NVL(CIIRI.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
166 AND TRUNC(SYSDATE) < TRUNC(NVL(CIIRI.ACTIVE_END_DATE,SYSDATE+1))
167 CONNECT BY PRIOR CIIRI.SUBJECT_ID = CIIRI.OBJECT_ID
168 AND CIIRI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
169 AND TRUNC(NVL(CIIRI.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
170 AND TRUNC(SYSDATE) < TRUNC(NVL(CIIRI.ACTIVE_END_DATE,SYSDATE+1))) CIIR,
171 CSI_ITEM_INSTANCES CII,
172 MTL_SYSTEM_ITEMS_KFV KFV,
173 AHL_UNIT_CONFIG_HEADERS UC,
174 AHL_UNIT_CONFIG_HEADERS UCI
175 WHERE CII.INSTANCE_ID = CIIR.SUBJECT_ID
176 AND CII.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
177 AND CII.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
178 AND UC.CSI_ITEM_INSTANCE_ID(+) = CIIR.SUBJECT_ID
179 AND UCI.UNIT_CONFIG_HEADER_ID(+) = AHL_UTIL_UC_PKG.GET_SUB_UC_HEADER_ID(UC.CSI_ITEM_INSTANCE_ID)) A,
180 AHL_MC_RELATIONSHIPS MCR
181 WHERE MCR.MC_HEADER_ID(+) = A.MASTER_CONFIG_ID
182 AND nvl(MCR.PARENT_RELATIONSHIP_ID,-1) = -1;
183
184 CURSOR c_get_mtbf_data(c_relationship_id AHL_RA_DEFINITION_HDR.RELATIONSHIP_ID%TYPE,
185 c_inv_item_id AHL_RA_DEFINITION_HDR.INVENTORY_ITEM_ID%TYPE,
186 c_inv_org_id AHL_RA_DEFINITION_HDR.INVENTORY_ORG_ID%TYPE,
187 c_inv_item_revision AHL_RA_DEFINITION_HDR.ITEM_REVISION%TYPE) IS
188 SELECT DTLS.COUNTER_ID,
189 DTLS.MTBF_VALUE
190 FROM AHL_RA_DEFINITION_HDR HDR,
191 AHL_RA_DEFINITION_DTLS DTLS
192 WHERE HDR.RA_DEFINITION_HDR_ID = DTLS.RA_DEFINITION_HDR_ID
193 AND HDR.RELATIONSHIP_ID = TO_NUMBER(c_relationship_id)
194 AND HDR.INVENTORY_ITEM_ID = c_inv_item_id
195 AND HDR.INVENTORY_ORG_ID = c_inv_org_id
196 AND (HDR.ITEM_REVISION IS NULL OR HDR.ITEM_REVISION = c_inv_item_revision)
197 -- AND nvl(HDR.ITEM_REVISION,-1) = nvl(c_inv_item_revision,-1)
198 AND DTLS.MTBF_VALUE IS NOT NULL;
199
200 CURSOR c_get_path_postions(c_relationship_id AHL_RA_DEFINITION_HDR.RELATIONSHIP_ID%TYPE) IS
201 Select pos.path_position_id
202 from ahl_mc_path_position_nodes pos,
203 ahl_mc_relationships rel,
204 ahl_mc_headers_b hdr
205 where rel.mc_header_id = hdr.mc_header_id
206 and rel.relationship_id = to_number(c_relationship_id)
207 and hdr.mc_id = pos.mc_id
208 and hdr.version_number = nvl(pos.version_number, hdr.version_number)
209 and pos.position_key = rel.position_key
210 and pos.sequence in (select max(sequence)
211 from ahl_mc_path_position_nodes
212 where path_position_id = pos.path_position_id);
213
214 CURSOR c_fetch_dummy_assocs(c_fct_designator AHL_RA_FCT_ASSOCIATIONS.FORECAST_DESIGNATOR%TYPE,
215 c_arrival_org_id AHL_RA_FCT_ASSOCIATIONS.ORGANIZATION_ID%TYPE) IS
216 Select FORECAST_DESIGNATOR
217 from AHL_RA_FCT_ASSOCIATIONS
218 where ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL'
219 and FORECAST_DESIGNATOR <> c_fct_designator
220 and ORGANIZATION_ID = c_arrival_org_id;
221
222 -- Temp Tables used for fetching data from c_setup_counter_data.
223 -- Bulk Counter details data fetched would be further filtered using condition that the analyser input
224 -- start date and end date should fall between active dates of counter setup defined in CSI.
225 -- These tables have one - to - one correspondance to each another.
226 l_tmp_new_ctr_id_tbl NumTabType;
227 l_tmp_new_start_date_tbl DateTabType;
228 l_tmp_new_end_date_tbl DateTabType;
229 l_tmp_over_ctr_id_tbl NumTabType;
230 l_tmp_over_start_date_tbl DateTabType;
231 l_tmp_over_end_date_tbl DateTabType;
232
233 -- Table to store the final value of counter ids to be used to evaluate the probability of failure.
234 -- These tables have one - to - one correspondance to each another.
235 -- These tables have one - to - one correspondance to each another.
236 l_since_new_ctr_id_tbl NumTabType;
237 l_since_oh_ctr_id_tbl NumTabType;
238
239 -- Temp Tables used for fetching data from c_flight_schedule_data.
240 -- These tables have one - to - one correspondance to each another.
241 l_fs_unit_sch_id_tbl NumTabType;
242 l_fs_arr_org_id_tbl NumTabType;
243 l_fs_uc_header_id_tbl NumTabType;
244 l_fs_csi_instance_id_tbl NumTabType;
245 l_fs_arrival_date_tbl DateTabType;
246 l_fs_org_code_tbl Varchar3TabType;
247 l_flight_search_rec_type AHL_UA_FLIGHT_SCHEDULES_PUB.FLIGHT_SEARCH_REC_TYPE;
248 l_flight_schedules_tbl AHL_UA_FLIGHT_SCHEDULES_PVT.FLIGHT_SCHEDULES_TBL_TYPE;
249 l_fs_index NUMBER := 1;
250
251 -- Temp Tables used for fetching data from c_uc_node_details
252 -- These tables have one - to - one correspondance to each another.
253 l_dtls_object_id_tbl NumTabType;
254 l_dtls_subject_id_tbl NumTabType;
255 l_dtls_pos_ref_tbl Varchar30TabType;
256 l_dtls_inv_item_id_tbl NumTabType;
257 l_dtls_inv_master_org_id_tbl NumTabType;
258 l_dtls_inv_revision_tbl Varchar3TabType;
259 l_dtls_quantity_tbl NumTabType;
260 l_dtls_uom_tbl Varchar3TabType;
261 l_dtls_srl_no_tbl Varchar30TabType;
262 l_dtls_item_name_tbl Varchar40TabType;
263 l_dtls_item_desc_tbl Varchar240TabType;
264 --Modified by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
265 l_dtls_pos_ref_his_tbl Varchar30TabType;
266
267 -- These temp variables have one - to - one correspondance to each another.
268 l_root_pos_ref_code AHL_MC_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
269 l_root_quantity CSI_ITEM_INSTANCES.QUANTITY%TYPE;
270 l_root_uom CSI_ITEM_INSTANCES.UNIT_OF_MEASURE%TYPE;
271 l_root_inv_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE;
272 l_root_inv_master_org_id CSI_ITEM_INSTANCES.INV_MASTER_ORGANIZATION_ID%TYPE;
276 l_root_item_desc MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE;
273 l_root_item_revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE;
274 l_root_srl_no CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE;
275 l_root_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
277
278
279 -- Table Type to retrieve Counters Associated to a Item Instance.
280 l_ctr_values_tbl AHL_UMP_PROCESSUNIT_PVT.counter_values_tbl_type;
281 l_active_ctr_id_tbl NumTabType;
282 l_active_ctr_temp_id_tbl Num15TabType;
283 l_active_ctr_name_tbl Varchar80TabType;
284 l_active_ctr_value_tbl NumTabType;
285 l_active_uom_code_tbl Varchar3TabType;
286 l_ctr_template_id csi_counters_vl.CREATED_FROM_COUNTER_TMPL_ID%TYPE;
287
288 -- This is used for active counters table index
289 l_ctr_index NUMBER := 1;
290
291 -- Return Status Handling
292 l_msg_count NUMBER :=0;
293 l_return_status VARCHAR2(2000);
294 l_msg_data VARCHAR2(2000);
295 l_msg_index_out NUMBER;
296
297 -- Temp Tables used for fetching data from c_get_mtbf_data.
298 -- These tables have one - to - one correspondance to each another.
299 l_mtbf_ctr_id_tbl Num15TabType;
300 l_mtbf_value_tbl NumTabType;
301
302 -- For Path Position Handling for each Item Instance
303 l_path_pos_exist_flag VARCHAR2(1) := 'N';
304 l_path_position_id_tbl NumTabType;
305
306 -- These variables will store the Probability Attribs - A,B,C,D
307 l_prob_attrib_a NUMBER := 0;
308 l_prob_attrib_b NUMBER := 0;
309 l_prob_attrib_b_tmp NUMBER := 0;
310 l_prob_attrib_c NUMBER := 0;
311 l_prob_attrib_c_tmp NUMBER := 0;
312 l_prob_attrib_d NUMBER := 0;
313 l_prob_value NUMBER := 0;
314 l_prob_value_tmp NUMBER := 0;
315
316 -- This is used to store the Forecast Designator for each Item Instance Id
317 l_fct_designator VARCHAR2(10) := NULL;
318
319 -- Index for l_forecast_interface_tbl
320 l_fct_index NUMBER := 1;
321
322 -- Index for l_forecast_designator_tbl
323 l_dsg_index NUMBER := 1;
324 -- To indicate if duplicate forecast_desginator is already populated in l_forecast_designator_tbl
325 l_ds_rec_found VARCHAR2(1) := 'N';
326
327
328 -- This is used for each item instance to indicate if MTBF Data is defined or not
329 l_mtbf_data_defined VARCHAR2(1) := 'N';
330 l_mtbf_value_exceeds VARCHAR2(1) := 'N';
331
332 -- Plsql Table of rec type MRP_FORECAST_INTERFACE_PK.rec_forecast_interface
333 l_forecast_interface_tbl MRP_FORECAST_INTERFACE_PK.t_forecast_interface;
334 l_forecast_designator_tbl MRP_FORECAST_INTERFACE_PK.t_forecast_designator;
335 l_forecast_org_code_tbl Varchar3TabType;
336 l_forecast_srl_no_tbl Varchar30TabType;
337 l_forecast_item_name_tbl Varchar40TabType;
338 l_forecast_onhand_qty_tbl NumTabType;
339 l_forecast_item_desc_tbl Varchar240TabType;
340 l_forecast_req_prob_tbl NumTabType;
341 l_forecast_osp_qty_tbl NumTabType;
342 l_forecast_vwp_qty_tbl NumTabType;
343 l_forecast_non_qty_tbl NumTabType;
344
345 -- For ATP Call - Check Availability API
346 l_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
347 x_atp_rec MRP_ATP_PUB.ATP_Rec_Typ;
348 x_atp_supply_demand MRP_ATP_PUB.ATP_Supply_Demand_Typ;
349 x_atp_period MRP_ATP_PUB.ATP_Period_Typ;
350 x_atp_details MRP_ATP_PUB.ATP_Details_Typ;
351 l_session_id NUMBER;
352
353 -- Dummy PLSql table to fetch dummy forecast associations for population MRP with residual of 100% Probability.
354 l_dummy_fct_desg_tbl Varchar10TabType;
355 l_incl_in_rpt_flag_tbl Varchar1TabType;
356
357 l_mrp_api_return_flag BOOLEAN := FALSE;
358
359 -- Used to Write to O/P File
360 l_fct_data_lob CLOB;
361 l_row_count NUMBER :=0;
362 --mpothuku changed the length from 1000 to 5000 on 09-May-2007 after the XML encoding has been introduced to fix the Bug 6038466
363 l_dummy_string VARCHAR2(5000);
364
365 l_dummy_identifier NUMBER;
366
367 BEGIN
368
369 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
370 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.begin','At the start of PLSQL procedure');
371 END IF;
372
373 IF (p_concurrent_flag = 'Y') THEN
374 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- START ----');
375 END IF;
376
377 -- Standard start of API savepoint
378 SAVEPOINT PROCESS_RA_DATA_SP;
379
380 -- Standard call to check for call compatibility
381 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
383 END IF;
384
385 -- Initialize message list if p_init_msg_list is set to TRUE
386 IF FND_API.To_Boolean(p_init_msg_list) THEN
387 FND_MSG_PUB.Initialize;
388 END IF;
389
390 x_return_status := FND_API.G_RET_STS_SUCCESS;
391
395 fnd_log.string(fnd_log.level_statement,l_full_name,'RA -- PKG -- PROCESS_RA_DATA -------p_end_date-----------' || p_end_date);
392 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
393 fnd_log.string(fnd_log.level_statement,l_full_name,'RA -- PKG -- PROCESS_RA_DATA -------BEGIN-----------');
394 fnd_log.string(fnd_log.level_statement,l_full_name,'RA -- PKG -- PROCESS_RA_DATA -------p_start_date-----------' || p_start_date);
396 END IF;
397
398 IF (p_concurrent_flag = 'Y') THEN
399 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Validating Start / End Date ----');
400 END IF;
401
402 -- Input Data Validation
403 -- p_start_date and p_end_date should not be null.
404 -- p_start_date should be less than equal to p_end_date
405 -- p_start_date and p_end_date should not be less than sysdate
406 IF ( (p_start_date IS NULL)
407 OR (p_end_date IS NULL)
408 OR (p_start_date > p_end_date)
409 OR (p_start_date < trunc(sysdate))
410 OR (p_end_date < trunc(sysdate))
411 ) THEN
412
413 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
414 fnd_log.string(fnd_log.level_statement,l_full_name,'-- Invalid Param Passed --');
415 fnd_log.string(fnd_log.level_statement,l_full_name,'-- p_start_date -- '||p_start_date);
416 fnd_log.string(fnd_log.level_statement,l_full_name,'-- p_end_date -- '||p_end_date);
417 END IF;
418 IF (p_concurrent_flag = 'Y') THEN
419 fnd_file.put_line(fnd_file.log, '-- Invalid Param Passed -- Dates Validation Failed');
420 END IF;
421 FND_MESSAGE.Set_Name('AHL','AHL_RA_INV_PARAM_PASSED');
422 FND_MESSAGE.Set_Token('NAME','ANALYSER.PROCESS_DATA');
423 FND_MSG_PUB.ADD;
424 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
425 END IF;
426
427 IF (p_concurrent_flag = 'Y') THEN
428 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Fetching Counter Details Data from Setup ----');
429 END IF;
430
431 -- Fetch Counter Details Data from Setup
432 OPEN c_setup_counter_data;
433 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
434 fnd_log.string(fnd_log.level_statement,l_full_name,' Bulk Fetching Counter Details Data from Setup');
435 END IF;
436 IF (p_concurrent_flag = 'Y') THEN
437 fnd_file.put_line(fnd_file.log, '-- Bulk Fetching Counter Details Data from Setup --');
438 END IF;
439 FETCH c_setup_counter_data
440 BULK COLLECT INTO l_tmp_new_ctr_id_tbl,
441 l_tmp_new_start_date_tbl,
442 l_tmp_new_end_date_tbl,
443 l_tmp_over_ctr_id_tbl,
444 l_tmp_over_start_date_tbl,
445 l_tmp_over_end_date_tbl;
446 IF l_tmp_new_ctr_id_tbl.COUNT = 0 THEN
447 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
448 fnd_log.string(fnd_log.level_statement,l_full_name,' No Setup Data Found for Counters');
449 END IF;
450 IF (p_concurrent_flag = 'Y') THEN
451 fnd_file.put_line(fnd_file.log, '-- No Setup Data Found for Counters --');
452 END IF;
453 END IF;
454 CLOSE c_setup_counter_data;
455
456 --Filtering Counter Setup Data based on Active Dates
457 IF l_tmp_new_ctr_id_tbl.COUNT > 0 THEN
458
459 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
460 fnd_log.string(fnd_log.level_statement,l_full_name,'Filtering Counter Setup Data based on Active Dates - '||l_tmp_new_ctr_id_tbl.COUNT);
461 fnd_log.string(fnd_log.level_statement,l_full_name,'Analyser Start Date - '||p_start_date);
462 fnd_log.string(fnd_log.level_statement,l_full_name,'Analyser End Date - '||p_end_date);
463 END IF;
464
465 IF (p_concurrent_flag = 'Y') THEN
466 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Validating Active Dates of Counter Setup Data ----');
467 END IF;
468
469 FOR i IN l_tmp_new_ctr_id_tbl.FIRST .. l_tmp_new_ctr_id_tbl.LAST LOOP -- loop for Setup Cunters fetched
470
471 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
472 fnd_log.string(fnd_log.level_statement,l_full_name,'Looping for (i) - '||i);
473 fnd_log.string(fnd_log.level_statement,l_full_name,'l_tmp_new_ctr_id_tbl - '||l_tmp_new_ctr_id_tbl(i));
474 fnd_log.string(fnd_log.level_statement,l_full_name,'l_tmp_new_start_date_tbl - '||l_tmp_new_start_date_tbl(i));
475 fnd_log.string(fnd_log.level_statement,l_full_name,'l_tmp_new_end_date_tbl - '||l_tmp_new_end_date_tbl(i));
476 fnd_log.string(fnd_log.level_statement,l_full_name,'l_tmp_over_ctr_id_tbl - '||l_tmp_over_ctr_id_tbl(i));
477 fnd_log.string(fnd_log.level_statement,l_full_name,'l_tmp_over_start_date_tbl - '||l_tmp_over_start_date_tbl(i));
478 fnd_log.string(fnd_log.level_statement,l_full_name,'l_tmp_over_end_date_tbl - '||l_tmp_over_end_date_tbl(i));
479 END IF;
480
481 IF ((trunc(nvl(l_tmp_new_start_date_tbl(i),p_start_date)) <= trunc(p_start_date)) AND
482 (trunc(nvl(l_tmp_new_end_date_tbl(i),p_end_date + 1)) > trunc(p_end_date))) THEN
483 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
487 ELSE
484 fnd_log.string(fnd_log.level_statement,l_full_name,'Including Since New - l_since_new_ctr_id_tbl -'||i||'-'||l_tmp_new_ctr_id_tbl(i));
485 END IF;
486 l_since_new_ctr_id_tbl(i) := l_tmp_new_ctr_id_tbl(i);
488 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
489 fnd_log.string(fnd_log.level_statement,l_full_name,'Omiting Since New - l_tmp_new_ctr_id_tbl -'||i||'-'||l_tmp_new_ctr_id_tbl(i));
490 END IF;
491 l_since_new_ctr_id_tbl(i) := null;
492 END IF;
493
494 IF (l_tmp_over_ctr_id_tbl(i) IS NOT NULL) THEN
495 IF ((trunc(nvl(l_tmp_over_start_date_tbl(i),p_start_date)) <= trunc(p_start_date)) AND
496 (trunc(nvl(l_tmp_over_end_date_tbl(i),p_end_date + 1)) > trunc(p_end_date))) THEN
497 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
498 fnd_log.string(fnd_log.level_statement,l_full_name,'Including Since Overhaul - l_since_oh_ctr_id_tbl -'||i||'-'||l_tmp_over_ctr_id_tbl(i));
499 END IF;
500 l_since_oh_ctr_id_tbl(i) := l_tmp_over_ctr_id_tbl(i);
501 ELSE
502 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
503 fnd_log.string(fnd_log.level_statement,l_full_name,'Omiting Since Overhaul - l_tmp_over_ctr_id_tbl -'||i||'-'||l_tmp_over_ctr_id_tbl(i));
504 END IF;
505 l_since_oh_ctr_id_tbl(i) := null;
506 END IF;
507 ELSE
508 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
509 fnd_log.string(fnd_log.level_statement,l_full_name,'Omiting Since Overhaul - l_tmp_over_ctr_id_tbl -'||i||'-'||l_tmp_over_ctr_id_tbl(i));
510 END IF;
511 l_since_oh_ctr_id_tbl(i) := null;
512 END IF;
513
514 END LOOP; -- End Loop for Setup Counters Fetched
515
516 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
517 IF l_since_new_ctr_id_tbl.COUNT > 0 THEN
518 FOR t in l_since_new_ctr_id_tbl.FIRST .. l_since_new_ctr_id_tbl.LAST LOOP
519 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_since_new_ctr_id_tbl --- '||l_since_new_ctr_id_tbl(t));
520 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_since_oh_ctr_id_tbl --- '||l_since_oh_ctr_id_tbl(t));
521 END LOOP;
522 END IF;
523 END IF;
524
525 ELSE
526
527 IF (p_concurrent_flag = 'Y') THEN
528 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- No Counter Setup Done for Active Counters --');
529 END IF;
530
531 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
532 fnd_log.string(fnd_log.level_statement,l_full_name,' No Setup Data Found for Counters - In Loop Else Clause');
533 END IF;
534 END IF;
535
536 -- Return if no applicable Counters are retrieved
537 IF l_since_new_ctr_id_tbl.COUNT = 0 THEN
538 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
539 fnd_log.string(fnd_log.level_statement,l_full_name,'None of the defined counters - are active as per date');
540 END IF;
541 IF (p_concurrent_flag = 'Y') THEN
542 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- No Counter Setup - Non Active afer Data Validation - so returning ----');
543 END IF;
544
545 ELSE
546
547 IF (p_concurrent_flag = 'Y') THEN
548 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Fetching Flight Schedule Data ----');
549 END IF;
550
551 /*
552 -- Comment Here Starts
553 -- Commenting out Code Below for fetching Flight Schedule Data using cursor c_flight_schedule_data
554 -- Instead UA API is being called to derive the flight schedule data
555 -- Fetch Flight Schedules Data For Processing
556 OPEN c_flight_schedule_data(p_start_date,p_end_date);
557 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
558 fnd_log.string(fnd_log.level_statement,l_full_name,' Bulk Fetching Flight Schedules Data For Processing');
559 END IF;
560 IF (p_concurrent_flag = 'Y') THEN
561 fnd_file.put_line(fnd_file.log, ' Bulk Fetching Flight Schedules Data For Processing');
562 END IF;
563 FETCH c_flight_schedule_data
564 BULK COLLECT INTO l_fs_unit_sch_id_tbl,
565 l_fs_arr_org_id_tbl,
566 l_fs_uc_header_id_tbl,
567 l_fs_csi_instance_id_tbl,
568 l_fs_org_code_tbl,
569 l_fs_arrival_date_tbl;
570 IF l_fs_unit_sch_id_tbl.COUNT = 0 THEN
571 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
572 fnd_log.string(fnd_log.level_statement,l_full_name,' No Flight Schedules Data Retrieved');
573 END IF;
574 IF (p_concurrent_flag = 'Y') THEN
575 fnd_file.put_line(fnd_file.log, ' No Flight Schedules Data Retrieved');
576 END IF;
577 ELSE
578 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
579 fnd_log.string(fnd_log.level_statement,l_full_name,' Flight Schedules Data Retrieved'||l_fs_unit_sch_id_tbl.COUNT);
580 END IF;
581 IF (p_concurrent_flag = 'Y') THEN
582 fnd_file.put_line(fnd_file.log, 'Flight Schedules Data Retrieved'||l_fs_unit_sch_id_tbl.COUNT);
583 END IF;
584 END IF;
585 CLOSE c_flight_schedule_data;
586 -- Comment Here Ends
587 */
588
589 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
590 fnd_log.string(fnd_log.level_statement,l_full_name,' Bulk Fetching Flight Schedules Data For Processing - UA API');
591 END IF;
592 IF (p_concurrent_flag = 'Y') THEN
593 fnd_file.put_line(fnd_file.log, ' Bulk Fetching Flight Schedules Data For Processing - UA API');
594 END IF;
595
596 l_flight_search_rec_type.START_DATE := p_start_date - 1;
597 l_flight_search_rec_type.END_DATE := p_end_date + 1;
598 l_flight_search_rec_type.DATE_APPLY_TO_FLAG := AHL_UA_FLIGHT_SCHEDULES_PUB.G_APPLY_TO_ARRIVAL;
599
600 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
601 fnd_log.string(fnd_log.level_statement,l_full_name,' START_DATE - UA API' || l_flight_search_rec_type.START_DATE);
602 fnd_log.string(fnd_log.level_statement,l_full_name,' END_DATE - UA API' || l_flight_search_rec_type.END_DATE);
603 fnd_log.string(fnd_log.level_statement,l_full_name,' DATE_APPLY_TO_FLAG - UA API : ' || l_flight_search_rec_type.DATE_APPLY_TO_FLAG);
604 END IF;
605
606 AHL_UA_FLIGHT_SCHEDULES_PUB.Get_Flight_Schedule_Details(
607 -- standard IN params
608 p_api_version => 1,
609 p_init_msg_list => FND_API.G_FALSE,
610 p_commit => FND_API.G_FALSE,
611 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
612 p_default => FND_API.G_FALSE,
613 p_module_type => NULL,
614 -- standard OUT params
615 x_return_status => l_return_status,
616 x_msg_count => l_msg_count,
617 x_msg_data => l_msg_data,
618 -- procedure params
619 p_flight_search_rec => l_flight_search_rec_type,
620 x_flight_schedules_tbl => l_flight_schedules_tbl);
621
622 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
623 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
624 fnd_log.string(fnd_log.level_statement,l_full_name,'Called API AHL_UA_FLIGHT_SCHEDULES_PUB.Get_Flight_Schedule_Details Errored');
625 END IF;
626 IF (p_concurrent_flag = 'Y') THEN
627 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Error in getting Flight Scedule Details --');
628 END IF;
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631
632 IF l_flight_schedules_tbl.COUNT > 0 THEN
633 FOR f in l_flight_schedules_tbl.FIRST .. l_flight_schedules_tbl.LAST LOOP
634 IF (AHL_UTIL_UC_PKG.IS_UNIT_QUARANTINED(l_flight_schedules_tbl(f).UNIT_CONFIG_HEADER_ID,NULL) = FND_API.G_FALSE) THEN
635 l_fs_unit_sch_id_tbl(l_fs_index) := l_flight_schedules_tbl(f).UNIT_SCHEDULE_ID;
636 l_fs_arr_org_id_tbl(l_fs_index) := l_flight_schedules_tbl(f).ARRIVAL_ORG_ID;
637 l_fs_uc_header_id_tbl(l_fs_index) := l_flight_schedules_tbl(f).UNIT_CONFIG_HEADER_ID;
638 l_fs_csi_instance_id_tbl(l_fs_index) := l_flight_schedules_tbl(f).CSI_INSTANCE_ID;
639 l_fs_org_code_tbl(l_fs_index) := l_flight_schedules_tbl(f).ARRIVAL_ORG_CODE;
640 IF TRUNC(l_flight_schedules_tbl(f).ACTUAL_ARRIVAL_TIME) IS NULL THEN
641 l_fs_arrival_date_tbl(l_fs_index) := TRUNC(l_flight_schedules_tbl(f).EST_ARRIVAL_TIME);
642 ELSE
643 l_fs_arrival_date_tbl(l_fs_index) := TRUNC(l_flight_schedules_tbl(f).ACTUAL_ARRIVAL_TIME);
644 END IF;
645 l_fs_index := l_fs_index + 1;
646 END IF;
647 END LOOP;
648 END IF;
649 IF (p_concurrent_flag = 'Y') THEN
653 IF l_fs_unit_sch_id_tbl.COUNT > 0 THEN
650 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Flight Schedule Data fetched ----'||l_fs_unit_sch_id_tbl.COUNT);
651 END IF;
652
654 -- Loop for each Unit in the Unit Schedule Record fetched above.
655 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
656 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- LOOP FOR FLIGHT SCHEDULE BEGINS ----- '||l_fs_unit_sch_id_tbl.COUNT);
657 END IF;
658 IF (p_concurrent_flag = 'Y') THEN
659 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Processing Fligth Schedule Data ----');
660 END IF;
661
662 FOR i in l_fs_unit_sch_id_tbl.FIRST .. l_fs_unit_sch_id_tbl.LAST LOOP -- Loop for each Unit in the Unit Schedule Record fetched above.
663 -- Fetch details of the Root node of the UC
664 BEGIN
665
666 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
667 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- IN FS LOOP - l_fs_unit_sch_id_tbl ----- '||l_fs_unit_sch_id_tbl(i));
668 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- IN FS LOOP - l_fs_arr_org_id_tbl ----- '||l_fs_arr_org_id_tbl(i));
669 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- IN FS LOOP - l_fs_uc_header_id_tbl ----- '||l_fs_uc_header_id_tbl(i));
670 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- IN FS LOOP - l_fs_csi_instance_id_tbl ----- '||l_fs_csi_instance_id_tbl(i));
671 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- IN FS LOOP - l_fs_org_code_tbl ----- '||l_fs_org_code_tbl(i));
672 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- IN FS LOOP - l_fs_arrival_date_tbl ----- '||l_fs_arrival_date_tbl(i));
673 END IF;
674 IF (p_concurrent_flag = 'Y') THEN
675 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Fetching UC Root Node Details ----');
676 END IF;
677
678 Select MCR.RELATIONSHIP_ID,
679 CII.QUANTITY,
680 CII.UNIT_OF_MEASURE,
681 CII.INVENTORY_ITEM_ID,
682 CII.INV_MASTER_ORGANIZATION_ID,
683 CII.INVENTORY_REVISION,
684 CII.SERIAL_NUMBER,
685 KFV.CONCATENATED_SEGMENTS,
686 KFV.DESCRIPTION
687 INTO l_root_pos_ref_code,
688 l_root_quantity,
689 l_root_uom,
690 l_root_inv_item_id,
691 l_root_inv_master_org_id,
692 l_root_item_revision,
693 l_root_srl_no,
694 l_root_item_name,
695 l_root_item_desc
696 FROM AHL_UNIT_CONFIG_HEADERS UC,
697 CSI_ITEM_INSTANCES CII,
698 MTL_SYSTEM_ITEMS_KFV KFV,
699 ahl_mc_relationships MCR
700 WHERE UC.UNIT_CONFIG_HEADER_ID = l_fs_uc_header_id_tbl(i)
701 AND UC.CSI_ITEM_INSTANCE_ID = CII.INSTANCE_ID
702 AND KFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
703 AND KFV.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID
704 AND MCR.mc_header_id = UC.MASTER_CONFIG_ID
705 and MCR.parent_relationship_id is null;
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
709 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- Invalid -- UC_HEADER_ID ----- '||l_fs_uc_header_id_tbl(i));
710 END IF;
711 IF (p_concurrent_flag = 'Y') THEN
712 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Invalid UC Header Id - Data Corruption ----');
713 END IF;
714 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
715 WHEN OTHERS THEN
716 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
717 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- OTHERS EXEC BLOCK -- UC_HEADER_ID ----- '||l_fs_uc_header_id_tbl(i));
718 END IF;
719 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
720 END;
721
722 -- Fetch Unit Config Tree Node Details for the Unit under consideration in the Loop.
723 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
724 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- Fetching UC Node Data -----Flight Schedule Id --- '||l_fs_unit_sch_id_tbl(i));
725 END IF;
726 IF (p_concurrent_flag = 'Y') THEN
727 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- For Each UC - Fetching Tree Structure ----');
728 END IF;
729
730 OPEN c_uc_node_details(l_fs_csi_instance_id_tbl(i));
731 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
735 BULK COLLECT INTO l_dtls_object_id_tbl,
732 fnd_log.string(fnd_log.level_statement,l_full_name,' Bulk Fetching UC Tree for Root_Instance_id -- '||l_fs_csi_instance_id_tbl(i));
733 END IF;
734 FETCH c_uc_node_details
736 l_dtls_subject_id_tbl,
737 l_dtls_pos_ref_tbl,
738 l_dtls_inv_item_id_tbl,
739 l_dtls_inv_master_org_id_tbl,
740 l_dtls_inv_revision_tbl,
741 l_dtls_quantity_tbl,
742 l_dtls_uom_tbl,
743 l_dtls_srl_no_tbl,
744 l_dtls_item_name_tbl,
745 l_dtls_item_desc_tbl,
746 --Modified by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
747 l_dtls_pos_ref_his_tbl;
748 IF l_dtls_subject_id_tbl.COUNT = 0 THEN
749 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
750 fnd_log.string(fnd_log.level_statement,l_full_name,' No Nodes are Returned - Inserting Root Node at FIRST - index 1');
751 END IF;
752 IF (p_concurrent_flag = 'Y') THEN
753 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- No Nodes Fetched --Inserting Root Node at FIRST --');
754 END IF;
755 l_dtls_object_id_tbl(1) := l_fs_csi_instance_id_tbl(i);
756 l_dtls_subject_id_tbl(1) := l_fs_csi_instance_id_tbl(i);
757 l_dtls_pos_ref_tbl(1) := l_root_pos_ref_code;
758 l_dtls_inv_item_id_tbl(1) := l_root_inv_item_id;
759 l_dtls_inv_master_org_id_tbl(1) := l_root_inv_master_org_id;
760 l_dtls_inv_revision_tbl(1) := l_root_item_revision;
761 l_dtls_quantity_tbl(1) := l_root_quantity;
762 l_dtls_uom_tbl(1) := l_root_uom;
763 l_dtls_srl_no_tbl(1) := l_root_srl_no;
764 l_dtls_item_name_tbl(1) := l_root_item_name;
765 l_dtls_item_desc_tbl(1) := l_root_item_desc;
766 --Added by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
767 l_dtls_pos_ref_his_tbl(1) := l_root_pos_ref_code;
768 ELSE
769 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
770 fnd_log.string(fnd_log.level_statement,l_full_name,' Nodes are Returned - Inserting Root Node at LAST - index LAST + 1');
771 END IF;
772 IF (p_concurrent_flag = 'Y') THEN
773 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- All Nodes Fetched --Inserting Root Node at LAST --');
774 END IF;
775 l_dtls_object_id_tbl(l_dtls_object_id_tbl.LAST + 1) := l_fs_csi_instance_id_tbl(i);
776 l_dtls_subject_id_tbl(l_dtls_subject_id_tbl.LAST + 1) := l_fs_csi_instance_id_tbl(i);
777 l_dtls_pos_ref_tbl(l_dtls_pos_ref_tbl.LAST + 1) := l_root_pos_ref_code;
778 l_dtls_inv_item_id_tbl(l_dtls_inv_item_id_tbl.LAST + 1) := l_root_inv_item_id;
779 l_dtls_inv_master_org_id_tbl(l_dtls_inv_master_org_id_tbl.LAST + 1) := l_root_inv_master_org_id;
780 l_dtls_inv_revision_tbl(l_dtls_inv_revision_tbl.LAST + 1) := l_root_item_revision;
781 l_dtls_quantity_tbl(l_dtls_quantity_tbl.LAST + 1) := l_root_quantity;
782 l_dtls_uom_tbl(l_dtls_uom_tbl.LAST + 1) := l_root_uom;
783 l_dtls_srl_no_tbl(l_dtls_srl_no_tbl.LAST + 1) := l_root_srl_no;
784 l_dtls_item_name_tbl(l_dtls_item_name_tbl.LAST + 1) := l_root_item_name;
785 l_dtls_item_desc_tbl(l_dtls_item_desc_tbl.LAST + 1) := l_root_item_desc;
786 --Added by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
787 l_dtls_pos_ref_his_tbl(l_dtls_pos_ref_his_tbl.LAST + 1) := l_root_pos_ref_code;
788 END IF;
789 CLOSE c_uc_node_details;
790
791 IF l_dtls_subject_id_tbl.COUNT > 0 THEN
792 IF (p_concurrent_flag = 'Y') THEN
793 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Start Processing of Each Node --');
794 END IF;
795 FOR j IN l_dtls_subject_id_tbl.FIRST .. l_dtls_subject_id_tbl.LAST LOOP -- Loop for Each node in UC
796 l_ctr_index := 1; -- Reset if Index for Active Counters for each item instance.
797 l_mtbf_data_defined := 'N'; -- Reset if MTBF Data is defined or not indicator for each item instance.
798 l_fct_designator := null; -- Reset if Fct designator is derived or not indicator for each item instance.
799 l_prob_value := 0; -- Final Probability Value - Reset for Each Item Instance
800 l_ds_rec_found := 'N'; -- To indicate if duplicate forecast_desginator is already populated in l_forecast_designator_tbl
801 l_mtbf_value_exceeds := 'N'; -- To Indicates if MTBF Value exceeds for any of the Active counters of the Instance
802
806 fnd_log.string(fnd_log.level_statement,l_full_name,'p_end_date - '||to_char(p_end_date, 'DD-MON-YYYY HH24:MI:SS'));
803 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
804 fnd_log.string(fnd_log.level_statement,l_full_name,'Calling API AHL_UMP_PROCESSUNIT_PVT.Get_Forecasted_Counter_Values');
805 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_subject_id_tbl - ' ||l_dtls_subject_id_tbl(j));
807 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_object_id_tbl - ' ||l_dtls_object_id_tbl(j));
808 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_pos_ref_tbl - ' ||l_dtls_pos_ref_tbl(j));
809 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_inv_item_id_tbl - ' ||l_dtls_inv_item_id_tbl(j));
810 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_inv_master_org_id_tbl - ' ||l_dtls_inv_master_org_id_tbl(j));
811 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_inv_revision_tbl - ' ||l_dtls_inv_revision_tbl(j));
812 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_quantity_tbl - ' ||l_dtls_quantity_tbl(j));
813 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_srl_no_tbl - ' ||l_dtls_srl_no_tbl(j));
814 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_item_name_tbl - ' ||l_dtls_item_name_tbl(j));
815 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_item_desc_tbl - ' ||l_dtls_item_desc_tbl(j));
816 --Added by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
817 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_pos_ref_his_tbl - ' ||l_dtls_pos_ref_his_tbl(j));
818 END IF;
819
820 IF (p_concurrent_flag = 'Y') THEN
821 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Fetch Forecast Counter Value for Node in Context --');
822 END IF;
823
824 AHL_UMP_PROCESSUNIT_PVT.Get_Forecasted_Counter_Values(
825 x_return_status => l_return_status,
826 x_msg_data => l_msg_data,
827 x_msg_count => l_msg_count,
828 p_init_msg_list => FND_API.G_FALSE,
829 p_csi_item_instance_id => l_dtls_subject_id_tbl(j),
830 p_forecasted_date => l_fs_arrival_date_tbl(i),
831 x_counter_values_tbl => l_ctr_values_tbl);
832
833 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
834 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
835 fnd_log.string(fnd_log.level_statement,l_full_name,'Called API AHL_UMP_PROCESSUNIT_PVT.Get_Forecasted_Counter_Values Errored');
836 END IF;
837 IF (p_concurrent_flag = 'Y') THEN
838 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Error in getting Forecasted Counter Value for Node in Context --');
839 END IF;
840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
841 END IF;
842
843 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
844 fnd_log.string(fnd_log.level_statement,l_full_name,'Called API AHL_UMP_PROCESSUNIT_PVT.Get_Forecasted_Counter_Values After');
845 fnd_log.string(fnd_log.level_statement,l_full_name,'l_ctr_values_tbl - COUNT - '||l_ctr_values_tbl.COUNT);
846 fnd_log.string(fnd_log.level_statement,l_full_name,'l_return_status - '||l_return_status);
847 fnd_log.string(fnd_log.level_statement,l_full_name,'l_msg_data - '||l_msg_data);
848 fnd_log.string(fnd_log.level_statement,l_full_name,'l_msg_count - '||l_msg_count);
849
850 IF (fnd_msg_pub.count_msg > 0 ) THEN
851
852 FOR msg IN 1..fnd_msg_pub.count_msg LOOP
853
854 fnd_msg_pub.get(p_msg_index => msg,
855 p_encoded => FND_API.G_FALSE,
856 p_data => l_msg_data,
857 p_msg_index_out => l_msg_index_out);
858
859 fnd_log.string(fnd_log.level_statement,l_full_name,'UMP Returned - '||l_msg_data);
860
861 END LOOP;
862
863 END IF;
864
865 IF l_ctr_values_tbl.COUNT > 0 THEN
866 FOR u in l_ctr_values_tbl.FIRST .. l_ctr_values_tbl.LAST LOOP
867 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Returned From Get_Forecasted_Counter_Values - COUNTER_ID - '||l_ctr_values_tbl(u).COUNTER_ID);
868 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Returned From Get_Forecasted_Counter_Values - COUNTER_NAME - '||l_ctr_values_tbl(u).COUNTER_NAME);
869 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Returned From Get_Forecasted_Counter_Values - COUNTER_VALUE - '||l_ctr_values_tbl(u).COUNTER_VALUE);
873 fnd_log.string(fnd_log.level_statement,l_full_name,' - Deriving Active Counters - ');
870 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Returned From Get_Forecasted_Counter_Values - UOM_CODE - '||l_ctr_values_tbl(u).UOM_CODE);
871 END LOOP;
872 END IF;
874 END IF;
875
876 IF (p_concurrent_flag = 'Y') THEN
877 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Ater fetching the Foprecast Counter details for instance in context --');
878 END IF;
879
880 -- To hold the counter values.
881 -- Structure of l_ctr_values_tbl is
882 -- COUNTER_ID NUMBER
883 -- COUNTER_NAME VARCHAR2(80)
884 -- UOM_CODE VARCHAR2(3)
885 -- COUNTER_VALUE NUMBER
886
887 -- The below logic is as such ::::
888 -- Loop for Setup Counter Data -- A
889 -- For Each Since New/Since Overhaul in Setup Data Loop for Ctr Values returned from UMP API. -- B
890 -- If Since Overhaul counter value matches
891 -- retrieve corresponding ctr data returned from UMP API
892 -- Exit to Outer Loop - A
893 -- Elsif Since New counter value matches
894 -- retrieve corresponding ctr data returned from UMP API
895 -- Continue with inner loop - B
896 -- end if;
897 -- end loop; -- B
898 -- Increment counter assignment index and continue with outer loop
899 -- End Loop; - A
900
901 IF (p_concurrent_flag = 'Y') THEN
902 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Deriving Active Counters for the Item Instance - BEFORE -');
903 END IF;
904
905 IF (l_since_new_ctr_id_tbl.COUNT > 0 AND l_ctr_values_tbl.COUNT > 0) THEN
906 FOR m in l_since_new_ctr_id_tbl.FIRST .. l_since_new_ctr_id_tbl.LAST LOOP
907 FOR n in l_ctr_values_tbl.FIRST .. l_ctr_values_tbl.LAST LOOP
908
909 -- Fetching Corresponding Counter Template Id for the Counter Id Passed.
910 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
911 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_ctr_values_tbl.COUNTER_ID --- '||n||' - ' || l_ctr_values_tbl(n).COUNTER_ID);
912 END IF;
913
914 BEGIN
915 -- Bug Fix for 5296759
916 SELECT CREATED_FROM_COUNTER_TMPL_ID
917 INTO l_ctr_template_id
918 FROM csi_counters_vl
919 WHERE counter_id = l_ctr_values_tbl(n).COUNTER_ID
920 AND ( (CREATED_FROM_COUNTER_TMPL_ID IN (SELECT SINCE_NEW_COUNTER_ID
921 FROM AHL_RA_CTR_ASSOCIATIONS))
922 OR ( CREATED_FROM_COUNTER_TMPL_ID IN (SELECT SINCE_OVERHAUL_COUNTER_ID
923 FROM AHL_RA_CTR_ASSOCIATIONS)
924 AND EXISTS (SELECT 1
925 FROM CSI_COUNTER_READINGS
926 WHERE COUNTER_ID = l_ctr_values_tbl(n).COUNTER_ID
927 AND NET_READING IS NOT NULL
928 AND DISABLED_FLAG = 'N')
929 )
930 );
931
932 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
933 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_ctr_template_id --- '||l_ctr_template_id);
934 END IF;
935
936 EXCEPTION
937 WHEN OTHERS THEN
938 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
939 fnd_log.string(fnd_log.level_statement,l_full_name,' --- CSI Data Not Retrieved --- ');
940 END IF;
941 l_ctr_template_id := NULL;
942 NULL;
943 END;
944
945 IF l_ctr_template_id IS NOT NULL THEN
946 -- l_ctr_template_id can be NULL if
947 -- a. it is not defined as a since new counter
948 -- b. it is defined as a since overhaul counter but the counter reading is NULL
949
950 IF (l_since_oh_ctr_id_tbl(m) = l_ctr_template_id) THEN
954 l_active_ctr_value_tbl(l_ctr_index) := l_ctr_values_tbl(n).COUNTER_VALUE;
951 l_active_ctr_id_tbl(l_ctr_index) := l_ctr_values_tbl(n).COUNTER_ID;
952 l_active_ctr_temp_id_tbl(l_ctr_index) := l_ctr_template_id;
953 l_active_ctr_name_tbl(l_ctr_index) := l_ctr_values_tbl(n).COUNTER_NAME;
955 l_active_uom_code_tbl(l_ctr_index) := l_ctr_values_tbl(n).UOM_CODE;
956 EXIT; -- To outer Loop for Since New / Since Overhaul Counters
957 ELSIF (l_since_new_ctr_id_tbl(m) = l_ctr_template_id) THEN
958 l_active_ctr_id_tbl(l_ctr_index) := l_ctr_values_tbl(n).COUNTER_ID;
959 l_active_ctr_temp_id_tbl(l_ctr_index) := l_ctr_template_id;
960 l_active_ctr_name_tbl(l_ctr_index) := l_ctr_values_tbl(n).COUNTER_NAME;
961 l_active_ctr_value_tbl(l_ctr_index) := l_ctr_values_tbl(n).COUNTER_VALUE;
962 l_active_uom_code_tbl(l_ctr_index) := l_ctr_values_tbl(n).UOM_CODE;
963 END IF;
964
965 END IF;
966
967 END LOOP; -- For l_ctr_values_tbl -- n
968 l_ctr_index := l_ctr_index + 1; -- l_ctr_index is reset for each item instance
969 END LOOP; -- For l_since_new_ctr_id_tbl -- m
970
971 IF (p_concurrent_flag = 'Y') THEN
972 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Deriving Active Counters for the Item Instance - AFTER -');
973 END IF;
974
975 -- Active Counters are derived and Processing starts.
976 IF (l_active_ctr_id_tbl.COUNT > 0) THEN
977 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
978 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_active_ctr_id_tbl --- '||l_active_ctr_id_tbl.COUNT);
979 END IF;
980 FOR g in l_active_ctr_id_tbl.FIRST .. l_active_ctr_id_tbl.LAST LOOP
981 IF l_active_ctr_id_tbl.EXISTS(g) THEN
982 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
983 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_active_ctr_id_tbl --- '||g||' - ' || l_active_ctr_id_tbl(g));
984 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_active_ctr_temp_id_tbl --- '||g||' - ' || l_active_ctr_temp_id_tbl(g));
985 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_active_ctr_name_tbl --- '||g||' - ' || l_active_ctr_name_tbl(g));
986 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_active_ctr_value_tbl --- '||g||' - ' || l_active_ctr_value_tbl(g));
987 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_active_uom_code_tbl --- '||g||' - ' || l_active_uom_code_tbl(g));
988 END IF;
989 END IF;
990 END LOOP;
991
992 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
993 fnd_log.string(fnd_log.level_statement,l_full_name,' --- Bulk Fetching MTBF DATA --- ');
994 fnd_log.string(fnd_log.level_statement,l_full_name,' --- Position Reference --- '||l_dtls_pos_ref_tbl(j));
995 fnd_log.string(fnd_log.level_statement,l_full_name,' --- Item Id --- '||l_dtls_inv_item_id_tbl(j));
996 fnd_log.string(fnd_log.level_statement,l_full_name,' --- Item Org Id --- '||l_dtls_inv_master_org_id_tbl(j));
997 fnd_log.string(fnd_log.level_statement,l_full_name,' --- Item Revision --- '||l_dtls_inv_revision_tbl(j));
998 --Added by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
999 fnd_log.string(fnd_log.level_statement,l_full_name,' --- Pos Reference for Historical Flow --- ' ||l_dtls_pos_ref_his_tbl(j));
1000 END IF;
1001 IF (p_concurrent_flag = 'Y') THEN
1002 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Deriving MTBF Data for Active Counter if present - BEFORE -');
1003 END IF;
1004
1005 OPEN c_get_mtbf_data(l_dtls_pos_ref_tbl(j),
1006 l_dtls_inv_item_id_tbl(j),
1007 l_dtls_inv_master_org_id_tbl(j),
1008 l_dtls_inv_revision_tbl(j));
1009 FETCH c_get_mtbf_data
1010 BULK COLLECT INTO l_mtbf_ctr_id_tbl,
1011 l_mtbf_value_tbl;
1012 IF l_mtbf_ctr_id_tbl.COUNT = 0 THEN
1013 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1014 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- MTBF SETUP DATA IS NOT DEFINED ----');
1015 END IF;
1016 l_mtbf_data_defined := 'N';
1017 ELSE
1021 l_mtbf_data_defined := 'Y';
1018 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1019 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- MTBF SETUP DATA IS DEFINED ----');
1020 END IF;
1022 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1023 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_mtbf_ctr_id_tbl --COUNT-- '||l_mtbf_ctr_id_tbl.COUNT);
1024 FOR a in l_mtbf_ctr_id_tbl.FIRST .. l_mtbf_ctr_id_tbl.LAST LOOP
1025 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_mtbf_ctr_id_tbl --- '||l_mtbf_ctr_id_tbl(a));
1026 fnd_log.string(fnd_log.level_statement,l_full_name,' --- l_mtbf_value_tbl --- '||l_mtbf_value_tbl(a));
1027 END LOOP;
1028 END IF;
1029 END IF;
1030 CLOSE c_get_mtbf_data;
1031
1032 IF (p_concurrent_flag = 'Y') THEN
1033 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Deriving MTBF Data for Active Counter if present - BEFORE -');
1034 END IF;
1035
1036 IF l_mtbf_data_defined = 'Y' THEN
1037 -- Compare Current Counter Value of Item with MTBF data defined for the corresponding Counter
1038 -- If Value Exceeds, Derive the Forecast Designator using the Forecast Association Setup
1039
1040 IF (p_concurrent_flag = 'Y') THEN
1041 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Check in Ctr Value Exceeds MTBF Value');
1042 END IF;
1043
1044 FOR r in l_active_ctr_temp_id_tbl.FIRST .. l_active_ctr_temp_id_tbl.LAST LOOP
1045 IF l_active_ctr_temp_id_tbl.EXISTS(r) THEN
1046 IF l_mtbf_ctr_id_tbl.COUNT > 0 THEN -- Fall Safe case -- Never to be false
1047 FOR s in l_mtbf_ctr_id_tbl.FIRST .. l_mtbf_ctr_id_tbl.LAST LOOP
1048 IF (l_active_ctr_temp_id_tbl(r) = l_mtbf_ctr_id_tbl(s) AND
1049 l_active_ctr_value_tbl(r) >= l_mtbf_value_tbl(s)) THEN
1050 l_mtbf_value_exceeds := 'Y';
1051 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1052 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- MTBF VALUE EXCEEDS ----');
1053 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- l_active_ctr_temp_id_tbl ----'||l_active_ctr_temp_id_tbl(r));
1054 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- l_active_ctr_value_tbl ----'||l_active_ctr_value_tbl(r));
1055 END IF;
1056 EXIT;
1057 END IF;
1058 END LOOP;
1059 END IF;
1060 EXIT WHEN l_mtbf_value_exceeds = 'Y';
1061 END IF;
1062 END LOOP;
1063
1064 IF l_mtbf_value_exceeds = 'Y' THEN
1065
1066 IF (p_concurrent_flag = 'Y') THEN
1067 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- MTBF Defined - Fetching Forecast Designator - BEFORE');
1068 END IF;
1069
1070 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1071 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- MTBF SETUP DATA IS DEFINED --VALUE EXCEEDS--');
1072 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- Deriving Fct Designator ----');
1073 END IF;
1074
1075 BEGIN
1076 Select FORECAST_DESIGNATOR
1077 into l_fct_designator
1078 from AHL_RA_FCT_ASSOCIATIONS
1079 where ASSOCIATION_TYPE_CODE = 'ASSOC_MTBF'
1080 and ORGANIZATION_ID = l_fs_arr_org_id_tbl(i);
1081
1082 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1083 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- Derived Fct Designator ----'||l_fct_designator);
1084 END IF;
1085
1086 l_prob_value := 100;
1087
1088 EXCEPTION
1089 WHEN NO_DATA_FOUND THEN
1090 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1091 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- No Fct Designator Defined ----');
1095 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1092 END IF;
1093 l_fct_designator := null;
1094 WHEN TOO_MANY_ROWS THEN
1096 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- TOO_MANY_ROWS EXEC BLOCK -M- ORG_ID ----- '||l_fs_arr_org_id_tbl(i));
1097 END IF;
1098 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1099 WHEN OTHERS THEN
1100 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1101 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- OTHERS EXEC BLOCK -M- ORG_ID ----- '||l_fs_arr_org_id_tbl(i));
1102 END IF;
1103 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1104 END;
1105
1106 IF (p_concurrent_flag = 'Y') THEN
1107 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- MTBF Defined - Fetching Forecast Designator - AFTER ');
1108 END IF;
1109 ELSE
1110 l_fct_designator := null;
1111 IF (p_concurrent_flag = 'Y') THEN
1112 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Ctr Value Does not Exceed MTBF Value');
1113 END IF;
1114 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1115 fnd_log.string(fnd_log.level_statement,l_full_name,'RA Analyser Process ---- Ctr Value Does not Exceed MTBF Value');
1116 END IF;
1117 END IF;
1118
1119 ELSIF l_mtbf_data_defined = 'N' THEN
1120 -- Derive Probability of Failure of the Item using the Part Removal Data of the item.
1121 -- Derive this Probabilty for each Counter Assigned to the Item
1122 -- Derive the Forecast Designator using the Forecast Association Setup using the highest value
1123 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1124 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- MTBF SETUP DATA IS NOT DEFINED --Processing--');
1125 END IF;
1126 IF (p_concurrent_flag = 'Y') THEN
1127 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- MTBF Not Defined - Fetching Path Positions Ids for Instance - BEFORE');
1128 END IF;
1129
1130 --Modified by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
1131 --OPEN c_get_path_postions(l_dtls_pos_ref_tbl(j));
1132 OPEN c_get_path_postions(l_dtls_pos_ref_his_tbl(j));-- Derive all Path Positions for the Position Reference
1133 FETCH c_get_path_postions
1134 BULK COLLECT INTO l_path_position_id_tbl;
1135 IF l_path_position_id_tbl.COUNT = 0 THEN
1136 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1137 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- NO CORRESPONDING PATH POSITIONS FOUND ----');
1138 END IF;
1139 l_path_pos_exist_flag := 'N';
1140 ELSE
1141 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1142 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- CORRESPONDING PATH POSITIONS RETRIEVED ----');
1143 FOR pos_index in l_path_position_id_tbl.FIRST .. l_path_position_id_tbl.LAST LOOP
1144 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- l_path_position_id_tbl ----'||l_path_position_id_tbl(pos_index));
1145 END LOOP;
1146 END IF;
1147 l_path_pos_exist_flag := 'Y';
1148 END IF;
1149 CLOSE c_get_path_postions;
1150
1151 IF (p_concurrent_flag = 'Y') THEN
1152 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- MTBF Not Defined - Fetching Path Positions Ids for Instance - After');
1153 END IF;
1154
1155 IF l_path_pos_exist_flag = 'Y' THEN -- If NO then Move to Next Instance in Else Part
1156
1157 IF (p_concurrent_flag = 'Y') THEN
1158 fnd_file.put_line(fnd_file.log, 'Deriving Probability of Failure for Each Active Counter of Item Instance');
1159 END IF;
1160
1161 FOR p in l_active_ctr_id_tbl.FIRST .. l_active_ctr_id_tbl.LAST LOOP
1165 fnd_log.string(fnd_log.level_statement,l_full_name,' ----Active Counter Value -- A --'||l_active_ctr_value_tbl(p));
1162 IF l_active_ctr_id_tbl.EXISTS(p) THEN
1163 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1164 fnd_log.string(fnd_log.level_statement,l_full_name,' ----Looping for active counters --'||l_active_ctr_id_tbl(p));
1166 END IF;
1167
1168 l_prob_attrib_a := 0; -- Indicates Current Value of Counter
1169 l_prob_attrib_b := 0; -- Number of items per position that failed with counter values less than or equal to A from CMRO's unscheduled removals
1170 l_prob_attrib_c := 0; -- Total number of failed item per position that match the defined removal codes and status
1171 l_prob_attrib_d := 0; -- Number of installed serviceable items per position with current counter values > A
1172 l_prob_value_tmp := 0;
1173
1174 -- Note for B and C
1175 -- They have to be derived individually for each position path id mapped to the
1176 -- postion reference and then summed up to get the final values.
1177
1178 l_prob_attrib_a := l_active_ctr_value_tbl(p);
1179
1180 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1181 fnd_log.string(fnd_log.level_statement,l_full_name,' ----deriving l_prob_attrib_c_tmp ----');
1182 END IF;
1183
1184 IF l_path_position_id_tbl.COUNT > 0 THEN -- This should always be true due to l_path_pos_exist_flag = 'Y' check above
1185 FOR q in l_path_position_id_tbl.FIRST .. l_path_position_id_tbl.LAST LOOP
1186 -- These tmp variables store the Removal History Data for Each Path Position
1187 -- They are summed into l_prob_attrib_b and l_prob_attrib_c
1188 l_prob_attrib_b_tmp := 0;
1189 l_prob_attrib_c_tmp := 0;
1190
1191 IF (p_concurrent_flag = 'Y') THEN
1192 fnd_file.put_line(fnd_file.log, 'Deriving Attrib B and C for Each Path Position id mappip to Item Relationship Id');
1193 END IF;
1194
1195 -- For deriving l_prob_attrib_c_tmp
1196 Select count(*)
1197 into l_prob_attrib_c_tmp
1198 from (Select chg.removed_instance_id
1199 from ahl_part_changes chg,
1200 csi_item_instances cii,
1201 ahl_prd_dispositions_b dis
1202 where chg.part_change_type IN ('R','S')
1203 and chg.part_change_id = dis.part_change_id
1204 and chg.removal_code in (Select Removal_Code from AHL_RA_SETUPS where setup_code = 'REMOVAL_CODE')
1205 and dis.condition_id in (Select Status_Id from AHL_RA_SETUPS where setup_code = 'ITEM_STATUS')
1206 and chg.mc_relationship_id = to_number(l_path_position_id_tbl(q))
1207 AND cii.instance_id = chg.removed_instance_id
1208 AND cii.inventory_item_id = l_dtls_inv_item_id_tbl(j)
1209 AND cii.inv_master_organization_id = l_dtls_inv_master_org_id_tbl(j)) query_c;
1210
1211 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1212 fnd_log.string(fnd_log.level_statement,l_full_name,' ----derived l_prob_attrib_c_tmp ----' || l_prob_attrib_c_tmp);
1213 fnd_log.string(fnd_log.level_statement,l_full_name,' ----deriving l_prob_attrib_b_tmp ----');
1214 END IF;
1215
1216 -- For deriving l_prob_attrib_b_tmp
1217 -- If l_prob_attrib_c_tmp = 0, it indicates that part removal history contains no data
1218 -- irrespective of counter values -- so no need to derive l_prob_attrib_b_tmp
1219 IF l_prob_attrib_c_tmp > 0 THEN
1220
1221 Select count(*)
1222 into l_prob_attrib_b_tmp
1223 from (Select chg.removed_instance_id
1224 ,ctr.net_reading
1225 from ahl_part_changes chg,(Select assoc.source_object_id,
1229 from csi_counter_associations assoc,
1226 cv.net_reading,
1227 cv.VALUE_TIMESTAMP,
1228 cv.counter_id
1230 csi_counter_readings cv,
1231 csi_counters_vl cb1,
1232 csi_counters_vl cb2
1233 where assoc.source_object_code = 'CP'
1234 and assoc.counter_id = cb2.counter_id
1235 and cb1.counter_id = l_active_ctr_id_tbl(p)
1236 and cb1.CREATED_FROM_COUNTER_TMPL_ID = cb2.CREATED_FROM_COUNTER_TMPL_ID
1237 and cv.counter_id = cb2.counter_id
1238 AND cv.disabled_flag = 'N') ctr,
1239 csi_item_instances cii,
1240 ahl_prd_dispositions_b dis
1241 where chg.part_change_type IN ('R','S')
1242 and chg.part_change_id = dis.part_change_id
1243 AND chg.removed_instance_id = ctr.source_object_id
1244 and chg.removal_code in (Select Removal_Code from AHL_RA_SETUPS where setup_code = 'REMOVAL_CODE')
1245 and dis.condition_id in (Select Status_Id from AHL_RA_SETUPS where setup_code = 'ITEM_STATUS')
1246 and chg.mc_relationship_id = to_number(l_path_position_id_tbl(q))
1247 AND ctr.value_timestamp = (Select max(maxcv.value_timestamp)
1248 from csi_counter_readings maxcv
1249 where ctr.counter_id = maxcv.counter_id
1250 and trunc(maxcv.value_timestamp) <= trunc(chg.REMOVAL_DATE))
1251 AND cii.instance_id = chg.removed_instance_id
1252 AND cii.inventory_item_id = l_dtls_inv_item_id_tbl(j)
1253 AND cii.inv_master_organization_id = l_dtls_inv_master_org_id_tbl(j)) query_b
1254 where l_prob_attrib_a > query_b.net_reading;
1255
1256 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1257 fnd_log.string(fnd_log.level_statement,l_full_name,' ----derived l_prob_attrib_b_tmp as ----'||l_prob_attrib_b_tmp);
1258 END IF;
1259
1260 ELSE
1261
1262 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1263 fnd_log.string(fnd_log.level_statement,l_full_name,' ----derived l_prob_attrib_b_tmp as 0 since l_prob_attrib_c is 0 ----');
1264 END IF;
1265
1266 IF (p_concurrent_flag = 'Y') THEN
1267 fnd_file.put_line(fnd_file.log, 'Setting Attrib B to 0 as C is also 0');
1268 END IF;
1269
1270 l_prob_attrib_b_tmp := 0;
1271 END IF;
1272
1273 IF (p_concurrent_flag = 'Y') THEN
1274 fnd_file.put_line(fnd_file.log, 'Summing Up B and C retrieved for Each Path Position Id');
1275 END IF;
1276
1277 l_prob_attrib_b := l_prob_attrib_b + l_prob_attrib_b_tmp;
1278 l_prob_attrib_c := l_prob_attrib_c + l_prob_attrib_c_tmp;
1279
1280 END LOOP; -- l_path_position_id_tbl -- q
1281
1282 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1283 fnd_log.string(fnd_log.level_statement,l_full_name,' ----deriving l_prob_attrib_d_tmp ----');
1284 END IF;
1285 IF (p_concurrent_flag = 'Y') THEN
1286 fnd_file.put_line(fnd_file.log, 'Derive Attribute D for Probability');
1287 END IF;
1288
1292 FROM csi_ii_relationships CIIR,
1289 -- For deriving l_prob_attrib_d
1290 SELECT count(*)
1291 INTO l_prob_attrib_d
1293 csi_item_instances cii,
1294 (SELECT assoc.source_object_id,
1295 cv.net_reading,
1296 cv.VALUE_TIMESTAMP
1297 FROM csi_counter_associations assoc,
1298 csi_counter_readings cv,
1299 csi_counters_vl cb1,
1300 csi_counters_vl cb2
1301 WHERE assoc.source_object_code = 'CP'
1302 AND assoc.counter_id = cb2.counter_id
1303 AND cb1.counter_id = l_active_ctr_id_tbl(p)
1304 AND cb1.CREATED_FROM_COUNTER_TMPL_ID = cb2.CREATED_FROM_COUNTER_TMPL_ID
1305 AND cv.counter_id = cb2.counter_id
1306 AND cv.value_timestamp = (Select max(value_timestamp)
1307 from csi_counter_readings maxcv
1308 where cv.counter_id = maxcv.counter_id)
1309 AND cv.disabled_flag = 'N') ctr
1310 WHERE cii.instance_id = CIIR.subject_id
1311 AND CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1312 AND CII.inv_master_organization_id = l_dtls_inv_master_org_id_tbl(j)
1313 --Modified by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
1314 --AND ciir.position_reference = l_dtls_pos_ref_tbl(j)
1315 AND ciir.position_reference = l_dtls_pos_ref_his_tbl(j)
1316 AND ctr.net_reading > l_prob_attrib_a
1317 AND ctr.source_object_id = cii.instance_id -- CIIR.subject_id -- Perf Fix 4777658
1318 AND ciir.relationship_type_code = 'COMPONENT-OF'
1319 AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1320 AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1));
1321
1322 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1323 fnd_log.string(fnd_log.level_statement,l_full_name,' ----derived l_prob_attrib_d as ----'|| l_prob_attrib_d);
1324 END IF;
1325
1326 IF (p_concurrent_flag = 'Y') THEN
1327 fnd_file.put_line(fnd_file.log, 'Derive Probability using B C and D');
1328 END IF;
1329
1330 IF ((l_prob_attrib_c + l_prob_attrib_d) > 0) THEN
1331 l_prob_value_tmp := (l_prob_attrib_b)/(l_prob_attrib_c + l_prob_attrib_d);
1332 l_prob_value_tmp := l_prob_value_tmp * 100;
1333 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1334 fnd_log.string(fnd_log.level_statement,l_full_name,' ----denom > 0 .. compute temp prob ----'||l_prob_value_tmp);
1335 END IF;
1336 ELSE
1337 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1338 fnd_log.string(fnd_log.level_statement,l_full_name,' ----denom = 0 .. set temp prob to 0----');
1339 END IF;
1340 l_prob_value_tmp := 0;
1341 END IF;
1342
1343 IF (p_concurrent_flag = 'Y') THEN
1344 fnd_file.put_line(fnd_file.log, 'Retain Probability if Higher than Previous Value');
1345 END IF;
1346
1347 IF l_prob_value_tmp > l_prob_value THEN
1348 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1349 fnd_log.string(fnd_log.level_statement,l_full_name,' ----higher value of prob found .. Set final value ----'||l_prob_value_tmp);
1350 END IF;
1351 l_prob_value := l_prob_value_tmp;
1352 IF (p_concurrent_flag = 'Y') THEN
1353 fnd_file.put_line(fnd_file.log, 'Retained Probability');
1354 END IF;
1355 END IF;
1359 fnd_log.string(fnd_log.level_statement,l_full_name,' ----No Path Position Found -- so ----');
1356 ELSE -- l_path_position_id_tbl.COUNT = 0
1357 -- This should never be true due to l_path_pos_exist_flag = 'Y' check above
1358 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1360 fnd_log.string(fnd_log.level_statement,l_full_name,' ----Exit Counter Loop Jump to Next Item ----');
1361 fnd_log.string(fnd_log.level_statement,l_full_name,' ----This Code Should not be executed -- Extra redundant check ----');
1362 END IF;
1363 IF (p_concurrent_flag = 'Y') THEN
1364 fnd_file.put_line(fnd_file.log, ' ----No Path Position Found -- so ----');
1365 fnd_file.put_line(fnd_file.log, ' ----Exit Counter Loop Jump to Next Item ----');
1366 END IF;
1367 EXIT;
1368 END IF; -- l_path_position_id_tbl.COUNT > 0
1369 END IF; -- l_active_ctr_id_tbl.EXISTS(p)
1370 END LOOP; -- l_active_ctr_id_tbl -- p --
1371
1372 -- Derive Forecast Designator from the forecast association setup data
1373 -- for the Probability Data
1374 -- The Fetch for Forecast Designator is Unconditional because of the assumption
1375 -- that the user may also define a Forecast Association for zero probability.
1376 IF l_path_position_id_tbl.COUNT > 0 THEN -- redundant check -- for readability
1377
1378 --Fix for the Bug 5480658, Added by mpothuku on 28th August, 06
1379 --If the probability is derived as zero, the record should not be interfaced
1380 --irrespective of whether a FD for zero is defined.
1381 IF l_prob_value > 0 THEN
1382
1383 BEGIN
1384 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1385 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- Deriving Fct Designator -- for historical--');
1386 END IF;
1387 IF (p_concurrent_flag = 'Y') THEN
1388 fnd_file.put_line(fnd_file.log, ' ----No MTBF Data Found -- Retreive Forecast Designator ----');
1389 END IF;
1390 Select FORECAST_DESIGNATOR
1391 into l_fct_designator
1392 from AHL_RA_FCT_ASSOCIATIONS
1393 where ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL'
1394 and PROBABILITY_FROM <= l_prob_value
1395 and ( PROBABILITY_TO > l_prob_value
1399 WHEN NO_DATA_FOUND THEN
1396 OR (l_prob_value = 100 AND PROBABILITY_TO >= l_prob_value))
1397 and ORGANIZATION_ID = l_fs_arr_org_id_tbl(i);
1398 EXCEPTION
1400 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1401 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- No Fct Designator Defined ----');
1402 END IF;
1403 IF (p_concurrent_flag = 'Y') THEN
1404 fnd_file.put_line(fnd_file.log, ' ----No MTBF Data Found -- No Forecast Designator Found ----');
1405 END IF;
1406 l_fct_designator := null;
1407 WHEN TOO_MANY_ROWS THEN
1408 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1409 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- TOO_MANY_ROWS EXEC BLOCK -H- ORG_ID ----- '||l_fs_arr_org_id_tbl(i));
1410 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- TOO_MANY_ROWS EXEC BLOCK -H- l_prob_value ----- '||l_prob_value);
1411 END IF;
1412 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1413 WHEN OTHERS THEN
1414 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1415 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- OTHERS EXEC BLOCK -H- ORG_ID ----- '||l_fs_arr_org_id_tbl(i));
1416 fnd_log.string(fnd_log.level_statement,l_full_name,' ----- OTHERS EXEC BLOCK -H- l_prob_value ----- '||l_prob_value);
1417 END IF;
1418 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1419 END;
1420 ELSE
1421 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1422 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- l_prob_value -- for historical--'||l_prob_value);
1423 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- Since the derived Prob Value is Zero -- Assigning null to l_fct_designator--');
1424 END IF;
1425 l_fct_designator := null;
1426 END IF; --IF l_prob_value > 0
1427
1428 ELSE -- This Code Should not be executed -- Extra redundant check
1429 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1430 fnd_log.string(fnd_log.level_statement,l_full_name,' ----No Path Position Found -- so ----');
1434 IF (p_concurrent_flag = 'Y') THEN
1431 fnd_log.string(fnd_log.level_statement,l_full_name,' ----Set Fct Assoc Designator to NULL and Continue ----');
1432 fnd_log.string(fnd_log.level_statement,l_full_name,' ----This Code Should not be executed -- Extra redundant check ----');
1433 END IF;
1435 fnd_file.put_line(fnd_file.log, ' ----No Path Position Found -- so ----');
1436 fnd_file.put_line(fnd_file.log, ' ----Set Fct Assoc Designator to NULL and Continue ----');
1437 END IF;
1438 l_fct_designator := null;
1439 END IF;
1440 ELSE
1441 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1442 fnd_log.string(fnd_log.level_statement,l_full_name,' ----No Path Position Found -- First Check -- so bypass ----');
1443 fnd_log.string(fnd_log.level_statement,l_full_name,' ----Set Fct Assoc Designator to NULL and Continue ----');
1444 END IF;
1445 IF (p_concurrent_flag = 'Y') THEN
1446 fnd_file.put_line(fnd_file.log, ' ----No Path Position Found -- First Check -- so bypass ----');
1447 END IF;
1448 l_fct_designator := null;
1449 END IF; -- l_path_pos_exist_flag = 'Y'
1450 END IF; -- l_mtbf_data_defined -- Y or N
1451
1452 IF l_fct_designator IS NOT NULL THEN
1453 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1454 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Prepare IO Interface Data -- ');
1455 fnd_log.string(fnd_log.level_statement,l_full_name,' -- l_fct_designator -- ' || l_fct_designator);
1456 END IF;
1457 IF (p_concurrent_flag = 'Y') THEN
1458 fnd_file.put_line(fnd_file.log, ' -- Preparing IO Interface Data -- ');
1459 END IF;
1460
1461 l_forecast_interface_tbl(l_fct_index).INVENTORY_ITEM_ID := l_dtls_inv_item_id_tbl(j);
1462 l_forecast_interface_tbl(l_fct_index).FORECAST_DESIGNATOR := l_fct_designator;
1463 l_forecast_interface_tbl(l_fct_index).ORGANIZATION_ID := l_fs_arr_org_id_tbl(i);
1464 l_forecast_interface_tbl(l_fct_index).FORECAST_DATE := l_fs_arrival_date_tbl(i);
1465 l_forecast_interface_tbl(l_fct_index).LAST_UPDATE_DATE := sysdate;
1469 l_forecast_interface_tbl(l_fct_index).LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
1466 l_forecast_interface_tbl(l_fct_index).LAST_UPDATED_BY := fnd_global.USER_ID;
1467 l_forecast_interface_tbl(l_fct_index).CREATION_DATE := sysdate;
1468 l_forecast_interface_tbl(l_fct_index).CREATED_BY := fnd_global.USER_ID;
1470 l_forecast_interface_tbl(l_fct_index).QUANTITY := l_dtls_quantity_tbl(j);
1471 l_forecast_interface_tbl(l_fct_index).PROCESS_STATUS := 2;
1472 l_forecast_interface_tbl(l_fct_index).CONFIDENCE_PERCENTAGE := 100;
1473 l_forecast_interface_tbl(l_fct_index).COMMENTS := null;
1474 l_forecast_interface_tbl(l_fct_index).ERROR_MESSAGE := null;
1475 l_forecast_interface_tbl(l_fct_index).REQUEST_ID := null;
1476 l_forecast_interface_tbl(l_fct_index).PROGRAM_APPLICATION_ID := null;
1477 l_forecast_interface_tbl(l_fct_index).PROGRAM_ID := null;
1478 l_forecast_interface_tbl(l_fct_index).PROGRAM_UPDATE_DATE := null;
1479 l_forecast_interface_tbl(l_fct_index).WORKDAY_CONTROL := 3; -- shift backward.
1480 l_forecast_interface_tbl(l_fct_index).BUCKET_TYPE := 1;
1481 l_forecast_interface_tbl(l_fct_index).FORECAST_END_DATE := null;
1482 l_forecast_interface_tbl(l_fct_index).TRANSACTION_ID := null;
1483 l_forecast_interface_tbl(l_fct_index).SOURCE_CODE := 'RA-'||l_fs_unit_sch_id_tbl(i);
1484 l_forecast_interface_tbl(l_fct_index).SOURCE_LINE_ID := l_dtls_subject_id_tbl(j);
1485 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE1 := null;
1486 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE2 := null;
1487 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE3 := null;
1488 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE4 := null;
1489 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE5 := null;
1490 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE6 := null;
1491 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE7 := null;
1492 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE8 := null;
1493 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE9 := null;
1494 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE10 := null;
1495 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE11 := null;
1496 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE12 := null;
1497 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE13 := null;
1498 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE14 := null;
1499 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE15 := null;
1500 l_forecast_interface_tbl(l_fct_index).PROJECT_ID := null;
1501 l_forecast_interface_tbl(l_fct_index).TASK_ID := null;
1502 l_forecast_interface_tbl(l_fct_index).LINE_ID := null;
1503 l_forecast_interface_tbl(l_fct_index).ACTION := 'I';
1504 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE_CATEGORY := null;
1505
1506 IF (p_concurrent_flag = 'Y') THEN
1507 fnd_file.put_line(fnd_file.log, ' -- Preparing Designator Interface Data -- ');
1508 END IF;
1509 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1510 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Preparing Designator Interface Data -- ');
1511 END IF;
1512
1513 IF l_forecast_designator_tbl.COUNT > 0 THEN
1514 FOR d in l_forecast_designator_tbl.FIRST .. l_forecast_designator_tbl.LAST LOOP
1515 IF l_forecast_designator_tbl(d).FORECAST_DESIGNATOR = l_fct_designator THEN
1516 l_ds_rec_found := 'Y';
1517 IF (p_concurrent_flag = 'Y') THEN
1518 fnd_file.put_line(fnd_file.log, ' -- Designator already exists in Interface Data -- ');
1519 END IF;
1520 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1521 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Designator already exists in Interface Data -- ');
1522 END IF;
1523 EXIT;
1524 END IF;
1525 END LOOP;
1526 IF l_ds_rec_found = 'N' THEN
1530 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1527 IF (p_concurrent_flag = 'Y') THEN
1528 fnd_file.put_line(fnd_file.log, ' -- Designator does not exists in Interface Data -- ');
1529 END IF;
1531 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Designator does not exists in Interface Data -- ');
1532 END IF;
1533 l_forecast_designator_tbl(l_dsg_index).ORGANIZATION_ID := l_fs_arr_org_id_tbl(i);
1534 l_forecast_designator_tbl(l_dsg_index).FORECAST_DESIGNATOR := l_fct_designator;
1535 l_dsg_index := l_dsg_index + 1;
1536 END IF;
1537 ELSE
1538 IF (p_concurrent_flag = 'Y') THEN
1539 fnd_file.put_line(fnd_file.log, ' -- Inserting first rec in Designator Data -- ');
1540 END IF;
1541 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1542 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Insertinf first rec in Designator Data -- ');
1543 END IF;
1544 l_forecast_designator_tbl(l_dsg_index).ORGANIZATION_ID := l_fs_arr_org_id_tbl(i);
1545 l_forecast_designator_tbl(l_dsg_index).FORECAST_DESIGNATOR := l_fct_designator;
1546 l_dsg_index := l_dsg_index + 1;
1547 END IF;
1548
1549 -- Reset l_ds_rec_found as it is used for Dummy Recs below.
1550 l_ds_rec_found := 'N';
1551
1552 l_forecast_org_code_tbl(l_fct_index) := l_fs_org_code_tbl(i);
1553 l_forecast_srl_no_tbl(l_fct_index) := l_dtls_srl_no_tbl(j);
1554 l_forecast_item_name_tbl(l_fct_index):= l_dtls_item_name_tbl(j);
1555 l_forecast_item_desc_tbl(l_fct_index):= l_dtls_item_desc_tbl(j);
1556 l_forecast_req_prob_tbl(l_fct_index):= l_prob_value/100;
1557
1558 MSC_ATP_GLOBAL.Extend_ATP(l_atp_rec, x_return_status);
1559
1560 MSC_ATP_GLOBAL.GET_ATP_SESSION_ID(l_session_id,l_return_status);
1561
1562 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1563 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1564 fnd_log.string(fnd_log.level_statement,l_full_name,'Called API MSC_ATP_GLOBAL.GET_SESSION_ID U Errored');
1565 END IF;
1566 IF (p_concurrent_flag = 'Y') THEN
1567 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Error in getting on Session Id --');
1568 END IF;
1569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1570 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1571 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1572 fnd_log.string(fnd_log.level_statement,l_full_name,'Called API MSC_ATP_GLOBAL.GET_SESSION_ID E Errored');
1573 END IF;
1574 IF (p_concurrent_flag = 'Y') THEN
1575 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Error in getting on Session Id');
1576 END IF;
1577 RAISE FND_API.G_EXC_ERROR;
1578 END IF;
1579
1580 --Assign values to input record for ATP api.
1581 l_atp_rec.Inventory_Item_Id := Mrp_Atp_Pub.number_arr(l_dtls_inv_item_id_tbl(j));
1582 l_atp_rec.Organization_Id := Mrp_Atp_Pub.number_arr(l_fs_arr_org_id_tbl(i));
1583 l_atp_rec.Source_Organization_Id := Mrp_Atp_Pub.number_arr(l_fs_arr_org_id_tbl(i));
1584 l_atp_rec.Requested_Ship_Date := Mrp_Atp_Pub.date_arr(to_date(l_fs_arrival_date_tbl(i),'DD-MM-YYYY'));
1585 l_atp_rec.Quantity_Ordered := Mrp_Atp_Pub.number_arr(l_dtls_quantity_tbl(j));
1586 l_atp_rec.Quantity_UOM := Mrp_Atp_Pub.char3_arr(l_dtls_uom_tbl(j));
1587 l_atp_rec.Calling_Module := Mrp_Atp_Pub.number_arr(867);
1588 l_atp_rec.Action := Mrp_Atp_Pub.number_arr(100);
1589 l_atp_rec.override_flag := Mrp_Atp_Pub.char1_arr('N');
1590
1591 SELECT mrp_atp_schedule_temp_s.NEXTVAL
1592 INTO l_dummy_identifier
1593 from dual;
1594
1595 l_atp_rec.Identifier := Mrp_Atp_Pub.number_arr(l_dummy_identifier);
1596
1597 IF (p_concurrent_flag = 'Y') THEN
1598 fnd_file.put_line(fnd_file.log, ' - B - Calling ATP API -- ' || l_dtls_inv_item_id_tbl(j));
1602 END IF;
1599 END IF;
1600 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1601 fnd_log.string(fnd_log.level_statement,l_full_name,' -B- Calling ATP API -- ' || l_dtls_inv_item_id_tbl(j));
1603
1604 MRP_ATP_PUB.Call_ATP (l_session_id,
1605 l_atp_rec,
1606 x_atp_rec ,
1607 x_atp_supply_demand ,
1608 x_atp_period,
1609 x_atp_details,
1610 l_return_status,
1611 l_msg_data,
1612 l_msg_count
1613 );
1614
1615 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1616 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1617 fnd_log.string(fnd_log.level_statement,l_full_name,'Called API MRP_ATP_PUB.Call_ATP U Errored');
1618 END IF;
1619 IF (p_concurrent_flag = 'Y') THEN
1620 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Error in getting on Hand Quantity --' || l_session_id);
1621 END IF;
1622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1623 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
1624 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1625 fnd_log.string(fnd_log.level_statement,l_full_name,'Called API MRP_ATP_PUB.Call_ATP E Errored');
1626 END IF;
1627 IF (p_concurrent_flag = 'Y') THEN
1628 fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- Error in getting on Hand Quantity --' || l_session_id);
1629 END IF;
1630 RAISE FND_API.G_EXC_ERROR;
1631 END IF;
1632
1633 IF (p_concurrent_flag = 'Y') THEN
1634 fnd_file.put_line(fnd_file.log, ' - A - Calling ATP API -- ' || x_atp_rec.Requested_Date_Quantity(1));
1635 END IF;
1636 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1637 fnd_log.string(fnd_log.level_statement,l_full_name,' -A- Calling ATP API -- ' || l_dtls_inv_item_id_tbl(j));
1638 END IF;
1639
1640 l_forecast_onhand_qty_tbl(l_fct_index) := nvl(x_atp_rec.Requested_Date_Quantity(1),0);
1641
1642 SELECT COUNT(*)
1643 INTO l_forecast_osp_qty_tbl(l_fct_index)
1644 FROM AHL_OSP_ORDER_LINES_V OSPL,
1645 AHL_OSP_ORDERS_B OSP
1646 WHERE OSPL.OSP_ORDER_ID = OSP.OSP_ORDER_ID
1647 AND OSP.STATUS_CODE <> 'CLOSED'
1648 AND INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1649 AND OSPL.INVENTORY_ORG_ID= l_fs_arr_org_id_tbl(i)
1650 AND NVL(TRUNC(OSPL.NEED_BY_DATE), FND_API.G_MISS_DATE) = l_fs_arrival_date_tbl(i);
1651
1652 IF (p_concurrent_flag = 'Y') THEN
1653 fnd_file.put_line(fnd_file.log, ' - B - Deriving VWP Figure -- ');
1654 END IF;
1655 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1656 fnd_log.string(fnd_log.level_statement,l_full_name,' - B - Deriving VWP Figure -- ');
1657 fnd_log.string(fnd_log.level_statement,l_full_name,' - B - Param - Item ID -- '||l_dtls_inv_item_id_tbl(j));
1658 fnd_log.string(fnd_log.level_statement,l_full_name,' - B - Param - Item Org ID -- '||l_dtls_inv_master_org_id_tbl(j));
1662 END IF;
1659 fnd_log.string(fnd_log.level_statement,l_full_name,' - B - Param - Item Revision -- '||l_dtls_inv_revision_tbl(j));
1660 fnd_log.string(fnd_log.level_statement,l_full_name,' - B - Param - Arrival Org -- '||l_fs_arr_org_id_tbl(i));
1661 fnd_log.string(fnd_log.level_statement,l_full_name,' - B - Param - Arrival Date -- '||l_fs_arrival_date_tbl(i));
1663
1664 Select nvl(sum(nvl(QTY_GRP,0)),0) QTY
1665 INTO l_forecast_vwp_qty_tbl(l_fct_index)
1666 FROM (
1667 Select DISTINCT ITEM_INSTANCE AS ITEM_INSTANCE_GRP,
1668 VISIT_QUANTITY QTY_GRP
1669 From (
1670 -- Total Quantity from UC Tree in a Visit.
1671 SELECT CII.INSTANCE_ID AS ITEM_INSTANCE,
1672 CII.QUANTITY AS VISIT_QUANTITY
1673 FROM CSI_II_RELATIONSHIPS CIIR,
1674 CSI_ITEM_INSTANCES CII
1675 WHERE CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1676 AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
1677 AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
1678 AND CII.INSTANCE_ID = CIIR.SUBJECT_ID
1679 START WITH CIIR.OBJECT_ID IN (
1680 Select DISTINCT Visit.ITEM_INSTANCE_ID
1681 from AHL_VISITS_B Visit,
1682 AHL_SIMULATION_PLANS_B SPL
1683 Where Visit.unit_Schedule_id is NULL
1684 AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
1685 AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
1686 AND SPL.PRIMARY_PLAN_FLAG = 'Y'
1687 AND Visit.organization_id = l_fs_arr_org_id_tbl(i)
1688 AND l_fs_arrival_date_tbl(i) between TRUNC(Visit.START_DATE_TIME) AND TRUNC(Visit.CLOSE_DATE_TIME)
1689 )
1690 AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1691 AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1692 AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
1693 CONNECT BY PRIOR CIIR.SUBJECT_ID = CIIR.OBJECT_ID
1694 AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1695 AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1696 AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
1697
1698 UNION ALL
1699
1700 -- Total Quantity from Root Node of a Visit.
1701
1702 Select Visit.ITEM_INSTANCE_ID AS ITEM_INSTANCE,
1703 CII.QUANTITY AS VISIT_QUANTITY
1704 from AHL_VISITS_B Visit,
1708 AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
1705 AHL_SIMULATION_PLANS_B SPL,
1706 CSI_ITEM_INSTANCES CII
1707 Where Visit.unit_Schedule_id is NOT NULL
1709 AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
1710 AND SPL.PRIMARY_PLAN_FLAG = 'Y'
1711 AND Visit.organization_id = l_fs_arr_org_id_tbl(i)
1712 AND l_fs_arrival_date_tbl(i) between TRUNC(Visit.START_DATE_TIME) AND TRUNC(Visit.CLOSE_DATE_TIME)
1713 AND Visit.ITEM_INSTANCE_ID = CII.INSTANCE_ID
1714 AND CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1715 AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
1716 AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
1717
1718 UNION ALL
1719
1720 -- Total Quantity from Visit, which does not have a unit at the header level.
1721 -- Without Tree Reversal
1722 Select CII.INSTANCE_ID AS ITEM_INSTANCE,
1723 CII.QUANTITY AS VISIT_QUANTITY
1724 from AHL_VISIT_TASKS_B TASKS,
1725 AHL_VISITS_B Visit,
1726 AHL_SIMULATION_PLANS_B SPL,
1727 CSI_ITEM_INSTANCES CII
1728 Where Visit.VISIT_ID = TASKS.Visit_id
1729 AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
1730 AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
1731 AND SPL.PRIMARY_PLAN_FLAG = 'Y'
1732 AND Visit.unit_Schedule_id is NULL
1733 AND Visit.organization_id = l_fs_arr_org_id_tbl(i)
1734 AND l_fs_arrival_date_tbl(i) between TRUNC(Visit.START_DATE_TIME) AND TRUNC(Visit.CLOSE_DATE_TIME)
1735 AND TASKS.INSTANCE_ID = CII.INSTANCE_ID
1736 AND CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1737 AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
1738 AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
1739
1740 UNION ALL
1741
1742 -- Total Quantity from Visit, which does not have a unit at the header level.
1743 -- With Tree Reversal
1744 SELECT CII.INSTANCE_ID AS ITEM_INSTANCE,
1745 CII.QUANTITY AS VISIT_QUANTITY
1746 FROM CSI_II_RELATIONSHIPS CIIR,
1747 CSI_ITEM_INSTANCES CII
1748 WHERE CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1749 AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
1750 AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
1751 AND CII.INSTANCE_ID = CIIR.SUBJECT_ID
1752 START WITH CIIR.OBJECT_ID IN (
1753 Select CII.INSTANCE_ID AS ITEM_INSTANCE
1754 from AHL_VISIT_TASKS_B TASKS,
1755 AHL_VISITS_B Visit,
1756 AHL_SIMULATION_PLANS_B SPL,
1757 CSI_ITEM_INSTANCES CII
1758 Where Visit.VISIT_ID = TASKS.Visit_id
1759 AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
1760 AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
1761 AND SPL.PRIMARY_PLAN_FLAG = 'Y'
1762 AND Visit.unit_Schedule_id is NULL
1766 AND CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1763 AND Visit.organization_id = l_fs_arr_org_id_tbl(i)
1764 AND l_fs_arrival_date_tbl(i) between TRUNC(Visit.START_DATE_TIME) AND TRUNC(Visit.CLOSE_DATE_TIME)
1765 AND TASKS.INSTANCE_ID = CII.INSTANCE_ID
1767 AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
1768 AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
1769 )
1770 AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1771 AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1772 AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
1773 CONNECT BY PRIOR CIIR.SUBJECT_ID = CIIR.OBJECT_ID
1774 AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1775 AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
1776 AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
1777
1778 )
1779 );
1780
1781 IF (p_concurrent_flag = 'Y') THEN
1782 fnd_file.put_line(fnd_file.log, ' - A - Deriving VWP Figure -- ' || l_forecast_vwp_qty_tbl(l_fct_index));
1783 END IF;
1784 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1785 fnd_log.string(fnd_log.level_statement,l_full_name,' - A - Deriving VWP Figure -- ' || l_forecast_vwp_qty_tbl(l_fct_index));
1786 END IF;
1787
1788 SELECT nvl(SUM(nvl(TRANSACTION_QUANTITY,0)),0)
1789 INTO l_forecast_non_qty_tbl(l_fct_index)
1790 FROM MTL_ONHAND_QUANTITIES QUANT,
1791 MTL_SECONDARY_INVENTORIES SI
1792 WHERE QUANT.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1793 AND QUANT.ORGANIZATION_ID = l_fs_arr_org_id_tbl(i)
1794 AND QUANT.SUBINVENTORY_CODE = SI.SECONDARY_INVENTORY_NAME
1795 AND QUANT.ORGANIZATION_ID = SI.ORGANIZATION_ID
1796 AND SI.AVAILABILITY_TYPE <> 1;
1797
1798 l_incl_in_rpt_flag_tbl(l_fct_index) := 'Y';
1799
1800 l_fct_index := l_fct_index + 1;
1801
1802 -- As per IO Module requirements - if a Particular Item Requirment is Interfaced with a
1803 -- Probability Percentage of failure - x% then one or more dummy record/s also needs to be
1804 -- interfaced with probability requirment of 100%-x% and Required Quantity - 0.
1805 -- To achieve this - We interface data for all the forecast associations in the system
1806 -- for the arrival organization except the one picked above and interface it to MRP
1807 -- with required quantity - 0, for these records.
1808 -- The constraint that Aggregate Probability Percentages of the Forecast sets sum upto
1809 -- 100% exactly, is marked as a User Setup Data Creation Outline.
1810 IF (p_concurrent_flag = 'Y') THEN
1814 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Creating Dummy Data in FCT Interface Table -- ');
1811 fnd_file.put_line(fnd_file.log, ' -- Creating Dummy Data in FCT Interface Table -- ');
1812 END IF;
1813 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1815 END IF;
1816
1817 IF l_mtbf_data_defined = 'N' THEN
1818 -- Dummy Data is created only if Prob of Failure is derived using Historical Data
1819 -- In case in Fct is being created using MTBF data, only Fct Designator can be Associated
1820 -- to the arrival org, and the value of the associated designator would be 100% in IO Plan Setup.
1821
1822 IF (p_concurrent_flag = 'Y') THEN
1823 fnd_file.put_line(fnd_file.log, ' ---- FETCHING FCT ASSOCIATIONS ----');
1824 END IF;
1825 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1826 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- FETCHING FCT ASSOCIATIONS ----');
1827 END IF;
1828 OPEN c_fetch_dummy_assocs(l_fct_designator,l_fs_arr_org_id_tbl(i));
1829 FETCH c_fetch_dummy_assocs
1830 BULK COLLECT INTO l_dummy_fct_desg_tbl;
1831 IF l_dummy_fct_desg_tbl.COUNT = 0 THEN
1832 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1833 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- NO DUMMY FCT ASSOCIATIONS DERIVED ----');
1834 END IF;
1835 ELSE
1836 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1837 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- DUMMY FCT ASSOCIATIONS RETRIEVED----');
1838 END IF;
1839 END IF;
1840 CLOSE c_fetch_dummy_assocs;
1841
1842 IF l_dummy_fct_desg_tbl.COUNT > 0 THEN
1843 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1844 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- INSERTING MRP Recs for Dummy FCT ASSOCIATIONS ----');
1845 END IF;
1846 IF (p_concurrent_flag = 'Y') THEN
1847 fnd_file.put_line(fnd_file.log, ' ---- INSERTING MRP Recs for Dummy FCT ASSOCIATIONS ----');
1848 END IF;
1849 for f in l_dummy_fct_desg_tbl.FIRST .. l_dummy_fct_desg_tbl.LAST LOOP
1850 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1851 fnd_log.string(fnd_log.level_statement,l_full_name,' ---- INSERTING FOR DUMMY ASSOC ----' || l_dummy_fct_desg_tbl(f));
1852 END IF;
1853
1854 l_forecast_interface_tbl(l_fct_index).INVENTORY_ITEM_ID := l_dtls_inv_item_id_tbl(j);
1855 l_forecast_interface_tbl(l_fct_index).FORECAST_DESIGNATOR := l_dummy_fct_desg_tbl(f);
1856 l_forecast_interface_tbl(l_fct_index).ORGANIZATION_ID := l_fs_arr_org_id_tbl(i);
1857 l_forecast_interface_tbl(l_fct_index).FORECAST_DATE := l_fs_arrival_date_tbl(i);
1858 l_forecast_interface_tbl(l_fct_index).LAST_UPDATE_DATE := sysdate;
1859 l_forecast_interface_tbl(l_fct_index).LAST_UPDATED_BY := fnd_global.USER_ID;
1860 l_forecast_interface_tbl(l_fct_index).CREATION_DATE := sysdate;
1861 l_forecast_interface_tbl(l_fct_index).CREATED_BY := fnd_global.USER_ID;
1862 l_forecast_interface_tbl(l_fct_index).LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
1863 l_forecast_interface_tbl(l_fct_index).QUANTITY := 0;
1864 l_forecast_interface_tbl(l_fct_index).PROCESS_STATUS := 2;
1865 l_forecast_interface_tbl(l_fct_index).CONFIDENCE_PERCENTAGE := 100;
1866 l_forecast_interface_tbl(l_fct_index).COMMENTS := null;
1867 l_forecast_interface_tbl(l_fct_index).ERROR_MESSAGE := null;
1868 l_forecast_interface_tbl(l_fct_index).REQUEST_ID := null;
1869 l_forecast_interface_tbl(l_fct_index).PROGRAM_APPLICATION_ID := null;
1870 l_forecast_interface_tbl(l_fct_index).PROGRAM_ID := null;
1874 l_forecast_interface_tbl(l_fct_index).FORECAST_END_DATE := null;
1871 l_forecast_interface_tbl(l_fct_index).PROGRAM_UPDATE_DATE := null;
1872 l_forecast_interface_tbl(l_fct_index).WORKDAY_CONTROL := 3; -- shift backward.
1873 l_forecast_interface_tbl(l_fct_index).BUCKET_TYPE := 1;
1875 l_forecast_interface_tbl(l_fct_index).TRANSACTION_ID := null;
1876 l_forecast_interface_tbl(l_fct_index).SOURCE_CODE := 'RA-'||l_fs_unit_sch_id_tbl(i);
1877 l_forecast_interface_tbl(l_fct_index).SOURCE_LINE_ID := l_dtls_subject_id_tbl(j);
1878 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE1 := null;
1879 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE2 := null;
1880 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE3 := null;
1881 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE4 := null;
1882 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE5 := null;
1883 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE6 := null;
1884 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE7 := null;
1885 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE8 := null;
1886 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE9 := null;
1887 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE10 := null;
1888 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE11 := null;
1889 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE12 := null;
1890 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE13 := null;
1891 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE14 := null;
1892 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE15 := null;
1893 l_forecast_interface_tbl(l_fct_index).PROJECT_ID := null;
1894 l_forecast_interface_tbl(l_fct_index).TASK_ID := null;
1895 l_forecast_interface_tbl(l_fct_index).LINE_ID := null;
1896 l_forecast_interface_tbl(l_fct_index).ACTION := 'I';
1897 l_forecast_interface_tbl(l_fct_index).ATTRIBUTE_CATEGORY := null;
1898
1899
1900 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1901 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Preparing Designator Interface Data -DUMMY- ');
1902 END IF;
1903
1904 IF l_forecast_designator_tbl.COUNT > 0 THEN
1905 FOR h in l_forecast_designator_tbl.FIRST .. l_forecast_designator_tbl.LAST LOOP
1906 IF l_forecast_designator_tbl(h).FORECAST_DESIGNATOR = l_dummy_fct_desg_tbl(f) THEN
1907 l_ds_rec_found := 'Y';
1908 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1909 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Designator already exists in Interface Data -DUMMY- ');
1910 END IF;
1911 EXIT;
1912 END IF;
1913 END LOOP;
1914 IF l_ds_rec_found = 'N' THEN
1915 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1916 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Designator does not exists in Interface Data -DUMMY- ');
1917 END IF;
1918 l_forecast_designator_tbl(l_dsg_index).ORGANIZATION_ID := l_fs_arr_org_id_tbl(i);
1919 l_forecast_designator_tbl(l_dsg_index).FORECAST_DESIGNATOR := l_dummy_fct_desg_tbl(f);
1920 l_dsg_index := l_dsg_index + 1;
1921 END IF;
1922 ELSE
1923 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1924 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Inserting first rec in Designator Data -DUMMY- ');
1925 END IF;
1926 l_forecast_designator_tbl(l_dsg_index).ORGANIZATION_ID := l_fs_arr_org_id_tbl(i);
1927 l_forecast_designator_tbl(l_dsg_index).FORECAST_DESIGNATOR := l_dummy_fct_desg_tbl(f);
1928 l_dsg_index := l_dsg_index + 1;
1929 END IF;
1930
1931 l_ds_rec_found := 'N';
1932
1933 l_forecast_org_code_tbl(l_fct_index) := l_fs_org_code_tbl(i);
1934 l_forecast_srl_no_tbl(l_fct_index) := l_dtls_srl_no_tbl(j);
1935 l_forecast_item_name_tbl(l_fct_index):= l_dtls_item_name_tbl(j);
1936 l_forecast_item_desc_tbl(l_fct_index):= l_dtls_item_desc_tbl(j);
1937
1938 -- Set Flag to indicate that dummy records will not be reflected in Output Report.
1939 l_incl_in_rpt_flag_tbl(l_fct_index) := 'N';
1940
1941 l_fct_index := l_fct_index + 1;
1942
1943 END LOOP; -- f for l_dummy_fct_desg_tbl
1944 END IF; -- l_dummy_fct_desg_tbl.COUNT > 0
1945
1946 END IF; -- l_mtbf_data_defined = 'N'
1947 -- End of creation of dummy data.
1948
1949 IF (p_concurrent_flag = 'Y') THEN
1950 fnd_file.put_line(fnd_file.log, ' -- Prepared IO Interface Data -- ');
1951 END IF;
1952
1953 ELSE -- l_fct_designator IS NULL
1954 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1955 fnd_log.string(fnd_log.level_statement,l_full_name,' -- No Fct Designator Found -- ');
1956 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Jump to Next instance -- ' || l_fct_designator);
1957 END IF;
1958
1959 IF (p_concurrent_flag = 'Y') THEN
1960 fnd_file.put_line(fnd_file.log, ' -- No Fct Designator Found so No Interfacing to be done -- ');
1961 END IF;
1962
1963 END IF;
1964
1965 ELSE -- l_active_ctr_id_tbl.COUNT = 0
1966 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1967 fnd_log.string(fnd_log.level_statement,l_full_name,' -- No Matching ACTIVE COUNTERS derived -- ');
1968 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Move to next item instace -- l_dtls_subject_id_tbl - '||l_dtls_subject_id_tbl(j));
1969 END IF;
1970 IF (p_concurrent_flag = 'Y') THEN
1971 fnd_file.put_line(fnd_file.log, ' -- No Matching ACTIVE COUNTERS derived -- ');
1972 END IF;
1973
1977 fnd_log.string(fnd_log.level_statement,l_full_name,'Setup or Retrieved counters are NULL');
1974 END IF; -- l_active_ctr_id_tbl.COUNT > 0
1975 ELSE -- Setup or Retrieved counters are NULL
1976 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1978 fnd_log.string(fnd_log.level_statement,l_full_name,'l_since_new_ctr_id_tbl - COUNT - '||l_since_new_ctr_id_tbl.COUNT);
1979 fnd_log.string(fnd_log.level_statement,l_full_name,'l_ctr_values_tbl - COUNT - '||l_ctr_values_tbl.COUNT);
1980 fnd_log.string(fnd_log.level_statement,l_full_name,'l_dtls_subject_id_tbl - '||l_dtls_subject_id_tbl(j));
1981 END IF;
1982 IF (p_concurrent_flag = 'Y') THEN
1983 fnd_file.put_line(fnd_file.log, ' -- Setup or Retrieved counters are NULL -- ');
1984 END IF;
1985 END IF; -- l_since_new_ctr_id_tbl.COUNT > 0 AND l_ctr_values_tbl.COUNT > 0
1986 END LOOP; -- Loop for Item Instance - UC Nodes - index j
1987 ELSE
1988 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1989 fnd_log.string(fnd_log.level_statement,l_full_name,'No Nodes for Current Unit - Move to Next Sch-Unit');
1990 fnd_log.string(fnd_log.level_statement,l_full_name,'l_fs_unit_sch_id_tbl - '||l_fs_unit_sch_id_tbl(i));
1991 fnd_log.string(fnd_log.level_statement,l_full_name,'l_fs_uc_header_id_tbl - '||l_fs_uc_header_id_tbl(i));
1992 fnd_log.string(fnd_log.level_statement,l_full_name,'l_fs_csi_instance_id_tbl - '||l_fs_csi_instance_id_tbl(i));
1993 END IF;
1994 IF (p_concurrent_flag = 'Y') THEN
1995 fnd_file.put_line(fnd_file.log, ' -- No Nodes for Current Unit - Move to Next Sch-Unit -- ');
1996 END IF;
1997 END IF; -- l_dtls_subject_id_tbl.COUNT > 0
1998 END LOOP; -- Loop for Unit Schedules - i
1999 ELSE -- l_fs_unit_sch_id_tbl.COUNT = 0
2000 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2001 fnd_log.string(fnd_log.level_statement,l_full_name,'No Flight Schedules to Process');
2002 END IF;
2003 IF (p_concurrent_flag = 'Y') THEN
2004 fnd_file.put_line(fnd_file.log, ' -- No Flight Schedules to Process -- ');
2005 END IF;
2006 END IF; -- l_fs_unit_sch_id_tbl.COUNT > 0
2007 END IF; -- l_since_new_ctr_id_tbl.COUNT = 0
2008
2009 IF l_forecast_interface_tbl.COUNT > 0 THEN
2010 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2011 fnd_log.string(fnd_log.level_statement,l_full_name,'Calling MRP_FORECAST_INTERFACE_PK.MRP_FORECAST_INTERFACE -- Before');
2012 fnd_log.string(fnd_log.level_statement,l_full_name,' -- INTERFACE DATA -- ');
2013 FOR b IN l_forecast_interface_tbl.FIRST .. l_forecast_interface_tbl.LAST LOOP
2014 fnd_log.string(fnd_log.level_statement,l_full_name,' -- FCT REC STARTS -- '||b);
2015 fnd_log.string(fnd_log.level_statement,l_full_name,' -- INVENTORY_ITEM_ID -- '||l_forecast_interface_tbl(b).INVENTORY_ITEM_ID);
2016 fnd_log.string(fnd_log.level_statement,l_full_name,' -- FORECAST_DESIGNATOR -- '||l_forecast_interface_tbl(b).FORECAST_DESIGNATOR);
2017 fnd_log.string(fnd_log.level_statement,l_full_name,' -- ORGANIZATION_ID -- '||l_forecast_interface_tbl(b).ORGANIZATION_ID);
2018 fnd_log.string(fnd_log.level_statement,l_full_name,' -- FORECAST_DATE -- '||l_forecast_interface_tbl(b).FORECAST_DATE);
2022 fnd_log.string(fnd_log.level_statement,l_full_name,' -- CONFIDENCE_PERCENTAGE -- '||l_forecast_interface_tbl(b).CONFIDENCE_PERCENTAGE);
2019 fnd_log.string(fnd_log.level_statement,l_full_name,' -- FORECASTED MONTH -- '||to_char(l_forecast_interface_tbl(b).FORECAST_DATE,'MON-YYYY'));
2020 fnd_log.string(fnd_log.level_statement,l_full_name,' -- REQUIRED_QUANTITY -- '||l_forecast_interface_tbl(b).QUANTITY);
2021 fnd_log.string(fnd_log.level_statement,l_full_name,' -- PROCESS_STATUS -- '||l_forecast_interface_tbl(b).PROCESS_STATUS);
2023 fnd_log.string(fnd_log.level_statement,l_full_name,' -- WORKDAY_CONTROL -- '||l_forecast_interface_tbl(b).WORKDAY_CONTROL);
2024 fnd_log.string(fnd_log.level_statement,l_full_name,' -- BUCKET_TYPE -- '||l_forecast_interface_tbl(b).BUCKET_TYPE);
2025 fnd_log.string(fnd_log.level_statement,l_full_name,' -- SOURCE_CODE -- '||l_forecast_interface_tbl(b).SOURCE_CODE);
2026 fnd_log.string(fnd_log.level_statement,l_full_name,' -- SOURCE_LINE_ID -- '||l_forecast_interface_tbl(b).SOURCE_LINE_ID);
2027 fnd_log.string(fnd_log.level_statement,l_full_name,' -- ACTION -- '||l_forecast_interface_tbl(b).ACTION);
2028
2029 END LOOP;
2030 IF l_forecast_designator_tbl.COUNT > 0 THEN
2031 FOR c IN l_forecast_designator_tbl.FIRST .. l_forecast_designator_tbl.LAST LOOP
2032 fnd_log.string(fnd_log.level_statement,l_full_name,' -- DESIGNATOR REC STARTS -- '||c);
2033 fnd_log.string(fnd_log.level_statement,l_full_name,' -- ORGANIZATION_ID -- '||l_forecast_designator_tbl(c).ORGANIZATION_ID);
2034 fnd_log.string(fnd_log.level_statement,l_full_name,' -- FORECAST_DESIGNATOR -- '||l_forecast_designator_tbl(c).FORECAST_DESIGNATOR);
2035 END LOOP;
2036 END IF;
2037 END IF;
2038 IF (p_concurrent_flag = 'Y') THEN
2039 fnd_file.put_line(fnd_file.log, ' -- Calling MRP_FORECAST_INTERFACE_PK.MRP_FORECAST_INTERFACE -- Before -- ');
2040 END IF;
2041
2042 IF (MRP_FORECAST_INTERFACE_PK.MRP_FORECAST_INTERFACE
2043 (l_forecast_interface_tbl,
2044 l_forecast_designator_tbl)) THEN
2045 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2046 fnd_log.string(fnd_log.level_statement,l_full_name,'Calling MRP_FORECAST_INTERFACE_PK.MRP_FORECAST_INTERFACE -- After');
2047 END IF;
2048 IF (p_concurrent_flag = 'Y') THEN
2049 fnd_file.put_line(fnd_file.log, ' -- Calling MRP_FORECAST_INTERFACE_PK.MRP_FORECAST_INTERFACE -- After -- ');
2050 END IF;
2051 ELSE
2052 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2053 fnd_log.string(fnd_log.level_statement,l_full_name,'Failure Calling MRP_FORECAST_INTERFACE_PK.MRP_FORECAST_INTERFACE -- ');
2054 END IF;
2055 IF (p_concurrent_flag = 'Y') THEN
2056 fnd_file.put_line(fnd_file.log, ' -- Failure Calling MRP_FORECAST_INTERFACE_PK.MRP_FORECAST_INTERFACE ---- ');
2057 END IF;
2058 END IF;
2059
2060 ELSE
2061 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2062 fnd_log.string(fnd_log.level_statement,l_full_name,'MRP_FORECAST_INTERFACE_PK.MRP_FORECAST_INTERFACE Not to be called');
2063 END IF;
2064 IF (p_concurrent_flag = 'Y') THEN
2065 fnd_file.put_line(fnd_file.log, ' -- MRP_FORECAST_INTERFACE_PK.MRP_FORECAST_INTERFACE Not to be called ---- ');
2066 END IF;
2067 END IF;
2068
2069 -- Open a temporary lob for merging the contents.
2070 dbms_lob.createTemporary( l_fct_data_lob, true );
2071 dbms_lob.open( l_fct_data_lob, dbms_lob.lob_readwrite );
2072
2073 -- XML generated with dbms_xmlgen doesnt have encoding information. so we need to manually insert into the resultant CLOB.
2074 dbms_lob.write(l_fct_data_lob,length('<?xml version="1.0" encoding="UTF-8"?>'),1,'<?xml version="1.0" encoding="UTF-8"?>');
2075 /*
2076 mpothuku Added fnd_global.local_chr(10) (or new line) for the Bug 5724555 on 21-Dec-06. FND_FILE.put has a restriction of 32K characters.
2077 If there is no new-line in these 32K characters, it fails. So ensuring that there are new-line characters after every line
2078 of the XML
2079 */
2080 dbms_lob.write(l_fct_data_lob,length(fnd_global.local_chr(10)),length(l_fct_data_lob)+1,fnd_global.local_chr(10));
2081 --Put the root node to maintain the XML completeness.
2082 dbms_lob.write(l_fct_data_lob, length('<G_FCT_DATA_LIST>'),length(l_fct_data_lob)+1, '<G_FCT_DATA_LIST>');
2083 dbms_lob.write(l_fct_data_lob,length(fnd_global.local_chr(10)),length(l_fct_data_lob)+1,fnd_global.local_chr(10));
2084 --Put the Start Date and the End Date
2085 --mpothuku Added to_char on 23 Aug, 06 for XSL canonical date format to be used by the XMLP report for the Bug 5460793
2086 l_dummy_string := '<P_START_DATE>' || to_char(p_start_date,'YYYY-MM-DD') || '</P_START_DATE>'||fnd_global.local_chr(10);
2087 l_dummy_string := l_dummy_string || '<P_END_DATE>' || to_char(p_end_date,'YYYY-MM-DD') || '</P_END_DATE>' ||fnd_global.local_chr(10);
2088 dbms_lob.write(l_fct_data_lob, length(l_dummy_string),length(l_fct_data_lob)+1, l_dummy_string);
2089
2090 IF l_forecast_interface_tbl.COUNT > 0 THEN
2091 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2092 fnd_log.string(fnd_log.level_statement,l_full_name,'Creating Clob for Output File');
2093 END IF;
2094 IF (p_concurrent_flag = 'Y') THEN
2095 fnd_file.put_line(fnd_file.log, ' -- Creating Clob for Output File -- ');
2096 END IF;
2097 --mpothuku added dbms_xmlgen.convert on 08-May-2007 to fix the Bug 6038466
2098 /*
2102 */
2099 Note by mpothuku on 09-May-2007: Consider revising the string concat logic below and
2100 write each XML line immediately into the CLOB and the log, so that the l_dummy_string size is not huge.
2101 We already had to increase the size from 1000 to 5000, as XML encode bloats up the size of the strings.
2103 FOR c in l_forecast_interface_tbl.FIRST .. l_forecast_interface_tbl.LAST LOOP
2104 IF l_incl_in_rpt_flag_tbl(c) = 'Y' THEN -- Check if Interface rec is candidate for Output report.
2105 l_row_count := l_row_count + 1;
2106 l_dummy_string := '<G_FCT_ENTRY_REC>'||fnd_global.local_chr(10);
2107 l_dummy_string := l_dummy_string || '<ORGANIZATION_ID>' ||l_forecast_interface_tbl(c).ORGANIZATION_ID ||'</ORGANIZATION_ID>'||fnd_global.local_chr(10);
2108 l_dummy_string := l_dummy_string || '<ORGANIZATION_CODE>' ||dbms_xmlgen.convert(l_forecast_org_code_tbl(c)) ||'</ORGANIZATION_CODE>'||fnd_global.local_chr(10);
2109 l_dummy_string := l_dummy_string || '<INVENTORY_ITEM_ID>' ||l_forecast_interface_tbl(c).INVENTORY_ITEM_ID ||'</INVENTORY_ITEM_ID>'||fnd_global.local_chr(10);
2110 l_dummy_string := l_dummy_string || '<CONCATENATED_SEGMENTS>' ||dbms_xmlgen.convert(l_forecast_item_name_tbl(c)) ||'</CONCATENATED_SEGMENTS>'||fnd_global.local_chr(10);
2111 l_dummy_string := l_dummy_string || '<ITEM_DESCRIPTION>' ||dbms_xmlgen.convert(l_forecast_item_desc_tbl(c)) ||'</ITEM_DESCRIPTION>'||fnd_global.local_chr(10);
2112 l_dummy_string := l_dummy_string || '<SERIAL_NUMBER>' ||dbms_xmlgen.convert(l_forecast_srl_no_tbl(c)) ||'</SERIAL_NUMBER>'||fnd_global.local_chr(10);
2113 l_dummy_string := l_dummy_string || '<FORECAST_DESIGNATOR>' ||dbms_xmlgen.convert(l_forecast_interface_tbl(c).FORECAST_DESIGNATOR) ||'</FORECAST_DESIGNATOR>'||fnd_global.local_chr(10);
2114 --mpothuku Added to_char on 23 Aug, 06 for XSL canonical date format to be used by the XMLP report for the Bug 5460793
2115 l_dummy_string := l_dummy_string || '<FORECAST_DATE>' ||to_char(l_forecast_interface_tbl(c).FORECAST_DATE,'YYYY-MM-DD')||'</FORECAST_DATE>'||fnd_global.local_chr(10);
2116 l_dummy_string := l_dummy_string || '<FORECASTED_MONTH>' ||to_char(l_forecast_interface_tbl(c).FORECAST_DATE,'MON-YYYY')||'</FORECASTED_MONTH>'||fnd_global.local_chr(10);
2117 l_dummy_string := l_dummy_string || '<REQUIRED_QUANTITY>' ||l_forecast_interface_tbl(c).QUANTITY ||'</REQUIRED_QUANTITY>'||fnd_global.local_chr(10);
2118 l_dummy_string := l_dummy_string || '<REQUIRED_PROBABILITY>' ||l_forecast_req_prob_tbl(c) ||'</REQUIRED_PROBABILITY>'||fnd_global.local_chr(10);
2119 l_dummy_string := l_dummy_string || '<ONHAND_QUANTITY>' ||l_forecast_onhand_qty_tbl(c) ||'</ONHAND_QUANTITY>'||fnd_global.local_chr(10);
2120 l_dummy_string := l_dummy_string || '<QUANTITY_DUE_OSP>' ||l_forecast_osp_qty_tbl(c) ||'</QUANTITY_DUE_OSP>'||fnd_global.local_chr(10);
2121 l_dummy_string := l_dummy_string || '<QUANTITY_IN_VISIT>' ||l_forecast_vwp_qty_tbl(c) ||'</QUANTITY_IN_VISIT>'||fnd_global.local_chr(10);
2122 l_dummy_string := l_dummy_string || '<QUANTITY_NON_SERVICEABLE>' ||l_forecast_non_qty_tbl(c) ||'</QUANTITY_NON_SERVICEABLE>'||fnd_global.local_chr(10);
2123 l_dummy_string := l_dummy_string || '<PROCESS_STATUS>' ||l_forecast_interface_tbl(c).PROCESS_STATUS ||'</PROCESS_STATUS>'||fnd_global.local_chr(10);
2124 l_dummy_string := l_dummy_string || '<ERROR_MESSAGE>' ||dbms_xmlgen.convert(l_forecast_interface_tbl(c).ERROR_MESSAGE) ||'</ERROR_MESSAGE>'||fnd_global.local_chr(10);
2125 l_dummy_string := l_dummy_string || '<SOURCE_CODE>' ||dbms_xmlgen.convert(l_forecast_interface_tbl(c).SOURCE_CODE) ||'</SOURCE_CODE>'||fnd_global.local_chr(10);
2126 l_dummy_string := l_dummy_string || '<SOURCE_LINE_ID>' ||l_forecast_interface_tbl(c).SOURCE_LINE_ID ||'</SOURCE_LINE_ID>'||fnd_global.local_chr(10);
2127
2128 -- This 'Hard Coded' Data will be replaced by Lookups / Messages -- Pending
2129 IF l_forecast_interface_tbl(c).PROCESS_STATUS = 1 THEN
2130 l_dummy_string := l_dummy_string || '<PROCESS_STATUS_DESC>' || ' Do Not Process ' ||'</PROCESS_STATUS_DESC>'||fnd_global.local_chr(10);
2131 ELSIF l_forecast_interface_tbl(c).PROCESS_STATUS = 2 THEN
2132 l_dummy_string := l_dummy_string || '<PROCESS_STATUS_DESC>' || ' Waiting to be processed ' ||'</PROCESS_STATUS_DESC>'||fnd_global.local_chr(10);
2133 ELSIF l_forecast_interface_tbl(c).PROCESS_STATUS = 3 THEN
2134 l_dummy_string := l_dummy_string || '<PROCESS_STATUS_DESC>' || ' Being Processed ' ||'</PROCESS_STATUS_DESC>'||fnd_global.local_chr(10);
2135 ELSIF l_forecast_interface_tbl(c).PROCESS_STATUS = 4 THEN
2136 l_dummy_string := l_dummy_string || '<PROCESS_STATUS_DESC>' || ' Error ' ||'</PROCESS_STATUS_DESC>'||fnd_global.local_chr(10);
2137 ELSIF l_forecast_interface_tbl(c).PROCESS_STATUS = 5 THEN
2138 l_dummy_string := l_dummy_string || '<PROCESS_STATUS_DESC>' || ' Processed ' ||'</PROCESS_STATUS_DESC>'||fnd_global.local_chr(10);
2139 END IF;
2140
2141 l_dummy_string := l_dummy_string || '</G_FCT_ENTRY_REC>'||fnd_global.local_chr(10);
2142 dbms_lob.write(l_fct_data_lob, length(l_dummy_string),length(l_fct_data_lob)+1, l_dummy_string);
2143 END IF;
2144 END LOOP;
2145
2146 ELSE
2147 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2148 fnd_log.string(fnd_log.level_statement,l_full_name,' -- Clob content empty for Output File --');
2149 END IF;
2150 IF (p_concurrent_flag = 'Y') THEN
2154
2151 fnd_file.put_line(fnd_file.log, ' -- Clob content empty for Output File ---- ');
2152 END IF;
2153 END IF;
2155 l_dummy_string := '<ROW_COUNT>' || l_row_count || '</ROW_COUNT>'||fnd_global.local_chr(10);
2156 dbms_lob.write(l_fct_data_lob, length(l_dummy_string),length(l_fct_data_lob)+1, l_dummy_string);
2157
2158 dbms_lob.write(l_fct_data_lob, length('</G_FCT_DATA_LIST>'),length(l_fct_data_lob)+1, '</G_FCT_DATA_LIST>');
2159
2160 x_xml_data := l_fct_data_lob;
2161
2162 --Close and release the temporary lobs
2163 dbms_lob.close( l_fct_data_lob );
2164 dbms_lob.freeTemporary( l_fct_data_lob );
2165
2166 -- Standard check for p_commit
2167 IF FND_API.To_Boolean (p_commit) THEN
2168 COMMIT;
2169 END IF;
2170
2171 IF (p_concurrent_flag = 'Y') THEN
2172 fnd_file.put_line(fnd_file.log, ' -- RA -- PKG -- PROCESS_RA_DATA -------END--------------- ');
2173 END IF;
2174 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2175 fnd_log.string(fnd_log.level_statement,l_full_name,'RA -- PKG -- PROCESS_RA_DATA -------END-----------');
2176 END IF;
2177
2178 -- Standard call to get message count and if count is 1, get message
2179 FND_MSG_PUB.Count_And_Get
2180 ( p_count => x_msg_count,
2181 p_data => x_msg_data,
2182 p_encoded => fnd_api.g_false);
2183
2184 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2185 fnd_log.string(fnd_log.level_procedure,L_FULL_NAME||'.end','Return Status = ' || x_return_status);
2186 END IF;
2187
2188 EXCEPTION
2189 WHEN FND_API.G_EXC_ERROR THEN
2190 x_return_status := FND_API.G_RET_STS_ERROR;
2191 Rollback to PROCESS_RA_DATA_SP;
2192 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2193 p_data => x_msg_data,
2194 p_encoded => fnd_api.g_false);
2195 IF (p_concurrent_flag = 'Y') THEN
2196 fnd_file.put_line(fnd_file.log, 'RA Analyser Process Failed. Refer to the error message below.');
2197 log_error_messages;
2198 END IF;
2199
2200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2202 Rollback to PROCESS_RA_DATA_SP;
2203 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2204 p_data => x_msg_data,
2205 p_encoded => fnd_api.g_false);
2206 IF (p_concurrent_flag = 'Y') THEN
2207 fnd_file.put_line(fnd_file.log, 'RA Analyser Process Failed. Refer to the error message below.');
2208 log_error_messages;
2209 END IF;
2210
2211 WHEN OTHERS THEN
2212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2213 Rollback to PROCESS_RA_DATA_SP;
2214 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2215 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2216 p_procedure_name => 'PROCESS_RA_DATA',
2217 p_error_text => SUBSTR(SQLERRM,1,240));
2218 END IF;
2219 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2220 p_data => x_msg_data,
2221 p_encoded => fnd_api.g_false);
2222 IF (p_concurrent_flag = 'Y') THEN
2223 fnd_file.put_line(fnd_file.log, 'RA Analyser Process Failed. Refer to the error message below.');
2224 log_error_messages;
2225 END IF;
2226
2227 END PROCESS_RA_DATA;
2228
2229 PROCEDURE RA_ANALYSER_PROCESS (
2230 errbuf OUT NOCOPY VARCHAR2,
2231 retcode OUT NOCOPY NUMBER,
2232 p_api_version IN NUMBER,
2233 p_start_date IN VARCHAR2,
2234 p_end_date IN VARCHAR2
2235 )
2236 IS
2237
2238 l_return_status VARCHAR2(30);
2239 l_msg_count NUMBER;
2240 l_api_name VARCHAR2(30) := 'RA_ANALYSER_PROCESS';
2241 l_api_version NUMBER := 1.0;
2242 l_clob CLOB;
2243
2244 l_offset NUMBER;
2245 l_chunk_size NUMBER;
2246 l_clob_size NUMBER;
2247 l_chunk VARCHAR2(10000);
2248
2249 BEGIN
2250
2251 -- Initialize error message stack by default
2252 FND_MSG_PUB.Initialize;
2253
2254 -- Standard call to check for call compatibility
2255 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2256 THEN
2257 retcode := 2;
2258 errbuf := FND_MSG_PUB.Get;
2259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2260 END IF;
2261
2262 PROCESS_RA_DATA (
2263 p_api_version => 1,
2264 p_init_msg_list => FND_API.G_TRUE,
2265 p_commit => FND_API.G_TRUE,
2266 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2267 p_module_type => NULL,
2268 x_return_status => l_return_status,
2269 x_msg_count => l_msg_count,
2270 x_msg_data => errbuf,
2271 p_start_date => fnd_date.canonical_to_date(p_start_date),
2275
2272 p_end_date => fnd_date.canonical_to_date(p_end_date),
2273 p_concurrent_flag => 'Y',
2274 x_xml_data => l_clob);
2276 l_offset := 1;
2277 l_chunk_size := 3000;
2278 l_clob_size := dbms_lob.getlength(l_clob);
2279 fnd_file.put(fnd_file.log, 'l_clob_size - '||l_clob_size);
2280 WHILE (l_clob_size > 0) LOOP
2281 l_chunk := dbms_lob.substr (l_clob, l_chunk_size, l_offset);
2282 fnd_file.put(fnd_file.log, l_chunk);
2283 l_clob_size := l_clob_size - l_chunk_size;
2284 l_offset := l_offset + l_chunk_size;
2285 END LOOP;
2286
2287 l_msg_count := FND_MSG_PUB.Count_Msg;
2288
2289 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2290 retcode := 2; -- error based only on return status
2291 fnd_file.put(fnd_file.log, ' retcode - '||retcode);
2292 fnd_file.put(fnd_file.log, ' l_return_status - '||l_return_status);
2293 ELSIF (l_msg_count > 0 AND l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2294 retcode := 1; -- warning based on return status + msg count
2295 fnd_file.put(fnd_file.log, ' retcode - '||retcode);
2296 fnd_file.put(fnd_file.log, ' l_msg_count - '||l_msg_count);
2297 ELSE
2298 retcode := 0; -- success, since nothing is wrong
2299 fnd_file.put(fnd_file.log, ' retcode - '||retcode);
2300 l_offset := 1;
2301 l_chunk_size := 3000;
2302 l_clob_size := dbms_lob.getlength(l_clob);
2303 WHILE (l_clob_size > 0) LOOP
2304 l_chunk := dbms_lob.substr (l_clob, l_chunk_size, l_offset);
2305 fnd_file.put(fnd_file.output, l_chunk);
2306 l_clob_size := l_clob_size - l_chunk_size;
2307 l_offset := l_offset + l_chunk_size;
2308 END LOOP;
2309 END IF;
2310 END RA_ANALYSER_PROCESS;
2311
2312 --------------------------------------------------------------------------
2313 -- To log error messages into a log file if called from concurrent process.
2314 ---------------------------------------------------------------------------
2315 PROCEDURE log_error_messages IS
2316
2317 l_msg_count NUMBER;
2318 l_msg_index_out NUMBER;
2319 l_msg_data VARCHAR2(2000);
2320
2321 BEGIN
2322
2323 -- Standard call to get message count.
2324 l_msg_count := FND_MSG_PUB.Count_Msg;
2325
2326 FOR i IN 1..l_msg_count LOOP
2327 FND_MSG_PUB.get (
2328 p_msg_index => i,
2329 p_encoded => FND_API.G_FALSE,
2330 p_data => l_msg_data,
2331 p_msg_index_out => l_msg_index_out );
2332
2333 fnd_file.put_line(FND_FILE.LOG, 'Err message-'||l_msg_index_out||':' || l_msg_data);
2334 END LOOP;
2335
2336 END log_error_messages;
2337
2338 END AHL_RA_ANALYSER_PVT;
[Home] [Help]