[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NEW.COUNTER_ID, -- l_tmp_new_ctr_id_tbl
NEW.START_DATE_ACTIVE, -- l_tmp_new_start_date_tbl
NEW.END_DATE_ACTIVE, -- l_tmp_new_end_date_tbl
OVER.COUNTER_ID, -- l_tmp_over_ctr_id_tbl
OVER.START_DATE_ACTIVE, -- l_tmp_over_start_date_tbl
OVER.END_DATE_ACTIVE -- l_tmp_over_end_date_tbl
FROM csi_counter_template_vl NEW,
csi_counter_template_vl OVER,
AHL_RA_CTR_ASSOCIATIONS CTR
WHERE CTR.SINCE_NEW_COUNTER_ID = NEW.COUNTER_ID
AND CTR.SINCE_OVERHAUL_COUNTER_ID = OVER.COUNTER_ID(+);
SELECT US.UNIT_SCHEDULE_ID, -- l_fs_unit_sch_id_tbl
US.ARRIVAL_ORG_ID, -- l_fs_arr_org_id_tbl
US.UNIT_CONFIG_HEADER_ID, -- l_fs_uc_header_id_tbl
US.CSI_ITEM_INSTANCE_ID, -- l_fs_csi_instance_id_tbl
ORG.organization_code, -- l_fs_org_code_tbl
TRUNC(NVL(US.ACTUAL_ARRIVAL_TIME,US.EST_ARRIVAL_TIME)) -- l_fs_arrival_date_tbl
FROM AHL_UNIT_SCHEDULES_V US,org_organization_definitions org
WHERE TRUNC(NVL(US.ACTUAL_ARRIVAL_TIME,US.EST_ARRIVAL_TIME)) BETWEEN C_START_DATE AND C_END_DATE
AND ORG.ORGANIZATION_ID = US.ARRIVAL_ORG_ID
AND UC_STATUS_CODE IN ('COMPLETE', 'INCOMPLETE');
SELECT CIIR.OBJECT_ID, -- l_dtls_object_id_tbl
CIIR.SUBJECT_ID, -- l_dtls_subject_id_tbl
DECODE(UC.CSI_ITEM_INSTANCE_ID,
NULL,CIIR.POSITION_REFERENCE,
(SELECT RELATIONSHIP_ID
FROM AHL_MC_RELATIONSHIPS MCR,
AHL_UNIT_CONFIG_HEADERS UCI
WHERE MCR.MC_HEADER_ID = UCI.MASTER_CONFIG_ID
AND MCR.PARENT_RELATIONSHIP_ID IS NULL
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
CII.INVENTORY_ITEM_ID, -- l_dtls_inv_item_id_tbl
CII.INV_MASTER_ORGANIZATION_ID, -- l_dtls_inv_master_org_id_tbl
CII.INVENTORY_REVISION, -- l_dtls_inv_revision_tbl
CII.QUANTITY, -- l_dtls_quantity_tbl
CII.UNIT_OF_MEASURE, -- l_dtls_uom_tbl
CII.SERIAL_NUMBER, -- l_dtls_srl_no_tbl
KFV.CONCATENATED_SEGMENTS, -- l_dtls_item_name_tbl
KFV.DESCRIPTION -- l_dtls_item_desc_tbl
FROM CSI_II_RELATIONSHIPS CIIR,
CSI_ITEM_INSTANCES CII,
--MTL_SYSTEM_ITEMS MSI,
MTL_SYSTEM_ITEMS_KFV KFV,
AHL_UNIT_CONFIG_HEADERS UC
WHERE CII.INSTANCE_ID = CIIR.SUBJECT_ID
AND CII.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CII.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
--AND KFV.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
--AND KFV.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND UC.CSI_ITEM_INSTANCE_ID(+) = CIIR.SUBJECT_ID
START WITH CIIR.OBJECT_ID = c_csi_instance_id
AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
CONNECT BY PRIOR CIIR.SUBJECT_ID = CIIR.OBJECT_ID
AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1));
Select A.OBJECT_ID, -- l_dtls_object_id_tbl
A.SUBJECT_ID, -- l_dtls_subject_id_tbl
DECODE(A.CSI_ITEM_INSTANCE_ID,
NULL,A.POSITION_REFERENCE,
MCR.RELATIONSHIP_ID) X, -- l_dtls_pos_ref_tbl
A.INVENTORY_ITEM_ID, -- l_dtls_inv_item_id_tbl
A.INV_MASTER_ORGANIZATION_ID, -- l_dtls_inv_master_org_id_tbl
A.INVENTORY_REVISION, -- l_dtls_inv_revision_tbl
A.QUANTITY, -- l_dtls_quantity_tbl
A.UNIT_OF_MEASURE, -- l_dtls_uom_tbl
A.SERIAL_NUMBER, -- l_dtls_srl_no_tbl
A.CONCATENATED_SEGMENTS, -- l_dtls_item_name_tbl
A.DESCRIPTION, -- l_dtls_item_desc_tbl
--Added by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
/* This is a point of contention. For MTBF flow, the header node for the Sub-config is considered for
MTBF definition. But for part changes, the position_key correspnding to the node where the sub-config is considered
So we need to consider both */
A.POSITION_REFERENCE --l_dtls_pos_ref_his_tbl
FROM (SELECT CIIR.OBJECT_ID, -- l_dtls_object_id_tbl
CIIR.SUBJECT_ID, -- l_dtls_subject_id_tbl
CII.INVENTORY_ITEM_ID, -- l_dtls_inv_item_id_tbl
CII.INV_MASTER_ORGANIZATION_ID, -- l_dtls_inv_master_org_id_tbl
CII.INVENTORY_REVISION, -- l_dtls_inv_revision_tbl
CII.QUANTITY, -- l_dtls_quantity_tbl
CII.UNIT_OF_MEASURE, -- l_dtls_uom_tbl
CII.SERIAL_NUMBER, -- l_dtls_srl_no_tbl
KFV.CONCATENATED_SEGMENTS, -- l_dtls_item_name_tbl
KFV.DESCRIPTION, -- l_dtls_item_desc_tbl
UC.CSI_ITEM_INSTANCE_ID,
CIIR.POSITION_REFERENCE,
UCI.MASTER_CONFIG_ID
FROM ( Select CIIRI.SUBJECT_ID ,
CIIRI.OBJECT_ID ,
CIIRI.POSITION_REFERENCE
from CSI_II_RELATIONSHIPS CIIRI
START WITH CIIRI.OBJECT_ID = c_csi_instance_id
AND CIIRI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(CIIRI.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CIIRI.ACTIVE_END_DATE,SYSDATE+1))
CONNECT BY PRIOR CIIRI.SUBJECT_ID = CIIRI.OBJECT_ID
AND CIIRI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(CIIRI.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CIIRI.ACTIVE_END_DATE,SYSDATE+1))) CIIR,
CSI_ITEM_INSTANCES CII,
MTL_SYSTEM_ITEMS_KFV KFV,
AHL_UNIT_CONFIG_HEADERS UC,
AHL_UNIT_CONFIG_HEADERS UCI
WHERE CII.INSTANCE_ID = CIIR.SUBJECT_ID
AND CII.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CII.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND UC.CSI_ITEM_INSTANCE_ID(+) = CIIR.SUBJECT_ID
AND UCI.UNIT_CONFIG_HEADER_ID(+) = AHL_UTIL_UC_PKG.GET_SUB_UC_HEADER_ID(UC.CSI_ITEM_INSTANCE_ID)) A,
AHL_MC_RELATIONSHIPS MCR
WHERE MCR.MC_HEADER_ID(+) = A.MASTER_CONFIG_ID
AND nvl(MCR.PARENT_RELATIONSHIP_ID,-1) = -1;
SELECT DTLS.COUNTER_ID,
DTLS.MTBF_VALUE
FROM AHL_RA_DEFINITION_HDR HDR,
AHL_RA_DEFINITION_DTLS DTLS
WHERE HDR.RA_DEFINITION_HDR_ID = DTLS.RA_DEFINITION_HDR_ID
AND HDR.RELATIONSHIP_ID = TO_NUMBER(c_relationship_id)
AND HDR.INVENTORY_ITEM_ID = c_inv_item_id
AND HDR.INVENTORY_ORG_ID = c_inv_org_id
AND (HDR.ITEM_REVISION IS NULL OR HDR.ITEM_REVISION = c_inv_item_revision)
-- AND nvl(HDR.ITEM_REVISION,-1) = nvl(c_inv_item_revision,-1)
AND DTLS.MTBF_VALUE IS NOT NULL;
Select pos.path_position_id
from ahl_mc_path_position_nodes pos,
ahl_mc_relationships rel,
ahl_mc_headers_b hdr
where rel.mc_header_id = hdr.mc_header_id
and rel.relationship_id = to_number(c_relationship_id)
and hdr.mc_id = pos.mc_id
and hdr.version_number = nvl(pos.version_number, hdr.version_number)
and pos.position_key = rel.position_key
and pos.sequence in (select max(sequence)
from ahl_mc_path_position_nodes
where path_position_id = pos.path_position_id);
Select FORECAST_DESIGNATOR
from AHL_RA_FCT_ASSOCIATIONS
where ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL'
and FORECAST_DESIGNATOR <> c_fct_designator
and ORGANIZATION_ID = c_arrival_org_id;
Select MCR.RELATIONSHIP_ID,
CII.QUANTITY,
CII.UNIT_OF_MEASURE,
CII.INVENTORY_ITEM_ID,
CII.INV_MASTER_ORGANIZATION_ID,
CII.INVENTORY_REVISION,
CII.SERIAL_NUMBER,
KFV.CONCATENATED_SEGMENTS,
KFV.DESCRIPTION
INTO l_root_pos_ref_code,
l_root_quantity,
l_root_uom,
l_root_inv_item_id,
l_root_inv_master_org_id,
l_root_item_revision,
l_root_srl_no,
l_root_item_name,
l_root_item_desc
FROM AHL_UNIT_CONFIG_HEADERS UC,
CSI_ITEM_INSTANCES CII,
MTL_SYSTEM_ITEMS_KFV KFV,
ahl_mc_relationships MCR
WHERE UC.UNIT_CONFIG_HEADER_ID = l_fs_uc_header_id_tbl(i)
AND UC.CSI_ITEM_INSTANCE_ID = CII.INSTANCE_ID
AND KFV.INVENTORY_ITEM_ID = CII.INVENTORY_ITEM_ID
AND KFV.ORGANIZATION_ID = CII.INV_MASTER_ORGANIZATION_ID
AND MCR.mc_header_id = UC.MASTER_CONFIG_ID
and MCR.parent_relationship_id is null;
fnd_log.string(fnd_log.level_statement,l_full_name,' No Nodes are Returned - Inserting Root Node at FIRST - index 1');
fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- No Nodes Fetched --Inserting Root Node at FIRST --');
fnd_log.string(fnd_log.level_statement,l_full_name,' Nodes are Returned - Inserting Root Node at LAST - index LAST + 1');
fnd_file.put_line(fnd_file.log, 'RA Analyser Process ---- All Nodes Fetched --Inserting Root Node at LAST --');
SELECT CREATED_FROM_COUNTER_TMPL_ID
INTO l_ctr_template_id
FROM csi_counters_vl
WHERE counter_id = l_ctr_values_tbl(n).COUNTER_ID
AND ( (CREATED_FROM_COUNTER_TMPL_ID IN (SELECT SINCE_NEW_COUNTER_ID
FROM AHL_RA_CTR_ASSOCIATIONS))
OR ( CREATED_FROM_COUNTER_TMPL_ID IN (SELECT SINCE_OVERHAUL_COUNTER_ID
FROM AHL_RA_CTR_ASSOCIATIONS)
AND EXISTS (SELECT 1
FROM CSI_COUNTER_READINGS
WHERE COUNTER_ID = l_ctr_values_tbl(n).COUNTER_ID
AND NET_READING IS NOT NULL
AND DISABLED_FLAG = 'N')
)
);
Select FORECAST_DESIGNATOR
into l_fct_designator
from AHL_RA_FCT_ASSOCIATIONS
where ASSOCIATION_TYPE_CODE = 'ASSOC_MTBF'
and ORGANIZATION_ID = l_fs_arr_org_id_tbl(i);
Select count(*)
into l_prob_attrib_c_tmp
from (Select chg.removed_instance_id
from ahl_part_changes chg,
csi_item_instances cii,
ahl_prd_dispositions_b dis
where chg.part_change_type IN ('R','S')
and chg.part_change_id = dis.part_change_id
and chg.removal_code in (Select Removal_Code from AHL_RA_SETUPS where setup_code = 'REMOVAL_CODE')
and dis.condition_id in (Select Status_Id from AHL_RA_SETUPS where setup_code = 'ITEM_STATUS')
and chg.mc_relationship_id = to_number(l_path_position_id_tbl(q))
AND cii.instance_id = chg.removed_instance_id
AND cii.inventory_item_id = l_dtls_inv_item_id_tbl(j)
AND cii.inv_master_organization_id = l_dtls_inv_master_org_id_tbl(j)) query_c;
Select count(*)
into l_prob_attrib_b_tmp
from (Select chg.removed_instance_id
,ctr.net_reading
from ahl_part_changes chg,(Select assoc.source_object_id,
cv.net_reading,
cv.VALUE_TIMESTAMP,
cv.counter_id
from csi_counter_associations assoc,
csi_counter_readings cv,
csi_counters_vl cb1,
csi_counters_vl cb2
where assoc.source_object_code = 'CP'
and assoc.counter_id = cb2.counter_id
and cb1.counter_id = l_active_ctr_id_tbl(p)
and cb1.CREATED_FROM_COUNTER_TMPL_ID = cb2.CREATED_FROM_COUNTER_TMPL_ID
and cv.counter_id = cb2.counter_id
AND cv.disabled_flag = 'N') ctr,
csi_item_instances cii,
ahl_prd_dispositions_b dis
where chg.part_change_type IN ('R','S')
and chg.part_change_id = dis.part_change_id
AND chg.removed_instance_id = ctr.source_object_id
and chg.removal_code in (Select Removal_Code from AHL_RA_SETUPS where setup_code = 'REMOVAL_CODE')
and dis.condition_id in (Select Status_Id from AHL_RA_SETUPS where setup_code = 'ITEM_STATUS')
and chg.mc_relationship_id = to_number(l_path_position_id_tbl(q))
AND ctr.value_timestamp = (Select max(maxcv.value_timestamp)
from csi_counter_readings maxcv
where ctr.counter_id = maxcv.counter_id
and trunc(maxcv.value_timestamp) <= trunc(chg.REMOVAL_DATE))
AND cii.instance_id = chg.removed_instance_id
AND cii.inventory_item_id = l_dtls_inv_item_id_tbl(j)
AND cii.inv_master_organization_id = l_dtls_inv_master_org_id_tbl(j)) query_b
where l_prob_attrib_a > query_b.net_reading;
SELECT count(*)
INTO l_prob_attrib_d
FROM csi_ii_relationships CIIR,
csi_item_instances cii,
(SELECT assoc.source_object_id,
cv.net_reading,
cv.VALUE_TIMESTAMP
FROM csi_counter_associations assoc,
csi_counter_readings cv,
csi_counters_vl cb1,
csi_counters_vl cb2
WHERE assoc.source_object_code = 'CP'
AND assoc.counter_id = cb2.counter_id
AND cb1.counter_id = l_active_ctr_id_tbl(p)
AND cb1.CREATED_FROM_COUNTER_TMPL_ID = cb2.CREATED_FROM_COUNTER_TMPL_ID
AND cv.counter_id = cb2.counter_id
AND cv.value_timestamp = (Select max(value_timestamp)
from csi_counter_readings maxcv
where cv.counter_id = maxcv.counter_id)
AND cv.disabled_flag = 'N') ctr
WHERE cii.instance_id = CIIR.subject_id
AND CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
AND CII.inv_master_organization_id = l_dtls_inv_master_org_id_tbl(j)
--Modified by mpothuku on 09-Nov-2006 for fixing the Bug# 5651645
--AND ciir.position_reference = l_dtls_pos_ref_tbl(j)
AND ciir.position_reference = l_dtls_pos_ref_his_tbl(j)
AND ctr.net_reading > l_prob_attrib_a
AND ctr.source_object_id = cii.instance_id -- CIIR.subject_id -- Perf Fix 4777658
AND ciir.relationship_type_code = 'COMPONENT-OF'
AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1));
Select FORECAST_DESIGNATOR
into l_fct_designator
from AHL_RA_FCT_ASSOCIATIONS
where ASSOCIATION_TYPE_CODE = 'ASSOC_HISTORICAL'
and PROBABILITY_FROM <= l_prob_value
and ( PROBABILITY_TO > l_prob_value
OR (l_prob_value = 100 AND PROBABILITY_TO >= l_prob_value))
and ORGANIZATION_ID = l_fs_arr_org_id_tbl(i);
l_forecast_interface_tbl(l_fct_index).LAST_UPDATE_DATE := sysdate;
l_forecast_interface_tbl(l_fct_index).LAST_UPDATED_BY := fnd_global.USER_ID;
l_forecast_interface_tbl(l_fct_index).LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
l_forecast_interface_tbl(l_fct_index).PROGRAM_UPDATE_DATE := null;
fnd_file.put_line(fnd_file.log, ' -- Inserting first rec in Designator Data -- ');
fnd_log.string(fnd_log.level_statement,l_full_name,' -- Insertinf first rec in Designator Data -- ');
SELECT mrp_atp_schedule_temp_s.NEXTVAL
INTO l_dummy_identifier
from dual;
SELECT COUNT(*)
INTO l_forecast_osp_qty_tbl(l_fct_index)
FROM AHL_OSP_ORDER_LINES_V OSPL,
AHL_OSP_ORDERS_B OSP
WHERE OSPL.OSP_ORDER_ID = OSP.OSP_ORDER_ID
AND OSP.STATUS_CODE <> 'CLOSED'
AND INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
AND OSPL.INVENTORY_ORG_ID= l_fs_arr_org_id_tbl(i)
AND NVL(TRUNC(OSPL.NEED_BY_DATE), FND_API.G_MISS_DATE) = l_fs_arrival_date_tbl(i);
Select nvl(sum(nvl(QTY_GRP,0)),0) QTY
INTO l_forecast_vwp_qty_tbl(l_fct_index)
FROM (
Select DISTINCT ITEM_INSTANCE AS ITEM_INSTANCE_GRP,
VISIT_QUANTITY QTY_GRP
From (
-- Total Quantity from UC Tree in a Visit.
SELECT CII.INSTANCE_ID AS ITEM_INSTANCE,
CII.QUANTITY AS VISIT_QUANTITY
FROM CSI_II_RELATIONSHIPS CIIR,
CSI_ITEM_INSTANCES CII
WHERE CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
AND CII.INSTANCE_ID = CIIR.SUBJECT_ID
START WITH CIIR.OBJECT_ID IN (
Select DISTINCT Visit.ITEM_INSTANCE_ID
from AHL_VISITS_B Visit,
AHL_SIMULATION_PLANS_B SPL
Where Visit.unit_Schedule_id is NULL
AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
AND SPL.PRIMARY_PLAN_FLAG = 'Y'
AND Visit.organization_id = l_fs_arr_org_id_tbl(i)
AND l_fs_arrival_date_tbl(i) between TRUNC(Visit.START_DATE_TIME) AND TRUNC(Visit.CLOSE_DATE_TIME)
)
AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
CONNECT BY PRIOR CIIR.SUBJECT_ID = CIIR.OBJECT_ID
AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
UNION ALL
-- Total Quantity from Root Node of a Visit.
Select Visit.ITEM_INSTANCE_ID AS ITEM_INSTANCE,
CII.QUANTITY AS VISIT_QUANTITY
from AHL_VISITS_B Visit,
AHL_SIMULATION_PLANS_B SPL,
CSI_ITEM_INSTANCES CII
Where Visit.unit_Schedule_id is NOT NULL
AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
AND SPL.PRIMARY_PLAN_FLAG = 'Y'
AND Visit.organization_id = l_fs_arr_org_id_tbl(i)
AND l_fs_arrival_date_tbl(i) between TRUNC(Visit.START_DATE_TIME) AND TRUNC(Visit.CLOSE_DATE_TIME)
AND Visit.ITEM_INSTANCE_ID = CII.INSTANCE_ID
AND CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
UNION ALL
-- Total Quantity from Visit, which does not have a unit at the header level.
-- Without Tree Reversal
Select CII.INSTANCE_ID AS ITEM_INSTANCE,
CII.QUANTITY AS VISIT_QUANTITY
from AHL_VISIT_TASKS_B TASKS,
AHL_VISITS_B Visit,
AHL_SIMULATION_PLANS_B SPL,
CSI_ITEM_INSTANCES CII
Where Visit.VISIT_ID = TASKS.Visit_id
AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
AND SPL.PRIMARY_PLAN_FLAG = 'Y'
AND Visit.unit_Schedule_id is NULL
AND Visit.organization_id = l_fs_arr_org_id_tbl(i)
AND l_fs_arrival_date_tbl(i) between TRUNC(Visit.START_DATE_TIME) AND TRUNC(Visit.CLOSE_DATE_TIME)
AND TASKS.INSTANCE_ID = CII.INSTANCE_ID
AND CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
UNION ALL
-- Total Quantity from Visit, which does not have a unit at the header level.
-- With Tree Reversal
SELECT CII.INSTANCE_ID AS ITEM_INSTANCE,
CII.QUANTITY AS VISIT_QUANTITY
FROM CSI_II_RELATIONSHIPS CIIR,
CSI_ITEM_INSTANCES CII
WHERE CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
AND CII.INSTANCE_ID = CIIR.SUBJECT_ID
START WITH CIIR.OBJECT_ID IN (
Select CII.INSTANCE_ID AS ITEM_INSTANCE
from AHL_VISIT_TASKS_B TASKS,
AHL_VISITS_B Visit,
AHL_SIMULATION_PLANS_B SPL,
CSI_ITEM_INSTANCES CII
Where Visit.VISIT_ID = TASKS.Visit_id
AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
AND SPL.PRIMARY_PLAN_FLAG = 'Y'
AND Visit.unit_Schedule_id is NULL
AND Visit.organization_id = l_fs_arr_org_id_tbl(i)
AND l_fs_arrival_date_tbl(i) between TRUNC(Visit.START_DATE_TIME) AND TRUNC(Visit.CLOSE_DATE_TIME)
AND TASKS.INSTANCE_ID = CII.INSTANCE_ID
AND CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
)
AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
CONNECT BY PRIOR CIIR.SUBJECT_ID = CIIR.OBJECT_ID
AND CIIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(CIIR.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CIIR.ACTIVE_END_DATE,SYSDATE+1))
)
);
SELECT nvl(SUM(nvl(TRANSACTION_QUANTITY,0)),0)
INTO l_forecast_non_qty_tbl(l_fct_index)
FROM MTL_ONHAND_QUANTITIES QUANT,
MTL_SECONDARY_INVENTORIES SI
WHERE QUANT.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
AND QUANT.ORGANIZATION_ID = l_fs_arr_org_id_tbl(i)
AND QUANT.SUBINVENTORY_CODE = SI.SECONDARY_INVENTORY_NAME
AND QUANT.ORGANIZATION_ID = SI.ORGANIZATION_ID
AND SI.AVAILABILITY_TYPE <> 1;
fnd_log.string(fnd_log.level_statement,l_full_name,' ---- INSERTING MRP Recs for Dummy FCT ASSOCIATIONS ----');
fnd_file.put_line(fnd_file.log, ' ---- INSERTING MRP Recs for Dummy FCT ASSOCIATIONS ----');
fnd_log.string(fnd_log.level_statement,l_full_name,' ---- INSERTING FOR DUMMY ASSOC ----' || l_dummy_fct_desg_tbl(f));
l_forecast_interface_tbl(l_fct_index).LAST_UPDATE_DATE := sysdate;
l_forecast_interface_tbl(l_fct_index).LAST_UPDATED_BY := fnd_global.USER_ID;
l_forecast_interface_tbl(l_fct_index).LAST_UPDATE_LOGIN := fnd_global.LOGIN_ID;
l_forecast_interface_tbl(l_fct_index).PROGRAM_UPDATE_DATE := null;
fnd_log.string(fnd_log.level_statement,l_full_name,' -- Inserting first rec in Designator Data -DUMMY- ');