[Home] [Help]
1010: define_columns(l_def_systems_rec, l_cur_get_systems);
1011:
1012: l_ignore := dbms_sql.execute(l_cur_get_systems);
1013: --
1014: -- Get the last purge date from csi_item_instances table
1015: --
1016: BEGIN
1017: SELECT last_purge_date
1018: INTO l_last_purge_date
1015: --
1016: BEGIN
1017: SELECT last_purge_date
1018: INTO l_last_purge_date
1019: FROM CSI_ITEM_INSTANCES
1020: WHERE rownum < 2;
1021: EXCEPTION
1022: WHEN no_data_found THEN
1023: NULL;
2954: FOR UPDATE OF system_id ;
2955:
2956: CURSOR cont_csr IS
2957: SELECT party_id
2958: FROM csi_i_parties cip, csi_item_instances cii
2959: WHERE cip.instance_id=cii.instance_id
2960: AND cii.system_id=p_system_rec.system_id
2961: AND cip.relationship_type_code='OWNER'
2962: AND sysdate BETWEEN NVL(cii.active_start_date,sysdate)
2963: AND NVL(cii.active_end_date,sysdate);
2964:
2965: CURSOR site_csr IS
2966: SELECT ip_account_id
2967: FROM csi_item_instances cii,
2968: csi_i_parties cip,
2969: csi_ip_accounts cia
2970: WHERE cii.instance_id=cip.instance_id
2971: AND cii.system_id=p_system_rec.system_id
2976: CURSOR ins_csr IS
2977: SELECT cip.instance_id instance_id,cip.instance_party_id instance_party_id,
2978: cip.object_version_number party_obj_version_number
2979: ,cia.ip_account_id ip_account_id,cia.object_version_number account_obj_version_number
2980: FROM CSI_ITEM_INSTANCES cii,
2981: CSI_I_PARTIES cip,
2982: CSI_IP_ACCOUNTS cia
2983: WHERE cii.system_id = p_system_rec.system_id
2984: AND cip.instance_id = cii.instance_id
2991: --
2992: CURSOR ip_acct_csr(p_bill_to IN NUMBER,p_ship_to IN NUMBER) IS
2993: SELECT cia.ip_account_id ip_account_id,cia.object_version_number object_version_number
2994: ,cia.bill_to_address,cia.ship_to_address
2995: FROM CSI_ITEM_INSTANCES cii,
2996: CSI_I_PARTIES cip,
2997: CSI_IP_ACCOUNTS cia
2998: WHERE cii.system_id = p_system_rec.system_id
2999: AND cip.instance_id = cii.instance_id
3007: (NVL(cia.ship_to_address,-999) = NVL(p_ship_to,-999))) ;
3008: --
3009: CURSOR ins_party_csr IS
3010: SELECT cip.instance_party_id instance_party_id
3011: FROM CSI_ITEM_INSTANCES cii,
3012: CSI_I_PARTIES cip
3013: WHERE cii.system_id = p_system_rec.system_id
3014: AND cip.instance_id = cii.instance_id
3015: AND cip.relationship_type_code = 'OWNER'
3025: AND sysdate BETWEEN NVL(active_start_date,sysdate) AND NVL(active_end_date,sysdate);
3026: --
3027: CURSOR install_csr(p_sys_id IN NUMBER) IS
3028: SELECT instance_id, install_location_id, object_version_number
3029: FROM CSI_ITEM_INSTANCES
3030: WHERE sysdate BETWEEN NVL(active_start_date,sysdate) AND NVL(active_end_date,sysdate)
3031: AND system_id = p_sys_id;
3032: --
3033: l_msg_count NUMBER;
3541: l_exists := 'N';
3542: Begin
3543: select 'Y'
3544: into l_exists
3545: from CSI_ITEM_INSTANCES
3546: where instance_id = v_rec.instance_id
3547: and owner_party_id = l_party_id
3548: and nvl(owner_party_account_id,-999) = p_system_rec.customer_id;
3549: Exception
3687: l_exists := 'N';
3688: Begin
3689: select 'Y'
3690: into l_exists
3691: from CSI_ITEM_INSTANCES
3692: where instance_id = v_install_rec.instance_id
3693: and nvl(install_location_id,-999) = l_install_to;
3694: Exception
3695: when no_data_found then
4032:
4033:
4034: CURSOR expire_instance_csr(p_system_id NUMBER) IS
4035: SELECT *
4036: FROM csi_item_instances
4037: WHERE system_id=p_system_id
4038: AND sysdate BETWEEN NVL(active_start_date,(sysdate -1))
4039: AND NVL(active_end_date,(sysdate +1));
4040:
4179: l_exists := 'N';
4180: Begin
4181: select 'Y'
4182: into l_exists
4183: from CSI_ITEM_INSTANCES
4184: where instance_id = expire_csr.instance_id
4185: and nvl(active_end_date,(sysdate+1)) <= sysdate;
4186: Exception
4187: when no_data_found then
5642: define_columns(l_def_systems_rec, l_cur_get_systems);
5643:
5644: l_ignore := dbms_sql.execute(l_cur_get_systems);
5645: --
5646: -- Get the last purge date from csi_item_instances table
5647: --
5648: BEGIN
5649: SELECT last_purge_date
5650: INTO l_last_purge_date
5647: --
5648: BEGIN
5649: SELECT last_purge_date
5650: INTO l_last_purge_date
5651: FROM CSI_ITEM_INSTANCES
5652: WHERE rownum < 2;
5653: EXCEPTION
5654: WHEN no_data_found THEN
5655: NULL;