[Home] [Help]
PACKAGE BODY: APPS.OKL_CURE_REQUEST_PVT
Source
1 PACKAGE BODY OKL_CURE_REQUEST_PVT AS
2 /* $Header: OKLRREQB.pls 115.7 2003/10/10 19:00:27 jsanju noship $ */
3
4 PROCEDURE SEND_CURE_REQUEST
5 (
6 errbuf OUT NOCOPY VARCHAR2,
7 retcode OUT NOCOPY NUMBER,
8 p_vendor_number IN NUMBER ,
9 p_report_number IN VARCHAR2 ,
10 p_report_date IN DATE
11 )
12 AS
13 l_msg_count NUMBER ;
14 l_msg_data VARCHAR2(2000);
15 l_message VARCHAR2(2000);
16 l_api_version CONSTANT NUMBER := 1;
17 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
18 l_api_name CONSTANT VARCHAR2(30) := 'Vendor Cure Request';
19
20 l_rows_processed NUMBER := 0;
21 l_rows_failed NUMBER := 0;
22 l_vendor_notified NUMBER := 0;
23 l_vendor_not_notified NUMBER := 0;
24
25 l_bind_var JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
26 l_bind_val JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
27 l_bind_var_type JTF_FM_REQUEST_GRP.G_VARCHAR_TBL_TYPE;
28
29 l_vendor_id HZ_PARTIES.PARTY_ID%TYPE;
30 l_email HZ_CONTACT_POINTS.EMAIL_ADDRESS%TYPE;
31 l_subject VARCHAR2(2000);
32 l_content_id JTF_AMV_ITEMS_B.ITEM_ID%TYPE;
33 l_from VARCHAR2(2000);
34 l_agent_id NUMBER;
35 l_request_id NUMBER;
36 l_request_date okl_cure_reports.report_date%TYPE := TRUNC(SYSDATE);
37 l_request_number okl_cure_reports.report_number%TYPE;
38
39 l_organization_passed VARCHAR2(1) := OKL_API.G_TRUE;
40 l_set_of_books_passed VARCHAR2(1) := OKL_API.G_TRUE;
41 l_vendor_passed VARCHAR2(1) := OKL_API.G_TRUE;
42 l_contract_passed VARCHAR2(1) := OKL_API.G_TRUE;
43
44 l_current_date DATE;
45 l_msg_index_out NUMBER :=0;
46
47 subtype crtv_rec_type is OKL_cure_reports_pub.crtv_rec_type;
48 l_crtv_rec crtv_rec_type;
49 lx_crtv_rec crtv_rec_type;
50
51 -- Get Report Data for Fulfilment
52 CURSOR report_csr( l_report_date IN DATE
53 ,l_request_number IN VARCHAR2
54 ,l_vendor_id IN NUMBER)
55 IS
56 SELECT crt.vendor_id
57 ,crt.report_date
58 ,pvs.email_address
59 ,crt.report_number
60 ,crt.cure_report_id
61 ,crt.object_version_number
62 ,crt.report_type
63 FROM OKL_cure_reports crt
64 ,po_vendor_sites_all pvs
65 WHERE trunc(crt.report_date) = NVL(l_report_date , trunc(crt.report_date))
66 AND crt.report_number = NVL(l_request_number, crt.report_number)
67 AND crt.vendor_id = NVL(l_vendor_id,crt.vendor_id)
68 AND crt.vendor_site_id = pvs.vendor_site_id
69 AND crt.approval_status = 'APPROVED';
70
71 Cursor c_get_content_id ( p_process_code IN VARCHAR2) IS
72 SELECT jtf_amv_item_id, email_subject_line
73 FROM okl_cs_process_tmplts_uv
74 WHERE PTM_CODE=p_process_code;
75
76 l_ptm_code VARCHAR2(100);
77
78 BEGIN
79
80 Fnd_File.PUT_LINE(Fnd_File.LOG,
81 'Start of process');
82
83 l_current_date := p_report_date;
84 l_request_number := p_report_number;
85 l_vendor_id := p_vendor_number;
86
87 /* l_subject := fnd_profile.value('OKL_CURE_REQUEST_SUBJECT');
88 l_content_id := to_number(fnd_profile.value('OKL_CURE_REQUEST_TEMPLATE'));
89 */
90
91
92 l_agent_id:= to_number(fnd_profile.value('OKL_FULFILLMENT_USER'));
93 l_from := fnd_profile.value('OKL_EMAIL_IDENTITY');
94
95 Fnd_File.PUT_LINE(Fnd_File.LOG,l_current_date|| 'Request ' ||
96 l_request_number|| 'Vendor '||
97 l_vendor_id );
98
99 -- open cursor for requests to be processed
100 FOR i IN report_csr(l_current_date, l_request_number, l_vendor_id)
101 LOOP
102 l_email := i.email_address;
103 IF (l_email = OKL_API.G_MISS_CHAR OR l_email IS NULL) THEN
104 RAISE G_MISSING_EMAIL_ID;
105 END IF;
106 Fnd_File.PUT_LINE(Fnd_File.LOG,'Email is '||l_email);
107
108 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
109 l_bind_var(1) := 'p_report_id';
110 l_bind_val(1) := i.cure_report_id;
111 l_bind_var_type(1) := 'NUMBER';
112
113
114 --10/07/03 jsanju
115 -- if cure only, ptm_code ='COCURE'
116 -- else if repurchase only ptm_code ='COCURP'
117 --else if both cure and repurchase then ptm_code = 'COCARP'
118
119 IF i.report_type ='BOTH' THEN
120 l_ptm_code :='COCARP';
121 ELSIF i.report_type ='CURE' THEN
122 l_ptm_code :='COCURE';
123 ELSE l_ptm_code := 'COCURP';
124
125 END IF;
126
127 OPEN c_get_content_id (l_ptm_code);
128 FETCH c_get_content_id INTO l_content_id ,l_subject;
129 CLOSE c_get_content_id;
130
131 Fnd_File.PUT_LINE(Fnd_File.LOG,'Process Code '
132 ||l_ptm_code ||' content_id '|| l_content_id);
133
134 IF l_content_id is Null THEN
135 RAISE G_MISSING_TEMPLATE;
136 END IF;
137
138 --call fulfillment
139 OKL_FULFILLMENT_PUB.create_fulfillment
140 (
141 p_api_version => l_api_version,
142 p_init_msg_list => okl_api.G_TRUE,
143 p_agent_id => l_agent_id,
144 p_content_id => l_content_id,
145 p_from => l_from,
146 p_subject => l_subject,
147 p_email => l_email,
148 p_bind_var => l_bind_var,
149 p_bind_val => l_bind_val,
150 p_bind_var_type => l_bind_var_type,
151 x_request_id => l_request_id,
152 x_return_status => l_return_status,
153 x_msg_count => l_msg_count,
154 x_msg_data => l_msg_data
155 );
156 -- If the return status is error write to log and proceed
157 -- with the rest of the requests
158 IF (l_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
159 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,
160 'Cure Request could not be sent for request = '
161 || i.report_number);
162 IF l_msg_count IS NULL THEN
163 l_msg_count := 2;
164 END IF;
165
166 FOR i in 1..l_msg_count LOOP
167 fnd_msg_pub.get (
168 p_encoded => 'F',
169 p_data => l_msg_data,
170 p_msg_index_out => l_msg_index_out);
171 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, l_msg_data);
172 END LOOP;
173 END IF;
174
175 Fnd_File.PUT_LINE(Fnd_File.LOG,
176 'After calling okl_fulfill api and return status is'
177 || l_return_status);
178
179
180 -- Return = SUCCESS - update report status to
181 -- SENT_TO_VENDOR
182 IF (l_return_status = okl_api.G_RET_STS_SUCCESS) THEN
183
184 l_crtv_rec.approval_status :='SENT_TO_VENDOR';
185 l_crtv_rec.cure_report_id :=i.cure_report_id;
186 l_crtv_rec.object_version_number := i.object_version_number;
187
188 OKL_cure_reports_pub.update_cure_reports(
189 p_api_version => l_api_version
190 ,p_init_msg_list => okl_api.G_TRUE
191 ,x_return_status => l_return_status
192 ,x_msg_count => l_msg_count
193 ,x_msg_data => l_msg_data
194 ,p_crtv_rec => l_crtv_rec
195 ,x_crtv_rec => lx_crtv_rec);
196 -- If the return status is error write to log and proceed
197 -- with the rest of the requests
198 IF (l_return_status <> okl_api.G_RET_STS_SUCCESS) THEN
199 Fnd_File.PUT_LINE(Fnd_File.OUTPUT,
200 'Cure Request Status could not be updated for request = '
201 || i.report_number);
202 IF l_msg_count IS NULL THEN
203 l_msg_count := 2;
204 END IF;
205 FOR i in 1..l_msg_count LOOP
206 fnd_msg_pub.get (
207 p_encoded => 'F',
208 p_data => l_msg_data,
209 p_msg_index_out => l_msg_index_out);
210 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, l_msg_data);
211 END LOOP;
212 END IF; --return_status of update cure reports
213 Fnd_File.PUT_LINE(Fnd_File.LOG,
214 'After calling update cure reports and return status is'
215 || l_return_status);
216
217 END IF; --return of fulfil api
218 END IF; --return of bind variable
219
220 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'Cure Request = ' || i.report_number);
221 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'Request sent for Vendor = ' || to_char(l_vendor_id));
222 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'Request sent on Date = ' || SYSDATE);
223 l_return_status :=OKL_API.G_RET_STS_SUCCESS;
224
225 END LOOP;
226
227 retcode := 0;
228
229 EXCEPTION
230 WHEN G_MISSING_EMAIL_ID THEN
231 IF report_csr%ISOPEN THEN
232 CLOSE report_csr;
233 END IF;
234
235 errbuf := 'G_MISSING_EMAIL_ID';
236 retcode := 1;
237 FND_MESSAGE.SET_NAME('OKL', 'OKL_CO_MISSING_EMAIL_ID');
238 --dbms_output.put_line(FND_MESSAGE.GET);
239 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, FND_MESSAGE.GET );
240
241 WHEN G_MISSING_TEMPLATE THEN
242 IF report_csr%ISOPEN THEN
243 CLOSE report_csr;
244 END IF;
245
246 errbuf := 'G_MISSING_TEMPLATE';
247 retcode := 1;
248 FND_MESSAGE.SET_NAME('OKL', 'OKL_CO_MISSING_FUL_TEMPLATE');
249 --dbms_output.put_line(FND_MESSAGE.GET);
250 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, FND_MESSAGE.GET );
251
252 WHEN OTHERS THEN
253 IF report_csr%ISOPEN THEN
254 CLOSE report_csr;
255 END IF;
256
257 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS)IN SEND_CURE_REQUEST => '||SQLERRM);
258 retcode :=2;
259 errbuf :=SQLERRM;
260
261 END SEND_CURE_REQUEST;
262
263 END OKL_CURE_REQUEST_PVT;
264