eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_UMP_SMRINSTANCE_PVT dependencies on AHL_APPLICABLE_INSTANCES

Line 467: --fix for bug# 7562008. Added hint /+ dynamic_sampling(AAI1 4) / to queries based on AHL_APPLICABLE_INSTANCES table

463: --start fix for bug#7327283
464: --SELECT Clause
465: --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';
466:
467: --fix for bug# 7562008. Added hint /*+ dynamic_sampling(AAI1 4) */ to queries based on AHL_APPLICABLE_INSTANCES table
468: --as per feedback from Application Performance team.
469: IF (l_is_pm_installed = 'Y') THEN
470: l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';
471: ELSE

Line 475: l_select_sql_string := 'SELECT /+ dynamic_sampling(AAI1 4) / UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1 ';

471: ELSE
472: IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
473: <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
474: --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= ''AHL'' ';
475: l_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1 ';
476: l_select_sql_string := l_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id ';
477:
478: l_mr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1, AHL_MR_HEADERS_B MR';
479: l_mr_select_sql_string := l_mr_select_sql_string || ' WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND UMP.mr_header_id = MR.mr_header_id ';

Line 478: l_mr_select_sql_string := 'SELECT /+ dynamic_sampling(AAI1 4) / UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1, AHL_MR_HEADERS_B MR';

474: --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= ''AHL'' ';
475: l_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1 ';
476: l_select_sql_string := l_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id ';
477:
478: l_mr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1, AHL_MR_HEADERS_B MR';
479: l_mr_select_sql_string := l_mr_select_sql_string || ' WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND UMP.mr_header_id = MR.mr_header_id ';
480:
481: l_bindvar_tbl(l_bind_index) := ltrim(rtrim(fnd_profile.value('AHL_APPLN_USAGE')));
482: l_bind_index := l_bind_index + 1;

Line 486: l_nr_select_sql_string := 'SELECT /+ dynamic_sampling(AAI1 4) / UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP , AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI, CS_INCIDENT_TYPES_VL CIT ';

482: l_bind_index := l_bind_index + 1;
483:
484: IF p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL THEN
485: IF nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%' THEN
486: l_nr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP , AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI, CS_INCIDENT_TYPES_VL CIT ';
487: l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND ';
488: l_nr_select_sql_string := l_nr_select_sql_string || 'UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';
489:
490: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;

Line 497: l_nr_select_sql_string := 'SELECT /+ dynamic_sampling(AAI1 4) / UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI,CS_INCIDENT_TYPES_VL CIT ';

493: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
494: l_bind_index := l_bind_index + 1;
495:
496: ELSE -- servc req num is null.
497: l_nr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI,CS_INCIDENT_TYPES_VL CIT ';
498: l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id AND UMP.cs_incident_id = CI.incident_id';
499: l_nr_select_sql_string := l_nr_select_sql_string || ' AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';
500: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
501: l_bind_index := l_bind_index + 1;

Line 506: l_nr_select_sql_string := 'SELECT /+ dynamic_sampling(AAI1 4) / UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI ';

502: END IF; -- p_search_mr_instance_rec.SERVICE_REQ_NUM
503: ELSE -- INCIDENT_TYPE_ID is null
504: IF (nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%') THEN
505: -- servc req is not null
506: l_nr_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,AHL_APPLICABLE_INSTANCES AAI1, CS_INCIDENTS_ALL_B CI ';
507: l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND AAI1.csi_item_instance_id = UMP.csi_item_instance_id ';
508: l_nr_select_sql_string := l_nr_select_sql_string || 'AND UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM ';
509: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
510: l_bind_index := l_bind_index + 1;

Line 745: DELETE AHL_APPLICABLE_INSTANCES;

741: /*
742: * Temporary table use is introduced to improve performance to fix bug # 3786626
743: */
744: IF p_search_mr_instance_rec.components_flag is NOT NULL THEN
745: DELETE AHL_APPLICABLE_INSTANCES;
746: IF p_search_mr_instance_rec.components_flag = 'N' THEN
747: IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
748: <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
749: populate_instances

Line 755: -- l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' from AHL_APPLICABLE_INSTANCES AAI WHERE AAI.csi_item_instance_id = UMP.csi_item_instance_id)' ; */

751: p_module_type => p_module_type,
752: p_search_mr_instance_rec => p_search_mr_instance_rec
753: );
754: -- not required as included in join in l_select_sql_string
755: -- l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' from AHL_APPLICABLE_INSTANCES AAI WHERE AAI.csi_item_instance_id = UMP.csi_item_instance_id)' ; */
756: END IF;
757: /*ELSE
758: l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' From csi_unit_instances_v csiu WHERE csiu.instance_id = UMP.csi_item_instance_id )';
759: END IF;*/

