[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
2583: BEGIN
2584: SELECT msi.concatenated_segments
2585: INTO p_instance_header_tbl(tab_row).INVENTORY_ITEM_NAME
2586: FROM mtl_system_items_b_kfv msi
2587: ,csi_item_instances cii
2588: WHERE msi.inventory_item_id = p_instance_header_tbl(tab_row).inventory_item_id
2589: AND msi.organization_id = Nvl(p_instance_header_tbl(tab_row).vld_organization_id,cii.last_vld_organization_id)
2590: AND cii.instance_id = p_instance_header_tbl(tab_row).instance_id;
2591: EXCEPTION
2708: null;
2709: END;
2710: --start of code for bug 4754569--
2711: ELSE
2712: --To get the vld_organization_id from csi_item_instances--
2713: BEGIN
2714: SELECT last_vld_organization_id
2715: INTO l_vld_organization_id
2716: FROM csi_item_instances
2712: --To get the vld_organization_id from csi_item_instances--
2713: BEGIN
2714: SELECT last_vld_organization_id
2715: INTO l_vld_organization_id
2716: FROM csi_item_instances
2717: WHERE instance_id = p_instance_header_tbl(tab_row).instance_id;
2718: EXCEPTION
2719: WHEN OTHERS THEN
2720: NULL;
2725: IF ( (p_instance_header_tbl(tab_row).inventory_item_id IS NOT NULL) AND
2726: (p_instance_header_tbl(tab_row).inventory_item_id <> FND_API.G_MISS_NUM) ) THEN
2727: l_inventory_item_id:=p_instance_header_tbl(tab_row).inventory_item_id;
2728: ELSE
2729: --To get the inventory_item_id from csi_item_instances--
2730: BEGIN
2731: SELECT inventory_item_id
2732: INTO l_inventory_item_id
2733: FROM csi_item_instances
2729: --To get the inventory_item_id from csi_item_instances--
2730: BEGIN
2731: SELECT inventory_item_id
2732: INTO l_inventory_item_id
2733: FROM csi_item_instances
2734: WHERE instance_id = p_instance_header_tbl(tab_row).instance_id;
2735: EXCEPTION
2736: WHEN OTHERS THEN
2737: NULL;
2835: BEGIN
2836: SELECT msn.gen_object_id
2837: INTO l_gen_object_id
2838: FROM mtl_serial_numbers msn
2839: ,csi_item_instances cii
2840: WHERE msn.inventory_item_id = cii.inventory_item_id
2841: AND msn.serial_number = cii.serial_number
2842: AND cii.instance_id = p_instance_header_tbl(tab_row).instance_id;
2843: EXCEPTION
2853: INTO p_instance_header_tbl(tab_row).parent_asset_group
2854: ,p_instance_header_tbl(tab_row).parent_asset_number
2855: FROM mtl_system_items_b_kfv msi
2856: ,mtl_serial_numbers msn
2857: ,csi_item_instances cii
2858: ,mtl_object_genealogy mog
2859: WHERE msn.gen_object_id= mog.parent_object_id
2860: AND mog.object_id = l_gen_object_id
2861: AND mog.genealogy_type =5
3264: l_covered_level_code OKC_LINE_STYLES_B.LTY_CODE%TYPE;
3265: l_coverage_level_id NUMBER;
3266: --End Addition
3267:
3268: l_union_stmt VARCHAR2(2000) := ' select instance_id from csi_item_instances_h '||
3269: ' where transaction_id = :p_transaction_id UNION select a.instance_id '||
3270: ' from csi_i_parties a, csi_i_parties_h b where a.instance_party_id = b.instance_party_id '||
3271: ' and b.transaction_id = :p_transaction_id UNION select a.instance_id '||
3272: ' from csi_i_assets a, csi_i_assets_h b where a.instance_asset_id = b.instance_asset_id '||
3283: ' UNION SELECT a.instance_id FROM csi_i_pricing_attribs a , csi_i_pricing_attribs_h b '||
3284: ' WHERE a.pricing_attribute_id = b.pricing_attribute_id and b.transaction_id = :p_transaction_id '||
3285: ' UNION SELECT a.instance_id FROM csi_i_version_labels a, csi_i_version_labels_h b '||
3286: ' WHERE a.version_label_id = b.version_label_id and b.transaction_id = :p_transaction_id '||
3287: ' UNION SELECT a.instance_id FROM csi_item_instances a, csi_systems_b b, csi_systems_h c '||
3288: ' WHERE a.system_id = b.system_id and b.system_id = c.system_id and c.transaction_id = :p_transaction_id ';
3289:
3290:
3291: BEGIN
3372: OR (p_instance_query_rec.OPERATIONAL_STATUS_CODE IS NULL )
3373: THEN
3374:
3375: l_instance_rec_exists := TRUE;
3376: l_from_clause := ' csi_item_instances a ';
3377: l_select_columns := ' a.INSTANCE_ID ';
3378:
3379:
3380: END IF;
6041: END IF;
6042: csi_gen_utility_pvt.put_line('Before Insert row...');
6043: -- End addition by sk on 02/06/02 for bug 2213922
6044: IF p_called_from_grp <> fnd_api.g_true THEN
6045: -- Create a row in csi_item_instances
6046: CSI_ITEM_INSTANCES_PKG.Insert_Row
6047: ( px_INSTANCE_ID => p_instance_rec.INSTANCE_ID
6048: ,p_INSTANCE_NUMBER => p_instance_rec.INSTANCE_NUMBER
6049: ,p_EXTERNAL_REFERENCE => p_instance_rec.EXTERNAL_REFERENCE
6042: csi_gen_utility_pvt.put_line('Before Insert row...');
6043: -- End addition by sk on 02/06/02 for bug 2213922
6044: IF p_called_from_grp <> fnd_api.g_true THEN
6045: -- Create a row in csi_item_instances
6046: CSI_ITEM_INSTANCES_PKG.Insert_Row
6047: ( px_INSTANCE_ID => p_instance_rec.INSTANCE_ID
6048: ,p_INSTANCE_NUMBER => p_instance_rec.INSTANCE_NUMBER
6049: ,p_EXTERNAL_REFERENCE => p_instance_rec.EXTERNAL_REFERENCE
6050: ,p_INVENTORY_ITEM_ID => p_instance_rec.INVENTORY_ITEM_ID
6350: -- Get a unique history item instance id from the sequence
6351: l_csi_item_instance_h_id :=
6352: csi_item_instance_vld_pvt.get_csi_item_instance_h_id;
6353:
6354: -- Create a row in csi_item_instances_history table
6355: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
6356: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
6357: ,p_INSTANCE_ID => p_instance_rec.INSTANCE_ID
6358: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
6351: l_csi_item_instance_h_id :=
6352: csi_item_instance_vld_pvt.get_csi_item_instance_h_id;
6353:
6354: -- Create a row in csi_item_instances_history table
6355: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
6356: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
6357: ,p_INSTANCE_ID => p_instance_rec.INSTANCE_ID
6358: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
6359: ,p_OLD_INSTANCE_NUMBER => NULL
6957: l_instance_rec.object_version_number,
6958: l_instance_rec.inventory_item_id,
6959: l_instance_rec.vld_organization_id,
6960: l_instance_rec.quantity
6961: FROM csi_item_instances
6962: WHERE instance_id = l_instance_rec.instance_id;
6963: EXCEPTION
6964: WHEN NO_DATA_FOUND THEN
6965: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_INSTANCE_NUM');
7234: SALES_CURRENCY_CODE,
7235: OPERATIONAL_STATUS_CODE,
7236: -- End addition of columns for FA Integration
7237: SOURCE_CODE -- Added Code for Siebel Genesis Project
7238: FROM csi_item_instances
7239: WHERE instance_id = p_instance_id
7240: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
7241:
7242: l_curr_instance_rec curr_instance_rec%ROWTYPE;
7241:
7242: l_curr_instance_rec curr_instance_rec%ROWTYPE;
7243: --
7244: CURSOR new_instance_rec(p_instance_id IN NUMBER) IS
7245: select * from csi_item_instances
7246: where instance_id = p_instance_id;
7247: --
7248: l_new_instance_rec new_instance_rec%ROWTYPE;
7249:
7453: -- Begin Add Code for Siebel Genesis Project
7454: ,OLD_SOURCE_CODE
7455: ,NEW_SOURCE_CODE
7456: -- End Add Code for Siebel Genesis Project
7457: FROM csi_item_instances_h
7458: WHERE instance_history_id = p_instance_history_id
7459: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
7460:
7461: l_ins_hist_id NUMBER;
7631: --Start of modifications for Bug#2887590
7632: --
7633: -- srramakr commented out active_end_date condition since the inheritance should look only for Qty
7634: -- active_end_date check will not allow you to un-expire a child instance having qty > 0.
7635: -- No need to select from CSI_ITEM_INSTANCES since l_curr_instance_re already has the instance info.
7636: IF l_curr_instance_rec.quantity = 0 THEN
7637: RAISE PROCESS_NEXT;
7638: END IF;
7639: --End of modifications for Bug#2887590
7755: --Added for bug 7708851, base bug 7239642
7756: BEGIN
7757: SELECT 'Y'
7758: INTO l_create_event_called
7759: FROM csi_item_instances_h
7760: WHERE instance_id = l_rel_tbl(j).subject_id
7761: AND new_accounting_class_code = 'CUST_PROD'
7762: AND ROWNUM = 1;
7763: EXCEPTION
7767: IF (l_debug_level > 1) THEN
7768: csi_gen_utility_pvt.put_line('l_create_event_called : '||l_create_event_called);
7769: END IF;
7770:
7771: CSI_ITEM_INSTANCES_PKG.Update_Row
7772: ( p_INSTANCE_ID => l_rel_tbl(j).subject_id,
7773: p_INSTANCE_NUMBER => FND_API.G_MISS_CHAR,
7774: p_EXTERNAL_REFERENCE => FND_API.G_MISS_CHAR,
7775: p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
8017: -- Start of modifications for Bug#2547034 on 09/20/02 - rtalluri
8018: BEGIN
8019: SELECT instance_history_id
8020: INTO l_ins_hist_id
8021: FROM csi_item_instances_h h
8022: WHERE h.transaction_id = p_txn_rec.transaction_id
8023: AND h.instance_id = l_rel_tbl(j).subject_id;
8024:
8025: OPEN ins_hist_csr(l_ins_hist_id);
8027: CLOSE ins_hist_csr;
8028:
8029: IF l_ins_hist_csr.full_dump_flag = 'Y'
8030: THEN
8031: CSI_ITEM_INSTANCES_H_PKG.Update_Row
8032: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
8033: ,p_INSTANCE_ID => fnd_api.g_miss_num
8034: ,p_TRANSACTION_ID => fnd_api.g_miss_num
8035: ,p_OLD_INSTANCE_NUMBER => fnd_api.g_miss_char
8655: l_ins_hist_csr.new_operational_status_code := p_instance_rec.operational_status_code;
8656: END IF;
8657:
8658:
8659: CSI_ITEM_INSTANCES_H_PKG.Update_Row
8660: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
8661: ,p_INSTANCE_ID => fnd_api.g_miss_num
8662: ,p_TRANSACTION_ID => fnd_api.g_miss_num
8663: ,p_OLD_INSTANCE_NUMBER => fnd_api.g_miss_char
9052: -- p_instance_rec.OPERATIONAL_STATUS_CODE := l_curr_instance_rec.OPERATIONAL_STATUS_CODE;
9053: -- End addition of columns for FA Integration
9054:
9055: /* Commented out to implement bulk processing.... Bug:5615169
9056: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
9057: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
9058: ,p_INSTANCE_ID => l_rel_tbl(j).subject_id
9059: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
9060: ,p_OLD_INSTANCE_NUMBER => l_curr_instance_rec.INSTANCE_NUMBER
9766: l_instance_hist_rec.new_operational_status_code := p_instance_rec.operational_status_code ;
9767: END IF;
9768:
9769: /* Commented to implement bulk operations...Bug:;
9770: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
9771: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
9772: ,p_INSTANCE_ID => l_rel_tbl(j).SUBJECT_ID
9773: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
9774: ,p_OLD_INSTANCE_NUMBER => FND_API.G_MISS_CHAR
10453: l_ciih_blk_ins_cnt := l_inst_hist_rec_tab.instance_history_id.count;
10454: --
10455: FORALL i in 1 .. l_ciih_blk_ins_cnt
10456: INSERT
10457: INTO CSI_ITEM_INSTANCES_H
10458: ( INSTANCE_HISTORY_ID,
10459: INSTANCE_ID,
10460: TRANSACTION_ID,
10461: OLD_INSTANCE_NUMBER,
10976: )IS
10977:
10978: CURSOR instance_csr (p_ins_id IN NUMBER) IS
10979: SELECT *
10980: FROM csi_item_instances
10981: WHERE instance_id = p_ins_id;
10982:
10983: --Start of: Added for bug 5615162
10984: CURSOR instance_txn_chk_csr(p_ins_id IN NUMBER,p_txn_id in NUMBER, p_src_txn_date in DATE) IS
10984: CURSOR instance_txn_chk_csr(p_ins_id IN NUMBER,p_txn_id in NUMBER, p_src_txn_date in DATE) IS
10985: SELECT 'X' FROM CSI_TRANSACTIONS CST,
10986: (
10987: SELECT CIIH.TRANSACTION_ID TRANSACTION_ID, CIIH.INSTANCE_ID INSTANCE_ID
10988: FROM CSI_ITEM_INSTANCES_H CIIH
10989: WHERE INSTANCE_ID=p_ins_id AND TRANSACTION_ID<>p_txn_id
10990: UNION ALL
10991:
10992: SELECT CIVH.TRANSACTION_ID TRANSACTION_ID, CIV.INSTANCE_ID INSTANCE_ID
11913: SALES_CURRENCY_CODE,
11914: OPERATIONAL_STATUS_CODE,
11915: -- End addition of columns for FA Integration
11916: SOURCE_CODE -- Added Code for Siebel Genesis Project
11917: FROM csi_item_instances
11918: WHERE instance_id = p_instance_id
11919: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
11920: l_curr_instance_rec curr_instance_rec%ROWTYPE;
11921:
11920: l_curr_instance_rec curr_instance_rec%ROWTYPE;
11921:
11922: CURSOR cur_instance_rec(p_instance_id IN NUMBER) IS
11923: SELECT last_vld_organization_id
11924: FROM csi_item_instances
11925: WHERE instance_id = p_instance_id;
11926:
11927: l_cur_instance_rec cur_instance_rec%ROWTYPE;
11928:
12136: -- Begin Add Code for Siebel Genesis Project
12137: ,OLD_SOURCE_CODE
12138: ,NEW_SOURCE_CODE
12139: -- End Add Code for Siebel Genesis Project
12140: FROM csi_item_instances_h
12141: WHERE instance_history_id = p_instance_history_id
12142: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
12143:
12144:
13706: p_instance_rec.owner_party_id = fnd_api.g_miss_num
13707: THEN
13708: SELECT owner_party_id
13709: INTO p_instance_rec.owner_party_id
13710: FROM csi_item_instances
13711: WHERE instance_id=p_instance_rec.instance_id;
13712: END IF;
13713:
13714: IF p_instance_rec.owner_party_id = l_int_party_id
14641: END IF;
14642:
14643: --- Update the instance with the object version number incremented by 1.
14644: p_instance_rec.object_version_number := l_curr_instance_rec.object_version_number + 1;
14645: CSI_ITEM_INSTANCES_PKG.Update_Row
14646: ( p_INSTANCE_ID => p_instance_rec.INSTANCE_ID,
14647: p_INSTANCE_NUMBER => p_instance_rec.INSTANCE_NUMBER,
14648: p_EXTERNAL_REFERENCE => p_instance_rec.EXTERNAL_REFERENCE,
14649: p_INVENTORY_ITEM_ID => p_instance_rec.INVENTORY_ITEM_ID,
15030: IF l_upd_ctr_assn_csr.start_date_active IS NULL THEN
15031: BEGIN
15032: SELECT active_start_date
15033: INTO l_ctr_associations_tbl(l_ctr).start_date_active
15034: FROM csi_item_instances
15035: WHERE instance_id=p_instance_rec.instance_id;
15036: EXCEPTION
15037: WHEN NO_DATA_FOUND THEN
15038: l_ctr_associations_tbl(l_ctr).start_date_active := p_instance_rec.active_start_date;
15117: -- Start of modification for Bug#2547034 on 09/20/02 - rtalluri
15118: BEGIN
15119: SELECT instance_history_id
15120: INTO l_ins_hist_id
15121: FROM csi_item_instances_h h
15122: WHERE h.transaction_id = p_txn_rec.transaction_id
15123: AND h.instance_id = p_instance_rec.instance_id;
15124:
15125: OPEN ins_hist_csr(l_ins_hist_id);
15127: CLOSE ins_hist_csr;
15128:
15129: IF l_ins_hist_csr.full_dump_flag = 'Y'
15130: THEN
15131: CSI_ITEM_INSTANCES_H_PKG.Update_Row
15132: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
15133: ,p_INSTANCE_ID => fnd_api.g_miss_num
15134: ,p_TRANSACTION_ID => fnd_api.g_miss_num
15135: ,p_OLD_INSTANCE_NUMBER => fnd_api.g_miss_char
16858:
16859:
16860:
16861: -- End addition for bug 2671179
16862: CSI_ITEM_INSTANCES_H_PKG.Update_Row
16863: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
16864: ,p_INSTANCE_ID => fnd_api.g_miss_num
16865: ,p_TRANSACTION_ID => fnd_api.g_miss_num
16866: ,p_OLD_INSTANCE_NUMBER => l_ins_hist_csr.OLD_INSTANCE_NUMBER
17408: p_instance_rec.OPERATIONAL_STATUS_CODE := l_curr_instance_rec.OPERATIONAL_STATUS_CODE;
17409: END IF;
17410: -- End addition of columns for FA Integration
17411:
17412: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
17413: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
17414: ,p_INSTANCE_ID => p_instance_rec.instance_id
17415: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
17416: ,p_OLD_INSTANCE_NUMBER => l_curr_instance_rec.INSTANCE_NUMBER
18719: l_instance_hist_rec.new_operational_status_code := p_instance_rec.operational_status_code ;
18720: END IF;
18721: -- End addition of columns for FA Integration
18722:
18723: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
18724: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
18725: ,p_INSTANCE_ID => p_instance_rec.instance_id
18726: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
18727: ,p_OLD_INSTANCE_NUMBER => l_instance_hist_rec.OLD_INSTANCE_NUMBER
19466: SALES_CURRENCY_CODE,
19467: OPERATIONAL_STATUS_CODE,
19468: -- End addition of columns for FA Integration
19469: SOURCE_CODE -- Added Code for Siebel Genesis Project
19470: FROM csi_item_instances
19471: WHERE instance_id = p_instance_id
19472: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
19473:
19474: CURSOR instance_csr (p_ins_id IN NUMBER) IS
19472: FOR UPDATE OF OBJECT_VERSION_NUMBER ;
19473:
19474: CURSOR instance_csr (p_ins_id IN NUMBER) IS
19475: SELECT *
19476: FROM csi_item_instances
19477: WHERE instance_id = p_ins_id;
19478:
19479: l_curr_instance_rec curr_instance_rec%ROWTYPE;
19480: l_instance_csr instance_csr%ROWTYPE;
19631: THEN
19632: BEGIN
19633: SELECT last_vld_organization_id
19634: INTO l_vld_org
19635: FROM csi_item_instances
19636: WHERE instance_id = p_instance_rec.instance_id;
19637: EXCEPTION
19638: WHEN NO_DATA_FOUND THEN
19639: fnd_message.set_name('CSI','CSI_API_INVALID_INSTANCE_ID');
19688: RAISE fnd_api.g_exc_error;
19689: END IF;
19690: END IF;
19691:
19692: CSI_ITEM_INSTANCES_PKG.Update_Row
19693: ( p_INSTANCE_ID => l_rel_tbl(j).subject_id,
19694: p_INSTANCE_NUMBER => FND_API.G_MISS_CHAR,
19695: p_EXTERNAL_REFERENCE => FND_API.G_MISS_CHAR,
19696: p_INVENTORY_ITEM_ID => FND_API.G_MISS_NUM,
19857: -- If the mod value is 0 then dump all the columns both changed and unchanged
19858: -- changed columns have old and new values while the unchanged values have old and new values
19859: -- exactly same
19860:
19861: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
19862: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
19863: ,p_INSTANCE_ID => l_rel_tbl(j).subject_id
19864: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
19865: ,p_OLD_INSTANCE_NUMBER => l_curr_instance_rec.INSTANCE_NUMBER
20078: ELSE
20079: -- If the mod value is not equal to zero then dump only the changed columns
20080: -- while the unchanged values have old and new values as null
20081:
20082: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
20083: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
20084: ,p_INSTANCE_ID => l_rel_tbl(j).subject_id
20085: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
20086: ,p_OLD_INSTANCE_NUMBER => FND_API.G_MISS_CHAR
20576: SALES_CURRENCY_CODE,
20577: OPERATIONAL_STATUS_CODE,
20578: -- End addition of columns for FA Integration
20579: SOURCE_CODE -- Added Code for Siebel Genesis Project
20580: FROM csi_item_instances
20581: WHERE instance_id = p_instance_id ;
20582: -- AND (( ACTIVE_END_DATE IS NULL) OR (ACTIVE_END_DATE >= SYSDATE)); -- COMMENTED BY SK bug 2151760
20583: l_curr_instance_rec get_instance_rec%ROWTYPE;
20584:
20583: l_curr_instance_rec get_instance_rec%ROWTYPE;
20584:
20585: CURSOR instance_csr (p_ins_id IN NUMBER) IS
20586: SELECT *
20587: FROM csi_item_instances
20588: WHERE instance_id = p_ins_id;
20589:
20590: -- Added cursor for bug 6788183
20591: CURSOR relationship_csr (p_subject_id IN NUMBER) IS
20917: THEN
20918: BEGIN
20919: SELECT last_vld_organization_id
20920: INTO l_vld_org
20921: FROM csi_item_instances
20922: WHERE instance_id = p_instance_rec.instance_id;
20923: EXCEPTION
20924: WHEN NO_DATA_FOUND THEN
20925: fnd_message.set_name('CSI','CSI_API_INVALID_INSTANCE_ID');
20974: RAISE fnd_api.g_exc_error;
20975: END IF;
20976: END IF;
20977:
20978: CSI_ITEM_INSTANCES_PKG.Update_Row
20979: ( p_INSTANCE_ID => p_instance_rec.INSTANCE_ID,
20980: p_INSTANCE_NUMBER => l_instance_rec.INSTANCE_NUMBER,
20981: p_EXTERNAL_REFERENCE => l_instance_rec.EXTERNAL_REFERENCE,
20982: p_INVENTORY_ITEM_ID => l_instance_rec.INVENTORY_ITEM_ID,
21148: -- Added for the bug 6788183
21149: BEGIN
21150: SELECT instance_history_id
21151: INTO l_ins_hist_id
21152: FROM csi_item_instances_h h
21153: WHERE h.transaction_id = p_txn_rec.transaction_id
21154: AND h.instance_id = p_instance_rec.instance_id;
21155:
21156: CSI_ITEM_INSTANCES_H_PKG.Update_Row
21152: FROM csi_item_instances_h h
21153: WHERE h.transaction_id = p_txn_rec.transaction_id
21154: AND h.instance_id = p_instance_rec.instance_id;
21155:
21156: CSI_ITEM_INSTANCES_H_PKG.Update_Row
21157: ( p_INSTANCE_HISTORY_ID => l_ins_hist_id
21158: ,p_INSTANCE_ID => fnd_api.g_miss_num
21159: ,p_TRANSACTION_ID => fnd_api.g_miss_num
21160: ,p_OLD_INSTANCE_NUMBER => fnd_api.g_miss_char
21379: IF (l_mod_value = 0) THEN
21380: -- If the mod value is 0 then dump all the columns both changed and unchanged
21381: -- changed columns have old and new values while the unchanged values have old and new values
21382: -- exactly same
21383: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
21384: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
21385: ,p_INSTANCE_ID => p_instance_rec.instance_id
21386: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
21387: ,p_OLD_INSTANCE_NUMBER => l_curr_instance_rec.INSTANCE_NUMBER
21601:
21602: ELSE
21603: -- If the mod value is not equal to zero then dump only the changed columns
21604: -- while the unchanged values have old and new values as null
21605: CSI_ITEM_INSTANCES_H_PKG.Insert_Row
21606: ( px_INSTANCE_HISTORY_ID => l_csi_item_instance_h_id
21607: ,p_INSTANCE_ID => p_instance_rec.instance_id
21608: ,p_TRANSACTION_ID => p_txn_rec.TRANSACTION_ID
21609: ,p_OLD_INSTANCE_NUMBER => l_instance_rec.INSTANCE_NUMBER
22123: END IF;
22124: END IF;
22125: CLOSE C1;
22126:
22127: --get the quantity from the csi_item_instances for the instance_id passed
22128:
22129: BEGIN
22130: SELECT quantity
22131: INTO l_quantity
22128:
22129: BEGIN
22130: SELECT quantity
22131: INTO l_quantity
22132: FROM csi_item_instances
22133: WHERE instance_id = p_source_instance_rec.instance_id;
22134: EXCEPTION
22135: WHEN NO_DATA_FOUND THEN
22136: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_QUANITY');
22151: THEN
22152: BEGIN
22153: SELECT last_vld_organization_id
22154: INTO p_source_instance_rec.vld_organization_id
22155: FROM csi_item_instances
22156: WHERE instance_id = p_source_instance_rec.instance_id;
22157: EXCEPTION
22158: WHEN OTHERS THEN
22159: NULL;
22296: --update the existing record with 'p_quantity1')
22297: BEGIN
22298: SELECT object_version_number
22299: INTO l_object_version_number
22300: FROM csi_item_instances
22301: WHERE instance_id = p_source_instance_rec.instance_id;
22302: EXCEPTION
22303: WHEN NO_DATA_FOUND THEN
22304: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OBJ_NUM');
22625: END IF;
22626: END IF;
22627: CLOSE C1;
22628:
22629: --get the quantity from the csi_item_instances for the instance_id passed
22630: BEGIN
22631: SELECT quantity
22632: INTO l_quantity
22633: FROM csi_item_instances
22629: --get the quantity from the csi_item_instances for the instance_id passed
22630: BEGIN
22631: SELECT quantity
22632: INTO l_quantity
22633: FROM csi_item_instances
22634: WHERE instance_id = p_source_instance_rec.instance_id;
22635: EXCEPTION
22636: WHEN NO_DATA_FOUND THEN
22637: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_QUANITY');
22644: THEN
22645: BEGIN
22646: SELECT last_vld_organization_id
22647: INTO p_source_instance_rec.vld_organization_id
22648: FROM csi_item_instances
22649: WHERE instance_id = p_source_instance_rec.instance_id;
22650: EXCEPTION
22651: WHEN OTHERS THEN
22652: NULL;
22873: --update the existing record with 'p_quantity1')
22874: BEGIN
22875: SELECT object_version_number
22876: INTO l_object_version_number
22877: FROM csi_item_instances
22878: WHERE instance_id = p_source_instance_rec.instance_id;
22879: EXCEPTION
22880: WHEN NO_DATA_FOUND THEN
22881: FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_OBJ_NUM');
23657: CSI_Instance_parties_vld_pvt.Check_Reqd_Param_date
23658: ( p_version_label_rec.date_time_stamp ,
23659: ' p_version_label_rec.date_time_stamp ',
23660: l_api_name );
23661: -- Validate the Instance id exists in csi_item_instances
23662: IF NOT( CSI_Instance_parties_vld_pvt.Is_InstanceID_Valid
23663: (p_version_label_rec.INSTANCE_ID)) THEN
23664: RAISE FND_API.G_EXC_ERROR;
23665: END IF;
24010: ( p_version_label_rec.version_label_id ,
24011: ' p_version_label_rec.version_label_id ',
24012: l_api_name );
24013:
24014: -- Validate the Instance id exists in csi_item_instances
24015: IF p_version_label_rec.INSTANCE_ID <> FND_API.G_MISS_NUM THEN
24016: IF NOT( CSI_Instance_parties_vld_pvt.Is_InstanceID_Valid
24017: (p_version_label_rec.INSTANCE_ID)) THEN
24018: RAISE FND_API.G_EXC_ERROR;
27978:
27979: END IF;
27980: -- Start API body
27981: -- Check if an instance id is passed in p_source_instance_rec or not.
27982: -- If yes, then query the item instance from from csi_item_instances table
27983: -- into l_curr_instance_rec.
27984: /*
27985: IF ((p_source_instance_rec.VLD_ORGANIZATION_ID = FND_API.G_MISS_NUM)
27986: OR (p_source_instance_rec.VLD_ORGANIZATION_ID IS NULL)) THEN
27998: THEN
27999: BEGIN
28000: SELECT last_vld_organization_id
28001: INTO l_vld_org_id
28002: FROM csi_item_instances
28003: WHERE instance_id = p_source_instance_rec.instance_id;
28004: EXCEPTION
28005: WHEN OTHERS THEN
28006: NULL;
28715: IF l_obj_id <> p_source_instance_rec.instance_id THEN
28716: BEGIN
28717: SELECT instance_usage_code
28718: INTO l_new_instance_rec.instance_usage_code
28719: FROM csi_item_instances
28720: WHERE instance_id=l_obj_id;
28721: EXCEPTION
28722: WHEN NO_DATA_FOUND THEN
28723: NULL;
28950: ELSE
28951: l_new_instance_rec.OPERATIONAL_STATUS_CODE := p_source_instance_rec.OPERATIONAL_STATUS_CODE;
28952: END IF;
28953: -- End addition of columns for FA Integration
28954: -- Call Create_item_instance to create the new instance in csi_item_instances table.
28955:
28956: CSI_ITEM_INSTANCE_PVT.create_item_instance
28957: ( p_api_version => p_api_version
28958: ,p_commit => p_commit
30385: ,x_msg_data OUT NOCOPY VARCHAR2
30386: ) IS
30387: CURSOR txn_hist_csr (p_txn_id IN NUMBER) IS
30388: SELECT *
30389: FROM csi_item_instances_h
30390: WHERE transaction_id = p_txn_id;
30391:
30392: l_old_ins_header_rec csi_datastructures_pub.instance_header_rec;
30393: l_new_ins_header_rec csi_datastructures_pub.instance_header_rec;
32852: -- Get the last_vld_organization_id for the instance_id passed
32853: BEGIN
32854: SELECT last_vld_organization_id
32855: INTO l_par_vld_organization_id
32856: FROM csi_item_instances
32857: WHERE instance_id = p_source_instance_rec.instance_id;
32858: EXCEPTION
32859: WHEN OTHERS THEN
32860: NULL;
33721: IF l_obj_id <> p_source_instance_rec.instance_id THEN
33722: BEGIN
33723: SELECT instance_usage_code
33724: INTO l_new_instance_rec.instance_usage_code
33725: FROM csi_item_instances
33726: WHERE instance_id=l_obj_id;
33727: EXCEPTION
33728: WHEN NO_DATA_FOUND THEN
33729: NULL;
33757: ELSE
33758: l_new_instance_rec.SOURCE_CODE := p_source_instance_rec.SOURCE_CODE;
33759: END IF;
33760:
33761: -- Call Create_item_instance to create the new instance in csi_item_instances table.
33762: CSI_ITEM_INSTANCE_PVT.create_item_instance
33763: ( p_api_version => p_api_version
33764: ,p_commit => p_commit
33765: ,p_init_msg_list => p_init_msg_list
34442: select 'Y'
34443: into l_contract_exists
34444: from okc_k_lines_b where id in
34445: ( select CLE_ID from okc_k_items
34446: where object1_id1=( select to_char(instance_id) from csi_item_instances where instance_id = p_source_instance_rec.instance_id) --15866675
34447: and jtot_object1_code= 'OKX_CUSTPROD') and rownum=1;
34448: EXCEPTION
34449: WHEN OTHERS THEN
34450: l_contract_exists := NULL;
34698:
34699: BEGIN
34700: SELECT owner_party_id
34701: INTO l_owner_party_id
34702: FROM csi_item_instances
34703: WHERE instance_id = l_new_instance_rec.instance_id;
34704: EXCEPTION
34705: when no_data_found then
34706: l_owner_party_id := null;
35173: SELECT last_vld_organization_id,
35174: inventory_item_id
35175: INTO l_instance_header_tbl(1).vld_organization_id,
35176: l_instance_header_tbl(1).inventory_item_id
35177: FROM csi_item_instances
35178: WHERE instance_id =l_instance_header_tbl(1).instance_id;
35179: EXCEPTION
35180: WHEN OTHERS THEN
35181: NULL;
35357: ,config_inst_item_id
35358: INTO p_config_rec.config_inst_hdr_id
35359: ,p_config_rec.config_inst_rev_num
35360: ,p_config_rec.config_inst_item_id
35361: FROM csi_item_instances
35362: WHERE instance_id=p_instance_id;
35363: EXCEPTION
35364: WHEN NO_DATA_FOUND THEN
35365: NULL;