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_UNIT_EFFECTIVITIES_B

Line 121: FROM ahl_unit_effectivities_b

117: l_sql_segment_count NUMBER;
118: -- Added for deferral details.
119: CURSOR ahl_defer_to_ue_csr(p_unit_effectivity_id IN NUMBER) IS
120: SELECT unit_effectivity_id
121: FROM ahl_unit_effectivities_b
122: WHERE defer_from_ue_id = p_unit_effectivity_id
123: AND rownum < 2;
124: -- cursor to actually fecth details
125: -- anraj: added UMP.cs_incident_id, UMP.cs_incident_number for bug#4133332

Line 474: --l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= ''AHL'' ';

470: l_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_V UMP WHERE 0=0 ';
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';

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 517: l_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= :APPL_USG_CODE ';

513: null;
514: END IF;
515: END IF; -- p_search_mr_instance_rec.INCIDENT_TYPE_ID
516: ELSE -- p_search_mr_instance_rec.unit_name
517: l_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= :APPL_USG_CODE ';
518:
519: l_mr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_MR_HEADERS_B MR WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.mr_header_id = MR.mr_header_id ';
520: l_bindvar_tbl(l_bind_index) := ltrim(rtrim(fnd_profile.value('AHL_APPLN_USAGE')));
521: l_bind_index := l_bind_index + 1;

Line 519: l_mr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_MR_HEADERS_B MR WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.mr_header_id = MR.mr_header_id ';

515: END IF; -- p_search_mr_instance_rec.INCIDENT_TYPE_ID
516: ELSE -- p_search_mr_instance_rec.unit_name
517: l_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP WHERE UMP.application_usg_code= :APPL_USG_CODE ';
518:
519: l_mr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, AHL_MR_HEADERS_B MR WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.mr_header_id = MR.mr_header_id ';
520: l_bindvar_tbl(l_bind_index) := ltrim(rtrim(fnd_profile.value('AHL_APPLN_USAGE')));
521: l_bind_index := l_bind_index + 1;
522: IF p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL THEN
523: IF nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%' THEN

Line 524: l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, CS_INCIDENTS_ALL_B CI, CS_INCIDENT_TYPES_VL CIT WHERE UMP.application_usg_code= :APPL_USG_CODE ';

520: l_bindvar_tbl(l_bind_index) := ltrim(rtrim(fnd_profile.value('AHL_APPLN_USAGE')));
521: l_bind_index := l_bind_index + 1;
522: IF p_search_mr_instance_rec.INCIDENT_TYPE_ID IS NOT NULL THEN
523: IF nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%' THEN
524: l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP, CS_INCIDENTS_ALL_B CI, CS_INCIDENT_TYPES_VL CIT WHERE UMP.application_usg_code= :APPL_USG_CODE ';
525: 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 AND CI.incident_type_id = CIT.incident_type_id AND CIT.incident_type_id = :INC_TYPE_ID ';
526:
527: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
528: l_bind_index := l_bind_index + 1;

Line 534: l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,CS_INCIDENTS_ALL_B CI,CS_INCIDENT_TYPES_VL CIT ';

530: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;
531: l_bind_index := l_bind_index + 1;
532:
533: ELSE -- servc req num is null.
534: l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,CS_INCIDENTS_ALL_B CI,CS_INCIDENT_TYPES_VL CIT ';
535: l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.cs_incident_id = CI.incident_id ';
536: 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 ';
537:
538: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.INCIDENT_TYPE_ID;

Line 544: l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,CS_INCIDENTS_ALL_B CI ';

540: END IF; -- p_search_mr_instance_rec.SERVICE_REQ_NUM
541: ELSE -- INCIDENT_TYPE_ID is null
542: IF (nvl(p_search_mr_instance_rec.SERVICE_REQ_NUM, '%') <> '%') THEN
543: -- servc req is not null
544: l_nr_select_sql_string := 'SELECT UMP.unit_effectivity_id FROM AHL_UNIT_EFFECTIVITIES_B UMP,CS_INCIDENTS_ALL_B CI ';
545: l_nr_select_sql_string := l_nr_select_sql_string || 'WHERE UMP.application_usg_code= :APPL_USG_CODE AND UMP.cs_incident_id = CI.incident_id AND CI.incident_number like :CS_SERVC_NUM ';
546:
547: l_bindvar_tbl(l_bind_index) := p_search_mr_instance_rec.SERVICE_REQ_NUM;
548: l_bind_index := l_bind_index + 1;

Line 735: l_sql_string := l_sql_string || ' AND NOT EXISTS (SELECT ''x'' FROM AHL_UNIT_EFFECTIVITIES_B PARENT_UE, AHL_UE_RELATIONSHIPS CHILD_UER';

731: l_sql_string := l_sql_string || ' AND NOT EXISTS (SELECT ''x'' FROM AHL_UE_RELATIONSHIPS WHERE RELATED_UE_ID = UMP.unit_effectivity_id)';
732: ELSE
733: -- Added to fix bug# 6972854.
734: -- Child MRs for parent MR that is Init-Accomplished should not be displayed.
735: l_sql_string := l_sql_string || ' AND NOT EXISTS (SELECT ''x'' FROM AHL_UNIT_EFFECTIVITIES_B PARENT_UE, AHL_UE_RELATIONSHIPS CHILD_UER';
736: l_sql_string := l_sql_string || ' WHERE PARENT_UE.UNIT_EFFECTIVITY_ID = CHILD_UER.ORIGINATOR_UE_ID AND CHILD_UER.RELATED_UE_ID = UMP.unit_effectivity_id AND PARENT_UE.STATUS_CODE = ''INIT-ACCOMPLISHED'') ';
737:
738: END IF;
739: END IF;

Line 1300: l_unit_related_ii_sql := l_unit_related_ii_sql || 'and EXISTS (select ''x'' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.instance_id)';

1296: l_bind_index := l_bind_index + 1;
1297: END IF;
1298:
1299: IF p_search_mr_instance_rec.components_flag = 'N' THEN
1300: l_unit_related_ii_sql := l_unit_related_ii_sql || 'and EXISTS (select ''x'' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.instance_id)';
1301: END IF;
1302:
1303: AHL_OSP_UTIL_PKG.OPEN_SEARCH_CURSOR
1304: (

Line 1334: EXISTS (select 'x' from ahl_unit_effectivities_b UE where UE.csi_item_instance_id = csii.subject_id)

1330: p_search_mr_instance_rec IN AHL_UMP_SMRINSTANCE_PVT.Search_MRInstance_Rec_Type)IS
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)