[Home] [Help]
PACKAGE BODY: APPS.RCV_INSERT_FROM_INL
Source
1 PACKAGE BODY RCV_INSERT_FROM_INL AS
2 /* $Header: RCVINSTB.pls 120.3.12020000.2 2013/02/26 09:30:27 yilali ship $ */
3
4 PROCEDURE insert_rcv_tables (p_int_rec IN rti_rec_table,
5 p_ship_header_id IN NUMBER)
6 IS
7
8 l_user_defined_ship_num_code VARCHAR2(25);
9 l_next_ship_num NUMBER;
10 l_ship_num NUMBER;
11 l_ship_type_id NUMBER;
12 l_legal_entity_id NUMBER;
13 l_taxation_country VARCHAR2(2);
14 l_ship_header_int_id NUMBER;
15 l_group_id NUMBER;
16 l_party_id NUMBER;
17 l_party_site_id NUMBER;
18 l_ship_line_type_id NUMBER;
19 l_trx_business_category VARCHAR2(240);
20 l_line_intended_use VARCHAR2(240);
21 l_product_fisc_classification VARCHAR2(240);
22 l_product_category VARCHAR2(240);
23 l_product_type VARCHAR2(240);
24 l_user_defined_fisc_class VARCHAR2(30);
25 l_output_tax_classf_code VARCHAR2(50);
26 l_ship_lines_int_id NUMBER;
27 l_org_id NUMBER;
28 st NUMBER := 1;
29
30 l_vendor_id NUMBER;
31 l_vendor_site_id NUMBER;
32 l_ship_to_org_id NUMBER;
33 l_receipt_num VARCHAR2(500);
34 l_req_id NUMBER := 0;
35 l_header_interface_id NUMBER;
36 l_notice_creation_date DATE;
37 l_transaction_type VARCHAR2(50);
38 l_processing_status_code VARCHAR2(50);
39 l_receipt_source_code VARCHAR2(50);
40 l_validation_flag VARCHAR2(50);
41
42 l_po_header_id NUMBER;
43 l_po_line_id NUMBER;
44 l_po_release_id NUMBER;
45
46 l_interface_source_code VARCHAR2(50);
47 l_processing_mode_code VARCHAR2(50);
48 l_transaction_status_code VARCHAR2(50);
49 l_interface_transaction_id NUMBER;
50
51 cursor c_rcv_header(l_ship_header_id IN NUMBER, l_ship_line_group_id IN NUMBER)
52 IS
53 SELECT sh.organization_id,
54 -- Bug #7661019
55 sh.ship_num||'.'||TO_CHAR(slg.ship_line_group_num) ship_num,
56 sh.ship_header_id,
57 ship_date,
58 slg.ship_line_group_id,
59 slg.party_id,
60 slg.party_site_id,
61 sh.org_id,
62 slg.src_type_code
63 FROM inl_ship_headers sh,
64 inl_ship_line_groups slg
65 WHERE sh.ship_header_id = slg.ship_header_id
66 AND sh.ship_header_id = l_ship_header_id
67 AND slg.ship_line_group_id = l_ship_line_group_id
68 AND slg.src_type_code = 'PO';
69
70 l_rcv_header c_rcv_header%ROWTYPE;
71
72 currentLineGrpId NUMBER;
73 l_primary_uom VARCHAR2(25); /* Bug 8210608: Added to fetch unit_of_measure from uom_code */
74 l_secondary_uom VARCHAR2(25); -- Bug 8911750
75
76 BEGIN
77
78 asn_debug.put_line('Entering RCV_INSERT_FROM_INL.insert_rcv_tables' || to_char(sysdate,'DD-MON-YYYY HH:MI:SS'));
79 asn_debug.put_line ('count: '||p_int_rec.COUNT);
80
81 currentLineGrpId := -9999;
82 for i in 1..p_int_rec.COUNT loop
83
84
85 if (currentLineGrpId <> p_int_rec(i).ship_line_group_id) then
86
87 if (currentLineGrpId <> -9999) then -- It is not the first record
88
89 /*
90 update RCV_TRANSACTIONS_INTERFACE RTI
91 set PROCESSING_STATUS_CODE = 'PENDING'
92 where RTI.header_interface_id = ( select rhi.header_interface_id
93 from rcv_headers_interface rhi
94 where rhi.header_interface_id = rti.header_interface_id
95 and rhi.group_id = rti.group_id
96 and rhi.receipt_header_id = p_int_rec(i).shipment_header_id)
97 and RTI.PROCESSING_STATUS_CODE = 'INSERTING';
98
99 asn_debug.put_line ('no of rows updated: '|| SQL%ROWCOUNT);
100
101 update rcv_headers_interface
102 set receipt_header_id = NULL
103 where receipt_header_id = p_int_rec(i).shipment_header_id;
104
105 */
106
107 COMMIT;
108
109 -- launch RTP
110 -- bug 16274612 set OU_ID =NULL
111 l_req_id := fnd_request.submit_request('PO', 'RVCTP',null,null,false,'BATCH',l_group_id,NULL,NULL,
112 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
113 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
114 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
115 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
116 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
117 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
118 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
119 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
120 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
121 NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
122 NULL, NULL, NULL, NULL, NULL, NULL);
123
124 asn_debug.put_line ('request id: '||l_req_id);
125
126 if (l_req_id <= 0 or l_req_id is null) then
127 raise fnd_api.g_exc_unexpected_error;
128 end if;
129
130 -- null;
131
132 end if;
133
134 asn_debug.put_line ('ship line group id: '||p_int_rec(i).ship_line_group_id);
135
136 SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
137 INTO l_header_interface_id
138 FROM dual;
139
140 SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
141 INTO l_group_id
142 FROM dual;
143
144 l_notice_creation_date := SYSDATE;
145 l_transaction_type := 'NEW';
146 l_processing_status_code := 'PENDING';
147 l_receipt_source_code := 'VENDOR';
148 l_validation_flag := 'Y';
149
150 asn_debug.put_line ('header interface id: '||l_header_interface_id);
151 asn_debug.put_line ('group id: '||l_group_id);
152
153 /*
154 receipt_header_id is used to store the LCM shipment header id in order to populate the
155 next set of RTI columnss with the corresponding columns of the earlier set of RTIs.
156 */
157
158 asn_debug.put_line ('ship_date: '||l_rcv_header.ship_date);
159
160 open c_rcv_header(p_ship_header_id, p_int_rec(i).ship_line_group_id);
161 fetch c_rcv_header into l_rcv_header;
162
163 /*SELECT apv.vendor_id,
164 aps.vendor_site_id
165 INTO l_vendor_id,
166 l_vendor_site_id
167 FROM ap_supplier_sites aps,
168 ap_suppliers apv
169 WHERE aps.party_site_id = l_rcv_header.party_site_id
170 AND apv.party_id = l_rcv_header.party_id
171 AND aps.org_id = l_rcv_header.org_id;*/
172
173 -- Bug #8354404
174 SELECT ph.vendor_id,
175 ph.vendor_site_id
176 INTO l_vendor_id,
177 l_vendor_site_id
178 FROM po_headers ph,
179 po_line_locations pll
180 WHERE ph.po_header_id = pll.po_header_id
181 AND pll.line_location_id = p_int_rec(i).ship_line_source_id;
182
183
184 INSERT INTO RCV_HEADERS_INTERFACE
185 (header_interface_id,
186 group_id,
187 processing_status_code,
188 receipt_source_code,
189 transaction_type,
190 ship_to_organization_id,
191 notice_creation_date,
192 vendor_id,
193 vendor_site_id,
194 validation_flag,
195 shipped_date,
196 shipment_num,
197 asn_type,
198 last_update_date,
199 last_updated_by,
200 creation_date,
201 created_by,
202 last_update_login)
203 VALUES
204 (l_header_interface_id,
205 l_group_id,
206 l_processing_status_code,
207 l_receipt_source_code,
208 l_transaction_type,
209 l_rcv_header.organization_id,
210 l_notice_creation_date,
211 l_vendor_id,
212 l_vendor_site_id,
213 l_validation_flag,
214 l_rcv_header.ship_date,
215 l_rcv_header.ship_num,
216 'LCM',
217 sysdate,
218 fnd_global.user_id,
219 sysdate,
220 fnd_global.user_id,
221 fnd_global.login_id);
222
223 currentLineGrpId := p_int_rec(i).ship_line_group_id;
224
225 IF c_rcv_header%ISOPEN THEN
226 CLOSE c_rcv_header;
227 END IF;
228
229 asn_debug.put_line ('inserted header');
230
231 end if;
232
233 asn_debug.put_line ('ship line group ID: '||p_int_rec(i).ship_line_group_id);
234
235 l_interface_source_code := 'LCM';
236 l_transaction_type := 'SHIP';
237 l_processing_status_code := 'PENDING';
238 l_processing_mode_code := 'BATCH';
239 l_transaction_status_code := 'PENDING';
240 l_receipt_source_code := 'VENDOR';
241 l_validation_flag := 'Y';
242
243 SELECT pll.po_header_id, pll.po_line_id, pll.po_release_id
244 INTO l_po_header_id, l_po_line_id, l_po_release_id
245 FROM po_line_locations pll,
246 po_headers ph
247 WHERE ph.po_header_id = pll.po_header_id
248 AND pll.line_location_id = p_int_rec(i).ship_line_source_id;
249
250
251 /* Bug 8210608: Fetching unit_of_measure from uom_code */
252 BEGIN
253
254 SELECT UNIT_OF_MEASURE
255 INTO l_primary_uom
256 FROM mtl_units_of_measure_vl
257 WHERE uom_code = p_int_rec(i).primary_uom_code;
258
259 EXCEPTION
260 WHEN OTHERS THEN
261 l_primary_uom := NULL;
262 END;
263
264 -- Bug 8911750
265 BEGIN
266 SELECT UNIT_OF_MEASURE
267 INTO l_secondary_uom
268 FROM mtl_units_of_measure_vl
269 WHERE uom_code = p_int_rec(i).secondary_uom_code;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 l_secondary_uom := NULL;
274 END;
275
276
277 SELECT RCV_TRANSACTIONS_INTERFACE_S.NEXTVAL
278 INTO l_interface_transaction_id
279 FROM dual;
280
281 asn_debug.put_line ('interface id: '||l_interface_transaction_id);
282 asn_debug.put_line ('lcm line id: '||p_int_rec(i).interface_source_line_id);
283 asn_debug.put_line ('landed cost: '||p_int_rec(i).unit_landed_cost);
284
285 INSERT INTO rcv_transactions_interface
286 (interface_transaction_id, -- 01
287 group_id, -- 02
288 lpn_group_id, -- 03
289 transaction_type, -- 04
290 transaction_date, -- 05
291 processing_status_code, -- 06
292 processing_mode_code, -- 07
293 transaction_status_code, -- 08
294 quantity, -- 09
295 uom_code, -- 10
296 ship_to_location_id, -- 11
297 vendor_item_num, -- 12
298 interface_source_code, -- 13
299 interface_source_line_id, -- 14
300 item_id, -- 15
301 item_num, -- 16
302 item_description, -- 17
303 receipt_source_code, -- 18
304 vendor_id, -- 19
305 vendor_site_id, -- 20
306 source_document_code, -- 21
307 po_header_id, -- 22
308 po_line_id, -- 23
309 po_release_id, -- 24
310 po_line_location_id, -- 25
311 header_interface_id, -- 26
312 validation_flag, -- 27
313 org_id, -- 28
314 to_organization_id, -- 29
315 location_id, -- 30
316 deliver_to_location_id, -- 31
317 last_update_date, -- 32
318 last_updated_by, -- 33
319 creation_date, -- 34
320 created_by, -- 35
321 last_update_login, -- 36
322 lcm_shipment_line_id, -- 37
323 unit_landed_cost, -- 38
324 auto_transact_code, -- 39
325 primary_quantity, -- 40 /* Bug 8210608 */
326 primary_unit_of_measure, -- 41 /* Bug 8210608 */
327 secondary_quantity, -- 42 /* Bug 8911750 */
328 secondary_uom_code, -- 43 /* Bug 8911750 */
329 secondary_unit_of_measure) -- 44 /* Bug 8911750 */
330 VALUES(
331 l_interface_transaction_id, -- 01
332 l_group_id, -- 02
333 l_group_id, -- 03
334 l_transaction_type, -- 04
335 sysdate, -- 05
336 l_processing_status_code, -- 06
337 l_processing_mode_code, -- 07
338 l_transaction_status_code, -- 08
339 p_int_rec(i).txn_qty, -- 09
340 p_int_rec(i).txn_uom_code, -- 10
341 p_int_rec(i).location_id, -- 11
342 NULL, -- 12
343 l_interface_source_code, -- 13
344 p_int_rec(i).ship_line_id, -- 14
345 p_int_rec(i).inventory_item_id, -- 15
346 NULL, -- 16
347 p_int_rec(i).item_description, -- 17
348 l_receipt_source_code, -- 18
349 l_vendor_id, -- 19
350 l_vendor_site_id, -- 20
351 p_int_rec(i).src_type_code, -- 21
352 l_po_header_id, -- 22
353 l_po_line_id, -- 23
354 l_po_release_id, -- 24
355 p_int_rec(i).ship_line_source_id, -- 25
356 l_header_interface_id, -- 26
357 l_validation_flag, -- 27
358 p_int_rec(i).org_id, -- 28
359 p_int_rec(i).organization_id, -- 29
360 p_int_rec(i).location_id, -- 30
361 p_int_rec(i).location_id, -- 31
362 sysdate, -- 32
363 fnd_global.user_id, -- 33
364 sysdate, -- 34
365 fnd_global.user_id, -- 35
366 fnd_global.login_id, -- 36
367 p_int_rec(i).ship_line_id, -- 37
368 p_int_rec(i).unit_landed_cost, -- 38
369 'SHIP', -- 39
370 p_int_rec(i).primary_qty, -- 40 /* Bug 8210608 */
371 l_primary_uom, -- 41 /* Bug 8210608 */
372 p_int_rec(i).secondary_qty, -- 42 /* Bug 8911750 */
373 p_int_rec(i).secondary_uom_code, -- 43 /* Bug 8911750 */
374 l_secondary_uom); -- 44 /* Bug 8911750 */
375
376 asn_debug.put_line('inserted line');
377
378 end loop;
379 COMMIT;
380
381 -- launch RTP
382 -- bug 16274612 set OU_ID =NULL
383 l_req_id := fnd_request.submit_request('PO', 'RVCTP',null,null,false,'BATCH',l_group_id,NULL,NULL,
384 NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
385 NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
386 NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL,
387 NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL,
388 NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL,
389 NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL,
390 NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
391 NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL,
392 NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
393 NULL, NULL, NULL, NULL, NULL, NULL);
394
395 asn_debug.put_line ('request id: '||l_req_id);
396
397 if (l_req_id <= 0 or l_req_id is null) then
398 raise fnd_api.g_exc_unexpected_error;
399 end if;
400
401 EXCEPTION
402 WHEN OTHERS THEN
403
404 IF c_rcv_header%ISOPEN THEN
405 CLOSE c_rcv_header;
406 END IF;
407
408 ROLLBACK;
409
410 asn_debug.put_line('the error is: ' || sqlcode ||' '||substr(SQLERRM, 1, 1000));
411
412 END insert_rcv_tables;
413
414 END RCV_INSERT_FROM_INL;
415