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 533: --fix for bug# 7562008. Added hint /+ dynamic_sampling(AAI1 4) / to queries based on AHL_APPLICABLE_INSTANCES table

529: --start fix for bug#7327283
530: --SELECT Clause
531: --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';
532:
533: --fix for bug# 7562008. Added hint /*+ dynamic_sampling(AAI1 4) */ to queries based on AHL_APPLICABLE_INSTANCES table
534: --as per feedback from Application Performance team.
535: IF (l_is_pm_installed = 'Y') THEN
536: l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';
537: ELSE

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

537: ELSE
538: IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
539: <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
540: --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= ''AHL'' ';
541: l_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1 ';
542: 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 ';
543: 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';
544: 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 ';
545:

Line 543: 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';

539: <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
540: --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= ''AHL'' ';
541: l_select_sql_string := 'SELECT /*+ dynamic_sampling(AAI1 4) */ UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_APPLICABLE_INSTANCES AAI1 ';
542: 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 ';
543: 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';
544: 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 ';
545:
546: l_bindvar_tbl(l_bind_index) := ltrim(rtrim(fnd_profile.value('AHL_APPLN_USAGE')));
547: l_bind_index := l_bind_index + 1;

Line 551: 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 ';

547: l_bind_index := l_bind_index + 1;
548:
549: IF p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL THEN
550: IF nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%' THEN
551: 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 ';
552: 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 ';
553: 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 ';
554:
555: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;

Line 562: 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 ';

558: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
559: l_bind_index := l_bind_index + 1;
560:
561: ELSE -- servc req num is null.
562: 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 ';
563: 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';
564: 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 ';
565: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
566: l_bind_index := l_bind_index + 1;

Line 571: 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 ';

567: END IF; -- p_search_mr_instance_rec.SERVICE_REQ_NUM
568: ELSE -- INCIDENT_TYPE_ID is null
569: IF (nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%') THEN
570: -- servc req is not null
571: 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 ';
572: 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 ';
573: 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 ';
574: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
575: l_bind_index := l_bind_index + 1;

Line 828: DELETE AHL_APPLICABLE_INSTANCES;

824: /*
825: * Temporary table use is introduced to improve performance to fix bug # 3786626
826: */
827: IF p_search_mr_instance_rec.components_flag is NOT NULL THEN
828: DELETE AHL_APPLICABLE_INSTANCES;
829: IF p_search_mr_instance_rec.components_flag = 'N' THEN
830: IF (p_search_mr_instance_rec.unit_name is NOT NULL OR nvl(p_search_mr_instance_rec.part_number,'%')
831: <> '%' OR p_search_mr_instance_rec.serial_number IS NOT NULL) THEN
832: populate_instances

Line 838: -- 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)' ; */

834: p_module_type => p_module_type,
835: p_search_mr_instance_rec => p_search_mr_instance_rec
836: );
837: -- not required as included in join in l_select_sql_string
838: -- 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)' ; */
839: END IF;
840: /*ELSE
841: 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 )';
842: END IF;*/

Line 856: --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)' ;

852: (
853: p_module_type => p_module_type
854: );
855: -- not required as included in join in l_select_sql_string
856: --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)' ;
857: END IF;
858: END IF;
859: END IF;
860:

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

1421: --l_all_csi_ii_id sql_string [ Blind search at unit level]
1422: IF (p_search_mr_instance_rec.unit_name is NOT NULL AND nvl(p_search_mr_instance_rec.part_number,'%')
1423: = '%' AND p_search_mr_instance_rec.serial_number is NULL) THEN
1424: FOR only_units_rec IN only_units_csr(p_search_mr_instance_rec.unit_name) LOOP
1425: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES (only_units_rec.csi_item_instance_id,0);
1426: END LOOP;
1427:
1428: ELSE
1429: l_bind_index := 1;

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

1503: EXIT WHEN (l_inst_tbl.count = 0);
1504: FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1505: --FETCH l_cur INTO l_instance_id;
1506: --EXIT WHEN l_cur%NOTFOUND;
1507: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),0);
1508: END LOOP; -- l_ue_id_tbl loop
1509: l_inst_tbl.DELETE;
1510: END LOOP;
1511:

Line 1564: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES (ONLY_UNITS_REC.CSI_ITEM_INSTANCE_ID,0);

1560: IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL AND NVL(P_SEARCH_UMP_REC.ITEM,'%')
1561: = '%' AND P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NULL) THEN
1562: IF (P_SEARCH_UMP_REC.ARRIVAL_ORG IS NULL) THEN
1563: FOR ONLY_UNITS_REC IN ONLY_UNITS_CSR(P_SEARCH_UMP_REC.UNIT_NAME) LOOP
1564: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES (ONLY_UNITS_REC.CSI_ITEM_INSTANCE_ID,0);
1565: END LOOP;
1566: ELSE
1567: L_BIND_INDEX := 1;
1568: l_unit_related_ii_sql := ' Select DISTINCT ahlu.csi_item_instance_id from ahl_unit_config_headers ahlu,ahl_unit_schedules aus

Line 1599: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(L_INST_TBL(J),0);

1595: EXIT WHEN (L_INST_TBL.COUNT = 0);
1596: FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1597: --FETCH l_cur INTO l_instance_id;
1598: --EXIT WHEN l_cur%NOTFOUND;
1599: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(L_INST_TBL(J),0);
1600: END LOOP; -- l_ue_id_tbl loop
1601: L_INST_TBL.DELETE;
1602: END LOOP;
1603: CLOSE L_CUR;

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

