[Home] [Help]
PACKAGE BODY: APPS.CSE_WFM_TRX_GRP_PVT
Source
1 PACKAGE BODY cse_wfm_trx_grp_pvt AS
2 /* $Header: CSEWBWRB.pls 120.1 2006/05/31 20:52:33 brmanesh noship $ */
3
4 PROCEDURE wfm_transactions(
5 p_api_version IN NUMBER,
6 p_commit IN VARCHAR2,
7 p_validation_level IN NUMBER,
8 p_init_msg_list IN VARCHAR2,
9 p_transaction_type IN VARCHAR2,
10 p_wfm_values_tbl IN OUT NOCOPY cse_datastructures_pub.wfm_trx_values_tbl,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2)
14 IS
15
16 g_pkg_name VARCHAR2(30) := 'CSE_WFMSG_TRX_GRP_PVT';
17 l_wfm_values_tbl cse_datastructures_pub.WFM_TRX_VALUES_TBL;
18 l_msg_count NUMBER;
19 l_msg_data VARCHAR2(2000);
20 l_error_msg VARCHAR2(2000);
21 l_sql_error VARCHAR2(2000);
22 l_api_name VARCHAR2(100) := 'wfm_transactions';
23 j PLS_INTEGER;
24 l_file VARCHAR2(500);
25 l_sysdate DATE := SYSDATE;
26 l_tbl_count NUMBER := 0;
27 l_fnd_success VARCHAR2(1);
28 l_fnd_unexpected VARCHAR2(1);
29 l_return_status VARCHAR2(1);
30 l_txn_type VARCHAR2(30);
31 e_error EXCEPTION;
32 TYPE return_Status_tbl_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
33 TYPE error_message_tbl_type IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
34 l_return_status_tbl return_Status_tbl_type;
35 l_error_message_tbl error_message_tbl_type;
36
37 BEGIN
38
39 l_fnd_success := FND_API.G_RET_STS_SUCCESS;
40 l_fnd_unexpected := FND_API.G_RET_STS_UNEXP_ERROR;
41
42 IF p_transaction_type = 'PROJECT_ITEM_INSTALLED' THEN
43 l_txn_type :='PROJ_ITEM_INSTALLED';
44 ELSIF p_transaction_type = 'PROJECT_ITEM_UNINSTALLED' THEN
45 l_txn_type :='PROJ_ITEM_UNINSTALLED';
46 ELSIF p_transaction_type = 'PROJECT_ITEM_IN_SERVICE' THEN
47 l_txn_type :='PROJ_ITEM_IN_SERVICE';
48 ELSE
49 l_txn_type := p_transaction_type;
50 END IF;
51
52 x_return_status := l_fnd_success;
53 x_msg_data := NULL;
54
55 j := 1;
56
57 l_tbl_count := 0;
58 l_tbl_count := p_wfm_values_tbl.count;
59
60 IF NOT l_tbl_count = 0 THEN
61 l_wfm_values_tbl := p_wfm_values_tbl;
62 BEGIN
63 FOR j in l_wfm_values_tbl.FIRST .. l_wfm_values_tbl.LAST LOOP
64 IF l_wfm_values_tbl.EXISTS(j) THEN
65
66 cse_wfm_proc_logic.processing_logic(
67 p_item_id => l_wfm_values_tbl(j).inventory_item_id,
68 p_revision => l_wfm_values_tbl(j).inventory_revision,
69 p_lot_number => l_wfm_values_tbl(j).lot_number,
70 p_serial_number => l_wfm_values_tbl(j).serial_number,
71 p_quantity => l_wfm_values_tbl(j).quantity,
72 p_project_id => l_wfm_values_tbl(j).project_id,
73 p_task_id => l_wfm_values_tbl(j).task_id,
74 p_from_network_loc_id => l_wfm_values_tbl(j).from_network_location_id,
75 p_to_network_loc_id => l_wfm_values_tbl(j).to_network_location_id,
76 p_from_party_site_id => l_wfm_values_tbl(j).from_party_site_id,
77 p_to_party_site_id => l_wfm_values_tbl(j).to_party_site_id,
78 p_work_order_number => l_wfm_values_tbl(j).work_order_number,
79 p_transaction_date => nvl(l_wfm_values_tbl(j).transaction_date,sysdate),
80 p_effective_date => l_wfm_values_tbl(j).effective_date,
81 p_transacted_by => nvl(l_wfm_values_tbl(j).transacted_by,-1),
82 p_message_id => null,
83 p_transaction_type => l_txn_type,
84 x_return_status => l_return_status,
85 x_error_message => l_msg_data);
86
87 IF NOT l_return_status = FND_API.g_ret_sts_success THEN
88 RAISE e_error;
89 END IF;
90 END IF;
91 END LOOP; -- End of Main FOR LOOP
92 EXCEPTION
93 WHEN e_error THEN
94 x_return_status := fnd_api.g_ret_sts_error;
95 x_msg_data := l_msg_data;
96 x_msg_count := 1;
97 l_return_Status_tbl(j) := fnd_api.g_ret_sts_error;
98 l_error_message_tbl(j) := l_msg_data;
99
100 END;
101
102 IF NOT l_return_status_tbl.COUNT = 0 THEN
103 FOR j in l_return_status_tbl.FIRST .. l_return_status_tbl.LAST
104 LOOP
105 IF l_return_status_tbl.EXISTS(j) THEN
106 p_wfm_values_tbl(j).return_status := l_return_status_tbl(j);
107 END IF;
108 IF l_error_message_tbl.EXISTS(j) THEN
109 p_wfm_values_tbl(j).error_message := l_error_message_tbl(j);
110 END IF;
111 END LOOP;
112 END IF;
113 END IF;
114
115 EXCEPTION
116 WHEN others THEN
117 l_sql_error := SQLERRM;
118 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
119 fnd_message.set_token('API_NAME',l_api_name);
120 fnd_message.set_token('SQL_ERROR',SQLERRM);
121 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
122 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME, l_api_name );
123 END IF;
124 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count, p_data => x_msg_data);
125 x_msg_data := fnd_message.get;
126 x_return_status := l_fnd_unexpected;
127
128 END wfm_transactions;
129
130 END cse_wfm_trx_grp_pvt;