[Home] [Help]
PACKAGE BODY: APPS.QA_ERES_SHIPPING
Source
1 PACKAGE BODY qa_eres_shipping AS
2 /* $Header: qaerwshb.pls 115.2 2004/07/23 01:35:35 isivakum noship $ */
3
4
5
6 PROCEDURE wrapper (ERRBUF OUT NOCOPY VARCHAR2,
7 RETCODE OUT NOCOPY NUMBER,
8 ARGUMENT1 IN VARCHAR2,
9 ARGUMENT2 IN VARCHAR2) IS
10
11 from_pickup_dt DATE;
12 to_pickup_dt DATE;
13 out_status VARCHAR2(50) := FND_API.G_RET_STS_SUCCESS;
14 edr_profile VARCHAR2(20) := 'N';
15 BEGIN
16
17 -- ARGUMENT1 --> From Date
18 -- ARGUMENT2 --> To Date
19 fnd_file.put_line(fnd_file.log, 'qa_eres_shipping.wrapper entered');
20
21 edr_profile := FND_PROFILE.VALUE('EDR_ERES_ENABLED');
22 fnd_file.put_line (fnd_file.log, 'ERES Profile is set to:'||edr_profile);
23
24 IF (edr_profile is not null AND edr_profile = 'Y' and
25 ARGUMENT1 IS NOT NULL AND ARGUMENT2 IS NOT NULL) THEN
26
27 from_pickup_dt := fnd_date.canonical_to_date(argument1);
28 to_pickup_dt := fnd_date.canonical_to_date(argument2);
29
30 fnd_file.put_line (fnd_file.log, 'dates fetched');
31
32 --BUG 3352407 - Date validation needed
33 IF (from_pickup_dt >= to_pickup_dt) THEN
34 fnd_file.put_line (fnd_file.log, 'ERROR: From date must be less than To date');
35 RETCODE := 2; -- return code for error
36 ERRBUF := 'ERROR: From date must be less than To date';
37 RETURN;
38 END IF;
39
40 IF ( to_pickup_dt - from_pickup_dt > 5) THEN
41 fnd_file.put_line (fnd_file.log, 'ERROR: Date Range should be within 5 days');
42 RETCODE := 2; -- return code for error
43 ERRBUF := 'ERROR: Date Range should be within 5 days';
44 RETURN;
45 END IF;
46
47
48 --default value for out_status is SUCCESS
49 delivery_erecord(from_pickup_dt, to_pickup_dt, out_status);
50 END IF;
51
52 if (out_status = FND_API.G_RET_STS_ERROR
53 or out_status = FND_API.G_RET_STS_UNEXP_ERROR)
54 then
55 RETCODE := 1;
56 ERRBUF := 'Warning: Some eRecords may have resulted in error';
57 else
58 RETCODE := 0;
59 ERRBUF := '';
60 end if;
61 fnd_file.put_line(fnd_file.log, 'qa_eres_shipping.wrapper exiting');
62 END wrapper;
63
64 -----------------------------------------------------------------------------
65
66 PROCEDURE delivery_erecord(p_from_date IN DATE,
67 p_to_date IN DATE,
68 x_status OUT NOCOPY VARCHAR2) IS
69
70
71 --BUG 3763874 : shipment direction needs to be taken into account
72 -- we only support outbound sales order shipments direction 'O'
73
74 CURSOR wsh_delivery_cur(c_from_date DATE, c_to_date DATE,
75 c_evt_name VARCHAR2) IS
76 SELECT wnd.delivery_id, wnd.name
77 FROM wsh_new_deliveries wnd
78 WHERE wnd.initial_pickup_date >= c_from_date
79 AND wnd.initial_pickup_date <= c_to_date
80 AND (wnd.status_code = 'IT' OR wnd.status_code = 'CL')
81 AND nvl(wnd.shipment_direction, 'O') in ('O') --BUG 3763874
82 AND NOT EXISTS
83 (SELECT 1
84 FROM EDR_PSIG_DOCUMENTS epd
85 WHERE epd.event_name = c_evt_name
86 AND epd.event_key = wnd.delivery_id
87 AND epd.psig_status = 'COMPLETE');
88 --following indexes exist on wsh_new_deliveries table
89 --WSH_NEW_DELIVERIES_N1 - on wnd.status_code column
90 --WSH_NEW_DELIVERIES_N7 - on initial_pickup_date column
91 --following indexes exist on EDR table
92 --EDR_PSIG_DOCUMENTS_N1 - on event_name and event_key
93
94 out_erecord_id NUMBER;
95 out_status VARCHAR2(50) := FND_API.G_RET_STS_SUCCESS;
96
97 BEGIN
98
99 FOR wsh_rec IN wsh_delivery_cur (p_from_date, p_to_date, g_event_name_const)
100 LOOP
101 --get the delivery id and call the edr raise event
102 raise_delivery_event(wsh_rec.delivery_id, wsh_rec.name,
103 out_erecord_id, out_status);
104
105 --even if one of the events gives an error, mark it so
106 --Warning can be raised for Concurrent program completion
107 --if everything fine, default value will be success
108 if (out_status = FND_API.G_RET_STS_ERROR
109 or out_status = FND_API.G_RET_STS_UNEXP_ERROR)
110 then
111 x_status := out_status;
112 end if;
113 END LOOP;
114
115 NULL;
116
117 END delivery_erecord;
118 -------------------------------------------------------------------
119
120 PROCEDURE raise_delivery_event(p_delivery_id IN NUMBER,
121 p_delivery_name IN VARCHAR2,
122 p_erecord_id OUT NOCOPY NUMBER,
123 x_status OUT NOCOPY VARCHAR2)
124 IS
125
126 l_child_erecords qa_edr_standard.ERECORD_ID_TBL_TYPE;
127 l_event qa_edr_standard.ERES_EVENT_REC_TYPE;
128
129
130 o_return_status VARCHAR2(50);
131 o_msg_count NUMBER;
132 o_msg_data VARCHAR2(2000);
133 o_erecord_id NUMBER;
134
135 --BUG 3763874 : along with the shipment direction bug
136 --a minor improvement was made to print out the error message if any
137 --in the LOG file output - this is mainly to aid any trouble-shooting
138
139 o_msg_index NUMBER; --BUG 3763874
140 f_msg_data VARCHAR2(2000); --BUG 3763874
141
142 BEGIN
143
144 l_event.param_name_1 := 'DEFERRED';
145 l_event.param_value_1 := 'Y';
146
147 l_event.param_name_2 := 'POST_OPERATION_API';
148 l_event.param_value_2 := 'NONE';
149
150 l_event.param_name_3 := 'PSIG_USER_KEY_LABEL';
151 l_event.param_value_3 := qa_eres_util.get_mfg_lookups_meaning
152 ('QA_ERES_KEY_LABEL',60);
153
154 l_event.param_name_4 := 'PSIG_USER_KEY_VALUE';
155 l_event.param_value_4 := p_delivery_name;
156
157 l_event.param_name_5 := 'PSIG_TRANSACTION_AUDIT_ID';
158 l_event.param_value_5 := '-1';
159
160 l_event.param_name_6 := '#WF_SOURCE_APPLICATION_TYPE';
161 l_event.param_value_6 := 'DB';
162
163 l_event.param_name_7 := '#WF_SIGN_REQUESTER';
164 l_event.param_value_7 := FND_GLOBAL.USER_NAME;
165
166 l_event.event_name := g_event_name_const;
167 l_event.event_key := p_delivery_id;
168 --l_event.payload := l_payload;
169
170 --BUG 3763874: p_init_msg_list should be TRUE
171 --to clear the error buffer for each delivery erecord call
172
173 QA_EDR_STANDARD.RAISE_ERES_EVENT
174 (
175 p_api_version => 1.0,
176 p_init_msg_list => FND_API.G_TRUE, --BUG 3763874
177 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
178 x_return_status => o_return_status,
179 x_msg_count => o_msg_count,
180 x_msg_data => o_msg_data,
181 p_child_erecords => l_child_erecords,
182 x_event => l_event);
183 commit; -- this commit is needed becos of EDR bug. Remove later.
184
185 fnd_file.put_line(fnd_file.log, 'Delivery id:'||p_delivery_id || '('
186 ||p_delivery_name|| ')'
187 || ' eRec status: ' || l_event.event_status
188 || ' eRec ID:' || l_event.erecord_id);
189
190 --BUG 3763874: if any error messages, print them to the LOG
191 --minor improvement done as part of fixing above bug
192 if ( o_msg_count > 1) then
193 FOR m IN 1..o_msg_count LOOP
194 fnd_msg_pub.get ( p_data => f_msg_data,
195 p_encoded => 'F',
196 p_msg_index_out => o_msg_index);
197
198 fnd_file.put_line (fnd_file.log, 'MSG'|| m ||' :'|| f_msg_data);
199 END LOOP;
200 end if; --END BUG 3763874
201
202 o_erecord_id := l_event.erecord_id;
203 p_erecord_id := l_event.erecord_id;
204 x_status := o_return_status;
205
206 IF (o_erecord_id IS NOT NULL AND o_erecord_id > 0)
207 THEN
208 QA_EDR_STANDARD.SEND_ACKN
209 ( p_api_version => 1.0
210 , p_init_msg_list => FND_API.G_TRUE
211 , x_return_status => o_return_status
212 , x_msg_count => o_msg_count
213 , x_msg_data => o_msg_data
214 , p_event_name => l_event.event_name
215 , p_event_key => l_event.event_key
216 , p_erecord_id => o_erecord_id
217 , p_trans_status => 'SUCCESS'
218 , p_ackn_by => 'Shipment Delivery eRecord Program'
219 , p_ackn_note => ''
220 , p_autonomous_commit => FND_API.G_TRUE);
221 --english ok here for p_ackn_by, since internal
222 --program name
223 END IF;
224 END raise_delivery_event;
225
226 END qa_eres_shipping;
227