[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;
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 '||
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
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;
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,
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
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:
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
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
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
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;