1712: EXIT WHEN (l_inst_tbl.count = 0);
1713: FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1714: --FETCH l_cur INTO l_instance_id;
1715: --EXIT WHEN l_cur%NOTFOUND;
1716: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),0);
1717: END LOOP; -- l_ue_id_tbl loop
1718: l_inst_tbl.DELETE;
1719: END LOOP;
1720:

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

1729:
1730: CURSOR dependent_components_csr IS
1731: SELECT subject_id from csi_ii_relationships csii WHERE
1732: EXISTS (select 'x' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.subject_id)
1733: AND NOT EXISTS (select 'x' from AHL_APPLICABLE_INSTANCES where csi_item_instance_id = csii.subject_id)
1734: START WITH object_id IN (SELECT csi_item_instance_id FROM AHL_APPLICABLE_INSTANCES WHERE POSITION_ID = 0)
1735: AND trunc(nvl(csii.active_start_date, sysdate)) <= Trunc(sysdate)
1736: AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
1737: AND relationship_type_code = 'COMPONENT-OF'

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

1730: CURSOR dependent_components_csr IS
1731: SELECT subject_id from csi_ii_relationships csii WHERE
1732: EXISTS (select 'x' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.subject_id)
1733: AND NOT EXISTS (select 'x' from AHL_APPLICABLE_INSTANCES where csi_item_instance_id = csii.subject_id)
1734: START WITH object_id IN (SELECT csi_item_instance_id FROM AHL_APPLICABLE_INSTANCES WHERE POSITION_ID = 0)
1735: AND trunc(nvl(csii.active_start_date, sysdate)) <= Trunc(sysdate)
1736: AND trunc(nvl(csii.active_end_date, sysdate+1)) > Trunc(sysdate)
1737: AND relationship_type_code = 'COMPONENT-OF'
1738: CONNECT BY PRIOR subject_id = object_id

Line 1750: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID)

1746:
1747: BEGIN
1748: /*FOR dependent_component_rec IN dependent_components_csr
1749: LOOP
1750: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID)
1751: VALUES(dependent_component_rec.subject_id,1);
1752: END LOOP;*/
1753:
1754: OPEN dependent_components_csr;

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

1755: LOOP
1756: FETCH dependent_components_csr BULK COLLECT INTO l_inst_tbl LIMIT l_buffer_limit;
1757: EXIT WHEN (l_inst_tbl.count = 0);
1758: FOR j IN l_inst_tbl.FIRST..l_inst_tbl.LAST LOOP
1759: INSERT INTO AHL_APPLICABLE_INSTANCES (CSI_ITEM_INSTANCE_ID, POSITION_ID) VALUES(l_inst_tbl(j),1);
1760: END LOOP;
1761: l_inst_tbl.DELETE;
1762: END LOOP;
1763:

Line 1955: L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' ,AHL_APPLICABLE_INSTANCES AAI1 ';

1951: AND ( (0 = (SELECT COUNT(MR_ORGANIZATION_ID) FROM AHL_MR_ORGANIZATIONS WHERE MR_TITLE = AMO.MR_TITLE
1952: AND (OPERATING_ORG_ID IS NULL OR OPERATING_ORG_ID = AFH.OPERATING_ORG_ID))))) ) ) MAINTTORG ';
1953: IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
1954: <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL OR P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
1955: L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY || ' ,AHL_APPLICABLE_INSTANCES AAI1 ';
1956: END IF;
1957: L_MR_SEARCH_QUERY := L_MR_SEARCH_QUERY ||
1958: 'where b.mr_header_id = mrb.mr_header_id
1959: AND B.CSI_ITEM_INSTANCE_ID = csib.INSTANCE_ID

Line 2250: L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' ,AHL_APPLICABLE_INSTANCES AAI1 ';

2246: CSI_ITEM_INSTANCES CSIB,
2247: mtl_system_items_kfv mtl ';
2248: IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
2249: <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL OR P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
2250: L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY || ' ,AHL_APPLICABLE_INSTANCES AAI1 ';
2251: END IF;
2252: L_NR_SEARCH_QUERY := L_NR_SEARCH_QUERY ||
2253: 'where B.CSI_ITEM_INSTANCE_ID = csib.INSTANCE_ID
2254: AND csib.INVENTORY_ITEM_ID = MTL.INVENTORY_ITEM_ID

Line 2448: DELETE AHL_APPLICABLE_INSTANCES;

2444: X_UMP_SEARCH_QUERY.UMP_SEARCH_QUERY_STR := L_DUMMY_SEARCH_QUERY ;
2445: END IF;
2446:
2447: -- populate dependent instances
2448: DELETE AHL_APPLICABLE_INSTANCES;
2449: IF NVL(P_SEARCH_UMP_REC.SHOW_DEPENDENT_REQUIREMENTS,'N') = 'N' THEN
2450: IF (P_SEARCH_UMP_REC.UNIT_NAME IS NOT NULL OR NVL(P_SEARCH_UMP_REC.ITEM,'%')
2451: <> '%' OR P_SEARCH_UMP_REC.ITEM_SERIAL_NUMBER IS NOT NULL OR P_SEARCH_UMP_REC.ARRIVAL_ORG IS NOT NULL) THEN
2452: populate_unit_instances