[Home] [Help]
PACKAGE BODY: APPS.POR_HISTORY_FEED_PKG
Source
1 PACKAGE BODY POR_History_Feed_Pkg AS
2 /* $Header: PORHSFDB.pls 120.6 2006/08/25 23:08:57 tolick noship $ */
3
4 --global variables
5 -- Logging Static Variables
6 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
7 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
8 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
9 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
10 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
11 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
12 G_MODULE_NAME CONSTANT VARCHAR2(30) := 'PO.PLSQL.POR_HISTORY_FEED_PKG';
13
14 -- Concurrent Program Input Parameters
15 g_card_brand VARCHAR2(25) := NULL; -- Card brand Name
16 g_card_issuer_id NUMBER := 0; -- Card Issuer
17 g_card_issuer_site_id NUMBER := 0; -- Card Isuer Site
18 g_from_date_time DATE := NULL; -- From Date
19 g_to_date_time DATE := NULL; -- To Date
20 g_output_filename VARCHAR2(80) := NULL;
21
22 g_inv_org_id NUMBER := 0; -- Inventory Organization
23 g_delimiter VARCHAR2(1) := NULL; -- Account structure delimiter character
24 -- that needs to be replaced by
25 -- ; (AMEX character)
26 g_outfile UTL_FILE.FILE_TYPE;
27
28 --***NOTE: Format_id is a defaulted for this release to identify different...
29 g_format_id NUMBER := 1;
30 --...file formats. When other card issuers are supported in the future, a format
31 --table should be created where the primary key should be format_id. This is
32 --the hook for future use. Format_id replaces need for card issuer ID and
33 --card issuer site ID to identify the field formats.
34
35 g_created_by NUMBER := TO_NUMBER(fnd_profile.value ('USER_ID'));
36 g_last_updated_by NUMBER := TO_NUMBER (fnd_profile.value ('USER_ID'));
37 g_last_update_login NUMBER := TO_NUMBER (fnd_profile.value ('LOGIN_ID'));
38 g_conc_req_id NUMBER := TO_NUMBER (FND_PROFILE.value('CONC_REQUEST_ID'));
39 g_org_id NUMBER := TO_NUMBER (FND_PROFILE.value('ORG_ID'));
40
41 g_po_num_err VARCHAR2(2000) := NULL;
42 g_card_num_err VARCHAR2(2000) := NULL;
43 g_max_card_num_size NUMBER := 0; -- maximum card size that is acceptable by AMEX :16
44 g_max_po_num_size NUMBER := 0; -- maximum po number size that is acceptable by AMEX : 15
45
46 --global cursor for use by write_control_record and write_header_records;
47 CURSOR g_cur_po_headers IS
48 SELECT po_header_id,
49 po_release_id,
50 concurrent_request_id
51 FROM por_feed_records
52 WHERE concurrent_request_id = g_conc_req_id
53 GROUP BY po_header_id,
54 po_release_id,
55 concurrent_request_id;
56
57 ------------------------------------------------------------------------------
58 --This function returns the predefined length of a column to be written. The
59 --length definition must be seeded in the por_feed_field_formats table.
60 -------------------------------------------------------------------------------
61
62 FUNCTION Get_Field_Size (
63 i_column IN VARCHAR2,
64 i_record_type IN VARCHAR2) RETURN NUMBER IS
65
66 l_size NUMBER := 0;
67
68 BEGIN
69
70 SELECT field_length
71 INTO l_size
72 FROM por_feed_field_formats
73 WHERE column_name = i_column
74 AND record_type = i_record_type
75 AND format_id = g_format_id
76 AND effective_date <= SYSDATE
77 AND expiration_date IS NULL;
78
79 RETURN l_size;
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 RETURN 0;
84 RAISE;
85
86 END Get_Field_Size;
87
88 -------------------------------------------------------------------------------
89 --This procedure writes detail records for valid PO distributions to the output file.
90 -------------------------------------------------------------------------------
91
92 PROCEDURE Write_Detail_Record (
93 i_po_id IN NUMBER,
94 i_rel_id IN NUMBER) IS
95
96 CURSOR cur_po_lines (i_po_header_id IN NUMBER,
97 i_release_id IN NUMBER) IS
98
99 SELECT
100 po_number,
101 release_number,
102 quantity,
103 unit_of_measure,
104 unit_price,
105 amount,
106 item_description,
107 chart_of_accounts_id,
108 accounting_code,
109 item_number,
110 line_cancel_flag,
111 shipment_cancel_flag
112 FROM
113 por_feed_records
114 WHERE
115 po_header_id = i_po_header_id
116 AND po_release_id = i_release_id
117 AND concurrent_request_id = g_conc_req_id;
118
119 l_distr_line_number NUMBER := 0;
120 l_cr_dr_indicator VARCHAR2(1) := NULL;
121 l_po_number por_feed_records.po_number%TYPE := NULL;
122 l_line_num NUMBER := 0;
123 l_detail_record VARCHAR2(2000) := NULL;
124
125
126 CURSOR cur_field_order IS
127
128 SELECT column_name,
129 field_length,
130 default_value,
131 pad_character
132 FROM por_feed_field_formats
133 WHERE format_id = g_format_id
134 AND record_type = 'D'
135 AND effective_date <= SYSDATE
136 AND expiration_date IS NULL
137 ORDER BY start_position;
138
139 l_column_name VARCHAR2(100) := NULL;
140 l_default_value VARCHAR2(50) := NULL;
141 l_field_length NUMBER := 0;
142 l_pad_char VARCHAR2(1) := NULL;
143
144 e_do_not_send_distr EXCEPTION;
145 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
146 l_progress VARCHAR2(4) := '000';
147
148 BEGIN
149
150 l_progress := '000';
151
152 IF cur_PO_lines%ISOPEN THEN
153 CLOSE cur_PO_lines;
154 END IF;
155
156 FOR rec_line IN cur_po_lines (i_po_id,i_rel_id) LOOP
157 BEGIN
158
159 l_progress := '010';
160
161 IF rec_line.line_cancel_flag = 'Y' OR rec_line.shipment_cancel_flag = 'Y' THEN
162 RAISE e_do_not_send_distr;
163 END IF;
164
165 IF rec_line.release_number = 0 THEN
166 l_po_number := LTRIM(RTRIM(rec_line.po_number));
167 ELSE
168 l_po_number := LTRIM(RTRIM(rec_line.po_number)) || '-' || rec_line.release_number;
169 END IF;
170
171 l_line_num := l_line_num + 1;
172
173 --fnd_file.put_line(fnd_file.log, 'line # : ' || to_char(l_line_num));
174
175 IF rec_line.amount >= 0 THEN
176 l_cr_dr_indicator := 'D';
177 ELSE
178 l_cr_dr_indicator := 'C';
179 END IF;
180
181 --build record to write fields in start position order
182
183 l_detail_record := NULL; --initialize for writing
184
185 l_progress := '020';
186
187 FOR rec_field in cur_field_order
188 LOOP
189
190 l_column_name := rec_field.column_name;
191 l_default_value := rec_field.default_value;
192 l_field_length := rec_field.field_length;
193 l_pad_char := rec_field.pad_character;
194
195 IF l_column_name = 'RECORD_TYPE_INDICATOR' THEN
196 l_detail_record := l_detail_record ||
197 RPAD(NVL(l_default_value,' '),l_field_length,NVL(l_pad_char,' '));
198 ELSIF l_column_name = 'CM_REF_INDICATOR' THEN
199 l_detail_record := l_detail_record ||
200 RPAD(NVL(l_default_value,' '),l_field_length,NVL(l_pad_char,' '));
201 ELSIF l_column_name = 'PO_NUMBER' THEN
202 l_detail_record := l_detail_record ||
203 RPAD(l_po_number,l_field_length,NVL(l_pad_char,' '));
204 ELSIF l_column_name = 'PO_LINE_NUMBER' THEN
205 l_detail_record := l_detail_record ||
206 LPAD(l_line_num,l_field_length,NVL(l_pad_char,' '));
207 ELSIF l_column_name = 'QUANTITY' THEN
208 l_detail_record := l_detail_record ||
209 ltrim(to_char(rec_line.quantity,'0999999.90'));
210 ELSIF l_column_name = 'UOM' THEN
211 l_detail_record := l_detail_record ||
212 RPAD(rec_line.unit_of_measure,l_field_length,NVL(l_pad_char,' '));
213 ELSIF l_column_name = 'UNIT_PRICE' THEN
214 l_detail_record := l_detail_record ||
215 ltrim(to_char(rec_line.unit_price,'0999999.90'));
216 ELSIF l_column_name = 'ITEM_DESCRIPTION' THEN
217 l_detail_record := l_detail_record ||
218 RPAD(NVL(SUBSTR(rec_line.item_description,1,l_field_length),' '),
219 l_field_length,NVL(l_pad_char,' '));
220 ELSIF l_column_name = 'ACCOUNTING_CODE' THEN
221 l_detail_record := l_detail_record ||
222 RPAD(SUBSTR(REPLACE(SUBSTR(rec_line.accounting_code,1,l_field_length),g_delimiter,';'),1,l_field_length),
223 l_field_length,NVL(l_pad_char,' '));
224 ELSIF l_column_name = 'CLIENT_INV_NUMBER' THEN
225 l_detail_record := l_detail_record ||
226 RPAD(NVL(SUBSTR(rec_line.item_number,1,l_field_length),' '),l_field_length,NVL(l_pad_char,' '));
227 ELSIF l_column_name = 'UN_SPSC_CODE' THEN
228 l_detail_record := l_detail_record ||
229 RPAD(NVL(l_default_value,' '),l_field_length,NVL(l_pad_char,' '));
230 ELSIF l_column_name = 'RECEIPT_INDICATOR' THEN
231 l_detail_record := l_detail_record ||
232 RPAD(NVL(l_default_value,' '),l_field_length,NVL(l_pad_char,' '));
233 ELSIF l_column_name = 'LINE_DETAIL_MISC' THEN
234 l_detail_record := l_detail_record ||
235 RPAD(NVL(l_default_value,' '),l_field_length,NVL(l_pad_char,' '));
236 ELSIF l_column_name = 'CR_DR_INDICATOR' THEN
237 l_detail_record := l_detail_record ||
238 RPAD(l_cr_dr_indicator,l_field_length,NVL(l_pad_char,' '));
239 ELSIF l_column_name = 'VENDOR_PART_NUMBER' THEN
240 l_detail_record := l_detail_record ||
241 RPAD(NVL(l_default_value,' '),l_field_length,NVL(l_pad_char,' '));
242 END IF;
243
244 END LOOP;
245
246 l_progress := '030';
247
248 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
249 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||'Main', 'Detail Record = ' || l_detail_record);
250 END IF;
251
252 UTL_FILE.PUT_LINE(g_outfile,l_detail_record);
253
254 l_progress := '040';
255
256 EXCEPTION
257
258 WHEN e_do_not_send_distr THEN
259 NULL; --do not write a canceled line or shipment to the file
260
261 WHEN NO_DATA_FOUND THEN
262 NULL;
263
264 WHEN OTHERS THEN
265 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error writing detail for PO #: ' || l_po_number || '. ' ||
266 SUBSTR(SQLERRM,1,300));
267
268 END;
269
270 END LOOP;
271
272 l_progress := '050';
273
274 EXCEPTION
275 WHEN OTHERS THEN
276 IF (G_LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
277 l_log_msg := 'Error in Write_Detail_Record : SQLERRM= ' ||
278 SQLERRM || ' : Progress= ' || l_progress;
279 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||'Write_Detail_Record', l_log_msg);
280 END IF;
281
282 RAISE;
283
284 END Write_Detail_Record;
285
286
287 -------------------------------------------------------------------------------
288 --This procedure writes header records for valid PO distributions to the output file.
289 -------------------------------------------------------------------------------
290
291 PROCEDURE Write_Header_Record IS
292
293 l_po_number por_feed_records.po_number%TYPE := NULL;
294 l_card_member_name por_feed_records.card_member_name%TYPE := NULL;
295 l_requester_name HR_EMPLOYEES.FULL_NAME%TYPE := NULL;
296 l_requester_id por_feed_records.requester_id%TYPE := NULL;
297 l_payment_type_indicator VARCHAR2(1) := NULL;
298 l_order_status VARCHAR2(2):= NULL;
299 l_cr_dr_indicator VARCHAR2(1) := NULL;
300 l_card_number VARCHAR2(80) := NULL;
301 l_release_number por_feed_records.release_number%TYPE := 0;
302 l_card_type_lookup_code por_feed_records.card_type_lookup_code%TYPE := NULL;
303 l_vendor_name por_feed_records.vendor_name%TYPE := NULL;
304 l_vendor_id por_feed_records.vendor_id%TYPE := 0;
305 l_vendor_site_id por_feed_records.vendor_site_id%TYPE := 0;
306 l_vendor_site_code por_feed_records.vendor_site_code%TYPE := NULL;
307 l_order_date por_feed_records.order_date%TYPE := NULL;
308 l_base_currency_code por_feed_records.base_currency_code%TYPE := NULL;
309 l_local_currency_code por_feed_records.local_currency_code%TYPE := NULL;
310 l_po_header_amount por_feed_records.po_header_amount%TYPE := NULL;
311 l_approval_status por_feed_records.approval_status%TYPE := NULL;
312 l_control_status por_feed_records.control_status%TYPE := NULL;
313 l_approved_date por_feed_records.approved_date%TYPE := NULL;
314 l_cancel_flag por_feed_records.cancel_flag%TYPE := NULL;
315 l_hold_flag por_feed_records.hold_flag%TYPE := NULL;
316 l_line_cancel_flag por_feed_records.line_cancel_flag%TYPE := NULL;
317 l_shipment_cancel_flag por_feed_records.shipment_cancel_flag%TYPE := NULL;
318
319 l_object_type VARCHAR2(1) := NULL;
320 l_object_id NUMBER := 0;
321 l_char VARCHAR2(1) := NULL;
322
323 l_total_distr_records NUMBER := 0;
324 l_total_local_amount NUMBER := 0;
325
326 l_header_record VARCHAR2(2000) := NULL;
327
328 e_do_not_send_po EXCEPTION;
329
330 CURSOR cur_field_order IS
331
332 SELECT column_name,
333 field_length,
334 default_value,
335 pad_character
336 FROM por_feed_field_formats
337 WHERE format_id = g_format_id
338 AND record_type = 'H'
339 AND effective_date <= SYSDATE
340 AND expiration_date IS NULL
341 ORDER BY start_position;
342
343 l_column_name VARCHAR2(100) := NULL;
344 l_default_value VARCHAR2(50) := NULL;
345 l_field_length NUMBER := 0;
346 l_pad_char VARCHAR2(1) := NULL;
347
348 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
349 l_progress VARCHAR2(4) := '000';
350
351 BEGIN
352
353 l_progress := '000';
354
355 IF g_cur_po_headers%ISOPEN THEN
356 CLOSE g_cur_po_headers;
357 END IF;
358
359 FOR rec_header IN g_cur_po_headers LOOP
360
361 BEGIN
362
363 l_progress := '010';
364
365 SELECT po_number,
366 release_number,
367 card_number,
368 card_member_name,
369 card_type_lookup_code,
370 vendor_name,
371 vendor_id,
372 vendor_site_id,
373 vendor_site_code,
374 requester_id,
375 order_date,
376 base_currency_code,
377 local_currency_code,
378 po_header_amount,
379 approval_status,
380 control_status,
381 approved_date,
382 cancel_flag,
383 hold_flag
384 INTO l_po_number,
385 l_release_number,
386 l_card_number,
387 l_card_member_name,
388 l_card_type_lookup_code,
389 l_vendor_name,
390 l_vendor_id,
391 l_vendor_site_id,
392 l_vendor_site_code,
393 l_requester_id,
394 l_order_date,
395 l_base_currency_code,
396 l_local_currency_code,
397 l_po_header_amount,
398 l_approval_status,
399 l_control_status,
400 l_approved_date,
401 l_cancel_flag,
402 l_hold_flag
403 FROM por_feed_records
404 WHERE po_header_id = rec_header.po_header_id
405 AND po_release_id = rec_header.po_release_id
406 AND concurrent_request_id = g_conc_req_id
407 AND nvl(error_flag, 'N') = 'N'
408 AND rownum = 1; --to avoid multiple records for multiple distr
409 --for which header info is the same
410
411 l_progress := '020';
412
413 IF l_release_number <> 0 THEN
414 l_po_number := RTRIM(l_po_number) || '-' || l_release_number;
415 END IF;
416
417 IF l_card_type_lookup_code = 'PROCUREMENT' THEN
418
419 l_payment_type_indicator := 'P';
420
421 IF l_requester_id IS NOT NULL THEN
422
423 SELECT first_name || ' ' || middle_name ||
424 decode(middle_name, null, '', ' ') || last_name
425 INTO l_requester_name
426 FROM HR_EMPLOYEES
427 WHERE EMPLOYEE_ID = l_requester_id;
428
429 END IF;
430
431 ELSIF l_card_type_lookup_code = 'SUPPLIER' THEN
432
433 l_requester_id := 0;
434 l_requester_name := NULL;
435 l_card_member_name := l_vendor_name;
436 l_payment_type_indicator := 'O';
437
438 END IF;
439
440 l_progress := '030';
441
442 --Determine the Order Status for the PO: ON, HL, CN
443 IF l_cancel_flag = 'Y' AND l_control_status = 'CLOSED' AND
444 l_approval_status in ('APPROVED','REQUIRES REAPPROVAL') THEN
445 l_order_status := 'CN';
446 ELSIF (l_cancel_flag = 'Y' OR l_control_status = 'FINALLY CLOSED') AND
447 l_approval_status IN ('APPROVED','REQUIRES REAPPROVAL') THEN
448 l_order_status := 'CN';
449 ELSIF l_hold_flag = 'Y' THEN
450 l_order_status := 'HL';
451 ELSIF l_approval_status = 'APPROVED' AND
452 l_approved_date IS NOT NULL THEN
453 l_order_status := 'ON';
454 ELSIF l_approval_status = 'REQUIRES REAPPROVAL' AND
455 l_approved_date IS NOT NULL THEN
456 l_order_status := 'HL';
457 ELSIF l_approval_status = 'REQUIRES REAPPROVAL' AND
458 l_approved_date IS NULL THEN
459 raise e_do_not_send_po;
460 END IF;
461
462 --fnd_file.put_line(fnd_file.log,'status: ' || l_order_status);
463
464 --Determine the total number of distribution lines for the PO
465 SELECT count(*)
466 INTO l_total_distr_records
467 FROM por_feed_records
468 WHERE po_header_id = rec_header.po_header_id
469 AND po_release_id = rec_header.po_release_id
470 AND (line_cancel_flag IS NULL OR line_cancel_flag = 'N')
471 AND (shipment_cancel_flag IS NULL OR shipment_cancel_flag = 'N')
472 AND concurrent_request_id = g_conc_req_id;
473
474 --following will return amount totals given the PO identifier and type.
475 --result will be in functional/base currency.
476 IF l_base_currency_code = l_local_currency_code THEN
477
478 l_total_local_amount := l_po_header_amount;
479
480 ELSE --must explicitly pass boolean when currencies are different
481
482 --following will return amount in local currency
483
484 IF rec_header.po_release_id = 0 THEN
485 l_object_type := 'H';
486 l_object_id := rec_header.po_header_id;
487 ELSE
488 l_object_type := 'R';
489 l_object_id := rec_header.po_release_id;
490 END IF;
491
492 l_total_local_amount := PO_CORE_S.Get_Total (
493 l_object_type, --H/eader or R/elease
494 l_object_id, --based on object type
495 FALSE);
496 END IF;
497
498 IF l_po_header_amount >= 0 THEN
499 l_cr_dr_indicator := 'D';
500 ELSE
501 l_cr_dr_indicator := 'C';
502 END IF;
503
504
505 --build record to write fields in start position order
506
507 l_header_record := NULL; --initialize for writing
508
509 l_progress := '040';
510
511 FOR rec_field in cur_field_order
512 LOOP
513
514 l_column_name := rec_field.column_name;
515 l_default_value := rec_field.default_value;
516 l_field_length := rec_field.field_length;
517 l_pad_char := rec_field.pad_character;
518
519 IF l_column_name = 'RECORD_TYPE_INDICATOR' THEN
520 l_header_record := l_header_record ||
521 RPAD(NVL(l_default_value,' '),l_field_length,NVL(l_pad_char,' '));
522 ELSIF l_column_name = 'CM_REF_NUMBER' THEN
523 l_header_record := l_header_record ||
524 RPAD(l_default_value,l_field_length,NVL(l_pad_char,' '));
525 ELSIF l_column_name = 'PO_NUMBER' THEN
526 l_header_record := l_header_record ||
527 RPAD(l_po_number,l_field_length,NVL(l_pad_char,' '));
528 ELSIF l_column_name = 'CARD_NUMBER' THEN
529 l_header_record := l_header_record ||
530 RPAD(l_card_number,l_field_length,NVL(l_pad_char,' '));
531 ELSIF l_column_name = 'CARD_MEMBER_NAME' THEN
532 l_header_record := l_header_record ||
533 RPAD(NVL(SUBSTR(l_card_member_name,1,l_field_length),' '),
534 l_field_length,NVL(l_pad_char,' '));
535 ELSIF l_column_name = 'REQUESTER_NAME' THEN
536 l_header_record := l_header_record ||
537 RPAD(NVL(SUBSTR(l_requester_name,1,l_field_length),' '),
538 l_field_length,NVL(l_pad_char,' '));
539 ELSIF l_column_name = 'REQUESTER_ID' THEN
540 l_header_record := l_header_record ||
541 LPAD(NVL(SUBSTR(TO_CHAR(l_requester_id),1,l_field_length),0),l_field_length,NVL(l_pad_char,' '));
542 ELSIF l_column_name = 'PAYMENT_TYPE_INDICATOR' THEN
543 l_header_record := l_header_record ||
544 RPAD(l_payment_type_indicator,l_field_length,NVL(l_pad_char,' '));
545 ELSIF l_column_name = 'SUPPLIER_NAME' THEN
546 l_header_record := l_header_record ||
547 RPAD(SUBSTR(l_vendor_name || '-' || l_vendor_site_code,1,l_field_length),
548 l_field_length,NVL(l_pad_char,' '));
549 ELSIF l_column_name = 'CLIENT_SUPPLIER_NUM1' THEN
550 l_header_record := l_header_record ||
551 LPAD(SUBSTR(to_char(l_vendor_id),1,l_field_length),l_field_length,NVL(l_pad_char,' '));
552 ELSIF l_column_name = 'CLIENT_SUPPLIER_NUM2' THEN
553 l_header_record := l_header_record ||
554 LPAD(SUBSTR(to_char(l_vendor_site_id),1,l_field_length),l_field_length,NVL(l_pad_char,' '));
555 ELSIF l_column_name = 'ORDER_DATE' THEN
556 l_header_record := l_header_record ||
557 RPAD(to_char(l_order_date,'YYYYMMDD'),l_field_length,NVL(l_pad_char,' '));
558 ELSIF l_column_name = 'ORDER_STATUS' THEN
559 l_header_record := l_header_record ||
560 RPAD(l_order_status,l_field_length,NVL(l_pad_char,' '));
561 ELSIF l_column_name = 'TOTAL_LINES_PO' THEN
562 IF (l_order_status = 'CN') THEN
563 l_header_record := l_header_record ||
564 LPAD(0,l_field_length,NVL(l_pad_char,' '));
565 ELSE
566 l_header_record := l_header_record ||
567 LPAD(l_total_distr_records,l_field_length,NVL(l_pad_char,' '));
568 END IF;
569 ELSIF l_column_name = 'PO_AMOUNT' THEN
570 IF (l_order_status = 'CN') THEN
571 l_header_record := l_header_record ||
572 ltrim(to_char(0,'099999999999.90'));
573 ELSE
574 l_header_record := l_header_record ||
575 ltrim(to_char(l_po_header_amount,'099999999999.90'));
576 END IF;
577 ELSIF l_column_name = 'CR_DR_INDICATOR' THEN
578 l_header_record := l_header_record ||
579 RPAD(l_cr_dr_indicator,l_field_length,NVL(l_pad_char,' '));
580 ELSIF l_column_name = 'PO_CURRENCY' THEN
581 l_header_record := l_header_record ||
582 RPAD(l_base_currency_code,l_field_length,NVL(l_pad_char,' '));
583 ELSIF l_column_name = 'LOCAL_CURRENCY_AMOUNT' THEN
584 IF (l_order_status = 'CN') THEN
585 l_header_record := l_header_record ||
586 ltrim(to_char(0,'099999999999.90'));
587 ELSE
588 l_header_record := l_header_record ||
589 ltrim(to_char(l_total_local_amount,'099999999999.90'));
590 END IF;
591 ELSIF l_column_name = 'LOCAL_CURRENCY_CODE' THEN
592 l_header_record := l_header_record ||
593 RPAD(l_local_currency_code,l_field_length,NVL(l_pad_char,' '));
594 ELSIF l_column_name = 'HEADER_MISC' THEN
595 l_header_record := l_header_record ||
596 RPAD(NVL(l_default_value,' '),l_field_length,NVL(l_pad_char,' '));
597 END IF;
598
599 END LOOP;
600
601 l_progress := '050';
602
603 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
604 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||'Main', 'Header Record = ' || l_header_record);
605 END IF;
606
607 UTL_FILE.PUT_LINE(g_outfile,l_header_record);
608
609 IF RTRIM(l_order_status) <> 'CN' THEN --if PO header is not canceled
610 Write_Detail_Record (rec_header.po_header_id,rec_header.po_release_id);
611 END IF;
612
613 l_progress := '060';
614
615
616 EXCEPTION
617
618 WHEN e_do_not_send_po THEN
619 NULL; --PO already written to global error message
620
621 WHEN NO_DATA_FOUND THEN
622 NULL;
623
624 WHEN OTHERS THEN
625 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error writing header for PO #: ' || l_po_number || '. ' ||
626 SUBSTR(SQLERRM,1,300));
627
628 END; --block
629
630 END LOOP; --rec_header
631
632 EXCEPTION
633 WHEN OTHERS THEN
634 IF (G_LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
635 l_log_msg := 'Error in Write_Header_Record : SQLERRM= ' ||
636 SQLERRM || ' : Progress= ' || l_progress;
637 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||'Write_Header_Record', l_log_msg);
638 END IF;
639
640 RAISE;
641
642 END Write_Header_Record;
643
644 -------------------------------------------------------------------------------
645 --This procedure writes a control record to the output file.
646 -------------------------------------------------------------------------------
647
648 PROCEDURE Write_Control_Record IS
649
650 l_errbuf VARCHAR2(2000) := NULL;
651 l_retcode NUMBER := 0;
652
653 l_control_record VARCHAR2(2000) := NULL;
654
655 l_total_header_records NUMBER := 0;
656 l_total_detail_records NUMBER := 0;
657 l_po_amount NUMBER := 0;
658 l_total_PO_Amount NUMBER := 0;
659 l_PO_header_amount NUMBER := 0;
660 l_card_number VARCHAR2(80) := '';
661 l_po_number VARCHAR2(50) := '';
662 l_release_number NUMBER := 0;
663 l_cancel_flag VARCHAR2(1) := 'N';
664 l_control_status VARCHAR2(25) := '';
665
666 l_cr_dr_indicator VARCHAR2(1) := NULL;
667 l_trans_start_date DATE := NULL;
668 l_trans_end_date DATE := NULL;
669
670 l_po_header_id NUMBER := 0;
671 l_po_release_id NUMBER := 0;
672 l_po_count NUMBER := 0;
673 l_card_count NUMBER := 0;
674
675
676 CURSOR cur_field_order IS
677
678 SELECT column_name,
679 field_length,
680 default_value,
681 pad_character
682 FROM por_feed_field_formats
683 WHERE format_id = g_format_id
684 AND record_type = 'C'
685 AND effective_date <= SYSDATE
686 AND expiration_date IS NULL
687 ORDER BY start_position;
688
689 l_column_name VARCHAR2(100) := NULL;
690 l_default_value VARCHAR2(50) := NULL;
691 l_field_length NUMBER := 0;
692 l_pad_char VARCHAR2(1) := NULL;
693 l_no_error BOOLEAN := TRUE;
694
695 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
696 l_progress VARCHAR2(4) := '000';
697
698 BEGIN
699
700 l_progress := '000';
701
702 FOR rec_header IN g_cur_po_headers
703 LOOP
704 SELECT po_header_amount, card_number, po_number, release_number, cancel_flag, control_status
705 INTO l_po_header_amount, l_card_number, l_po_number, l_release_number, l_cancel_flag, l_control_status
706 FROM por_feed_records
707 WHERE po_header_id = rec_header.po_header_id
708 AND po_release_id = rec_header.po_release_id
709 AND concurrent_request_id = g_conc_req_id
710 AND rownum = 1;
711
712 l_progress := '010';
713
714 IF l_release_number <> 0 THEN
715 l_po_number := RTRIM(l_po_number) || '-' || l_release_number;
716 END IF;
717
718 IF LENGTH(RTRIM(l_po_number)) > g_max_po_num_size THEN
719
720 l_po_count := l_po_count + 1;
721
722 IF l_po_count < 5 THEN --5 is just a preferred number of po #s on one line
723 IF g_po_num_err IS NULL THEN
724 g_po_num_err := l_po_number;
725 ELSE
726 g_po_num_err := g_po_num_err || ', ' || l_po_number;
727 END IF;
728 ELSE
729 g_po_num_err := g_po_num_err || ', ' || l_po_number || fnd_global.local_chr(10);
730 l_po_count := 0; --restart string of 5 po #s
731 END IF;
732
733 l_progress := '020';
734
735 UPDATE por_feed_records
736 SET error_flag = 'Y'
737 WHERE po_header_id = rec_header.po_header_id
738 AND po_release_id = rec_header.po_release_id
739 AND concurrent_request_id = g_conc_req_id;
740
741 l_no_error := FALSE;
742
743 END IF;
744
745 IF LENGTH(RTRIM(l_card_number)) > g_max_card_num_size THEN
746
747 l_card_count := l_card_count + 1;
748
749 IF l_card_count < 5 THEN --5 is just a preferred number of card #s on one line
750 IF g_card_num_err IS NULL THEN
751 g_card_num_err := l_po_number;
752 ELSE
753 g_card_num_err := g_card_num_err || ', ' || l_po_number;
754 END IF;
755 ELSE
756 g_card_num_err := g_card_num_err || ', ' || l_po_number || fnd_global.local_chr(10);
757 l_card_count := 0;
758 END IF;
759
760 l_progress := '030';
761
762 UPDATE por_feed_records
763 SET error_flag = 'Y'
764 WHERE po_header_id = rec_header.po_header_id
765 AND po_release_id = rec_header.po_release_id
766 AND concurrent_request_id = g_conc_req_id;
767
768 l_no_error := FALSE;
769
770 END IF;
771
772 IF (l_no_error) THEN
773 IF (nvl(l_cancel_flag,'N') <> 'Y' AND
774 nvl(l_control_status,'OPEN') <> 'FINALLY CLOSED') THEN
775 l_total_po_amount := l_total_po_amount + l_po_header_amount;
776 END IF;
777 l_total_header_records := l_total_header_records + 1;
778 END IF;
779
780 l_no_error := TRUE;
781
782 END LOOP;
783
784 l_progress := '040';
785
786 -- get count of distribution/detail records for POs; do not include canceled and
787 -- finally closed PO header records
788 SELECT COUNT(*)
789 INTO l_total_detail_records
790 FROM por_feed_records
791 WHERE (line_cancel_fLag IS NULL OR line_cancel_flag = 'N')
792 AND (shipment_cancel_flag IS NULL OR shipment_cancel_flag = 'N')
793 AND (control_status <> 'FINALLY CLOSED' OR control_status IS NULL) --'CLOSED' ok
794 AND NVL(cancel_flag, 'N') = 'N'
795 AND nvl(error_flag, 'N') = 'N'
796 AND concurrent_request_id = g_conc_req_id;
797
798 -- fnd_file.put_line(fnd_file.log,'Sum of all_PO amounts: ' || to_char(l_total_po_amount));
799 -- fnd_file.put_line(fnd_file.log,'Total header lines retrieved: ' || to_char(l_total_header_records));
800 -- fnd_file.put_line(fnd_file.log,'Total detail lines retrieved: ' || to_char(l_total_detail_records));
801
802 IF l_total_po_amount >= 0 THEN
803 l_cr_dr_indicator := 'D';
804 ELSE
805 l_cr_dr_indicator := 'C';
806 END IF;
807
808 SELECT MIN(order_date), MAX(order_date)
809 INTO l_trans_start_date, l_trans_end_date
810 FROM por_feed_records
811 WHERE NVL(error_flag, 'N') = 'N'
812 AND concurrent_request_id = g_conc_req_id;
813
814 l_progress := '050';
815
816 --build record to write fields in start position order
817 FOR rec_field in cur_field_order
818 LOOP
819
820 l_column_name := rec_field.column_name;
821 l_default_value := rec_field.default_value;
822 l_field_length := rec_field.field_length;
823 l_pad_char := rec_field.pad_character;
824
825 IF l_column_name = 'RECORD_TYPE_INDICATOR' THEN
826 l_control_record := l_control_record ||
827 RPAD(l_default_value,l_field_length,NVL(l_pad_char,' '));
828 ELSIF l_column_name = 'CREATION_DATE_PREFIX' THEN
829 l_control_record := l_control_record ||
830 RPAD(l_default_value,l_field_length,NVL(l_pad_char,' '));
831 ELSIF l_column_name = 'FILE_CREATION_DATE' THEN
832 l_control_record := l_control_record ||
833 RPAD(to_char(sysdate,'YYYYMMDD'),l_field_length,NVL(l_pad_char,' '));
834 ELSIF l_column_name = 'TOTAL_FILE_AMT_PREFIX' THEN
835 l_control_record := l_control_record ||
836 RPAD(l_default_value,l_field_length,NVL(l_pad_char,' '));
837 ELSIF l_column_name = 'PO_AMOUNTS_SUM' THEN
838 l_control_record := l_control_record ||
839 ltrim(to_char(l_total_po_amount,'099999999999.90'));
840 ELSIF l_column_name = 'CR_DR_INDICATOR' THEN
841 l_control_record := l_control_record ||
842 RPAD(l_cr_dr_indicator,l_field_length,NVL(l_pad_char,' '));
843 ELSIF l_column_name = 'HEADER_RECORD_PREFIX' THEN
844 l_control_record := l_control_record ||
845 RPAD(l_default_value,l_field_length,NVL(l_pad_char,' '));
846 ELSIF l_column_name = 'TOTAL_HEADER_RECORDS' THEN
847 l_control_record := l_control_record ||
848 LPAD(l_total_header_records,l_field_length,NVL(l_pad_char,' '));
849 ELSIF l_column_name = 'DETAIL_RECORD_PREFIX' THEN
850 l_control_record := l_control_record ||
851 RPAD(l_default_value,l_field_length,NVL(l_pad_char,' '));
852 ELSIF l_column_name = 'TOTAL_DETAIL_RECORDS' THEN
853 l_control_record := l_control_record ||
854 LPAD(l_total_detail_records,l_field_length,NVL(l_pad_char,' '));
855 ELSIF l_column_name = 'START_DATE_PREFIX' THEN
856 l_control_record := l_control_record ||
857 RPAD(l_default_value,l_field_length,NVL(l_pad_char,' '));
858 ELSIF l_column_name = 'TRANS_START_DATE' THEN
859 IF (l_trans_start_date IS NULL) THEN
860 l_control_record := l_control_record || RPAD(' ', l_field_length,NVL(l_pad_char,' '));
861 ELSE
862 l_control_record := l_control_record ||
863 RPAD(to_char(l_trans_start_date,'YYYYMMDD'),l_field_length,NVL(l_pad_char,' '));
864 END IF;
865 ELSIF l_column_name = 'END_DATE_PREFIX' THEN
866 l_control_record := l_control_record ||
867 RPAD(l_default_value,l_field_length,NVL(l_pad_char,' '));
868 ELSIF l_column_name = 'TRANS_END_DATE' THEN
869 IF (l_trans_end_date IS NULL) THEN
870 l_control_record := l_control_record || RPAD(' ', l_field_length,NVL(l_pad_char,' '));
871 ELSE
872 l_control_record := l_control_record ||
873 RPAD(to_char(l_trans_end_date,'YYYYMMDD'),l_field_length,NVL(l_pad_char,' '));
874 END IF;
875 END IF;
876
877 END LOOP;
878
879 l_progress := '060';
880
881 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
882 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||'Main', 'Control Record = ' || l_control_record);
883 END IF;
884
885 UTL_FILE.PUT_LINE(g_outfile,l_control_record);
886
887 l_progress := '070';
888
889 EXCEPTION
890
891 WHEN OTHERS THEN
892 IF (G_LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
893 l_log_msg := 'Error in Write_Control_Record : SQLERRM= ' ||
894 SQLERRM || ' : Progress= ' || l_progress;
895 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||'Write_Control_Record', l_log_msg);
896 END IF;
897
898 RAISE;
899
900 END Write_Control_Record;
901
902 ------------------------------------------------------------------------
903 --following will remove non-numeric characters from a card number.
904 ------------------------------------------------------------------------
905
906 PROCEDURE Trim_Card_Numbers IS
907
908 CURSOR cur_card_numbers IS
909 SELECT card_number
910 FROM por_feed_records
911 WHERE concurrent_request_id = g_conc_req_id
912 GROUP BY card_number;
913
914 l_card_number por_feed_records.card_number%TYPE;
915 l_trim_card_number por_feed_records.card_number%TYPE;
916
917 l_char VARCHAR2(1) := NULL;
918
919 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
920 l_progress VARCHAR2(4) := '000';
921
922 BEGIN
923
924 l_progress := '000';
925
926 FOR rec_card IN cur_card_numbers
927 LOOP
928
929 l_trim_card_number := NULL; -- initialize
930 l_card_number := TRIM(rec_card.card_number);
931
932 IF (l_card_number is not null) THEN
933 --remove non-numeric chars from card number
934 FOR i in 1..LENGTH(l_card_number)
935 LOOP
936
937 l_char := substr(l_card_number,i,1);
938 -- ascii('0') = 48 and ascii('9') = 57
939 IF ASCII(l_char) >= ASCII('0') AND ASCII(l_char) <= ASCII('9') THEN
940 l_trim_card_number := l_trim_card_number || l_char;
941 END IF;
942
943 END LOOP;
944
945 l_progress := '010';
946
947 UPDATE por_feed_records
948 SET card_number = l_trim_card_number
949 WHERE card_number = l_card_number
950 AND concurrent_request_id = g_conc_req_id;
951 END IF;
952 END LOOP;
953 COMMIT;
954
955 l_progress := '020';
956
957 EXCEPTION
958
959 WHEN OTHERS THEN
960 IF (G_LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
961 l_log_msg := 'Error in Trim_Card_Numbers : SQLERRM= ' ||
962 SQLERRM || ' : Progress= ' || l_progress;
963 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||'Trim_Card_Numbers', l_log_msg);
964 END IF;
965
966 RAISE;
967
968 END Trim_Card_Numbers;
969
970
971 ------------------------------------------------------------------------
972 --following will return header amount totals given the PO identifier and type.
973 --result will be in functional/base currency.
974 ------------------------------------------------------------------------
975
976 PROCEDURE Get_Header_Amounts IS
977
978 l_base_curr_code por_feed_records.base_currency_code%TYPE;
979 l_local_curr_code por_feed_records.local_currency_code%TYPE;
980
981 l_object_type VARCHAR2(1) := NULL;
982 l_object_id NUMBER := 0;
983 l_po_amount NUMBER := 0;
984
985 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
986 l_progress VARCHAR2(4) := '000';
987
988 BEGIN
989 l_progress := '000';
990
991 FOR rec_header IN g_cur_po_headers
992 LOOP
993
994 SELECT base_currency_code,
995 local_currency_code
996 INTO l_base_curr_code,
997 l_local_curr_code
998 FROM por_feed_records
999 WHERE po_header_id = rec_header.po_header_id
1000 AND po_release_id = rec_header.po_release_id
1001 AND concurrent_request_id = g_conc_req_id
1002 AND rownum = 1;
1003
1004 l_progress := '010';
1005
1006 IF rec_header.po_release_id = 0 THEN
1007 l_object_type := 'H';
1008 l_object_id := rec_header.po_header_id;
1009 ELSE
1010 l_object_type := 'R';
1011 l_object_id := rec_header.po_release_id;
1012 END IF;
1013
1014 IF l_base_curr_code = l_local_curr_code THEN
1015
1016 --Note that the Get_Total API subtracts amounts for canceled lines/
1017 --shipments.
1018 l_po_amount := PO_CORE_S.Get_Total (
1019 l_object_type, --H/eader or R/elease
1020 l_object_id); --based on object type
1021
1022 ELSE
1023
1024 --Note that the Get_Total API subtracts amounts for canceled lines/
1025 --shipments.
1026 l_po_amount := PO_CORE_S.Get_Total (
1027 l_object_type, --H/eader or R/elease
1028 l_object_id, --based on object type
1029 TRUE); --result in base/func currency
1030 END IF;
1031
1032 l_progress := '020';
1033
1034 UPDATE por_feed_records
1035 SET po_header_amount = l_po_amount
1036 WHERE po_header_id = rec_header.po_header_id
1037 AND po_release_id = rec_header.po_release_id
1038 AND concurrent_request_id = g_conc_req_id;
1039
1040 END LOOP;
1041 COMMIT;
1042
1043 l_progress := '030';
1044
1045 EXCEPTION
1046 WHEN OTHERS THEN
1047 IF (G_LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1048 l_log_msg := 'Error in Get_Header_Amounts : SQLERRM= ' ||
1049 SQLERRM || ' : Progress= ' || l_progress;
1050 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||'Get_Header_Amounts', l_log_msg);
1051 END IF;
1052
1053 RAISE;
1054 END Get_Header_Amounts;
1055
1056
1057 -------------------------------------------------------------------------------
1058 --This procedure populates the por_feed_records table with valid PO distributions
1059 --using bulk binding.
1060 -------------------------------------------------------------------------------
1061
1062 PROCEDURE Get_PO_Distributions IS
1063
1064 TYPE tab_po_num IS TABLE OF po_headers.segment1%TYPE;
1065 TYPE tab_rel_num IS TABLE OF po_releases.release_num%TYPE;
1066 TYPE tab_po_header_id IS TABLE OF po_headers.po_header_id%TYPE;
1067 TYPE tab_po_release_id IS TABLE OF po_releases.po_release_id%TYPE;
1068 TYPE tab_cardno IS TABLE OF iby_creditcard.masked_cc_number%TYPE;
1069 TYPE tab_cardmember IS TABLE OF ap_cards.cardmember_name%TYPE;
1070 TYPE tab_card_type IS TABLE OF ap_card_programs.card_type_lookup_code%TYPE;
1071 TYPE tab_card_brand IS TABLE OF ap_card_programs.card_brand_lookup_code%TYPE;
1072 TYPE tab_requester_id IS TABLE OF ap_cards.employee_id%TYPE;
1073 TYPE tab_vendor_name IS TABLE OF po_vendors.vendor_name%TYPE;
1074 TYPE tab_vendor_site_code IS TABLE OF po_vendor_sites.vendor_site_code%TYPE;
1075 TYPE tab_vendor_id IS TABLE OF po_vendors.vendor_id%TYPE;
1076 TYPE tab_vendor_site_id IS TABLE OF po_vendor_sites.vendor_site_id%TYPE;
1077 TYPE tab_order_date IS TABLE OF po_headers.last_update_date%TYPE;
1078 TYPE tab_base_curr IS TABLE OF gl_sets_of_books.currency_code%TYPE;
1079 TYPE tab_local_curr IS TABLE OF po_headers.currency_code%TYPE;
1080 TYPE tab_uom IS TABLE OF po_lines.unit_meas_lookup_code%TYPE;
1081 TYPE tab_item_num IS TABLE OF mtl_system_items_kfv.concatenated_segments%TYPE;
1082 TYPE tab_item_desc IS TABLE OF po_lines.item_description%TYPE;
1083 TYPE tab_qty IS TABLE OF po_distributions.quantity_ordered%TYPE;
1084 TYPE tab_unit_price IS TABLE OF po_lines.unit_price%TYPE;
1085 TYPE tab_amount IS TABLE OF NUMBER;
1086 TYPE tab_acct_id IS TABLE OF gl_sets_of_books.chart_of_accounts_id%TYPE;
1087 TYPE tab_acct_code IS TABLE OF gl_code_combinations_kfv.concatenated_segments%TYPE;
1088 TYPE tab_appr_status IS TABLE OF po_headers.authorization_status%TYPE;
1089 TYPE tab_appr_date IS TABLE OF po_headers.approved_date%TYPE;
1090 TYPE tab_control_status IS TABLE OF po_headers.closed_code%TYPE;
1091 TYPE tab_cancel_flag IS TABLE OF po_headers.cancel_flag%TYPE;
1092 TYPE tab_hold_flag IS TABLE OF po_headers.user_hold_flag%TYPE;
1093 TYPE tab_line_cancel_flag IS TABLE OF po_lines.cancel_flag%TYPE;
1094 TYPE tab_shipment_cancel_flag IS TABLE OF po_line_locations.cancel_flag%TYPE;
1095
1096 l_po_num tab_po_num;
1097 l_rel_num tab_rel_num;
1098 l_header_id tab_po_header_id;
1099 l_release_id tab_po_release_id;
1100 l_card_num tab_cardno;
1101 l_cardmember tab_cardmember;
1102 l_card_brand tab_card_brand;
1103 l_card_type tab_card_type;
1104 l_req_id tab_requester_id;
1105 l_vendor_name tab_vendor_name;
1106 l_vendor_id tab_vendor_id;
1107 l_vendor_site_code tab_vendor_site_code;
1108 l_vendor_site_id tab_vendor_site_id;
1109 l_order_date tab_order_date;
1110 l_base_curr tab_base_curr;
1111 l_local_curr tab_local_curr;
1112 l_uom tab_uom;
1113 l_item_num tab_item_num;
1114 l_item_desc tab_item_desc;
1115 l_qty tab_qty;
1116 l_unit_price tab_unit_price;
1117 l_amount tab_amount;
1118 l_acct_id tab_acct_id;
1119 l_acct_code tab_acct_code;
1120 l_appr_status tab_appr_status;
1121 l_appr_date tab_appr_date;
1122 l_contr_status tab_control_status;
1123 l_cancel_flag tab_cancel_flag;
1124 l_line_cancel_flag tab_line_cancel_flag;
1125 l_shipment_cancel_flag tab_shipment_cancel_flag;
1126 l_hold_flag tab_hold_flag;
1127
1128 l_cur_count NUMBER := 0;
1129
1130 --This cursor retrieves all transactions that are on hold or 'open'.
1131 --'Open' refers to three types of records: Transactions with no
1132 --line/shipment cancellations, some line/shipment cancellations,
1133 --or all line/shipment cancellations.
1134
1135 CURSOR cur_PO_records IS
1136
1137 SELECT --standard POs with catalog items
1138
1139 PH.SEGMENT1 PO_NUM, --header,detail
1140 0 RELEASE_NUM, --NA for std PO
1141 PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
1142 0 PO_RELEASE_ID, --NA for std PO
1143 ibycc.masked_cc_number CARD_NUM, --header
1144 AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
1145 ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
1146 ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --for decode
1147 AC.EMPLOYEE_ID requester_ID, --header
1148 PV.VENDOR_NAME VENDOR_NAME, --for header, select key
1149 PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
1150 PV.VENDOR_ID VENDOR_ID, --header
1151 PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
1152 PH.LAST_UPDATE_DATE ORDER_DATE, --header
1153 GSB.CURRENCY_CODE BASE_CURR, --header
1154 PH.CURRENCY_CODE LOCAL_CURR, --header
1155 PL.UNIT_MEAS_LOOKUP_CODE UOM, --detail
1156 SUBSTR(MSI.CONCATENATED_SEGMENTS,1,40) ITEM_NUM, --detail
1157 PL.ITEM_DESCRIPTION ITEM_DESC, --detail
1158 DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, PD.QUANTITY_ORDERED) QTY, --detail
1159 DECODE(PL.MATCHING_BASIS, 'AMOUNT', 1,NVL(PD.RATE,1) * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE)) UNIT_PRICE, --detail
1160 DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, NVL(PD.RATE,1) * (PD.QUANTITY_ORDERED * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE))) AMOUNT, --control, header
1161 GSB.CHART_OF_ACCOUNTS_ID CHART_ACCTS_ID, -- ref
1162 fnd_flex_ext.get_segs('SQLGL','GL#', GSB.CHART_OF_ACCOUNTS_ID, PD.CODE_COMBINATION_ID) ACCTNG_CODE,
1163 PH.AUTHORIZATION_STATUS APPR_STATUS, --for order status
1164 PH.APPROVED_DATE APPR_DATE, --for order status
1165 PH.CLOSED_CODE CONTROL_STATUS, --for order status
1166 PH.CANCEL_FLAG CANCEL_FLAG, --for order status
1167 PH.USER_HOLD_FLAG hold_flag, --for order status
1168 PL.CANCEL_FLAG line_cancel_flag, --ref
1169 PLL.CANCEL_FLAG shipment_cancel_flag --ref
1170
1171 FROM
1172
1173 PO_HEADERS PH,
1174 PO_LINES PL,
1175 PO_LINE_LOCATIONS PLL,
1176 PO_DISTRIBUTIONS PD,
1177 PO_VENDORS PV,
1178 PO_VENDOR_SITES PVS,
1179 AP_CARDS AC,
1180 AP_CARD_PROGRAMS ACP,
1181 MTL_SYSTEM_ITEMS_KFV MSI,
1182 GL_SETS_OF_BOOKS GSB,
1183 FINANCIALS_SYSTEM_PARAMETERS FSP,
1184 iby_creditcard ibycc
1185
1186 WHERE
1187
1188 PH.PO_HEADER_ID = PL.PO_HEADER_ID
1189 AND PH.TYPE_LOOKUP_CODE = 'STANDARD'
1190 AND PH.PCARD_ID IS NOT NULL --p-card used
1191 AND (PL.ITEM_ID = MSI.INVENTORY_ITEM_ID
1192 AND MSI.ORGANIZATION_ID = g_inv_org_id) --only item for that inv org
1193 AND PL.PO_LINE_ID = PLL.PO_LINE_ID
1194 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1195 AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
1196 AND PH.VENDOR_ID = PV.VENDOR_ID
1197 AND PV.VENDOR_ID = PVS.VENDOR_ID
1198 AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
1199 AND PH.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
1200 AND PH.APPROVED_DATE IS NOT NULL
1201 AND (PH.CANCEL_FLAG IS NULL OR PH.CANCEL_FLAG = 'N') --PO not canceled but lines/shipments may be
1202 AND PVS.PCARD_SITE_FLAG = 'Y' --verifies supplier site uses p-card
1203 and PH.PCARD_ID = AC.CARD_ID
1204 AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card registered for program
1205 AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
1206 AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand --valid card type
1207 AND ACP.VENDOR_ID = g_card_issuer_id
1208 AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
1209 AND PH.LAST_UPDATE_DATE >= g_from_date_time
1210 AND PH.LAST_UPDATE_DATE < g_to_date_time
1211 AND ac.card_reference_id = ibycc.instrid
1212
1213 UNION ALL -- Standard POs with non-catalog items
1214
1215 SELECT
1216 PH.SEGMENT1 PO_NUM, --header,detail
1217 0 RELEASE_NUM, --NA for std PO
1218 PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
1219 0 PO_RELEASE_ID, --NA for std PO
1220 ibycc.masked_cc_number CARD_NUM, --header
1221 AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
1222 ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
1223 ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --for decode
1224 AC.EMPLOYEE_ID requester_ID, --header
1225 PV.VENDOR_NAME VENDOR_NAME, --for header, select key
1226 PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
1227 PV.VENDOR_ID VENDOR_ID, --header
1228 PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
1229 PH.LAST_UPDATE_DATE ORDER_DATE, --header
1230 GSB.CURRENCY_CODE BASE_CURR, --header
1231 PH.CURRENCY_CODE LOCAL_CURR, --header
1232 PL.UNIT_MEAS_LOOKUP_CODE UOM, --detail
1233 NULL ITEM_NUM, --NA for non-catalog
1234 PL.ITEM_DESCRIPTION ITEM_DESC, --NA for non-catalog
1235 DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED,PD.QUANTITY_ORDERED) QTY, --detail
1236 DECODE(PL.MATCHING_BASIS, 'AMOUNT', 1,NVL(PD.RATE,1) * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE)) UNIT_PRICE, --detail
1237 DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED,NVL(PD.RATE,1) * (PD.QUANTITY_ORDERED * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE))) AMOUNT, --control, header
1238 GSB.CHART_OF_ACCOUNTS_ID CHART_ACCTS_ID, -- ref
1239 fnd_flex_ext.get_segs('SQLGL','GL#', GSB.CHART_OF_ACCOUNTS_ID, PD.CODE_COMBINATION_ID) ACCTNG_CODE,
1240 PH.AUTHORIZATION_STATUS APPR_STATUS, --for order status
1241 PH.APPROVED_DATE APPR_DATE, --for order status
1242 PH.CLOSED_CODE CONTROL_STATUS, --for order status
1243 PH.CANCEL_FLAG CANCEL_FLAG, --for order status
1244 PH.USER_HOLD_FLAG hold_flag, --for order status
1245 PL.CANCEL_FLAG line_cancel_flag, --ref
1246 PLL.CANCEL_FLAG shipment_cancel_flag --ref
1247
1248 FROM
1249 PO_HEADERS PH,
1250 PO_LINES PL,
1251 PO_LINE_LOCATIONS PLL,
1252 PO_DISTRIBUTIONS PD,
1253 PO_VENDORS PV,
1254 PO_VENDOR_SITES PVS,
1255 AP_CARDS AC,
1256 AP_CARD_PROGRAMS ACP,
1257 GL_SETS_OF_BOOKS GSB,
1258 FINANCIALS_SYSTEM_PARAMETERS FSP,
1259 iby_creditcard ibycc
1260
1261 WHERE
1262 PH.PO_HEADER_ID = PL.PO_HEADER_ID
1263 AND PH.TYPE_LOOKUP_CODE = 'STANDARD'
1264 AND PH.PCARD_ID IS NOT NULL --p-card is used
1265 AND PL.ITEM_ID IS NULL --non-catalog item has no id
1266 AND PL.PO_LINE_ID = PLL.PO_LINE_ID
1267 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1268 AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
1269 AND PH.VENDOR_ID = PV.VENDOR_ID
1270 AND PV.VENDOR_ID = PVS.VENDOR_ID
1271 AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
1272 AND PH.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
1273 AND PH.APPROVED_DATE IS NOT NULL
1274 AND (PH.CANCEL_FLAG IS NULL OR PH.CANCEL_FLAG = 'N') --PO not canceled but lines/shipments may be
1275 AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
1276 and PH.PCARD_ID = AC.CARD_ID --valid p-card
1277 AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for program
1278 AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
1279 AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand --valid card type
1280 AND ACP.VENDOR_ID = g_card_issuer_id
1281 AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
1282 AND PH.LAST_UPDATE_DATE >= g_from_date_time
1283 AND PH.LAST_UPDATE_DATE < g_to_date_time
1284 AND ac.card_reference_id = ibycc.instrid
1285
1286 UNION ALL -- Blanket releases with non-catalog items
1287
1288 SELECT
1289 PH.SEGMENT1 PO_NUM, --header, detail
1290 PR.RELEASE_NUM RELEASE_NUM, --header
1291 PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
1292 PR.PO_RELEASE_ID PO_RELEASE_ID, --for get_total API
1293 ibycc.masked_cc_number CARD_NUM, --header
1294 AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
1295 ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
1296 ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --decode key
1297 AC.EMPLOYEE_ID requester_ID, --header
1298 PV.VENDOR_NAME VENDOR_NAME, --for header, select key
1299 PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
1300 PV.VENDOR_ID VENDOR_ID, --header
1301 PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
1302 PR.LAST_UPDATE_DATE ORDER_DATE, --header
1303 GSB.CURRENCY_CODE BASE_CURR, --header
1304 PH.CURRENCY_CODE LOCAL_CURR, --header
1305 PL.UNIT_MEAS_LOOKUP_CODE UOM, --detail
1306 NULL ITEM_NUM, --NA for non-catalog
1307 PL.ITEM_DESCRIPTION ITEM_DESC, --NA for non-catalog
1308 DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, PD.QUANTITY_ORDERED) QTY, --detail
1309 DECODE(PL.MATCHING_BASIS, 'AMOUNT', 1, NVL(PD.RATE,1) * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE)) UNIT_PRICE, --detail
1310 DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, NVL(PD.RATE,1) * (PD.QUANTITY_ORDERED * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE))) AMOUNT, --control, header
1311 GSB.CHART_OF_ACCOUNTS_ID CHART_ACCTS_ID, --ref
1312 fnd_flex_ext.get_segs('SQLGL','GL#', GSB.CHART_OF_ACCOUNTS_ID, PD.CODE_COMBINATION_ID) ACCTNG_CODE,
1313 PR.AUTHORIZATION_STATUS APPR_STATUS, --for order status
1314 PR.APPROVED_DATE APPR_DATE, --for order status
1315 PR.CLOSED_CODE CONTROL_STATUS, --for order status
1316 PR.CANCEL_FLAG CANCEL_FLAG, --for order status
1317 PR.HOLD_FLAG hold_flag, --for order status
1318 PL.CANCEL_FLAG line_cancel_flag, --ref
1319 PLL.CANCEL_FLAG shipment_cancel_flag --ref
1320
1321 FROM
1322 PO_HEADERS PH,
1323 PO_RELEASES PR,
1324 PO_LINES PL,
1325 PO_LINE_LOCATIONS PLL,
1326 PO_DISTRIBUTIONS PD,
1327 PO_VENDORS PV,
1328 PO_VENDOR_SITES PVS,
1329 AP_CARDS AC,
1330 AP_CARD_PROGRAMS ACP,
1331 GL_SETS_OF_BOOKS GSB,
1332 FINANCIALS_SYSTEM_PARAMETERS FSP,
1333 iby_creditcard ibycc
1334
1335 WHERE
1336 PH.PO_HEADER_ID = PR.PO_HEADER_ID
1337 AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
1338 AND PH.TYPE_LOOKUP_CODE = 'BLANKET'
1339 AND PR.PCARD_ID IS NOT NULL --p-card is used
1340 AND PL.ITEM_ID IS NULL --non-catalog item has no id
1341 AND PL.PO_LINE_ID = PLL.PO_LINE_ID
1342 AND PR.PO_RELEASE_ID = PLL.PO_RELEASE_ID
1343 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1344 AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
1345 AND PH.VENDOR_ID = PV.VENDOR_ID
1346 AND PV.VENDOR_ID = PVS.VENDOR_ID
1347 AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
1348 AND PR.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
1349 AND PR.APPROVED_DATE IS NOT NULL
1350 AND (PR.CANCEL_FLAG IS NULL OR PR.CANCEL_FLAG = 'N') --release not canceled but lines/shipments may be
1351 AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
1352 and PR.PCARD_ID = AC.CARD_ID --valid p-card
1353 AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for progr
1354 AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
1355 AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand
1356 AND ACP.VENDOR_ID = g_card_issuer_id
1357 AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
1358 AND PR.LAST_UPDATE_DATE >= g_from_date_time
1359 AND PR.LAST_UPDATE_DATE < g_to_date_time
1360 AND ac.card_reference_id = ibycc.instrid
1361
1362 UNION ALL -- Blanket releases with catalog items
1363
1364 SELECT
1365 PH.SEGMENT1 PO_NUM, --header
1366 PR.RELEASE_NUM RELEASE_NUM, --header
1367 PH.PO_HEADER_ID PO_HEADER_ID, --get_total API
1368 PR.PO_RELEASE_ID PO_RELEASE_ID, --get_total API
1369 ibycc.masked_cc_number CARD_NUM, --header
1370 AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
1371 ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
1372 ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --decode key
1373 AC.EMPLOYEE_ID requester_ID, --header
1374 PV.VENDOR_NAME VENDOR_NAME, --header
1375 PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
1376 PV.VENDOR_ID VENDOR_ID, --header
1377 PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --header
1378 PR.LAST_UPDATE_DATE ORDER_DATE, --header
1379 GSB.CURRENCY_CODE FUNC_CURR, --header
1380 PH.CURRENCY_CODE LOCAL_CURR, --header
1381 PL.UNIT_MEAS_LOOKUP_CODE UOM, --detail
1382 SUBSTR(MSI.CONCATENATED_SEGMENTS,1,40) ITEM_NUM, --detail
1383 PL.ITEM_DESCRIPTION ITEM_DESC, --detail
1384 DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, PD.QUANTITY_ORDERED) QTY, --detail
1385 DECODE(PL.MATCHING_BASIS, 'AMOUNT', 1, NVL(PD.RATE,1) * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE)) UNIT_PRICE, --detail
1386 DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, NVL(PD.RATE,1) * (PD.QUANTITY_ORDERED * NVL(PLL.PRICE_OVERRIDE,PL.UNIT_PRICE))) AMOUNT, --control, header
1387 GSB.CHART_OF_ACCOUNTS_ID CHART_ACCTS_ID, -- ref
1388 fnd_flex_ext.get_segs('SQLGL','GL#', GSB.CHART_OF_ACCOUNTS_ID, PD.CODE_COMBINATION_ID) ACCTNG_CODE,
1389 PR.AUTHORIZATION_STATUS APPR_STATUS, --for order status
1390 PR.APPROVED_DATE APPR_DATE, --for order status
1391 PR.CLOSED_CODE CONTROL_STATUS, --for order status
1392 PR.CANCEL_FLAG CANCEL_FLAG, --for order status
1393 PR.HOLD_FLAG hold_flag, --for order status
1394 PL.CANCEL_FLAG line_cancel_flag, --ref
1395 PLL.CANCEL_FLAG shipment_cancel_flag --ref
1396
1397 FROM
1398 PO_HEADERS PH,
1399 PO_RELEASES PR,
1400 PO_LINES PL,
1401 PO_LINE_LOCATIONS PLL,
1402 PO_DISTRIBUTIONS PD,
1403 PO_VENDORS PV,
1404 PO_VENDOR_SITES PVS,
1405 AP_CARDS AC,
1406 AP_CARD_PROGRAMS ACP,
1407 MTL_SYSTEM_ITEMS_KFV MSI,
1408 GL_SETS_OF_BOOKS GSB,
1409 FINANCIALS_SYSTEM_PARAMETERS FSP,
1410 iby_creditcard ibycc
1411
1412 WHERE
1413 PH.PO_HEADER_ID = PR.PO_HEADER_ID
1414 AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
1415 AND PH.TYPE_LOOKUP_CODE = 'BLANKET'
1416 AND PR.PCARD_ID IS NOT NULL --p-card is used
1417 AND (PL.ITEM_ID = MSI.INVENTORY_ITEM_ID
1418 AND MSI.ORGANIZATION_ID = g_inv_org_id) --only item for that inv org
1419 AND PR.PO_RELEASE_ID = PLL.PO_RELEASE_ID
1420 AND PL.PO_LINE_ID = PLL.PO_LINE_ID
1421 AND PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1422 AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
1423 AND PH.VENDOR_ID = PV.VENDOR_ID
1424 AND PV.VENDOR_ID = PVS.VENDOR_ID
1425 AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
1426 AND PR.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
1427 AND PR.APPROVED_DATE IS NOT NULL
1428 AND (PR.CANCEL_FLAG IS NULL OR PR.CANCEL_FLAG = 'N') --release not canceled but lines/shipments may be
1429 AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
1430 and PR.PCARD_ID = AC.CARD_ID --valid p-card
1431 AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for progr
1432 AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
1433 AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand --valid card type
1434 AND ACP.VENDOR_ID = g_card_issuer_id
1435 AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
1436 AND PR.LAST_UPDATE_DATE >= g_from_date_time
1437 AND PR.LAST_UPDATE_DATE < g_to_date_time
1438 AND ac.card_reference_id = ibycc.instrid;
1439
1440
1441 --This is a separate cursor to capture canceled POs canceled at the header level.
1442 --Distribution records for canceled POs are not written to the output file so this
1443 --cursor does not pick them up.
1444
1445 CURSOR cur_canceled_POs IS
1446
1447 SELECT --standard POs
1448
1449 PH.SEGMENT1 PO_NUM, --header,detail
1450 0 RELEASE_NUM, --NA for std PO
1451 PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
1452 0 PO_RELEASE_ID, --NA for std PO
1453 ibycc.masked_cc_number CARD_NUM, --header
1454 AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
1455 ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
1456 ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --for decode
1457 AC.EMPLOYEE_ID requester_ID, --header
1458 PV.VENDOR_NAME VENDOR_NAME, --for header, select key
1459 PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
1460 PV.VENDOR_ID VENDOR_ID, --header
1461 PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
1462 PH.LAST_UPDATE_DATE ORDER_DATE, --header
1463 GSB.CURRENCY_CODE BASE_CURR, --header
1464 PH.CURRENCY_CODE LOCAL_CURR, --header
1465 PH.AUTHORIZATION_STATUS APPR_STATUS, --for order status
1466 PH.APPROVED_DATE APPR_DATE, --for order status
1467 PH.CLOSED_CODE CONTROL_STATUS, --for order status
1468 PH.CANCEL_FLAG CANCEL_FLAG, --for order status
1469 PH.USER_HOLD_FLAG hold_flag --for order status
1470
1471 FROM
1472
1473 PO_HEADERS PH,
1474 PO_VENDORS PV,
1475 PO_VENDOR_SITES PVS,
1476 AP_CARDS AC,
1477 AP_CARD_PROGRAMS ACP,
1478 GL_SETS_OF_BOOKS GSB,
1479 FINANCIALS_SYSTEM_PARAMETERS FSP,
1480 iby_creditcard ibycc
1481
1482 WHERE
1483 PH.TYPE_LOOKUP_CODE = 'STANDARD'
1484 AND PH.PCARD_ID IS NOT NULL --p-card is used
1485 AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
1486 AND PH.VENDOR_ID = PV.VENDOR_ID
1487 AND PV.VENDOR_ID = PVS.VENDOR_ID
1488 AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
1489 AND PH.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
1490 AND PH.APPROVED_DATE IS NOT NULL
1491 AND PH.CANCEL_FLAG = 'Y'
1492 AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
1493 and PH.PCARD_ID = AC.CARD_ID --valid p-card
1494 AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for program
1495 AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
1496 AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand --valid card type
1497 AND ACP.VENDOR_ID = g_card_issuer_id
1498 AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
1499 AND PH.LAST_UPDATE_DATE >= g_from_date_time
1500 AND PH.LAST_UPDATE_DATE < g_to_date_time
1501 AND ac.card_reference_id = ibycc.instrid
1502
1503 UNION ALL --blanket releases
1504
1505 SELECT
1506 PH.SEGMENT1 PO_NUM, --header, detail
1507 PR.RELEASE_NUM RELEASE_NUM, --header
1508 PH.PO_HEADER_ID PO_HEADER_ID, --for get_total API
1509 PR.PO_RELEASE_ID PO_RELEASE_ID, --for get_total API
1510 ibycc.masked_cc_number CARD_NUM, --header
1511 AC.CARDMEMBER_NAME CARD_MEMBER_NAME, --header
1512 ACP.CARD_BRAND_LOOKUP_CODE CARD_BRAND, --select key
1513 ACP.CARD_TYPE_LOOKUP_CODE CARD_TYPE, --decode key
1514 AC.EMPLOYEE_ID requester_ID, --header
1515 PV.VENDOR_NAME VENDOR_NAME, --for header, select key
1516 PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, --header
1517 PV.VENDOR_ID VENDOR_ID, --header
1518 PVS.VENDOR_SITE_ID VENDOR_SITE_ID, --for header, select key
1519 PR.LAST_UPDATE_DATE ORDER_DATE, --header
1520 GSB.CURRENCY_CODE BASE_CURR, --header
1521 PH.CURRENCY_CODE LOCAL_CURR, --header
1522 PR.AUTHORIZATION_STATUS APPR_STATUS, --for order status
1523 PR.APPROVED_DATE APPR_DATE, --for order status
1524 PR.CLOSED_CODE CONTROL_STATUS, --for order status
1525 PR.CANCEL_FLAG CANCEL_FLAG, --for order status
1526 PR.HOLD_FLAG hold_flag --for order status
1527
1528 FROM
1529
1530 PO_HEADERS PH,
1531 PO_RELEASES PR,
1532 PO_VENDORS PV,
1533 PO_VENDOR_SITES PVS,
1534 AP_CARDS AC,
1535 AP_CARD_PROGRAMS ACP,
1536 GL_SETS_OF_BOOKS GSB,
1537 FINANCIALS_SYSTEM_PARAMETERS FSP,
1538 iby_creditcard ibycc
1539
1540 WHERE
1541 PH.PO_HEADER_ID = PR.PO_HEADER_ID
1542 AND PH.TYPE_LOOKUP_CODE = 'BLANKET'
1543 AND PR.PCARD_ID IS NOT NULL --p-card is used
1544 AND FSP.SET_OF_BOOKS_ID = GSB.SET_OF_BOOKS_ID
1545 AND PH.VENDOR_ID = PV.VENDOR_ID
1546 AND PV.VENDOR_ID = PVS.VENDOR_ID
1547 AND PH.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
1548 AND PR.AUTHORIZATION_STATUS IN ('APPROVED','REQUIRES REAPPROVAL')
1549 AND PR.APPROVED_DATE IS NOT NULL
1550 AND PR.CANCEL_FLAG = 'Y'
1551 AND PVS.PCARD_SITE_FLAG = 'Y' --verifies site uses p-card
1552 and PR.PCARD_ID = AC.CARD_ID --valid p-card
1553 AND AC.CARD_PROGRAM_ID = ACP.CARD_PROGRAM_ID --card reg for progr
1554 AND ACP.CARD_TYPE_LOOKUP_CODE IN ('PROCUREMENT','SUPPLIER')
1555 AND ACP.CARD_BRAND_LOOKUP_CODE = g_card_brand
1556 AND ACP.VENDOR_ID = g_card_issuer_id
1557 AND ACP.VENDOR_SITE_ID = g_card_issuer_site_id
1558 AND PR.LAST_UPDATE_DATE >= g_from_date_time
1559 AND PR.LAST_UPDATE_DATE < g_to_date_time
1560 AND ac.card_reference_id = ibycc.instrid;
1561
1562 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1563 l_progress VARCHAR2(4) := '000';
1564
1565 BEGIN
1566
1567 l_progress := '000';
1568
1569 --Insert standard POs and blanket releases with catalog or non-catalog items into por_feed_records
1570 IF cur_PO_records%ISOPEN THEN
1571 CLOSE cur_PO_records;
1572 END IF;
1573
1574 OPEN cur_PO_records;
1575
1576 FETCH cur_PO_records BULK COLLECT
1577 INTO l_po_num,
1578 l_rel_num,
1579 l_header_id,
1580 l_release_id,
1581 l_card_num,
1582 l_cardmember,
1583 l_card_brand,
1584 l_card_type,
1585 l_req_id,
1586 l_vendor_name,
1587 l_vendor_site_code,
1588 l_vendor_id,
1589 l_vendor_site_id,
1590 l_order_date,
1591 l_base_curr,
1592 l_local_curr,
1593 l_uom,
1594 l_item_num,
1595 l_item_desc,
1596 l_qty,
1597 l_unit_price,
1598 l_amount,
1599 l_acct_id,
1600 l_acct_code,
1601 l_appr_status,
1602 l_appr_date,
1603 l_contr_status,
1604 l_cancel_flag,
1605 l_hold_flag,
1606 l_line_cancel_flag,
1607 l_shipment_cancel_flag;
1608
1609 -- fnd_file.put_line(fnd_file.log, '# fetched : ' || to_char(cur_po_records%rowcount));
1610
1611 l_progress := '010';
1612
1613 IF (G_LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1614 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||'Get_PO_Distributions', '# of PO Records = ' || cur_PO_records%ROWCOUNT);
1615 END IF;
1616
1617 FORALL i in 1 .. cur_PO_records%ROWCOUNT
1618
1619 INSERT INTO por_feed_records (
1620 concurrent_request_id,
1621 po_number,
1622 release_number,
1623 po_header_id,
1624 po_release_id,
1625 card_number,
1626 card_member_name,
1627 card_brand_lookup_code,
1628 card_type_lookup_code,
1629 requester_id,
1630 vendor_name,
1631 vendor_site_code,
1632 vendor_id,
1633 vendor_site_id,
1634 order_date,
1635 base_currency_code,
1636 local_currency_code,
1637 unit_of_measure,
1638 item_number,
1639 item_description,
1640 quantity,
1641 unit_price,
1642 amount,
1643 chart_of_accounts_id,
1644 accounting_code,
1645 approval_status,
1646 approved_date,
1647 control_status,
1648 cancel_flag,
1649 hold_flag,
1650 line_cancel_flag,
1651 shipment_cancel_flag
1652 )
1653 VALUES (
1654 g_conc_req_id,
1655 l_po_num(i),
1656 l_rel_num(i),
1657 l_header_id(i),
1658 l_release_id(i),
1659 l_card_num(i),
1660 l_cardmember(i),
1661 l_card_brand(i),
1662 l_card_type(i),
1663 l_req_id(i),
1664 l_vendor_name(i),
1665 l_vendor_site_code(i),
1666 l_vendor_id(i),
1667 l_vendor_site_id(i),
1668 l_order_date(i),
1669 l_base_curr(i),
1670 l_local_curr(i),
1671 l_uom(i),
1672 l_item_num(i),
1673 l_item_desc(i),
1674 l_qty(i),
1675 l_unit_price(i),
1676 l_amount(i),
1677 l_acct_id(i),
1678 l_acct_code(i),
1679 l_appr_status(i),
1680 l_appr_date(i),
1681 l_contr_status(i),
1682 l_cancel_flag(i),
1683 l_hold_flag(i),
1684 l_line_cancel_flag(i),
1685 l_shipment_cancel_flag(i)
1686 );
1687
1688 COMMIT;
1689
1690 IF cur_PO_records%ISOPEN THEN
1691 CLOSE cur_PO_records;
1692 END IF;
1693
1694 l_progress := '020';
1695
1696
1697 --Insert canceled standard POs and blanket releases into por_feed_records
1698
1699 IF cur_canceled_POs%ISOPEN THEN
1700 CLOSE cur_canceled_POs;
1701 END IF;
1702
1703 OPEN cur_canceled_POs;
1704
1705 FETCH cur_canceled_POs BULK COLLECT
1706 INTO l_po_num,
1707 l_rel_num,
1708 l_header_id,
1709 l_release_id,
1710 l_card_num,
1711 l_cardmember,
1712 l_card_brand,
1713 l_card_type,
1714 l_req_id,
1715 l_vendor_name,
1716 l_vendor_site_code,
1717 l_vendor_id,
1718 l_vendor_site_id,
1719 l_order_date,
1720 l_base_curr,
1721 l_local_curr,
1722 l_appr_status,
1723 l_appr_date,
1724 l_contr_status,
1725 l_cancel_flag,
1726 l_hold_flag;
1727
1728 -- fnd_file.put_line(fnd_file.log, '# canceled fetched : ' || to_char(cur_canceled_POs%rowcount));
1729
1730 l_progress := '030';
1731
1732 IF (G_LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1733 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||'Get_PO_Distributions', '# of Cancelled POs = ' || cur_canceled_POs%ROWCOUNT);
1734 END IF;
1735
1736 FORALL i in 1 .. cur_canceled_POs%ROWCOUNT
1737
1738 INSERT INTO por_feed_records (
1739 concurrent_request_id,
1740 po_number,
1741 release_number,
1742 po_header_id,
1743 po_release_id,
1744 card_number,
1745 card_member_name,
1746 card_brand_lookup_code,
1747 card_type_lookup_code,
1748 requester_id,
1749 vendor_name,
1750 vendor_site_code,
1751 vendor_id,
1752 vendor_site_id,
1753 order_date,
1754 base_currency_code,
1755 local_currency_code,
1756 approval_status,
1757 approved_date,
1758 control_status,
1759 cancel_flag,
1760 hold_flag
1761 )
1762 VALUES (
1763 g_conc_req_id,
1764 l_po_num(i),
1765 l_rel_num(i),
1766 l_header_id(i),
1767 l_release_id(i),
1768 l_card_num(i),
1769 l_cardmember(i),
1770 l_card_brand(i),
1771 l_card_type(i),
1772 l_req_id(i),
1773 l_vendor_name(i),
1774 l_vendor_site_code(i),
1775 l_vendor_id(i),
1776 l_vendor_site_id(i),
1777 l_order_date(i),
1778 l_base_curr(i),
1779 l_local_curr(i),
1780 l_appr_status(i),
1781 l_appr_date(i),
1782 l_contr_status(i),
1783 l_cancel_flag(i),
1784 l_hold_flag(i)
1785 );
1786
1787 COMMIT;
1788
1789 IF cur_canceled_POs%ISOPEN THEN
1790 CLOSE cur_canceled_POs;
1791 END IF;
1792
1793 l_progress := '040';
1794
1795 EXCEPTION
1796
1797 WHEN OTHERS THEN
1798 IF (G_LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1799 l_log_msg := 'Error in Get_PO_Distributions : SQLERRM= ' ||
1800 SQLERRM || ' : Progress= ' || l_progress;
1801 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||'Get_PO_Distributions', l_log_msg);
1802 END IF;
1803
1804 RAISE;
1805
1806 END Get_PO_Distributions;
1807
1808 ---------------------------------------------------------------------------------------
1809 -- Parameters are passed to the Main Program in order to specify the set of PO records
1810 -- to retrieve. All the visible parameters are mandatory and are defaulted or user-
1811 -- entered. They are validated in the Concurrent Manager upon user-entry before being
1812 -- passed to the program. Inv_org_id (MFG_ORGANIZATION_ID profile value) is passed to
1813 -- restrict item selection to the inventory organization associated with the
1814 -- responsibility used.
1815 ---------------------------------------------------------------------------------------
1816
1817 PROCEDURE Main (
1818 ERRBUF OUT NOCOPY VARCHAR2,
1819 RETCODE OUT NOCOPY VARCHAR2,
1820 i_card_brand IN VARCHAR2,
1821 i_card_issuer_id IN NUMBER,
1822 i_card_issuer_site_id IN NUMBER,
1823 i_from_date_time IN VARCHAR2,
1824 i_to_date_time IN VARCHAR2,
1825 i_output_filename IN VARCHAR2
1826 ) IS
1827
1828 l_outdir VARCHAR2(100) := NULL;
1829 l_result BOOLEAN := FALSE;
1830 l_phase VARCHAR2(25) := NULL;
1831 l_status VARCHAR2(25) := NULL;
1832 l_dev_status VARCHAR2(25) := NULL;
1833 l_dev_phase VARCHAR2(25) := NULL;
1834 l_message VARCHAR2(2000) := NULL;
1835 l_org_chart_of_accounts_id NUMBER := 0;
1836 l_func_curr_code VARCHAR2(15) := NULL;
1837
1838 l_log_msg FND_LOG_MESSAGES.MESSAGE_TEXT%TYPE;
1839 l_progress VARCHAR2(4) := '000';
1840
1841 BEGIN
1842 l_progress := '000';
1843
1844 -- Logging Procedure level
1845 IF (G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1846 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||'Main.begin', '<-------------------->');
1847 END IF;
1848
1849 g_card_brand := i_card_brand;
1850 g_card_issuer_id := i_card_issuer_id;
1851 g_card_issuer_site_id := i_card_issuer_site_id;
1852 g_output_filename := i_output_filename;
1853
1854 g_from_date_time := to_date(rtrim (to_char (to_date (i_from_date_time,'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS')),
1855 'DD-MON-YYYY HH24:MI:SS');
1856 g_to_date_time := to_date(rtrim (to_char (to_date (i_to_date_time,'YYYY/MM/DD HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS')),
1857 'DD-MON-YYYY HH24:MI:SS');
1858
1859 l_progress := '100';
1860
1861 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1862 l_log_msg := 'Input Params :: i_card_brand = ' || i_card_brand ||
1863 ' :: i_card_issuer_id = ' || to_char(i_card_issuer_id) ||
1864 ' :: i_card_issuer_site_id = ' || to_char(i_card_issuer_site_id) ||
1865 ' :: i_from_date_time = ' || i_from_date_time ||
1866 ' :: i_to_date_time = ' || i_to_date_time ||
1867 ' :: i_output_filename = ' || i_output_filename;
1868 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||'Main', l_log_msg);
1869 END IF;
1870
1871 -- fnd_file.put_line(fnd_file.log, 'reformatted from date/time: ' || to_char(g_from_date_time,'DD-MON-YYYY HH24:MI:SS'));
1872 -- fnd_file.put_line(fnd_file.log, 'reformatted to date/time: ' || to_char(g_to_date_time,'DD-MON-YYYY HH24:MI:SS'));
1873
1874 mo_global.set_policy_context('S', g_org_id);
1875
1876 l_progress := '200';
1877
1878 SELECT fsp.inventory_organization_id, gsb.chart_of_accounts_id, gsb.currency_code
1879 INTO g_inv_org_id, l_org_chart_of_accounts_id, l_func_curr_code
1880 FROM financials_system_parameters fsp,
1881 gl_sets_of_books gsb
1882 WHERE fsp.set_of_books_id = gsb.set_of_books_id
1883 AND fsp.org_id = g_org_id;
1884
1885 l_progress := '300';
1886
1887 g_delimiter := fnd_flex_ext.get_delimiter('SQLGL', 'GL#', l_org_chart_of_accounts_id);
1888
1889 g_max_card_num_size := Get_Field_Size ('CARD_NUMBER','H');
1890 g_max_po_num_size := Get_Field_Size ('PO_NUMBER','H');
1891
1892 l_progress := '400';
1893
1894 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1895 l_log_msg := ' l_func_curr_code = ' || l_func_curr_code ||
1896 ' :: l_org_chart_of_accounts_id = ' || to_char(l_org_chart_of_accounts_id) ||
1897 ' :: g_inv_org_id = ' || to_char(g_inv_org_id);
1898 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||'Main', l_log_msg);
1899 END IF;
1900
1901 --log run in por_feed_history
1902
1903 INSERT INTO por_feed_history (
1904 concurrent_request_id,
1905 card_brand_lookup_code,
1906 vendor_id,
1907 vendor_site_id,
1908 from_date_time,
1909 to_date_time,
1910 output_filename,
1911 last_update_login,
1912 last_update_date,
1913 last_updated_by,
1914 creation_date,
1915 created_by
1916 )
1917 VALUES (
1918 g_conc_req_id,
1919 g_card_brand,
1920 g_card_issuer_id,
1921 g_card_issuer_site_id,
1922 g_from_date_time,
1923 g_to_date_time,
1924 g_output_filename,
1925 g_last_update_login,
1926 sysdate,
1927 g_last_updated_by,
1928 sysdate,
1929 g_created_by);
1930
1931 l_progress := '500';
1932
1933 COMMIT;
1934
1935 Get_PO_Distributions;
1936
1937 l_progress := '600';
1938
1939 Get_Header_Amounts;
1940
1941 l_progress := '700';
1942
1943 -- We no longer need to trim thr card numbers in R12 since AP now stores
1944 -- card numbers encrypted (i.e. XXXXXXXXXXXX1234)
1945 --Trim_Card_Numbers;
1946
1947 l_progress := '800';
1948
1949 FND_PROFILE.GET('ECE_OUT_FILE_PATH',l_outdir);
1950
1951 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1952 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||'Main', 'Output Directory = ' || l_outdir);
1953 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||'Main', 'Filename = ' || g_output_filename);
1954 END IF;
1955
1956 -- Set the linesize to the maximum value of 32767 while opening the file
1957 g_outfile := UTL_FILE.FOPEN(
1958 l_outdir,
1959 g_output_filename,
1960 'w',
1961 32767);
1962
1963 l_progress := '900';
1964
1965 Write_Control_Record;
1966
1967 l_progress := '1000';
1968
1969 Write_Header_Record;
1970
1971 l_progress := '1100';
1972
1973 UTL_FILE.FCLOSE(g_outfile);
1974
1975 DELETE FROM por_feed_records
1976 WHERE concurrent_request_id = g_conc_req_id;
1977
1978 --get conc mgr status;
1979 l_result := FND_CONCURRENT.GET_REQUEST_STATUS(
1980 g_conc_req_id, --request_id
1981 NULL, --application default null
1982 NULL, --program default null
1983 l_phase, --phase out
1984 l_status, --status out
1985 l_dev_status, --dev_status out
1986 l_dev_phase, --dev_phase out
1987 l_message --message out
1988 );
1989
1990 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Output file has been created successfully.');
1991
1992 IF (G_LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
1993 l_log_msg := ' g_po_num_err = ' || g_po_num_err ||
1994 ' :: g_card_num_err = ' || g_card_num_err ||
1995 ' :: l_message = ' || l_message;
1996 FND_LOG.STRING(G_LEVEL_STATEMENT, G_MODULE_NAME||'Main', l_log_msg);
1997 END IF;
1998
1999 l_progress := '1200';
2000
2001 IF g_po_num_err IS NOT NULL THEN
2002 --Write to Conc Mgr log file
2003 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('ICX','ICX_POR_HIST_FEED_PO_NUM_LONG'));
2004 FND_FILE.PUT_LINE (FND_FILE.LOG, g_po_num_err);
2005 ERRBUF := NULL; --appears to get truncated; might not be long enough for custom error to be assigned to it
2006 RETCODE := '1'; --forces warning status
2007 l_result :=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', FND_MESSAGE.GET_STRING('ICX','ICX_POR_HIST_FEED_COMP_TEXT')); --overwrite conc mgr status
2008 END IF;
2009
2010 IF g_card_num_err IS NOT NULL THEN
2011 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET_STRING('ICX','ICX_POR_HIST_FEED_CARDNO_LONG'));
2012 FND_FILE.PUT_LINE (FND_FILE.LOG, g_card_num_err);
2013 ERRBUF := NULL; --appears to get truncated; might not be long enough...
2014 RETCODE := '1'; --forces warning status
2015 l_result := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', FND_MESSAGE.GET_STRING('ICX','ICX_POR_HIST_FEED_COMP_TEXT')); --overwrite conc mgr status
2016 END IF;
2017
2018 --update por_feed_history to log status
2019
2020 UPDATE por_feed_history
2021 SET status = ltrim(l_status)
2022 WHERE concurrent_request_id = g_conc_req_id;
2023
2024 COMMIT;
2025
2026 l_progress := '1300';
2027
2028 -- Logging Procedure level
2029 IF (G_LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2030 FND_LOG.STRING(G_LEVEL_PROCEDURE, G_MODULE_NAME||'Main.end', '<-------------------->');
2031 END IF;
2032
2033 EXCEPTION
2034
2035 WHEN OTHERS THEN
2036 IF (G_LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
2037 l_log_msg := 'Error in Concurrent request : SQLERRM= ' ||
2038 SQLERRM || ' : Progress= ' || l_progress;
2039 FND_LOG.STRING(G_LEVEL_EXCEPTION, G_MODULE_NAME||'Main', l_log_msg);
2040 END IF;
2041
2042 UTL_FILE.FCLOSE(g_outfile);
2043 RAISE;
2044 END Main;
2045
2046 END POR_History_Feed_Pkg; --package