eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.AHL_FMP_PVT dependencies on CSI_ITEM_INSTANCES

Line 19: FROM csi_item_instances

15:
16: --Check whether the given item instance exists
17: CURSOR check_instance_exists(c_item_instance_id NUMBER) IS
18: SELECT instance_id
19: FROM csi_item_instances
20: WHERE instance_id = c_item_instance_id
21: AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
22:
23:

Line 27: FROM csi_item_instances

23:
24: --Get Inventory Item ID for a given item instance
25: CURSOR get_inventory_item(c_item_instance_id NUMBER) IS
26: SELECT inventory_item_id
27: FROM csi_item_instances
28: WHERE instance_id = c_item_instance_id
29: AND SYSDATE between nvl(active_start_date,sysdate) and NVL(active_end_date,sysdate+1);
30:
31: -- Declare Local Function CHECK_SN_INSIDE --

Line 147: FROM csi_item_instances A

143: --only inventory item defined in MR effectivity definition, just top node
144: /*CURSOR get_top_inst1(c_inventory_item_id NUMBER)
145: IS
146: SELECT instance_id
147: FROM csi_item_instances A
148: WHERE inventory_item_id = c_inventory_item_id
149: AND SYSDATE between trunc(nvl(A.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
150: AND NOT EXISTS (SELECT 'X'
151: FROM csi_ii_relationships B

Line 161: FROM csi_item_instances

157:
158: CURSOR get_inst1(c_inventory_item_id NUMBER)
159: IS
160: SELECT instance_id
161: FROM csi_item_instances
162: WHERE inventory_item_id = c_inventory_item_id
163: AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(nvl(active_end_date,sysdate+1));
164:
165: --only position in MC defined in MR effectivity definition, just UC top node

Line 211: FROM csi_item_instances B

207: AND api.position_id= C_RELATIONSHIP_ID
208: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
209: AND trunc(nvl(a.active_end_date,sysdate+1))
210: AND EXISTS (SELECT 'X'
211: FROM csi_item_instances B
212: WHERE B.instance_id = api.csi_item_instance_id
213: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
214: AND inventory_item_id = c_inventory_item_id);*/
215:

Line 226: FROM csi_item_instances B

222: AND api.position_id=c_relationship_id
223: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
224: and trunc(nvl(a.active_end_date,sysdate+1))
225: AND EXISTS (SELECT 'X'
226: FROM csi_item_instances B
227: WHERE B.instance_id = api.csi_item_instance_id
228: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate))
229: and trunc(nvl(b.active_end_date,sysdate+1))
230: AND inventory_item_id = c_inventory_item_id)

Line 240: FROM csi_item_instances B

236: AND relationship_type_code = 'COMPONENT-OF'
237: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate))
238: and trunc(nvl(a.active_end_date,sysdate+1))
239: AND EXISTS (SELECT 'X'
240: FROM csi_item_instances B
241: WHERE B.instance_id = api.csi_item_instance_id
242: AND sysdate between trunc(nvl(b.active_start_date,sysdate))
243: and trunc(nvl(b.active_end_date,sysdate+1))
244: AND inventory_item_id = c_inventory_item_id);

Line 251: FROM csi_item_instances B

247: IS
248: SELECT a.csi_item_instance_id instance_id
249: FROM ahl_unit_config_headers A
250: WHERE EXISTS (SELECT 'X'
251: FROM csi_item_instances B
252: WHERE B.instance_id = A.csi_item_instance_id
253: AND sysdate between trunc(nvl(b.active_start_date,sysdate))
254: and trunc(nvl(b.active_end_date,sysdate+1))
255: AND B.inventory_item_id = c_inventory_item_id)

Line 267: FROM csi_item_instances A

