[Home] [Help]
81: */
82:
83: /* -- Bug 4777658 : Perf Fix : Rewriting cursor c_uc_node_details below.
84: -- Perf Fix -- MTL_SYSTEM_ITEMS need not be used here as DESCRIPTION can be fetched from MTL_SYSTEM_ITEMS_KFV
85: CURSOR c_uc_node_details(c_csi_instance_id csi_ii_relationships.object_id%TYPE) IS
86: SELECT CIIR.OBJECT_ID, -- l_dtls_object_id_tbl
87: CIIR.SUBJECT_ID, -- l_dtls_subject_id_tbl
88: DECODE(UC.CSI_ITEM_INSTANCE_ID,
89: NULL,CIIR.POSITION_REFERENCE,
100: CII.UNIT_OF_MEASURE, -- l_dtls_uom_tbl
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
123: */
124:
125: -- Bug 4777658 : Perf Fix : Modified Cursor
126:
127: CURSOR c_uc_node_details(c_csi_instance_id csi_ii_relationships.object_id%TYPE) IS
128: Select A.OBJECT_ID, -- l_dtls_object_id_tbl
129: A.SUBJECT_ID, -- l_dtls_subject_id_tbl
130: DECODE(A.CSI_ITEM_INSTANCE_ID,
131: NULL,A.POSITION_REFERENCE,
158: UCI.MASTER_CONFIG_ID
159: FROM ( Select CIIRI.SUBJECT_ID ,
160: CIIRI.OBJECT_ID ,
161: CIIRI.POSITION_REFERENCE
162: from CSI_II_RELATIONSHIPS CIIRI
163: START WITH CIIRI.OBJECT_ID = c_csi_instance_id
164: AND CIIRI.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
165: AND TRUNC(NVL(CIIRI.ACTIVE_START_DATE,SYSDATE)) <= TRUNC(SYSDATE)
166: AND TRUNC(SYSDATE) < TRUNC(NVL(CIIRI.ACTIVE_END_DATE,SYSDATE+1))
1288:
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
1669: From (
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)
1742: -- Total Quantity from Visit, which does not have a unit at the header level.
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)