Line 774: --l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' from AHL_APPLICABLE_INSTANCES AAI WHERE AAI.csi_item_instance_id = UMP.csi_item_instance_id)' ;

770: p_module_type => p_module_type,
771: p_search_mr_instance_rec => p_search_mr_instance_rec
772: );
773: -- not required as included in join in l_select_sql_string
774: --l_sql_string := l_sql_string || ' AND EXISTS (Select ''x'' from AHL_APPLICABLE_INSTANCES AAI WHERE AAI.csi_item_instance_id = UMP.csi_item_instance_id)' ;
775: END IF;
776: END IF;
777: END IF;
778:

Line 1234: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES (only_units_rec.csi_item_instance_id,0);

1230: --l_all_csi_ii_id sql_string [ Blind search at unit level]
1231: IF (p_search_mr_instance_rec.unit_name is NOT NULL AND nvl(p_search_mr_instance_rec.part_number,'%')
1232: = '%' AND p_search_mr_instance_rec.serial_number is NULL) THEN
1233: FOR only_units_rec IN only_units_csr(p_search_mr_instance_rec.unit_name) LOOP
1234: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES (only_units_rec.csi_item_instance_id,0);
1235: END LOOP;
1236:
1237: ELSE
1238: l_bind_index := 1;

Line 1316: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),0);

1312: EXIT WHEN (l_inst_tbl.count = 0);
1313: FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1314: --FETCH l_cur INTO l_instance_id;
1315: --EXIT WHEN l_cur%NOTFOUND;
1316: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),0);
1317: END LOOP; -- l_ue_id_tbl loop
1318: l_inst_tbl.DELETE;
1319: END LOOP;
1320:

Line 1335: AND NOT EXISTS (select 'x' from AHL_APPLICABLE_INSTANCES where csi_item_instance_id = csii.subject_id)

1331:
1332: CURSOR dependent_components_csr IS
1333: SELECT subject_id from csi_ii_relationships csii WHERE
1334: EXISTS (select 'x' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.subject_id)
1335: AND NOT EXISTS (select 'x' from AHL_APPLICABLE_INSTANCES where csi_item_instance_id = csii.subject_id)
1336: START WITH object_id IN (SELECT csi_item_instance_id FROM AHL_APPLICABLE_INSTANCES WHERE POSITION_ID = 0)
1337: AND trunc(nvl(csii.active_start_date, sysdate)) <= Trunc(sysdate)
1338: AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
1339: AND relationship_type_code = 'COMPONENT-OF'

Line 1336: START WITH object_id IN (SELECT csi_item_instance_id FROM AHL_APPLICABLE_INSTANCES WHERE POSITION_ID = 0)

1332: CURSOR dependent_components_csr IS
1333: SELECT subject_id from csi_ii_relationships csii WHERE
1334: EXISTS (select 'x' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.subject_id)
1335: AND NOT EXISTS (select 'x' from AHL_APPLICABLE_INSTANCES where csi_item_instance_id = csii.subject_id)
1336: START WITH object_id IN (SELECT csi_item_instance_id FROM AHL_APPLICABLE_INSTANCES WHERE POSITION_ID = 0)
1337: AND trunc(nvl(csii.active_start_date, sysdate)) <= Trunc(sysdate)
1338: AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
1339: AND relationship_type_code = 'COMPONENT-OF'
1340: CONNECT BY PRIOR subject_id = object_id

Line 1352: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID)

1348:
1349: BEGIN
1350: /*FOR dependent_component_rec IN dependent_components_csr
1351: LOOP
1352: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID)
1353: VALUES(dependent_component_rec.subject_id,1);
1354: END LOOP;*/
1355:
1356: OPEN dependent_components_csr;

Line 1361: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),1);

1357: LOOP
1358: FETCH dependent_components_csr BULK COLLECT INTO l_inst_tbl LIMIT l_buffer_limit;
1359: EXIT WHEN (l_inst_tbl.count = 0);
1360: FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1361: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),1);
1362: END LOOP;
1363: l_inst_tbl.DELETE;
1364: END LOOP;
1365: