[Home] [Help]
1289: where cir.object_id = p_object_id
1290: and cir.relationship_type_code = p_rel_type_code
1291: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1292: and EXISTS (select 'x'
1293: from CSI_ITEM_INSTANCES csi
1294: where csi.instance_id = cir.subject_id
1295: and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
1296: --
1297: CURSOR OBJECT_CUR1(c_sysdate IN DATE) IS -- Used when Object ID and Rel Type are passed
1304: where cir.object_id = p_object_id
1305: and cir.relationship_type_code = p_rel_type_code
1306: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1307: and EXISTS (select 'x'
1308: from CSI_ITEM_INSTANCES csi
1309: where csi.instance_id = cir.subject_id
1310: and csi.config_inst_hdr_id is not null
1311: and csi.config_inst_item_id is not null
1312: and csi.config_inst_rev_num is not null
1321: from CSI_II_RELATIONSHIPS cir
1322: where cir.object_id = p_object_id
1323: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1324: and EXISTS (select 'x'
1325: from CSI_ITEM_INSTANCES csi
1326: where csi.instance_id = cir.subject_id
1327: and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
1328: --
1329: CURSOR SUBJECT_CUR(c_sysdate IN DATE) IS --Used when subject ID and Rel type are passed
1336: where cir.subject_id = p_subject_id
1337: and cir.relationship_type_code = p_rel_type_code
1338: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1339: and EXISTS (select 'x'
1340: from CSI_ITEM_INSTANCES csi
1341: where csi.instance_id = cir.subject_id
1342: and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
1343: --
1344: CURSOR REL_ID_CUR(c_sysdate IN DATE) IS -- Used when only relationship_id is passed
1350: from CSI_II_RELATIONSHIPS cir
1351: where cir.relationship_id = p_relationship_id
1352: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1353: and EXISTS (select 'x'
1354: from CSI_ITEM_INSTANCES csi
1355: where csi.instance_id = cir.subject_id
1356: and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
1357: --
1358: CURSOR OTHER_PARAM_CUR(c_sysdate IN DATE) IS
1367: and cir.relationship_type_code = NVL(p_rel_type_code,cir.relationship_type_code)
1368: and cir.subject_id = NVL(p_subject_id,cir.subject_id)
1369: and DECODE(l_active_relationship_only,FND_API.G_TRUE,NVL((cir.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate
1370: and EXISTS (select 'x'
1371: from CSI_ITEM_INSTANCES csi
1372: where csi.instance_id = cir.subject_id
1373: and DECODE(l_active_instances_only,FND_API.G_TRUE,NVL((csi.active_end_date),c_sysdate+1),c_sysdate+1) > sysdate);
1374: --
1375: l_ctr NUMBER := 0;
2202: );
2203:
2204: -- x_relationship_tbl.DELETE ;
2205: --
2206: -- Get the last purge date from csi_item_instances table
2207: --
2208: BEGIN
2209: SELECT last_purge_date
2210: INTO l_last_purge_date
2207: --
2208: BEGIN
2209: SELECT last_purge_date
2210: INTO l_last_purge_date
2211: FROM CSI_ITEM_INSTANCES
2212: WHERE rownum < 2;
2213: EXCEPTION
2214: WHEN no_data_found THEN
2215: NULL;
2343: ELSE -- p_time_stamp is not passed
2344: Begin
2345: Select 'x'
2346: into l_exists
2347: from CSI_ITEM_INSTANCES
2348: where instance_id = l_relationship_tbl(rel_row).subject_id
2349: and nvl(active_end_date,(sysdate+1)) < sysdate;
2350: l_ctr := l_exp_inst_tbl.count;
2351: l_ctr := l_ctr + 1;
2441: RAISE FND_API.G_EXC_ERROR;
2442: END IF;
2443:
2444: --
2445: -- Get the last purge date from csi_item_instances table
2446: --
2447: BEGIN
2448: SELECT last_purge_date
2449: INTO l_last_purge_date
2446: --
2447: BEGIN
2448: SELECT last_purge_date
2449: INTO l_last_purge_date
2450: FROM CSI_ITEM_INSTANCES
2451: WHERE rownum < 2;
2452: EXCEPTION
2453: WHEN no_data_found THEN
2454: NULL;
2710: ELSE -- p_time_stamp is not passed
2711: Begin
2712: Select 'x'
2713: into l_exists
2714: from CSI_ITEM_INSTANCES
2715: where instance_id = l_relationship_tbl(rel_row).subject_id
2716: and nvl(active_end_date,(sysdate+1)) < sysdate;
2717: l_ctr := l_exp_inst_tbl.count;
2718: FOR v_rec in CHILD_CUR(l_relationship_tbl(rel_row).subject_id) LOOP
3123: --
3124: BEGIN
3125: SELECT msi.ib_item_instance_class
3126: INTO l_link
3127: FROM csi_item_instances cii
3128: ,mtl_system_items_b msi
3129: WHERE cii.instance_id = p_instance_id
3130: AND msi.inventory_item_id=cii.inventory_item_id
3131: AND msi.organization_id=cii.last_vld_organization_id
4329: INTO l_object_version1,
4330: l_instance_rec.config_inst_hdr_id, -- added
4331: l_instance_rec.config_inst_item_id, -- added
4332: l_instance_rec.config_inst_rev_num -- added
4333: FROM csi_item_instances
4334: WHERE instance_id=l_ii_relationship_rec.subject_id;
4335: l_instance_rec.object_version_number:=l_object_version1;
4336: IF p_mode='CREATE' OR p_mode='UPDATE'
4337: THEN
4364: l_instance_rec.po_order_line_id,
4365: l_instance_rec.operational_status_code,
4366: l_instance_rec.install_location_id,
4367: l_instance_rec.install_location_type_code
4368: FROM csi_item_instances
4369: WHERE instance_id=l_ii_relationship_rec.object_id;
4370:
4371: -- Begin Add Code for Siebel Genesis Project
4372: csi_item_instance_pub.update_item_instance
4449: IF l_subject_id IS NOT NULL
4450: THEN
4451: SELECT COUNT(*)
4452: INTO l_dummy
4453: FROM csi_item_instances
4454: WHERE instance_id IN ( SELECT subject_id
4455: FROM csi_ii_relationships
4456: WHERE object_id = l_rel_tbl(j).object_id
4457: AND mandatory_flag = 'Y'
4469: INTO l_object_version,
4470: l_instance_rec1.config_inst_hdr_id, --added
4471: l_instance_rec1.config_inst_item_id, --added
4472: l_instance_rec1.config_inst_rev_num --added
4473: FROM csi_item_instances
4474: WHERE instance_id = l_rel_tbl(j).object_id;
4475:
4476: l_instance_rec1:=l_temp_ins_rec;
4477: l_instance_rec1.object_version_number:=l_object_version;
4592: SELECT instance_id
4593: ,object_version_number
4594: INTO l_instance_rec.instance_id
4595: ,l_instance_rec.object_version_number
4596: FROM csi_item_instances
4597: WHERE instance_id = p_ii_relationship_rec.subject_id;
4598:
4599: l_party_tbl(1).instance_id := p_ii_relationship_rec.subject_id;
4600: l_account_tbl(1).parent_tbl_index := 1;
5066: SELECT object_version_number,
5067: 'Y'
5068: INTO l_instance_rec.object_version_number,
5069: l_instance_rec.cascade_ownership_flag
5070: FROM csi_item_instances
5071: WHERE instance_id=l_instance_rec.instance_id
5072: AND (active_end_date IS NULL OR active_end_date > sysdate);
5073: EXCEPTION
5074: WHEN OTHERS
5073: EXCEPTION
5074: WHEN OTHERS
5075: THEN
5076: csi_gen_utility_pvt.put_line( 'Error from create relationship API.');
5077: csi_gen_utility_pvt.put_line( 'The object_id, which you are trying to cascade its ownership, is not found or expired in csi_item_instances table. ');
5078: RAISE fnd_api.g_exc_error;
5079: END;
5080:
5081: csi_item_instance_pub.update_item_instance
5562: IF l_object_id <> l_relship_csr.subject_id THEN
5563: BEGIN
5564: SELECT instance_usage_code
5565: INTO l_ins_usage_code
5566: FROM csi_item_instances
5567: WHERE instance_id=l_object_id;
5568: EXCEPTION
5569: WHEN NO_DATA_FOUND THEN
5570: NULL;
5585: l_instance_rec.config_inst_rev_num, --added
5586: l_loc_type_code,
5587: l_item_id,
5588: l_vld_org
5589: FROM csi_item_instances
5590: WHERE instance_id = l_relship_csr.subject_id;
5591: --
5592: -- Bug 4232599. Serialized at SO issue items cannot have usage as IN_INVENTORY
5593: -- as taken from parent. Since relationship can be broken only thru' RMA,
5909: SELECT object_version_number,
5910: 'Y'
5911: INTO l_inst_rec.object_version_number,
5912: l_inst_rec.cascade_ownership_flag
5913: FROM csi_item_instances
5914: WHERE instance_id=l_inst_rec.instance_id
5915: AND (active_end_date IS NULL OR active_end_date > sysdate);
5916: EXCEPTION
5917: WHEN OTHERS
5916: EXCEPTION
5917: WHEN OTHERS
5918: THEN
5919: csi_gen_utility_pvt.put_line( 'Error from update relationship API.');
5920: csi_gen_utility_pvt.put_line( 'The object_id, which you are trying to cascade its ownership, is not found or expired in csi_item_instances table. ');
5921: RAISE fnd_api.g_exc_error;
5922: END;
5923:
5924: csi_item_instance_pub.update_item_instance
6162: IF l_object_id <> l_relship_csr.subject_id THEN
6163: BEGIN
6164: SELECT instance_usage_code
6165: INTO l_ins_usage_code
6166: FROM csi_item_instances
6167: WHERE instance_id=l_object_id;
6168: EXCEPTION
6169: WHEN NO_DATA_FOUND THEN
6170: NULL;
6184: l_instance_rec.config_inst_rev_num,
6185: l_item_id,
6186: l_vld_org,
6187: l_loc_type_code
6188: FROM csi_item_instances
6189: WHERE instance_id = l_relship_csr.subject_id;
6190: --
6191: -- Bug 4232599. Serialized at SO issue items cannot have usage as IN_INVENTORY
6192: -- as taken from parent. Since relationship can be broken only thru' RMA,
6644: -- The following code has been added by sguthiva for bug 2416144.
6645: BEGIN
6646: SELECT 'x'
6647: INTO l_active
6648: FROM csi_item_instances cii
6649: WHERE cii.instance_id=p_object_id
6650: AND (SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE));
6651: EXCEPTION
6652: WHEN NO_DATA_FOUND THEN
6669: ,l_quantity
6670: ,l_location_type_code
6671: ,l_inventory_item_id
6672: ,l_vld_org_id
6673: FROM csi_item_instances
6674: WHERE instance_id=p_object_id;
6675: IF l_quantity IS NOT NULL THEN
6676: IF l_quantity<>1 THEN
6677: fnd_message.set_name('CSI', 'CSI_QTY_NOTEQUAL_TO_ONE');
6766: ,l_quantity
6767: ,l_location_type_code
6768: ,l_inventory_item_id
6769: ,l_vld_org_id
6770: FROM csi_item_instances
6771: WHERE instance_id=p_object_id;
6772:
6773: IF l_quantity IS NOT NULL THEN
6774: IF l_quantity<>1 THEN
6889: THEN
6890: BEGIN
6891: SELECT 'x'
6892: INTO l_active
6893: FROM csi_item_instances cii
6894: WHERE cii.instance_id=p_subject_id
6895: AND (SYSDATE BETWEEN NVL(cii.active_start_date, SYSDATE) AND NVL(cii.active_end_date, SYSDATE));
6896: EXCEPTION
6897: WHEN NO_DATA_FOUND THEN
6905: -- End of addition for bug 2416144.
6906: BEGIN
6907: SELECT instance_id,location_type_code
6908: INTO l_instance_id,l_location_type_code
6909: FROM csi_item_instances
6910: WHERE instance_id=p_subject_id;
6911: /* commented by sk for bug 2151750
6912: IF l_location_type_code IS NOT NULL THEN
6913: IF UPPER(l_location_type_code) = 'INVENTORY' THEN
7143: (p_rel_history_tbl(tab_row).OLD_SUBJECT_ID <> FND_API.G_MISS_NUM) ) THEN
7144: BEGIN
7145: SELECT instance_number
7146: INTO p_rel_history_tbl(tab_row).old_subject_number
7147: FROM csi_item_instances
7148: WHERE instance_id = p_rel_history_tbl(tab_row).old_subject_id;
7149: EXCEPTION
7150: WHEN OTHERS THEN
7151: NULL;
7156: (p_rel_history_tbl(tab_row).NEW_SUBJECT_ID <> FND_API.G_MISS_NUM) ) THEN
7157: BEGIN
7158: SELECT instance_number
7159: INTO p_rel_history_tbl(tab_row).new_subject_number
7160: FROM csi_item_instances
7161: WHERE instance_id = p_rel_history_tbl(tab_row).new_subject_id;
7162: EXCEPTION
7163: WHEN OTHERS THEN
7164: NULL;