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