[Home] [Help]
101: CII.SERIAL_NUMBER, -- l_dtls_srl_no_tbl
102: KFV.CONCATENATED_SEGMENTS, -- l_dtls_item_name_tbl
103: KFV.DESCRIPTION -- l_dtls_item_desc_tbl
104: FROM CSI_II_RELATIONSHIPS CIIR,
105: CSI_ITEM_INSTANCES CII,
106: --MTL_SYSTEM_ITEMS MSI,
107: MTL_SYSTEM_ITEMS_KFV KFV,
108: AHL_UNIT_CONFIG_HEADERS UC
109: WHERE CII.INSTANCE_ID = CIIR.SUBJECT_ID
167: CONNECT BY PRIOR CIIRI.SUBJECT_ID = CIIRI.OBJECT_ID
168: AND CIIRI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
169: AND TRUNC(NVL(CIIRI.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
170: AND TRUNC(SYSDATE) < TRUNC(NVL(CIIRI.ACTIVE_END_DATE,SYSDATE+1))) CIIR,
171: CSI_ITEM_INSTANCES CII,
172: MTL_SYSTEM_ITEMS_KFV KFV,
173: AHL_UNIT_CONFIG_HEADERS UC,
174: AHL_UNIT_CONFIG_HEADERS UCI
175: WHERE CII.INSTANCE_ID = CIIR.SUBJECT_ID
265: l_dtls_pos_ref_his_tbl Varchar30TabType;
266:
267: -- These temp variables have one - to - one correspondance to each another.
268: l_root_pos_ref_code AHL_MC_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
269: l_root_quantity CSI_ITEM_INSTANCES.QUANTITY%TYPE;
270: l_root_uom CSI_ITEM_INSTANCES.UNIT_OF_MEASURE%TYPE;
271: l_root_inv_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE;
272: l_root_inv_master_org_id CSI_ITEM_INSTANCES.INV_MASTER_ORGANIZATION_ID%TYPE;
273: l_root_item_revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE;
266:
267: -- These temp variables have one - to - one correspondance to each another.
268: l_root_pos_ref_code AHL_MC_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
269: l_root_quantity CSI_ITEM_INSTANCES.QUANTITY%TYPE;
270: l_root_uom CSI_ITEM_INSTANCES.UNIT_OF_MEASURE%TYPE;
271: l_root_inv_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE;
272: l_root_inv_master_org_id CSI_ITEM_INSTANCES.INV_MASTER_ORGANIZATION_ID%TYPE;
273: l_root_item_revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE;
274: l_root_srl_no CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE;
267: -- These temp variables have one - to - one correspondance to each another.
268: l_root_pos_ref_code AHL_MC_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
269: l_root_quantity CSI_ITEM_INSTANCES.QUANTITY%TYPE;
270: l_root_uom CSI_ITEM_INSTANCES.UNIT_OF_MEASURE%TYPE;
271: l_root_inv_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE;
272: l_root_inv_master_org_id CSI_ITEM_INSTANCES.INV_MASTER_ORGANIZATION_ID%TYPE;
273: l_root_item_revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE;
274: l_root_srl_no CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE;
275: l_root_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
268: l_root_pos_ref_code AHL_MC_RELATIONSHIPS.RELATIONSHIP_ID%TYPE;
269: l_root_quantity CSI_ITEM_INSTANCES.QUANTITY%TYPE;
270: l_root_uom CSI_ITEM_INSTANCES.UNIT_OF_MEASURE%TYPE;
271: l_root_inv_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE;
272: l_root_inv_master_org_id CSI_ITEM_INSTANCES.INV_MASTER_ORGANIZATION_ID%TYPE;
273: l_root_item_revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE;
274: l_root_srl_no CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE;
275: l_root_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
276: l_root_item_desc MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE;
269: l_root_quantity CSI_ITEM_INSTANCES.QUANTITY%TYPE;
270: l_root_uom CSI_ITEM_INSTANCES.UNIT_OF_MEASURE%TYPE;
271: l_root_inv_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE;
272: l_root_inv_master_org_id CSI_ITEM_INSTANCES.INV_MASTER_ORGANIZATION_ID%TYPE;
273: l_root_item_revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE;
274: l_root_srl_no CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE;
275: l_root_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
276: l_root_item_desc MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE;
277:
270: l_root_uom CSI_ITEM_INSTANCES.UNIT_OF_MEASURE%TYPE;
271: l_root_inv_item_id CSI_ITEM_INSTANCES.INVENTORY_ITEM_ID%TYPE;
272: l_root_inv_master_org_id CSI_ITEM_INSTANCES.INV_MASTER_ORGANIZATION_ID%TYPE;
273: l_root_item_revision CSI_ITEM_INSTANCES.INVENTORY_REVISION%TYPE;
274: l_root_srl_no CSI_ITEM_INSTANCES.SERIAL_NUMBER%TYPE;
275: l_root_item_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
276: l_root_item_desc MTL_SYSTEM_ITEMS_KFV.DESCRIPTION%TYPE;
277:
278:
693: l_root_srl_no,
694: l_root_item_name,
695: l_root_item_desc
696: FROM AHL_UNIT_CONFIG_HEADERS UC,
697: CSI_ITEM_INSTANCES CII,
698: MTL_SYSTEM_ITEMS_KFV KFV,
699: ahl_mc_relationships MCR
700: WHERE UC.UNIT_CONFIG_HEADER_ID = l_fs_uc_header_id_tbl(i)
701: AND UC.CSI_ITEM_INSTANCE_ID = CII.INSTANCE_ID
1196: Select count(*)
1197: into l_prob_attrib_c_tmp
1198: from (Select chg.removed_instance_id
1199: from ahl_part_changes chg,
1200: csi_item_instances cii,
1201: ahl_prd_dispositions_b dis
1202: where chg.part_change_type IN ('R','S')
1203: and chg.part_change_id = dis.part_change_id
1204: and chg.removal_code in (Select Removal_Code from AHL_RA_SETUPS where setup_code = 'REMOVAL_CODE')
1235: and cb1.counter_id = l_active_ctr_id_tbl(p)
1236: and cb1.CREATED_FROM_COUNTER_TMPL_ID = cb2.CREATED_FROM_COUNTER_TMPL_ID
1237: and cv.counter_id = cb2.counter_id
1238: AND cv.disabled_flag = 'N') ctr,
1239: csi_item_instances cii,
1240: ahl_prd_dispositions_b dis
1241: where chg.part_change_type IN ('R','S')
1242: and chg.part_change_id = dis.part_change_id
1243: AND chg.removed_instance_id = ctr.source_object_id
1289: -- For deriving l_prob_attrib_d
1290: SELECT count(*)
1291: INTO l_prob_attrib_d
1292: FROM csi_ii_relationships CIIR,
1293: csi_item_instances cii,
1294: (SELECT assoc.source_object_id,
1295: cv.net_reading,
1296: cv.VALUE_TIMESTAMP
1297: FROM csi_counter_associations assoc,
1670: -- Total Quantity from UC Tree in a Visit.
1671: SELECT CII.INSTANCE_ID AS ITEM_INSTANCE,
1672: CII.QUANTITY AS VISIT_QUANTITY
1673: FROM CSI_II_RELATIONSHIPS CIIR,
1674: CSI_ITEM_INSTANCES CII
1675: WHERE CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1676: AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
1677: AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
1678: AND CII.INSTANCE_ID = CIIR.SUBJECT_ID
1702: Select Visit.ITEM_INSTANCE_ID AS ITEM_INSTANCE,
1703: CII.QUANTITY AS VISIT_QUANTITY
1704: from AHL_VISITS_B Visit,
1705: AHL_SIMULATION_PLANS_B SPL,
1706: CSI_ITEM_INSTANCES CII
1707: Where Visit.unit_Schedule_id is NOT NULL
1708: AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
1709: AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
1710: AND SPL.PRIMARY_PLAN_FLAG = 'Y'
1723: CII.QUANTITY AS VISIT_QUANTITY
1724: from AHL_VISIT_TASKS_B TASKS,
1725: AHL_VISITS_B Visit,
1726: AHL_SIMULATION_PLANS_B SPL,
1727: CSI_ITEM_INSTANCES CII
1728: Where Visit.VISIT_ID = TASKS.Visit_id
1729: AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
1730: AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
1731: AND SPL.PRIMARY_PLAN_FLAG = 'Y'
1743: -- With Tree Reversal
1744: SELECT CII.INSTANCE_ID AS ITEM_INSTANCE,
1745: CII.QUANTITY AS VISIT_QUANTITY
1746: FROM CSI_II_RELATIONSHIPS CIIR,
1747: CSI_ITEM_INSTANCES CII
1748: WHERE CII.INVENTORY_ITEM_ID = l_dtls_inv_item_id_tbl(j)
1749: AND CII.INV_MASTER_ORGANIZATION_ID = l_dtls_inv_master_org_id_tbl(j)
1750: AND nvl(CII.INVENTORY_REVISION,FND_API.G_MISS_CHAR) = nvl(l_dtls_inv_revision_tbl(j),FND_API.G_MISS_CHAR)
1751: AND CII.INSTANCE_ID = CIIR.SUBJECT_ID
1753: Select CII.INSTANCE_ID AS ITEM_INSTANCE
1754: from AHL_VISIT_TASKS_B TASKS,
1755: AHL_VISITS_B Visit,
1756: AHL_SIMULATION_PLANS_B SPL,
1757: CSI_ITEM_INSTANCES CII
1758: Where Visit.VISIT_ID = TASKS.Visit_id
1759: AND VISIT.STATUS_CODE NOT IN ('CLOSED', 'CANCELLED', 'DELETED')
1760: AND SPL.SIMULATION_PLAN_ID = VISIT.SIMULATION_PLAN_ID
1761: AND SPL.PRIMARY_PLAN_FLAG = 'Y'