[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MR.mr_header_id,
MR.title,
MR.revision,
MR.repetitive_flag,
MR.implement_status_code,
MR.effective_from,
MR.effective_to
FROM ahl_mr_headers_b MR
WHERE MR.mr_header_id = p_mr_header_id_csr
AND MR.repetitive_flag = 'Y'
AND TRUNC(sysdate) <= TRUNC(NVL(MR.effective_to,sysdate+1));
SELECT rel.mr_header_id,
rel.related_mr_header_id,
relationship_code
FROM ahl_mr_relationships rel,
ahl_mr_headers_b MR
WHERE (rel.mr_header_id = MR.mr_header_id
OR rel.related_mr_header_id = MR.mr_header_id)
AND (rel.mr_header_id = p_mr_header_id_csr
OR rel.related_mr_header_id = p_mr_header_id_csr)
AND TRUNC(p_effective_date) <= TRUNC(NVL(MR.effective_to, p_effective_date + 1))
AND MR.MR_STATUS_CODE IN ('DRAFT','COMPLETE','APPROVAL_PENDING');
SELECT relationship_code INTO l_mr_loop_chain_rel_cd FROM ahl_mr_loop_chain_relns WHERE mr_relationship_id = l_other_valid_start_mr;
SELECT rel.mr_header_id,
rel.start_mr_relationship_id,
rel.sequence_number,
rel.relationship_code,
mr.mr_status_code,
mr.effective_from,
mr.effective_to
FROM ahl_mr_loop_chain_relns rel,
ahl_mr_headers_b mr
WHERE mr.mr_header_id = rel.mr_header_id
AND rel.start_mr_relationship_id IN
(SELECT start_mr_relationship_id
FROM ahl_mr_loop_chain_relns A
WHERE mr_header_id = c_mr_header_id
AND ((c_rel_code IS NULL)
OR (relationship_code = c_rel_code))
AND ( c_start_mr_rel_id IS NULL
OR (start_mr_relationship_id NOT IN
(SELECT b.start_mr_relationship_id
FROM ahl_mr_headers_b a,
ahl_mr_loop_chain_relns b
WHERE a.mr_header_id = b.mr_header_id
AND b.start_mr_relationship_id = c_start_mr_rel_id))
)
)
AND ((c_effective_from IS NULL)
OR (TRUNC(c_effective_from) >= TRUNC(MR.effective_from)))
AND ((c_start_mr_header_id IS NULL)
OR (rel.start_mr_relationship_id NOT IN
(SELECT start_mr_relationship_id FROM ahl_mr_loop_chain_relns
WHERE mr_header_id IN (SELECT mr_header_id FROM
ahl_mr_headers_b WHERE title = c_start_mr_title
AND mr_header_id <> c_start_mr_header_id)
)))
AND
(c_effective_to IS NULL
OR (TRUNC(c_effective_to) < TRUNC(NVL(MR.effective_to,sysdate+1))))
AND mr.mr_status_code IN ('DRAFT','COMPLETE','APPROVAL_PENDING')
AND mr.repetitive_flag = 'Y'
AND mr.application_usg_code = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
ORDER BY rel.start_mr_relationship_id,
rel.sequence_number;
SELECT MAX(sequence_number) INTO l_mr_rel_count FROM ahl_mr_loop_chain_relns WHERE start_mr_relationship_id = l_start_mr_rel_id;
SELECT rel.mr_header_id,
rel.start_mr_relationship_id,
rel.sequence_number,
rel.relationship_code,
mr.mr_status_code,
mr.effective_from,
mr.effective_to
FROM ahl_mr_loop_chain_relns rel,
ahl_mr_headers_b mr
WHERE mr.mr_header_id = rel.mr_header_id
and rel.start_mr_relationship_id in
(select reln.start_mr_relationship_id
from ahl_mr_loop_chain_relns reln
WHERE reln.mr_header_id = c_mr_header_id
and ((c_rel_code is null)
OR (reln.relationship_code = c_rel_code))
)
-- Modified for SBE Bug#12837789
AND TRUNC(SYSDATE) < TRUNC(NVL(MR.effective_to,SYSDATE+1))
AND mr.mr_status_code IN ('DRAFT','COMPLETE','APPROVAL_PENDING')
AND MR.repetitive_flag = 'Y'
ORDER BY rel.start_mr_relationship_id,
rel.sequence_number;
SELECT MR.title,
MR_TL.description,
STATUS.MEANING status,
MR.revision,
MR.version_number,
MR.effective_from,
MR.effective_to
FROM ahl_mr_headers_b MR,
ahl_mr_headers_tl MR_TL,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS'
AND LANGUAGE = USERENV('LANG')
) STATUS
WHERE MR.mr_header_id = p_mr_header_id
AND MR_TL.LANGUAGE = USERENV('LANG')
AND STATUS.LOOKUP_CODE = MR.mr_status_code
AND MR.mr_header_id = MR_TL.mr_header_id
AND MR.repetitive_flag = 'Y'
-- Modified for SBE Bug#12837789
AND TRUNC(sysdate) < TRUNC(NVL(MR.effective_to,sysdate+1));
SELECT MAX(sequence_number) INTO l_max_rel_seq FROM ahl_mr_loop_chain_relns WHERE
start_mr_relationship_id = l_mr_rel_id;
x_mr_item_instances.delete(itemInstanceIndex);
x_mr_item_instances.DELETE;
SELECT *
FROM
(SELECT rel.sequence_number sequence_no ,
MR.mr_header_id,
MR.title,
MR_TL.description,
STATUS.MEANING status,
MR.revision,
MR.version_number,
MR.effective_from,
MR.effective_to,
rel.sequence_number,
rel.mr_relationship_id,
rel.relationship_code,
/* Modified for Bug# 12686413 - inactive MRs displayed in view loop/chain UI*/
(ROW_NUMBER() OVER( PARTITION BY rel.sequence_number ORDER BY rel.sequence_number, NVL(effective_to, SYSDATE) DESC, MR.mr_status_code)) orderSeq
FROM ahl_mr_headers_b MR,
ahl_mr_headers_tl MR_TL,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS'
AND LANGUAGE = USERENV('LANG')
) STATUS,
AHL_MR_LOOP_CHAIN_RELNS rel
WHERE MR_TL.LANGUAGE = USERENV('LANG')
AND rel.start_mr_relationship_id = c_start_mr_reln_id
AND STATUS.LOOKUP_CODE = MR.mr_status_code
AND rel.mr_header_id = MR.mr_header_id
AND MR.mr_header_id = MR_TL.mr_header_id
AND TRUNC(MR.effective_from) <= SYSDATE
AND NVL(TRUNC(MR.effective_to), SYSDATE) >= SYSDATE
ORDER BY rel.sequence_number,
MR.effective_from
) LoopChain
WHERE LoopChain.orderSeq = 1;
SELECT MAX(sequence_number) INTO l_max_seq FROM ahl_mr_loop_chain_relns WHERE start_mr_relationship_id = l_start_mr_reln_id;
x_cm_mr_chain_loop.DELETE;
x_cm_mr_chain_loop.DELETE;
SELECT unit_config_header_id, name
FROM ahl_unit_config_headers
WHERE csi_item_instance_id IN ( SELECT object_id
FROM csi_ii_relationships
START WITH subject_id = p_item_instance_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1))
CONNECT BY subject_id = PRIOR object_id
AND relationship_type_code = 'COMPONENT-OF'
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1))
)
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1));
SELECT unit_config_header_id, name
FROM ahl_unit_config_headers
WHERE csi_item_instance_id = p_item_instance_id
AND sysdate between trunc(nvl(active_start_date,sysdate))
AND trunc(nvl(active_end_date, SYSDATE+1));
SELECT MR.mr_header_id,
MR.title,
MR_TL.description,
STATUS.MEANING status,
MR.revision,
MR.version_number,
MR.effective_from,
MR.effective_to,
eff.loop_chain_seq_num,
eff.accomplish_trigger_type relationship
FROM ahl_unit_effectivities_b eff,
(SELECT LOOKUP_CODE,
MEANING
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'AHL_FMP_REVISION_STATUS'
AND LANGUAGE = USERENV('LANG')
) STATUS,
ahl_mr_headers_b MR,
ahl_mr_headers_tl MR_TL
WHERE eff.mr_header_id = MR.mr_header_id
AND MR_TL.LANGUAGE = USERENV('LANG')
AND status.lookup_code = MR.mr_status_code
AND MR.mr_header_id = MR_TL.mr_header_id
AND (eff.start_lc_ue_id = c_start_ue_id
/*Commented because, BUE populates start_lc_ue_id for the first sequence also
OR (eff.unit_effectivity_id = c_start_ue_id AND loop_chain_seq_num = 1)*/
)
AND nvl(effective_to, SYSDATE + 1) > SYSDATE
ORDER BY loop_chain_seq_num;
SELECT ii.serial_number,
(select kfv.concatenated_segments from mtl_system_items_kfv kfv
where kfv.inventory_item_id = ii.inventory_item_id
AND kfv.organization_id = ii.inv_master_organization_id) item_number,
ii.inventory_item_id,
ahl_util_uc_pkg.getcsi_locationDesc(ii.location_id, ii.location_type_code,
ii.inv_organization_id, ii.inv_subinventory_name,
ii.inv_locator_id, ii.wip_job_id) Location_description,
(select f.meaning from csi_lookups f where ii.instance_usage_code = f.lookup_code
AND f.lookup_type = 'CSI_INSTANCE_USAGE_CODE') Status,
(select p.party_name from csi_inst_party_details_v p
where p.instance_id = ii.instance_id and p.relationship_type_code = 'OWNER') owner_name,
(select mat.description from mtl_material_statuses mat where ii.INSTANCE_CONDITION_ID = mat.status_id) condition
FROM csi_item_instances ii
WHERE ii.instance_id = c_item_instance_id;
SELECT start_lc_ue_id INTO l_start_ue_id FROM ahl_unit_effectivities_b WHERE unit_effectivity_id = p_ue_id;
DELETE FROM ahl_mr_instances_temp;
INSERT INTO ahl_mr_instances_temp
(
MR_INSTANCE_TEMP_ID,
MR_EFFECTIVITY_ID,
ITEM_INSTANCE_ID,
SERIAL_NUMBER,
ITEM_NUMBER,
INVENTORY_ITEM_ID,
LOCATION,
STATUS,
OWNER,
CONDITION,
UNIT_NAME,
UC_HEADER_ID
)
VALUES
(
i,
x_mr_item_instances(i).mr_effectivity_id,
x_mr_item_instances(i).item_instance_id,
x_mr_item_instances(i).serial_number,
x_mr_item_instances(i).item_number,
x_mr_item_instances(i).inventory_item_id,
x_mr_item_instances(i).location,
x_mr_item_instances(i).status,
x_mr_item_instances(i).owner,
x_mr_item_instances(i).condition,
x_mr_item_instances(i).UNIT_NAME,
x_mr_item_instances(i).UC_HEADER_ID
);
SELECT rel.mr_header_id,
rel.start_mr_relationship_id,
rel.sequence_number,
rel.relationship_code,
mr.mr_status_code,
mr.effective_from,
mr.effective_to
FROM ahl_mr_loop_chain_relns rel,
ahl_mr_headers_b mr
WHERE mr.mr_header_id = rel.mr_header_id
AND rel.start_mr_relationship_id IN
(SELECT reln.start_mr_relationship_id
FROM ahl_mr_loop_chain_relns reln
WHERE reln.mr_header_id = c_mr_header_id
AND (c_rel_code IS NULL
OR reln.relationship_code = c_rel_code)
)
AND TRUNC(mr.effective_from) <= TRUNC(c_effective_from)
AND TRUNC(NVL(mr.effective_to,sysdate+1)) > TRUNC(c_effective_to)
AND mr.mr_status_code = 'COMPLETE'
AND mr.repetitive_flag = 'Y'
ORDER BY rel.start_mr_relationship_id,
rel.sequence_number;
x_start_mr_relns.DELETE;
SELECT MAX(sequence_number)
INTO l_mr_rel_count
FROM ahl_mr_loop_chain_relns
WHERE start_mr_relationship_id = l_start_mr_rel_id;
x_start_mr_relns.DELETE;
x_start_mr_relns.DELETE;
SELECT 'X' FROM ahl_mr_headers_b WHERE mr_header_id = p_mr_header_id_csr AND
NVL(effective_to, SYSDATE + 1) > SYSDATE;
SELECT 'X' FROM ahl_mr_headers_b WHERE mr_header_id = p_mr_header_id_csr AND
NVL(effective_to, SYSDATE + 1) > SYSDATE;
SELECT 'X' FROM ahl_mr_headers_b WHERE mr_header_id = p_mr_header_id_csr AND
NVL(effective_to, SYSDATE + 1) > SYSDATE;