[Home] [Help]
1983: AND ACTIVE_START_DATE <= SYSDATE
1984: AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
1985: --AND LOCATION_TYPE_CODE = 'WIP'
1986: --AND LOCATION_ID = p_wip_location
1987: AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
1988: WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
1989: AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
1990: --AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE,SYSDATE));
1991: AND NVL(ACTIVE_START_DATE,SYSDATE) <= SYSDATE
2000: WHERE INVENTORY_ITEM_ID = p_item_id
2001: AND SERIAL_NUMBER = p_serial_num
2002: AND ACTIVE_START_DATE <= SYSDATE
2003: AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE > SYSDATE))
2004: AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
2005: WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
2006: AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
2007: AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE,SYSDATE));
2008:
2847: -- End changes for bug fix.
2848:
2849: l_sql := l_sql || ' AND ACTIVE_START_DATE <=SYSDATE ';
2850: l_sql := l_sql || ' AND ((ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE>SYSDATE))';
2851: l_sql := l_sql || ' AND NOT EXISTS (SELECT null FROM CSI_II_RELATIONSHIPS CIR ';
2852: l_sql := l_sql || ' WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID ';
2853: l_sql := l_sql || ' AND CIR.RELATIONSHIP_TYPE_CODE = ''COMPONENT-OF''';
2854: l_sql := l_sql || ' AND ((CIR.ACTIVE_START_DATE IS NULL) OR (CIR.ACTIVE_START_DATE <= SYSDATE))';
2855: l_sql := l_sql || ' AND ((CIR.ACTIVE_END_DATE IS NULL) OR (CIR.ACTIVE_END_DATE > SYSDATE)) )';
5084: AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
5085: AND CII.QUANTITY > 0
5086: AND CII.LOCATION_TYPE_CODE = 'WIP'
5087: AND CII.WIP_JOB_ID = p_wip_entity_id
5088: AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
5089: WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
5090: AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
5091: AND NVL(CIR.ACTIVE_START_DATE,SYSDATE) <= SYSDATE AND
5092: (CIR.ACTIVE_END_DATE IS NULL OR CIR.ACTIVE_END_DATE > SYSDATE));
5092: (CIR.ACTIVE_END_DATE IS NULL OR CIR.ACTIVE_END_DATE > SYSDATE));
5093: */
5094:
5095: SELECT cii.quantity,
5096: (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
5097: WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
5098: AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
5099: AND NVL(CIR.ACTIVE_START_DATE,SYSDATE) <= SYSDATE
5100: AND (CIR.ACTIVE_END_DATE IS NULL OR CIR.ACTIVE_END_DATE > SYSDATE)) reln_exists
6159: AND ((CII.ACTIVE_END_DATE IS NULL) OR (CII.ACTIVE_END_DATE > SYSDATE))
6160: AND CII.QUANTITY > 0
6161: AND CII.LOCATION_TYPE_CODE = 'WIP'
6162: AND CII.WIP_JOB_ID = p_wip_entity_id
6163: AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
6164: WHERE CIR.SUBJECT_ID = CII.INSTANCE_ID
6165: AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
6166: AND SYSDATE BETWEEN NVL(CIR.ACTIVE_START_DATE,SYSDATE) AND NVL(CIR.ACTIVE_END_DATE,SYSDATE));
6167: */
6618: AND UPPER(NVL(W.INCIDENT_NUMBER,'x')) LIKE UPPER(NVL(p_incident_number,NVL(W.INCIDENT_NUMBER,'x')))
6619: AND NVL(W.PRIORITY,0) = NVL(p_priority,NVL(W.PRIORITY,0))
6620: AND W.VISIT_NUMBER = NVL(p_visit_number,W.VISIT_NUMBER)
6621: AND UPPER(W.JOB_NUMBER) LIKE UPPER(NVL(p_job_number,W.job_number))
6622: AND NOT EXISTS (SELECT 'X' FROM CSI_II_RELATIONSHIPS CIR
6623: WHERE CIR.SUBJECT_ID = CSI.INSTANCE_Id
6624: AND CIR.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
6625: AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE)
6626: AND NVL(ACTIVE_END_DATE,SYSDATE))
6778: AND ( ( I.END_DATE_ACTIVE IS NULL )
6779: OR ( I.END_DATE_ACTIVE >= SYSDATE ) )
6780: AND NOT EXISTS
6781: (SELECT ''X''
6782: FROM CSI_II_RELATIONSHIPS CIR
6783: WHERE CIR.SUBJECT_ID = C.INSTANCE_ID
6784: AND CIR.RELATIONSHIP_TYPE_CODE = ''COMPONENT-OF''
6785: AND SYSDATE BETWEEN NVL(ACTIVE_START_DATE,SYSDATE) AND NVL(ACTIVE_END_DATE, SYSDATE)
6786: )