[Home] [Help]
611: ,NEW_SALES_UNIT_PRICE
612: ,NEW_SALES_CURRENCY_CODE
613: ,NEW_OPERATIONAL_STATUS_CODE
614: -- End addition of columns for FA Integration
615: FROM CSI_ITEM_INSTANCES_H
616: WHERE instance_history_id = p_instance_hist_id
617: AND full_dump_flag = 'Y' ;
618:
619: BEGIN
830: ,NEW_SALES_UNIT_PRICE
831: ,NEW_SALES_CURRENCY_CODE
832: ,NEW_OPERATIONAL_STATUS_CODE
833: -- End addition of columns for FA Integration
834: FROM CSI_ITEM_INSTANCES_H
835: WHERE instance_id = p_instance_id
836: --and creation_date <= x_first_no_dump
837: --and full_dump_flag = 'N'
838: ORDER BY creation_date;
965:
966: CURSOR get_nearest_full_dump(p_instance_id IN NUMBER ,p_time IN DATE) IS
967: SELECT
968: MAX(instance_history_id)
969: FROM CSI_ITEM_INSTANCES_H
970: WHERE creation_date <= p_time
971: and instance_id = p_instance_id
972: and full_dump_flag = 'Y' ;
973:
1177: ,NEW_SALES_CURRENCY_CODE
1178: ,OLD_OPERATIONAL_STATUS_CODE
1179: ,NEW_OPERATIONAL_STATUS_CODE
1180: -- End addition of columns for FA Integration
1181: FROM CSI_ITEM_INSTANCES_H
1182: WHERE creation_date <= p_time
1183: and creation_date >= p_nearest_full_dump
1184: and instance_id = p_instance_id
1185: ORDER BY creation_date;
1893: -- Addition of columns for FA Integration
1894: ' purchase_unit_price, purchase_currency_code, payables_unit_price, payables_currency_code, '||
1895: ' sales_unit_price, sales_currency_code, operational_status_code '||
1896: -- End addition of columns for FA Integration
1897: ' from csi_item_instances where instance_id = :l_inst_id';
1898:
1899: BEGIN
1900:
1901: -- Open the cursor
2689: null;
2690: END;
2691: --start of code for bug 4754569--
2692: ELSE
2693: --To get the vld_organization_id from csi_item_instances--
2694: BEGIN
2695: SELECT last_vld_organization_id
2696: INTO l_vld_organization_id
2697: FROM csi_item_instances
2693: --To get the vld_organization_id from csi_item_instances--
2694: BEGIN
2695: SELECT last_vld_organization_id
2696: INTO l_vld_organization_id
2697: FROM csi_item_instances
2698: WHERE instance_id = p_instance_header_tbl(tab_row).instance_id;
2699: EXCEPTION
2700: WHEN OTHERS THEN
2701: NULL;
2706: IF ( (p_instance_header_tbl(tab_row).inventory_item_id IS NOT NULL) AND
2707: (p_instance_header_tbl(tab_row).inventory_item_id <> FND_API.G_MISS_NUM) ) THEN
2708: l_inventory_item_id:=p_instance_header_tbl(tab_row).inventory_item_id;
2709: ELSE
2710: --To get the inventory_item_id from csi_item_instances--
2711: BEGIN
2712: SELECT inventory_item_id
2713: INTO l_inventory_item_id
2714: FROM csi_item_instances
2710: --To get the inventory_item_id from csi_item_instances--
2711: BEGIN
2712: SELECT inventory_item_id
2713: INTO l_inventory_item_id
2714: FROM csi_item_instances
2715: WHERE instance_id = p_instance_header_tbl(tab_row).instance_id;
2716: EXCEPTION
2717: WHEN OTHERS THEN
2718: NULL;
2816: BEGIN
2817: SELECT msn.gen_object_id
2818: INTO l_gen_object_id
2819: FROM mtl_serial_numbers msn
2820: ,csi_item_instances cii
2821: WHERE msn.inventory_item_id = cii.inventory_item_id
2822: AND msn.serial_number = cii.serial_number
2823: AND cii.instance_id = p_instance_header_tbl(tab_row).instance_id;
2824: EXCEPTION
2834: INTO p_instance_header_tbl(tab_row).parent_asset_group
2835: ,p_instance_header_tbl(tab_row).parent_asset_number
2836: FROM mtl_system_items_b_kfv msi
2837: ,mtl_serial_numbers msn
2838: ,csi_item_instances cii
2839: ,mtl_object_genealogy mog
2840: WHERE msn.gen_object_id= mog.parent_object_id
2841: AND mog.object_id = l_gen_object_id
2842: AND mog.genealogy_type =5
3245: l_covered_level_code OKC_LINE_STYLES_B.LTY_CODE%TYPE;
3246: l_coverage_level_id NUMBER;
3247: --End Addition
3248:
3249: l_union_stmt VARCHAR2(2000) := ' select instance_id from csi_item_instances_h '||
3250: ' where transaction_id = :p_transaction_id UNION select a.instance_id '||
3251: ' from csi_i_parties a, csi_i_parties_h b where a.instance_party_id = b.instance_party_id '||
3252: ' and b.transaction_id = :p_transaction_id UNION select a.instance_id '||
3253: ' from csi_i_assets a, csi_i_assets_h b where a.instance_asset_id = b.instance_asset_id '||
3264: ' UNION SELECT a.instance_id FROM csi_i_pricing_attribs a , csi_i_pricing_attribs_h b '||
3265: ' WHERE a.pricing_attribute_id = b.pricing_attribute_id and b.transaction_id = :p_transaction_id '||
3266: ' UNION SELECT a.instance_id FROM csi_i_version_labels a, csi_i_version_labels_h b '||
3267: ' WHERE a.version_label_id = b.version_label_id and b.transaction_id = :p_transaction_id '||
3268: ' UNION SELECT a.instance_id FROM csi_item_instances a, csi_systems_b b, csi_systems_h c '||
3269: ' WHERE a.system_id = b.system_id and b.system_id = c.system_id and c.transaction_id = :p_transaction_id ';
3270:
3271:
3272: BEGIN
3353: OR (p_instance_query_rec.OPERATIONAL_STATUS_CODE IS NULL )
3354: THEN
3355:
3356: l_instance_rec_exists := TRUE;
3357: l_from_clause := ' csi_item_instances a ';
3358: l_select_columns := ' a.INSTANCE_ID ';
3359:
3360:
3361: END IF;
5977: END IF;
5978: csi_gen_utility_pvt.put_line('Before Insert row...');
5979: -- End addition by sk on 02/06/02 for bug 2213922
5980: IF p_called_from_grp <> fnd_api.g_true THEN
5981: -- Create a row in csi_item_instances
5982: CSI_ITEM_INSTANCES_PKG.Insert_Row
5983: ( px_INSTANCE_ID => p_instance_rec.INSTANCE_ID
5984: ,p_INSTANCE_NUMBER => p_instance_rec.INSTANCE_NUMBER
5985: ,p_EXTERNAL_REFERENCE => p_instance_rec.EXTERNAL_REFERENCE
5978: csi_gen_utility_pvt.put_line('Before Insert row...');
5979: -- End addition by sk on 02/06/02 for bug 2213922
5980: IF p_called_from_grp <> fnd_api.g_true THEN
5981: -- Create a row in csi_item_instances
5982: CSI_ITEM_INSTANCES_PKG.Insert_Row
5983: ( px_INSTANCE_ID => p_instance_rec.INSTANCE_ID
5984: ,p_INSTANCE_NUMBER => p_instance_rec.INSTANCE_NUMBER
5985: ,p_EXTERNAL_REFERENCE => p_instance_rec.EXTERNAL_REFERENCE
5986: ,p_INVENTORY_ITEM_ID => p_instance_rec.INVENTORY_ITEM_ID
6277: -- Get a unique history item instance id from the sequence
6278: l_csi_item_instance_h_id :=
6279: csi_item_instance_vld_pvt.get_csi_item_instance_h_id;
6280:
6281: -- Create a row in csi_item_instances_history table
6282: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
6283: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
6284: ,p_INSTANCE_ID => p_instance_rec.INSTANCE_ID
6285: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
6278: l_csi_item_instance_h_id :=
6279: csi_item_instance_vld_pvt.get_csi_item_instance_h_id;
6280:
6281: -- Create a row in csi_item_instances_history table
6282: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
6283: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
6284: ,p_INSTANCE_ID => p_instance_rec.INSTANCE_ID
6285: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
6286: ,p_OLD_INSTANCE_NUMBER => NULL
6880: l_instance_rec.object_version_number,
6881: l_instance_rec.inventory_item_id,
6882: l_instance_rec.vld_organization_id,
6883: l_instance_rec.quantity
6884: FROM csi_item_instances
6885: WHERE instance_id = l_instance_rec.instance_id;
6886: EXCEPTION
6887: WHEN NO_DATA_FOUND THEN
6888: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_NUM');
7149: SALES_CURRENCY_CODE,
7150: OPERATIONAL_STATUS_CODE,
7151: -- End addition of columns for FA Integration
7152: SOURCE_CODE -- Added Code for Siebel Genesis Project
7153: FROM csi_item_instances
7154: WHERE instance_id = p_instance_id
7155: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
7156:
7157: l_curr_instance_rec curr_instance_rec%ROWTYPE;
7156:
7157: l_curr_instance_rec curr_instance_rec%ROWTYPE;
7158: --
7159: CURSOR new_instance_rec(p_instance_id IN NUMBER) IS
7160: select * from csi_item_instances
7161: where instance_id = p_instance_id;
7162: --
7163: l_new_instance_rec new_instance_rec%ROWTYPE;
7164:
7368: -- Begin Add Code for Siebel Genesis Project
7369: ,OLD_SOURCE_CODE
7370: ,NEW_SOURCE_CODE
7371: -- End Add Code for Siebel Genesis Project
7372: FROM csi_item_instances_h
7373: WHERE instance_history_id = p_instance_history_id
7374: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
7375:
7376: l_ins_hist_id NUMBER;
7545: --Start of modifications for Bug#2887590
7546: --
7547: -- srramakr commented out active_end_date condition since the inheritance should look only for Qty
7548: -- active_end_date check will not allow you to un-expire a child instance having qty > 0.
7549: -- No need to select from CSI_ITEM_INSTANCES since l_curr_instance_re already has the instance info.
7550: IF l_curr_instance_rec.quantity = 0 THEN
7551: RAISE PROCESS_NEXT;
7552: END IF;
7553: --End of modifications for Bug#2887590
7651: l_curr_instance_rec.LAST_OE_ORDER_LINE_ID := fnd_api.g_miss_num;
7652: END IF;
7653: --End of:Added for bug 6177045
7654:
7655: CSI_ITEM_INSTANCES_PKG.Update_Row
7656: ( p_INSTANCE_ID => l_rel_tbl(j).subject_id,
7657: p_INSTANCE_NUMBER => FND_API.G_MISS_CHAR,
7658: p_EXTERNAL_REFERENCE => FND_API.G_MISS_CHAR,
7659: p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
7802: -- Start of modifications for Bug#2547034 on 09/20/02 - rtalluri
7803: BEGIN
7804: SELECT instance_history_id
7805: INTO l_ins_hist_id
7806: FROM csi_item_instances_h h
7807: WHERE h.transaction_id = p_txn_rec.transaction_id
7808: AND h.instance_id = l_rel_tbl(j).subject_id;
7809:
7810: OPEN ins_hist_csr(l_ins_hist_id);
7812: CLOSE ins_hist_csr;
7813:
7814: IF l_ins_hist_csr.full_dump_flag = 'Y'
7815: THEN
7816: CSI_ITEM_INSTANCES_H_PKG.Update_Row
7817: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
7818: ,p_INSTANCE_ID => fnd_api.g_miss_num
7819: ,p_TRANSACTION_ID => fnd_api.g_miss_num
7820: ,p_OLD_INSTANCE_NUMBER => fnd_api.g_miss_char
8440: l_ins_hist_csr.new_operational_status_code := p_instance_rec.operational_status_code;
8441: END IF;
8442:
8443:
8444: CSI_ITEM_INSTANCES_H_PKG.Update_Row
8445: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
8446: ,p_INSTANCE_ID => fnd_api.g_miss_num
8447: ,p_TRANSACTION_ID => fnd_api.g_miss_num
8448: ,p_OLD_INSTANCE_NUMBER => fnd_api.g_miss_char
8835: -- p_instance_rec.OPERATIONAL_STATUS_CODE := l_curr_instance_rec.OPERATIONAL_STATUS_CODE;
8836: -- End addition of columns for FA Integration
8837:
8838: /* Commented out to implement bulk processing.... Bug:5615169
8839: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
8840: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
8841: ,p_INSTANCE_ID => l_rel_tbl(j).subject_id
8842: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
8843: ,p_OLD_INSTANCE_NUMBER => l_curr_instance_rec.INSTANCE_NUMBER
9549: l_instance_hist_rec.new_operational_status_code := p_instance_rec.operational_status_code ;
9550: END IF;
9551:
9552: /* Commented to implement bulk operations...Bug:;
9553: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
9554: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
9555: ,p_INSTANCE_ID => l_rel_tbl(j).SUBJECT_ID
9556: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
9557: ,p_OLD_INSTANCE_NUMBER => FND_API.G_MISS_CHAR
10206: l_ciih_blk_ins_cnt := l_inst_hist_rec_tab.instance_history_id.count;
10207: --
10208: FORALL i in 1 .. l_ciih_blk_ins_cnt
10209: INSERT
10210: INTO CSI_ITEM_INSTANCES_H
10211: ( INSTANCE_HISTORY_ID,
10212: INSTANCE_ID,
10213: TRANSACTION_ID,
10214: OLD_INSTANCE_NUMBER,
10725: )IS
10726:
10727: CURSOR instance_csr (p_ins_id IN NUMBER) IS
10728: SELECT *
10729: FROM csi_item_instances
10730: WHERE instance_id = p_ins_id;
10731:
10732: --Start of: Added for bug 5615162
10733: CURSOR instance_txn_chk_csr(p_ins_id IN NUMBER,p_txn_id in NUMBER, p_src_txn_date in DATE) IS
10733: CURSOR instance_txn_chk_csr(p_ins_id IN NUMBER,p_txn_id in NUMBER, p_src_txn_date in DATE) IS
10734: SELECT 'X' FROM CSI_TRANSACTIONS CST,
10735: (
10736: SELECT CIIH.TRANSACTION_ID TRANSACTION_ID, CIIH.INSTANCE_ID INSTANCE_ID
10737: FROM CSI_ITEM_INSTANCES_H CIIH
10738: WHERE INSTANCE_ID=p_ins_id AND TRANSACTION_ID<>p_txn_id
10739: UNION ALL
10740:
10741: SELECT CIVH.TRANSACTION_ID TRANSACTION_ID, CIV.INSTANCE_ID INSTANCE_ID
11607: SALES_CURRENCY_CODE,
11608: OPERATIONAL_STATUS_CODE,
11609: -- End addition of columns for FA Integration
11610: SOURCE_CODE -- Added Code for Siebel Genesis Project
11611: FROM csi_item_instances
11612: WHERE instance_id = p_instance_id
11613: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
11614: l_curr_instance_rec curr_instance_rec%ROWTYPE;
11615:
11614: l_curr_instance_rec curr_instance_rec%ROWTYPE;
11615:
11616: CURSOR cur_instance_rec(p_instance_id IN NUMBER) IS
11617: SELECT last_vld_organization_id
11618: FROM csi_item_instances
11619: WHERE instance_id = p_instance_id;
11620:
11621: l_cur_instance_rec cur_instance_rec%ROWTYPE;
11622:
11830: -- Begin Add Code for Siebel Genesis Project
11831: ,OLD_SOURCE_CODE
11832: ,NEW_SOURCE_CODE
11833: -- End Add Code for Siebel Genesis Project
11834: FROM csi_item_instances_h
11835: WHERE instance_history_id = p_instance_history_id
11836: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
11837:
11838:
13332: p_instance_rec.owner_party_id = fnd_api.g_miss_num
13333: THEN
13334: SELECT owner_party_id
13335: INTO p_instance_rec.owner_party_id
13336: FROM csi_item_instances
13337: WHERE instance_id=p_instance_rec.instance_id;
13338: END IF;
13339:
13340: IF p_instance_rec.owner_party_id = l_int_party_id
14193: END IF;
14194:
14195: --- Update the instance with the object version number incremented by 1.
14196: p_instance_rec.object_version_number := l_curr_instance_rec.object_version_number + 1;
14197: CSI_ITEM_INSTANCES_PKG.Update_Row
14198: ( p_INSTANCE_ID => p_instance_rec.INSTANCE_ID,
14199: p_INSTANCE_NUMBER => p_instance_rec.INSTANCE_NUMBER,
14200: p_EXTERNAL_REFERENCE => p_instance_rec.EXTERNAL_REFERENCE,
14201: p_INVENTORY_ITEM_ID => p_instance_rec.INVENTORY_ITEM_ID,
14574: IF l_upd_ctr_assn_csr.start_date_active IS NULL THEN
14575: BEGIN
14576: SELECT active_start_date
14577: INTO l_ctr_associations_tbl(l_ctr).start_date_active
14578: FROM csi_item_instances
14579: WHERE instance_id=p_instance_rec.instance_id;
14580: EXCEPTION
14581: WHEN NO_DATA_FOUND THEN
14582: l_ctr_associations_tbl(l_ctr).start_date_active := p_instance_rec.active_start_date;
14661: -- Start of modification for Bug#2547034 on 09/20/02 - rtalluri
14662: BEGIN
14663: SELECT instance_history_id
14664: INTO l_ins_hist_id
14665: FROM csi_item_instances_h h
14666: WHERE h.transaction_id = p_txn_rec.transaction_id
14667: AND h.instance_id = p_instance_rec.instance_id;
14668:
14669: OPEN ins_hist_csr(l_ins_hist_id);
14671: CLOSE ins_hist_csr;
14672:
14673: IF l_ins_hist_csr.full_dump_flag = 'Y'
14674: THEN
14675: CSI_ITEM_INSTANCES_H_PKG.Update_Row
14676: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
14677: ,p_INSTANCE_ID => fnd_api.g_miss_num
14678: ,p_TRANSACTION_ID => fnd_api.g_miss_num
14679: ,p_OLD_INSTANCE_NUMBER => fnd_api.g_miss_char
16402:
16403:
16404:
16405: -- End addition for bug 2671179
16406: CSI_ITEM_INSTANCES_H_PKG.Update_Row
16407: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
16408: ,p_INSTANCE_ID => fnd_api.g_miss_num
16409: ,p_TRANSACTION_ID => fnd_api.g_miss_num
16410: ,p_OLD_INSTANCE_NUMBER => l_ins_hist_csr.OLD_INSTANCE_NUMBER
16952: p_instance_rec.OPERATIONAL_STATUS_CODE := l_curr_instance_rec.OPERATIONAL_STATUS_CODE;
16953: END IF;
16954: -- End addition of columns for FA Integration
16955:
16956: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
16957: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
16958: ,p_INSTANCE_ID => p_instance_rec.instance_id
16959: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
16960: ,p_OLD_INSTANCE_NUMBER => l_curr_instance_rec.INSTANCE_NUMBER
18263: l_instance_hist_rec.new_operational_status_code := p_instance_rec.operational_status_code ;
18264: END IF;
18265: -- End addition of columns for FA Integration
18266:
18267: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
18268: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
18269: ,p_INSTANCE_ID => p_instance_rec.instance_id
18270: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
18271: ,p_OLD_INSTANCE_NUMBER => l_instance_hist_rec.OLD_INSTANCE_NUMBER
18969: SALES_CURRENCY_CODE,
18970: OPERATIONAL_STATUS_CODE,
18971: -- End addition of columns for FA Integration
18972: SOURCE_CODE -- Added Code for Siebel Genesis Project
18973: FROM csi_item_instances
18974: WHERE instance_id = p_instance_id
18975: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
18976:
18977: CURSOR instance_csr (p_ins_id IN NUMBER) IS
18975: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
18976:
18977: CURSOR instance_csr (p_ins_id IN NUMBER) IS
18978: SELECT *
18979: FROM csi_item_instances
18980: WHERE instance_id = p_ins_id;
18981:
18982: l_curr_instance_rec curr_instance_rec%ROWTYPE;
18983: l_instance_csr instance_csr%ROWTYPE;
19134: THEN
19135: BEGIN
19136: SELECT last_vld_organization_id
19137: INTO l_vld_org
19138: FROM csi_item_instances
19139: WHERE instance_id = p_instance_rec.instance_id;
19140: EXCEPTION
19141: WHEN NO_DATA_FOUND THEN
19142: fnd_message.set_name('CSI','CSI_API_INVALID_INSTANCE_ID');
19191: RAISE fnd_api.g_exc_error;
19192: END IF;
19193: END IF;
19194:
19195: CSI_ITEM_INSTANCES_PKG.Update_Row
19196: ( p_INSTANCE_ID => l_rel_tbl(j).subject_id,
19197: p_INSTANCE_NUMBER => FND_API.G_MISS_CHAR,
19198: p_EXTERNAL_REFERENCE => FND_API.G_MISS_CHAR,
19199: p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
19360: -- If the mod value is 0 then dump all the columns both changed and unchanged
19361: -- changed columns have old and new values while the unchanged values have old and new values
19362: -- exactly same
19363:
19364: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
19365: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
19366: ,p_INSTANCE_ID => l_rel_tbl(j).subject_id
19367: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
19368: ,p_OLD_INSTANCE_NUMBER => l_curr_instance_rec.INSTANCE_NUMBER
19581: ELSE
19582: -- If the mod value is not equal to zero then dump only the changed columns
19583: -- while the unchanged values have old and new values as null
19584:
19585: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
19586: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
19587: ,p_INSTANCE_ID => l_rel_tbl(j).subject_id
19588: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
19589: ,p_OLD_INSTANCE_NUMBER => FND_API.G_MISS_CHAR
20075: SALES_CURRENCY_CODE,
20076: OPERATIONAL_STATUS_CODE,
20077: -- End addition of columns for FA Integration
20078: SOURCE_CODE -- Added Code for Siebel Genesis Project
20079: FROM csi_item_instances
20080: WHERE instance_id = p_instance_id ;
20081: -- AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE)); -- COMMENTED BY SK bug 2151760
20082: l_curr_instance_rec get_instance_rec%ROWTYPE;
20083:
20082: l_curr_instance_rec get_instance_rec%ROWTYPE;
20083:
20084: CURSOR instance_csr (p_ins_id IN NUMBER) IS
20085: SELECT *
20086: FROM csi_item_instances
20087: WHERE instance_id = p_ins_id;
20088:
20089: -- Added cursor for bug 6788183
20090: CURSOR relationship_csr (p_subject_id IN NUMBER) IS
20380: THEN
20381: BEGIN
20382: SELECT last_vld_organization_id
20383: INTO l_vld_org
20384: FROM csi_item_instances
20385: WHERE instance_id = p_instance_rec.instance_id;
20386: EXCEPTION
20387: WHEN NO_DATA_FOUND THEN
20388: fnd_message.set_name('CSI','CSI_API_INVALID_INSTANCE_ID');
20437: RAISE fnd_api.g_exc_error;
20438: END IF;
20439: END IF;
20440:
20441: CSI_ITEM_INSTANCES_PKG.Update_Row
20442: ( p_INSTANCE_ID => p_instance_rec.INSTANCE_ID,
20443: p_INSTANCE_NUMBER => l_instance_rec.INSTANCE_NUMBER,
20444: p_EXTERNAL_REFERENCE => l_instance_rec.EXTERNAL_REFERENCE,
20445: p_INVENTORY_ITEM_ID => l_instance_rec.INVENTORY_ITEM_ID,
20611: -- Added for the bug 6788183
20612: BEGIN
20613: SELECT instance_history_id
20614: INTO l_ins_hist_id
20615: FROM csi_item_instances_h h
20616: WHERE h.transaction_id = p_txn_rec.transaction_id
20617: AND h.instance_id = p_instance_rec.instance_id;
20618:
20619: CSI_ITEM_INSTANCES_H_PKG.Update_Row
20615: FROM csi_item_instances_h h
20616: WHERE h.transaction_id = p_txn_rec.transaction_id
20617: AND h.instance_id = p_instance_rec.instance_id;
20618:
20619: CSI_ITEM_INSTANCES_H_PKG.Update_Row
20620: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
20621: ,p_INSTANCE_ID => fnd_api.g_miss_num
20622: ,p_TRANSACTION_ID => fnd_api.g_miss_num
20623: ,p_OLD_INSTANCE_NUMBER => fnd_api.g_miss_char
20842: IF (l_mod_value = 0) THEN
20843: -- If the mod value is 0 then dump all the columns both changed and unchanged
20844: -- changed columns have old and new values while the unchanged values have old and new values
20845: -- exactly same
20846: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
20847: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
20848: ,p_INSTANCE_ID => p_instance_rec.instance_id
20849: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
20850: ,p_OLD_INSTANCE_NUMBER => l_curr_instance_rec.INSTANCE_NUMBER
21064:
21065: ELSE
21066: -- If the mod value is not equal to zero then dump only the changed columns
21067: -- while the unchanged values have old and new values as null
21068: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
21069: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
21070: ,p_INSTANCE_ID => p_instance_rec.instance_id
21071: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
21072: ,p_OLD_INSTANCE_NUMBER => l_instance_rec.INSTANCE_NUMBER
21582: END IF;
21583: END IF;
21584: CLOSE C1;
21585:
21586: --get the quantity from the csi_item_instances for the instance_id passed
21587:
21588: BEGIN
21589: SELECT quantity
21590: INTO l_quantity
21587:
21588: BEGIN
21589: SELECT quantity
21590: INTO l_quantity
21591: FROM csi_item_instances
21592: WHERE instance_id = p_source_instance_rec.instance_id;
21593: EXCEPTION
21594: WHEN NO_DATA_FOUND THEN
21595: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_QUANITY');
21610: THEN
21611: BEGIN
21612: SELECT last_vld_organization_id
21613: INTO p_source_instance_rec.vld_organization_id
21614: FROM csi_item_instances
21615: WHERE instance_id = p_source_instance_rec.instance_id;
21616: EXCEPTION
21617: WHEN OTHERS THEN
21618: NULL;
21755: --update the existing record with 'p_quantity1')
21756: BEGIN
21757: SELECT object_version_number
21758: INTO l_object_version_number
21759: FROM csi_item_instances
21760: WHERE instance_id = p_source_instance_rec.instance_id;
21761: EXCEPTION
21762: WHEN NO_DATA_FOUND THEN
21763: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OBJ_NUM');
22080: END IF;
22081: END IF;
22082: CLOSE C1;
22083:
22084: --get the quantity from the csi_item_instances for the instance_id passed
22085: BEGIN
22086: SELECT quantity
22087: INTO l_quantity
22088: FROM csi_item_instances
22084: --get the quantity from the csi_item_instances for the instance_id passed
22085: BEGIN
22086: SELECT quantity
22087: INTO l_quantity
22088: FROM csi_item_instances
22089: WHERE instance_id = p_source_instance_rec.instance_id;
22090: EXCEPTION
22091: WHEN NO_DATA_FOUND THEN
22092: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_QUANITY');
22099: THEN
22100: BEGIN
22101: SELECT last_vld_organization_id
22102: INTO p_source_instance_rec.vld_organization_id
22103: FROM csi_item_instances
22104: WHERE instance_id = p_source_instance_rec.instance_id;
22105: EXCEPTION
22106: WHEN OTHERS THEN
22107: NULL;
22328: --update the existing record with 'p_quantity1')
22329: BEGIN
22330: SELECT object_version_number
22331: INTO l_object_version_number
22332: FROM csi_item_instances
22333: WHERE instance_id = p_source_instance_rec.instance_id;
22334: EXCEPTION
22335: WHEN NO_DATA_FOUND THEN
22336: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OBJ_NUM');
23104: CSI_Instance_parties_vld_pvt.Check_Reqd_Param_date
23105: ( p_version_label_rec.date_time_stamp ,
23106: ' p_version_label_rec.date_time_stamp ',
23107: l_api_name );
23108: -- Validate the Instance id exists in csi_item_instances
23109: IF NOT( CSI_Instance_parties_vld_pvt.Is_InstanceID_Valid
23110: (p_version_label_rec.INSTANCE_ID)) THEN
23111: RAISE FND_API.G_EXC_ERROR;
23112: END IF;
23453: ( p_version_label_rec.version_label_id ,
23454: ' p_version_label_rec.version_label_id ',
23455: l_api_name );
23456:
23457: -- Validate the Instance id exists in csi_item_instances
23458: IF p_version_label_rec.INSTANCE_ID <> FND_API.G_MISS_NUM THEN
23459: IF NOT( CSI_Instance_parties_vld_pvt.Is_InstanceID_Valid
23460: (p_version_label_rec.INSTANCE_ID)) THEN
23461: RAISE FND_API.G_EXC_ERROR;
27401:
27402: END IF;
27403: -- Start API body
27404: -- Check if an instance id is passed in p_source_instance_rec or not.
27405: -- If yes, then query the item instance from from csi_item_instances table
27406: -- into l_curr_instance_rec.
27407: /*
27408: IF ((p_source_instance_rec.VLD_ORGANIZATION_ID = FND_API.G_MISS_NUM)
27409: OR (p_source_instance_rec.VLD_ORGANIZATION_ID IS NULL)) THEN
27421: THEN
27422: BEGIN
27423: SELECT last_vld_organization_id
27424: INTO l_vld_org_id
27425: FROM csi_item_instances
27426: WHERE instance_id = p_source_instance_rec.instance_id;
27427: EXCEPTION
27428: WHEN OTHERS THEN
27429: NULL;
28138: IF l_obj_id <> p_source_instance_rec.instance_id THEN
28139: BEGIN
28140: SELECT instance_usage_code
28141: INTO l_new_instance_rec.instance_usage_code
28142: FROM csi_item_instances
28143: WHERE instance_id=l_obj_id;
28144: EXCEPTION
28145: WHEN NO_DATA_FOUND THEN
28146: NULL;
28373: ELSE
28374: l_new_instance_rec.OPERATIONAL_STATUS_CODE := p_source_instance_rec.OPERATIONAL_STATUS_CODE;
28375: END IF;
28376: -- End addition of columns for FA Integration
28377: -- Call Create_item_instance to create the new instance in csi_item_instances table.
28378:
28379: CSI_ITEM_INSTANCE_PVT.create_item_instance
28380: ( p_api_version => p_api_version
28381: ,p_commit => p_commit
29783: ,x_msg_data OUT NOCOPY VARCHAR2
29784: ) IS
29785: CURSOR txn_hist_csr (p_txn_id IN NUMBER) IS
29786: SELECT *
29787: FROM csi_item_instances_h
29788: WHERE transaction_id = p_txn_id;
29789:
29790: l_old_ins_header_rec csi_datastructures_pub.instance_header_rec;
29791: l_new_ins_header_rec csi_datastructures_pub.instance_header_rec;
32231: -- Get the last_vld_organization_id for the instance_id passed
32232: BEGIN
32233: SELECT last_vld_organization_id
32234: INTO l_par_vld_organization_id
32235: FROM csi_item_instances
32236: WHERE instance_id = p_source_instance_rec.instance_id;
32237: EXCEPTION
32238: WHEN OTHERS THEN
32239: NULL;
33099: IF l_obj_id <> p_source_instance_rec.instance_id THEN
33100: BEGIN
33101: SELECT instance_usage_code
33102: INTO l_new_instance_rec.instance_usage_code
33103: FROM csi_item_instances
33104: WHERE instance_id=l_obj_id;
33105: EXCEPTION
33106: WHEN NO_DATA_FOUND THEN
33107: NULL;
33129: l_new_instance_rec.install_location_id := p_source_instance_rec.install_location_id;
33130: END IF;
33131: -- End addition by sguthiva for bug 2230264
33132:
33133: -- Call Create_item_instance to create the new instance in csi_item_instances table.
33134: CSI_ITEM_INSTANCE_PVT.create_item_instance
33135: ( p_api_version => p_api_version
33136: ,p_commit => p_commit
33137: ,p_init_msg_list => p_init_msg_list
34055:
34056: BEGIN
34057: SELECT owner_party_id
34058: INTO l_owner_party_id
34059: FROM csi_item_instances
34060: WHERE instance_id = l_new_instance_rec.instance_id;
34061: EXCEPTION
34062: when no_data_found then
34063: l_owner_party_id := null;
34522: SELECT last_vld_organization_id,
34523: inventory_item_id
34524: INTO l_instance_header_tbl(1).vld_organization_id,
34525: l_instance_header_tbl(1).inventory_item_id
34526: FROM csi_item_instances
34527: WHERE instance_id =l_instance_header_tbl(1).instance_id;
34528: EXCEPTION
34529: WHEN OTHERS THEN
34530: NULL;
34700: ,config_inst_item_id
34701: INTO p_config_rec.config_inst_hdr_id
34702: ,p_config_rec.config_inst_rev_num
34703: ,p_config_rec.config_inst_item_id
34704: FROM csi_item_instances
34705: WHERE instance_id=p_instance_id;
34706: EXCEPTION
34707: WHEN NO_DATA_FOUND THEN
34708: NULL;