[Home] [Help]
107:
108: CURSOR c_ins_label(c_instance_id NUMBER)
109: IS
110: SELECT civ.version_label
111: FROM csi_i_version_labels civ
112: WHERE (TRUNC(SYSDATE) BETWEEN TRUNC(NVL(civ.active_start_date,SYSDATE))
113: AND TRUNC(NVL(civ.active_end_date,SYSDATE)))
114: AND civ.instance_id = c_instance_id
115: ORDER BY LAST_UPDATE_DATE DESC;
375: FROM csm_mtl_onhand_qty_acc cqa
376: , csi_item_instances cii
377: , csi_instance_statuses iis
378: , asg_user asg
379: , csi_i_version_labels civ
380: , CSI_II_RELATIONSHIPS CIR
381: WHERE cii.inventory_item_id = cqa.inventory_item_id
382: AND cii.inv_organization_id = cqa.organization_id
383: AND cii.inv_subinventory_name = cqa.subinventory_code
394: AND asg.user_id = asg.owner_id
395: AND asg.user_id = cqa.user_id
396: AND asg.ENABLED = 'Y'
397: AND cii.instance_id = civ.instance_id(+)
398: AND civ.version_label_id = (SELECT MAX(version_label_id) FROM csi_i_version_labels --not using sorted desc on lud & rownum=1 as sub query doesn't honor join cond
399: WHERE instance_id = civ.instance_id
400: AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(active_start_date,SYSDATE)) AND TRUNC(NVL(active_end_date,SYSDATE)))
401: AND CII.INSTANCE_ID = CIR.SUBJECT_ID(+)
402: AND CIR.RELATIONSHIP_TYPE_CODE(+) = 'COMPONENT-OF'
468:
469:
470: TYPE instance_idTab IS TABLE OF csm_item_instances_acc.instance_id%TYPE INDEX BY BINARY_INTEGER;
471: TYPE rel_idTab IS TABLE OF csm_ii_relationships_acc.relationship_id%TYPE INDEX BY BINARY_INTEGER;
472: TYPE ver_lab_Tab IS TABLE OF csi_i_version_labels.version_label%TYPE INDEX BY BINARY_INTEGER;
473: l_rel_id_lst rel_idTab;
474: l_user_id_lst asg_download.user_list;
475: l_user_id2_lst asg_download.user_list;
476: l_acc_id_lst asg_download.access_list;