263: START WITH D.pc_node_id = c_pc_node_id
264: CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
265: UNION
266: SELECT instance_id
267: FROM csi_item_instances A
268: WHERE A.inventory_item_id = c_inventory_item_id
269: AND sysdate between trunc(nvl(a.active_start_date,sysdate))
270: and trunc(nvl(a.active_end_date,sysdate+1))
271: AND NOT EXISTS (SELECT 'X'

Line 291: FROM csi_item_instances B

287: CURSOR get_inst4(c_inventory_item_id NUMBER, c_pc_node_id NUMBER) IS
288: SELECT a.csi_item_instance_id instance_id
289: FROM ahl_unit_config_headers A
290: WHERE EXISTS (SELECT 'X'
291: FROM csi_item_instances B
292: WHERE B.instance_id = A.csi_item_instance_id
293: AND sysdate between trunc(nvl(b.active_start_date,sysdate))
294: and trunc(nvl(b.active_end_date,sysdate+1))
295: AND B.inventory_item_id = c_inventory_item_id)

Line 307: FROM csi_item_instances A

303: START WITH D.pc_node_id = c_pc_node_id
304: CONNECT BY D.parent_node_id = PRIOR D.pc_node_id))
305: UNION
306: SELECT instance_id
307: FROM csi_item_instances A
308: WHERE A.inventory_item_id = c_inventory_item_id
309: AND sysdate between trunc(nvl(a.active_start_date,sysdate))
310: and trunc(nvl(a.active_end_date,sysdate+1))
311: AND NOT EXISTS (SELECT 'X'

Line 331: FROM csi_item_instances B

327: FROM ahl_unit_config_headers A,ahl_applicable_instances api
328: WHERE SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
329: AND a.csi_item_instance_id=api.csi_item_instance_id
330: AND EXISTS (SELECT 'X'
331: FROM csi_item_instances B
332: WHERE B.instance_id = Api.csi_item_instance_id
333: AND sysdate between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
334: AND B.inventory_item_id = c_inventory_item_id)
335: AND EXISTS (SELECT 'X'

Line 352: FROM csi_item_instances B

348: AND sysdate between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
349: AND A.SUBJECT_id IN (SELECT csi_item_instance_id from ahl_applicable_instances
350: where csi_item_instance_id=a.subject_id )
351: AND EXISTS (SELECT 'X'
352: FROM csi_item_instances B
353: WHERE B.instance_id = A.subject_id
354: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
355: AND B.inventory_item_id = c_inventory_item_id)
356: START WITH object_id IN (SELECT csi_item_instance_id

Line 373: FROM csi_item_instances A

369: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
370: AND a.relationship_type_code = 'COMPONENT-OF'
371: UNION
372: SELECT a.instance_id
373: FROM csi_item_instances A
374: WHERE A.inventory_item_id = c_inventory_item_id
375: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
376: AND EXISTS (SELECT 'X'
377: FROM ahl_pc_associations B

Line 391: FROM csi_item_instances B

387: FROM csi_ii_relationships A
388: WHERE relationship_type_code = 'COMPONENT-OF'
389: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
390: AND EXISTS (SELECT 'X'
391: FROM csi_item_instances B
392: WHERE B.instance_id = A.subject_id
393: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
394: AND B.inventory_item_id = c_inventory_item_id
395: )

Line 397: FROM csi_item_instances C

393: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
394: AND B.inventory_item_id = c_inventory_item_id
395: )
396: START WITH object_id IN (SELECT C.instance_id
397: FROM csi_item_instances C
398: WHERE EXISTS (SELECT 'X'
399: FROM ahl_pc_associations D
400: WHERE D.unit_item_id = C.inventory_item_id
401: AND D.association_type_flag = 'I'

Line 436: FROM csi_item_instances B

432: --and A.master_config_id=api.position_id
433: and api.csi_item_instance_id=a.csi_item_instance_id
434: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
435: AND EXISTS (SELECT 'X'
436: FROM csi_item_instances B
437: WHERE B.instance_id = A.csi_item_instance_id
438: AND EXISTS (SELECT 'X'
439: FROM ahl_pc_associations C
440: WHERE C.unit_item_id = B.inventory_item_id

Line 497: FROM csi_item_instances B

493: WHERE api.position_id = c_relationship_id
494: and api.csi_item_instance_id= a.csi_item_instance_id
495: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
496: AND EXISTS (SELECT 'X'
497: FROM csi_item_instances B
498: WHERE B.instance_id = api.csi_item_instance_id
499: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
500: AND EXISTS (SELECT 'X'
501: FROM ahl_pc_associations C

Line 517: FROM csi_item_instances Ax

513: AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
514: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
515: AND subject_id in (select csi_item_instance_id from ahl_applicable_instances)
516: START WITH object_id IN (SELECT ax.instance_id
517: FROM csi_item_instances Ax
518: WHERE SYSDATE between trunc(nvl(ax.active_start_date,sysdate)) and trunc(nvl(ax.active_end_date,sysdate+1))
519: AND instance_id=a.subject_id
520: AND EXISTS (SELECT 'X'
521: FROM ahl_pc_associations B

Line 545: FROM csi_item_instances B

541: AND api.csi_item_instance_id=A.csi_item_instance_id
542: --and A.master_config_id=api.position_id
543: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
544: AND EXISTS (SELECT 'X'
545: FROM csi_item_instances B
546: WHERE B.instance_id = Api.csi_item_instance_id
547: AND B.inventory_item_id = c_inventory_item_id)
548: AND EXISTS (SELECT 'X'
549: FROM ahl_pc_associations C

Line 565: FROM csi_item_instances B

561: AND A.csi_item_instance_id=API.csi_item_instance_id
562: --and A.master_config_id=api.position_id
563: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
564: AND EXISTS (SELECT 'X'
565: FROM csi_item_instances B
566: WHERE B.instance_id = A.csi_item_instance_id
567: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
568: AND B.inventory_item_id = c_inventory_item_id
569: AND EXISTS (SELECT 'X'

Line 587: FROM csi_item_instances B

583: AND api.csi_item_instance_id=A.csi_item_instance_id
584: -- AND subject_id in (Select csi_item_instance_id from ahl_applicable_instances)
585: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
586: AND EXISTS (SELECT 'X'
587: FROM csi_item_instances B
588: WHERE B.instance_id = api.csi_item_instance_id
589: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
590: AND B.inventory_item_id = c_inventory_item_id)
591: AND EXISTS (SELECT 'X'

Line 607: FROM csi_item_instances B

603: WHERE relationship_type_code = 'COMPONENT-OF'
604: AND a.relationship_id in (Select position_id from AHL_APPLICABLE_INSTANCES)
605: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
606: AND EXISTS (SELECT 'X'
607: FROM csi_item_instances B
608: WHERE B.instance_id = A.subject_id
609: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
610: AND B.inventory_item_id = c_inventory_item_id)
611: START WITH object_id IN (SELECT c.csi_item_instance_id

Line 635: FROM csi_item_instances B

631: WHERE api.position_id = c_relationship_id
632: AND A.csi_item_instance_id=API.csi_item_instance_id
633: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
634: AND EXISTS (SELECT 'X'
635: FROM csi_item_instances B
636: WHERE B.instance_id = api.csi_item_instance_id
637: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
638: AND B.inventory_item_id = c_inventory_item_id
639: AND EXISTS (SELECT 'X'

Line 655: FROM csi_item_instances B

651: WHERE relationship_type_code = 'COMPONENT-OF'
652: AND a.subject_id in (Select csi_item_instance_id from AHL_APPLICABLE_INSTANCES )
653: AND SYSDATE between trunc(nvl(a.active_start_date,sysdate)) and trunc(nvl(a.active_end_date,sysdate+1))
654: AND EXISTS (SELECT 'X'
655: FROM csi_item_instances B
656: WHERE B.instance_id = A.subject_id
657: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
658: AND B.inventory_item_id = c_inventory_item_id)
659: START WITH object_id IN (SELECT instance_id

Line 660: FROM csi_item_instances C

656: WHERE B.instance_id = A.subject_id
657: AND SYSDATE between trunc(nvl(b.active_start_date,sysdate)) and trunc(nvl(b.active_end_date,sysdate+1))
658: AND B.inventory_item_id = c_inventory_item_id)
659: START WITH object_id IN (SELECT instance_id
660: FROM csi_item_instances C
661: WHERE SYSDATE between trunc(nvl(c.active_start_date,sysdate)) and trunc(nvl(C.active_end_date,sysdate+1))
662: AND EXISTS (SELECT 'X'
663: FROM ahl_pc_associations D
664: WHERE D.unit_item_id = C.inventory_item_id

Line 1095: from ahl_pc_associations itm, csi_item_instances csi,

1091: SELECT pc_node_id --amsriniv
1092: FROM ahl_pc_nodes_b B
1093: --WHERE B.pc_node_id = c_pc_node_id
1094: START WITH B.pc_node_id IN (select pc_node_id
1095: from ahl_pc_associations itm, csi_item_instances csi,
1096: (SELECT object_id
1097: FROM csi_ii_relationships E
1098: START WITH E.subject_id = c_instance_id
1099: AND E.relationship_type_code = 'COMPONENT-OF'

Line 1149: from ahl_pc_associations itm, csi_item_instances csi,ii

1145: SELECT 'x' --pc_node_id --amsriniv
1146: FROM ahl_pc_nodes_b B
1147: WHERE B.pc_node_id = c_pc_node_id
1148: START WITH B.pc_node_id IN (select pc_node_id
1149: from ahl_pc_associations itm, csi_item_instances csi,ii
1150: where itm.association_type_flag = 'I'
1151: and itm.unit_item_id = csi.inventory_item_id
1152: and csi.instance_id = ii.object_id
1153: UNION ALL

Line 1205: from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc

1201: (select cir2.object_id,
1202: cii2.instance_id subject_id,
1203: nvl(uc.master_config_id, cir2.position_reference) position_reference,
1204: 0 depth
1205: from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
1206: where cii2.instance_id = c_instance_id
1207: and cii2.instance_id = cir2.subject_id(+)
1208: and cii2.instance_id = uc.csi_item_instance_id(+)
1209: and uc.parent_uc_header_id(+) is null

Line 1223: csi_item_instances cii

1219: AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))
1220: CONNECT BY object_id = PRIOR subject_id
1221: AND relationship_type_code = 'COMPONENT-OF'
1222: AND SYSDATE between trunc(nvl(active_start_date,sysdate)) and trunc(NVL(active_end_date,sysdate+1))) cir,
1223: csi_item_instances cii
1224: WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1225: AND MR.mr_header_id = A.mr_header_id
1226: AND cir.subject_id = cii.instance_id
1227: AND MR.mr_status_code = 'COMPLETE'

Line 1244: from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc

1240: cii2.instance_id subject_id,
1241: --nvl(uc.master_config_id, cir2.position_reference) position_reference,
1242: 0 depth,
1243: cii2.inventory_item_id
1244: from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
1245: where cii2.instance_id = c_instance_id
1246: --and cii2.instance_id = cir2.subject_id(+)
1247: --and cii2.instance_id = uc.csi_item_instance_id(+)
1248: --and uc.parent_uc_header_id(+) is null

Line 1257: from csi_item_instances

1253: a.subject_id,
1254: --to_number(a.position_reference),
1255: level depth,
1256: (select inventory_item_id
1257: from csi_item_instances
1258: where instance_id = a.subject_id) inventory_item_id
1259: FROM csi_ii_relationships a
1260: WHERE c_components_flag = 'Y'
1261: START WITH object_id = c_instance_id

Line 1351: ) cir, csi_item_instances ii,

1347: CONNECT BY object_id = PRIOR subject_id
1348: AND relationship_type_code = 'COMPONENT-OF'
1349: AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1350: AND trunc(NVL(active_end_date,sysdate+1))
1351: ) cir, csi_item_instances ii,
1352: ahl_mr_effectivities A
1353: WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1354: AND ii.instance_id = cir.subject_id
1355: AND A.inventory_item_id = ii.inventory_item_id

Line 1382: FROM csi_item_instances ii,

1378: WHERE ciea1.instance_id = ii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1379: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1380: ii.serial_number --,
1381: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1382: FROM csi_item_instances ii,
1383: ahl_mr_effectivities A
1384: WHERE ii.instance_id = c_instance_id
1385: AND A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1386: AND A.inventory_item_id = ii.inventory_item_id

Line 1413: FROM ahl_applicable_instances aai,csi_item_instances cii,

1409: WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1410: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1411: cii.serial_number --,
1412: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1413: FROM ahl_applicable_instances aai,csi_item_instances cii,
1414: ahl_mr_effectivities A
1415: WHERE A.mr_header_id = NVL(c_mr_header_id, A.mr_header_id)
1416: AND A.relationship_id IS NOT NULL
1417: AND aai.position_id = A.relationship_id

Line 1444: from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc

1440: (select cir2.object_id,
1441: cii2.instance_id subject_id,
1442: nvl(uc.master_config_id, cir2.position_reference) position_reference,
1443: 0 depth
1444: from csi_item_instances cii2, csi_ii_relationships cir2, ahl_unit_config_headers uc
1445: where cii2.instance_id = c_instance_id
1446: and cii2.instance_id = cir2.subject_id(+)
1447: and cii2.instance_id = uc.csi_item_instance_id(+)
1448: and uc.parent_uc_header_id(+) is null

Line 1451: csi_item_instances cii

1447: and cii2.instance_id = uc.csi_item_instance_id(+)
1448: and uc.parent_uc_header_id(+) is null
1449: and SYSDATE between trunc(nvl(uc.active_start_date,sysdate)) and trunc(NVL(uc.active_end_date,sysdate+1))
1450: ) cir,
1451: csi_item_instances cii
1452: WHERE MR.mr_header_id = A.mr_header_id
1453: AND A.mr_header_id = vis.mr_header_id
1454: AND vis.mr_visit_type_code = c_visit_type_code
1455: AND cir.subject_id = cii.instance_id

Line 1473: from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc

1469: cii2.instance_id subject_id,
1470: --nvl(uc.master_config_id, cir2.position_reference) position_reference,
1471: 0 depth,
1472: cii2.inventory_item_id
1473: from csi_item_instances cii2 --, csi_ii_relationships cir2, ahl_unit_config_headers uc
1474: where cii2.instance_id = c_instance_id
1475: --and cii2.instance_id = cir2.subject_id(+)
1476: --and cii2.instance_id = uc.csi_item_instance_id(+)
1477: --and uc.parent_uc_header_id(+) is null

Line 1564: ) cir, csi_item_instances cii, ahl_mr_visit_types vis,

1560: CONNECT BY object_id = PRIOR subject_id
1561: AND relationship_type_code = 'COMPONENT-OF'
1562: AND SYSDATE between trunc(nvl(active_start_date,sysdate))
1563: AND trunc(NVL(active_end_date,sysdate+1))
1564: ) cir, csi_item_instances cii, ahl_mr_visit_types vis,
1565: ahl_mr_effectivities A
1566: WHERE A.mr_header_id = vis.mr_header_id
1567: AND vis.mr_visit_type_code = c_visit_type_code
1568: AND cir.subject_id = cii.instance_id

Line 1596: FROM csi_item_instances cii,ahl_mr_visit_types vis,

1592: WHERE cii.instance_id = ciea1.instance_id(+) AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
1593: AND ciea1.attribute_level(+) = 'GLOBAL') mfg_date,
1594: cii.serial_number --,
1595: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1596: FROM csi_item_instances cii,ahl_mr_visit_types vis,
1597: ahl_mr_effectivities A
1598: WHERE A.mr_header_id = vis.mr_header_id
1599: AND vis.mr_visit_type_code = c_visit_type_code
1600: AND cii.instance_id = c_instance_id

Line 1627: FROM ahl_applicable_instances aai,csi_item_instances cii,ahl_mr_visit_types vis,

1623: WHERE ciea1.instance_id = cii.instance_id AND ciea1.attribute_code = 'AHL_MFG_DATE'
1624: AND ciea1.attribute_level = 'GLOBAL') mfg_date,
1625: cii.serial_number --,
1626: --(SELECT 'Y' from ahl_mr_effectivity_dtls where mr_effectivity_id = a.mr_effectivity_id and ROWNUM < 2) eff_dtls_flag
1627: FROM ahl_applicable_instances aai,csi_item_instances cii,ahl_mr_visit_types vis,
1628: ahl_mr_effectivities A
1629: WHERE A.mr_header_id = vis.mr_header_id
1630: AND vis.mr_visit_type_code = c_visit_type_code
1631: AND A.relationship_id IS NOT NULL

Line 1732: FROM csi_item_instances csi,

1728: SELECT csi.serial_number serial_number ,
1729: to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
1730: 'm' manufacturer_id ,
1731: 'c' country_code
1732: FROM csi_item_instances csi,
1733: csi_inst_extend_attrib_v ciea1
1734: WHERE csi.instance_id = ciea1.instance_id(+)
1735: AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
1736: AND ciea1.attribute_level(+) = 'GLOBAL'

Line 2385: FROM csi_item_instances A

2381: IS
2382: -- Get top instance for top component
2383: CURSOR get_instance_top (p_item_instance_id IN NUMBER) IS
2384: SELECT A.instance_id
2385: FROM csi_item_instances A
2386: WHERE A.instance_id IN ( SELECT object_id
2387: FROM csi_ii_relationships
2388: START WITH subject_id = p_item_instance_id
2389: AND relationship_type_code = 'COMPONENT-OF'

Line 2409: FROM csi_item_instances

2405:
2406: -- Get instance if it is a top component
2407: CURSOR get_instance_com (p_item_instance_id IN NUMBER) IS
2408: SELECT instance_id
2409: FROM csi_item_instances
2410: WHERE instance_id = p_item_instance_id
2411: AND sysdate between trunc(nvl(active_start_date,sysdate))
2412: AND trunc(nvl(active_end_date, SYSDATE+1));
2413:

Line 2691: FROM csi_item_instances csi,

2687: SELECT csi.serial_number serial_number ,
2688: to_date(ciea1.attribute_value, 'DD/MM/YYYY') mfg_date ,
2689: 'm' manufacturer_id ,
2690: 'c' country_code
2691: FROM csi_item_instances csi,
2692: csi_inst_extend_attrib_v ciea1
2693: WHERE csi.instance_id = ciea1.instance_id(+)
2694: AND ciea1.attribute_code(+) = 'AHL_MFG_DATE'
2695: AND ciea1.attribute_level(+) = 'GLOBAL'