[Home] [Help]
PACKAGE BODY: APPS.FLM_KANBAN_MASSLOAD
Source
1 PACKAGE BODY FLM_KANBAN_MASSLOAD AS
2 /* $Header: FLMKBNMB.pls 120.2 2011/04/27 00:51:30 atjen noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(20) := 'FLM_KANBAN_MASSLOAD';
5
6 g_pull_seq_tbl pull_seq_tbl_type;
7 g_supplier_tbl kanban_supp_tbl_type;
8 g_kanban_card_tbl card_int_tbl_type;
9
10
11 ------------------------------------------------------------------
12 -- procedure to read pull seq interface, supplier interface tables
13 ------------------------------------------------------------------
14 PROCEDURE read_pull_sequence_interface
15 ( p_group_id IN NUMBER,
16 x_ret_status OUT NOCOPY NUMBER,
17 x_err_msg OUT NOCOPY VARCHAR2
18 )
19 IS
20
21 CURSOR c_ps_rows IS
22 SELECT *
23 FROM flm_ekb_pull_seq_interface
24 WHERE group_id = p_group_id
25 AND process_status = flm_kanban_massload.running
26 ORDER BY interface_id;
27
28 CURSOR c_supplier_rows IS
29 SELECT ksi.*
30 FROM flm_ekb_pull_seq_interface psi,
31 flm_ekb_supplier_interface ksi
32 WHERE psi.interface_id = ksi.parent_interface_id
33 AND psi.process_status = flm_kanban_massload.running
34 AND psi.group_id = p_group_id
35 ORDER BY ksi.parent_interface_id;
36
37 BEGIN
38
39 --set process_status to running..
40 UPDATE flm_ekb_pull_seq_interface
41 SET process_status = flm_kanban_massload.running,
42 request_id = FND_GLOBAL.CONC_REQUEST_ID,
43 program_application_id = FND_GLOBAL.PROG_APPL_ID,
44 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
45 program_update_date = SYSDATE
46 WHERE group_id = p_group_id
47 AND process_status = flm_kanban_massload.pending;
48
49 --load the pull seq interface records to plsql table
50 OPEN c_ps_rows;
51 FETCH c_ps_rows BULK COLLECT INTO g_pull_seq_tbl;
52 CLOSE c_ps_rows;
53
54 -- read kanban supplier interface table
55 OPEN c_supplier_rows;
56 FETCH c_supplier_rows BULK COLLECT INTO g_supplier_tbl;
57 CLOSE c_supplier_rows;
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 x_ret_status := FND_API.G_RET_STS_ERROR;
62 fnd_message.set_name('FLM', 'FLM_INTERFACE_ERR');
63 x_err_msg := fnd_message.get;
64
65 END read_pull_sequence_interface;
66
67
68 ----------------------------------------
69 -- Define read_cards_interface procedure
70 ----------------------------------------
71 PROCEDURE read_cards_interface
72 ( p_group_id IN NUMBER,
73 x_ret_status OUT NOCOPY NUMBER,
74 x_err_msg OUT NOCOPY VARCHAR2
75 )
76 IS
77
78 CURSOR c_card_rows IS
79 SELECT *
80 FROM flm_ekb_cards_interface
81 WHERE group_id = p_group_id
82 AND process_status = flm_kanban_massload.running
83 ORDER BY interface_id;
84
85 BEGIN
86
87 --set process_status to running..
88 UPDATE flm_ekb_cards_interface
89 SET process_status = flm_kanban_massload.running,
90 request_id = FND_GLOBAL.CONC_REQUEST_ID,
91 program_application_id = FND_GLOBAL.PROG_APPL_ID,
92 program_id = FND_GLOBAL.CONC_PROGRAM_ID,
93 program_update_date = SYSDATE
94 WHERE group_id = p_group_id
95 AND process_status = flm_kanban_massload.pending;
96
97 --load the Kanban Cards interface records to plsql table
98 OPEN c_card_rows;
99 FETCH c_card_rows BULK COLLECT INTO g_kanban_card_tbl;
100 CLOSE c_card_rows;
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 x_ret_status := FND_API.G_RET_STS_ERROR;
105 fnd_message.set_name('FLM', 'FLM_INTERFACE_ERR');
106 x_err_msg := fnd_message.get;
107
108 END read_cards_interface;
109
110 -- Main procedure called by the kanaban mass register concurrent program
111 PROCEDURE load_kanban
112 ( retcode OUT NOCOPY NUMBER,
113 errbuf OUT NOCOPY VARCHAR2,
114 p_group_id IN NUMBER
115 )
116 IS
117
118 l_ret_status VARCHAR2(1);
119 l_error_msg VARCHAR2(4000);
120 l_conc_status BOOLEAN;
121
122 BEGIN
123
124 --read pull seq interface records
125 read_pull_sequence_interface(p_group_id => p_group_id,
126 x_ret_status => l_ret_status,
127 x_err_msg => l_error_msg);
128
129 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
130 retcode := 2;
131 fnd_message.set_name('FLM','FLM_PULLSEQ_INTF_ERR');
132 errbuf := fnd_message.get;
133 fnd_file.put_line(fnd_file.log, errbuf);
134 l_conc_status := fnd_concurrent.set_completion_status('ERROR', errbuf);
135 RETURN;
136 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
138 END IF;
139
140 -- read Kanban cards interface records
141 read_cards_interface(p_group_id => p_group_id,
142 x_ret_status => l_ret_status,
143 x_err_msg => l_error_msg);
144
145 IF l_ret_status = FND_API.G_RET_STS_ERROR THEN
146 retcode := 2;
147 fnd_message.set_name('FLM','FLM_CARDS_INTF_ERR');
148 errbuf := fnd_message.get;
149 fnd_file.put_line(fnd_file.log, errbuf);
150 l_conc_status := fnd_concurrent.set_completion_status('ERROR', errbuf);
151 RETURN;
152 ELSIF l_ret_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155
156 -- process pull seq and kanban cards
157 FLM_KANBAN_PUB.process_kanban(p_pull_sequence_tbl => g_pull_seq_tbl,
158 p_supplier_tbl => g_supplier_tbl,
159 p_kanban_card_tbl => g_kanban_card_tbl,
160 x_ret_status => l_ret_status);
161
162 -- update pull seq interface status
163 FORALL i IN g_pull_seq_tbl.FIRST..g_pull_seq_tbl.LAST
164 UPDATE flm_ekb_pull_seq_interface
165 SET pull_sequence_id = g_pull_seq_tbl(i).pull_sequence_id,
166 process_status = g_pull_seq_tbl(i).process_status,
167 error_text = g_pull_seq_tbl(i).error_text
168 WHERE interface_id = g_pull_seq_tbl(i).interface_id;
169
170 -- update cards interface status
171 FORALL i IN g_kanban_card_tbl.FIRST..g_kanban_card_tbl.LAST
172 UPDATE flm_ekb_cards_interface
173 SET kanban_card_id = g_kanban_card_tbl(i).kanban_card_id,
174 process_status = g_kanban_card_tbl(i).process_status,
175 error_text = g_kanban_card_tbl(i).error_text
176 WHERE interface_id = g_kanban_card_tbl(i).interface_id;
177
178 IF l_ret_status = FND_API.G_RET_STS_SUCCESS THEN
179 retcode := 0;
180 fnd_message.set_name('FLM','FLM_INTERFACE_SUCCESS');
181 errbuf := fnd_message.get;
182 fnd_file.put_line(fnd_file.log, errbuf);
183 l_conc_status := fnd_concurrent.set_completion_status('NORMAL', errbuf);
184 ELSE
185 retcode := 1;
186 fnd_message.set_name('FLM','FLM_INTERFACE_ROW_ERR');
187 errbuf := fnd_message.get;
188 fnd_file.put_line(fnd_file.log, errbuf);
189 l_conc_status := fnd_concurrent.set_completion_status('WARNING', errbuf);
190 END IF;
191
192 EXCEPTION
193 WHEN OTHERS THEN
194 retcode := 2;
195 fnd_message.set_name('FLM','FLM_INTERFACE_ERR');
196 errbuf := fnd_message.get;
197 fnd_file.put_line(fnd_file.log, errbuf);
198 fnd_file.put_line(fnd_file.log, SQLERRM);
199 l_conc_status := fnd_concurrent.set_completion_status('ERROR', errbuf);
200
201 END load_kanban;
202
203 END FLM_KANBAN_MASSLOAD;