[Home] [Help]
PACKAGE BODY: APPS.AP_CREDIT_CARD_TRXN_LOADER_PKG
Source
1 PACKAGE BODY AP_CREDIT_CARD_TRXN_LOADER_PKG AS
2 /* $Header: apwccldb.pls 115.3 2002/12/26 10:10:39 srinvenk ship $ */
3
4 /*
5 Written by:
6 Ron Langi
7
8 Purpose:
9 Due to limitations in SQL*Loader certain file formats of
10 card issuers need to be preformatted prior to loading of the
11 transaction records
12
13 Input:
14 p_indatafilename - input of preformat
15 p_outdatafilename - output of preformat
16 p_cardbrand - card brand of input file
17
18 Output:
19 errbuf - contains error message; required by Concurrent Manager
20 retcode - contains return code; required by Concurrent Manager
21
22 Input/Output:
23
24 Assumption:
25 If datafile to be preformatted is from GE Capitol MasterCard,
26 the datafile must be of format 5000 financial transaction record.
27
28 If datafile to be preformatted is from US Bank Visa,
29 the datafile must be of US Bank Visa transaction record.
30
31 */
32 PROCEDURE Preformat(errbuf out nocopy varchar2,
33 retcode out nocopy number,
34 p_indatafilename in varchar2,
35 p_outdatafilename in varchar2,
36 p_cardbrand in varchar2) IS
37
38 l_indatapathname varchar2(240);
39 l_indatafilename varchar2(240);
40 l_indatafileptr utl_file.file_type;
41 l_outdatapathname varchar2(240);
42 l_outdatafilename varchar2(240);
43 l_outdatafileptr utl_file.file_type;
44 l_ntdir number;
45 l_unixdir number;
46 l_line varchar2(1000);
47 l_numrecs number;
48
49 BEGIN
50
51 --
52 -- Parse the indatafilename for the path and filename
53 --
54 fnd_file.put_line(fnd_file.log, 'Parsing p_indatafilename '|| p_indatafilename);
55 l_ntdir := instrb(p_indatafilename, '\', -1);
56 l_unixdir := instrb(p_indatafilename, '/', -1);
57 if (l_ntdir > 0) then
58 l_indatapathname := substrb(p_indatafilename, 0, l_ntdir-1);
59 l_indatafilename := substrb(p_indatafilename, l_ntdir+1);
60 elsif (l_unixdir > 0) then
61 l_indatapathname := substrb(p_indatafilename, 0, l_unixdir-1);
62 l_indatafilename := substrb(p_indatafilename, l_unixdir+1);
63 else
64 l_indatapathname := '';
65 l_indatafilename := p_indatafilename;
66 end if;
67 fnd_file.put_line(fnd_file.log, 'l_ntdir '|| to_char(l_ntdir));
68 fnd_file.put_line(fnd_file.log, 'l_unixdir '|| to_char(l_unixdir));
69 fnd_file.put_line(fnd_file.log, 'l_indatapathname '|| l_indatapathname);
70 fnd_file.put_line(fnd_file.log, 'l_indatafilename '|| l_indatafilename);
71
72 --
73 -- Parse the outdatafilename for the path and filename
74 --
75 fnd_file.put_line(fnd_file.log, 'Parsing p_outdatafilename '|| p_outdatafilename);
76 l_ntdir := instrb(p_outdatafilename, '\', -1);
77 l_unixdir := instrb(p_outdatafilename, '/', -1);
78 if (l_ntdir > 0) then
79 l_outdatapathname := substrb(p_outdatafilename, 0, l_ntdir-1);
80 l_outdatafilename := substrb(p_outdatafilename, l_ntdir+1);
81 elsif (l_unixdir > 0) then
82 l_outdatapathname := substrb(p_outdatafilename, 0, l_unixdir-1);
83 l_outdatafilename := substrb(p_outdatafilename, l_unixdir+1);
84 else
85 l_outdatapathname := '';
86 l_outdatafilename := p_outdatafilename;
87 end if;
88 fnd_file.put_line(fnd_file.log, 'l_ntdir '|| to_char(l_ntdir));
89 fnd_file.put_line(fnd_file.log, 'l_unixdir '|| to_char(l_unixdir));
90 fnd_file.put_line(fnd_file.log, 'l_outdatapathname '|| l_outdatapathname);
91 fnd_file.put_line(fnd_file.log, 'l_outdatafilename '|| l_outdatafilename);
92
93 --
94 -- Open the in/outdatafiles for read/write
95 --
96 fnd_file.put_line(fnd_file.log, 'Opening p_indatafilename '|| p_indatafilename);
97 l_indatafileptr := utl_file.fopen(l_indatapathname, l_indatafilename, 'r');
98 fnd_file.put_line(fnd_file.log, 'Opening p_outdatafilename '|| p_outdatafilename);
99 l_outdatafileptr := utl_file.fopen(l_outdatapathname, l_outdatafilename, 'w');
100
101 fnd_file.put_line(fnd_file.log, 'p_cardbrand '|| p_cardbrand);
102 l_numrecs := 0;
103 --
104 -- Preformat MasterCard by duplicating the addendum type which
105 -- is stored in position(44-46) in the transaction record
106 --
107 if (p_cardbrand = 'MasterCard') then
108 loop
109 begin
110 utl_file.get_line(l_indatafileptr, l_line);
111 utl_file.put_line(l_outdatafileptr, substrb(l_line, 0, 43) || substrb(l_line, 44, 3) || substrb(l_line, 44, 3) || substrb(l_line, 47));
112 l_numrecs := l_numrecs + 1;
113 exception
114 when no_data_found then
115 exit;
116 end;
117 end loop;
118 --
119 -- Preformat Visa by duplicating position(1-2) in the transaction record
120 --
121 elsif (p_cardbrand = 'Visa') then
122 loop
123 begin
124 utl_file.get_line(l_indatafileptr, l_line);
125 utl_file.put_line(l_outdatafileptr, substrb(l_line, 0, 2) || l_line);
126 l_numrecs := l_numrecs + 1;
127 exception
128 when no_data_found then
129 exit;
130 end;
131 end loop;
132 end if;
133 fnd_file.put_line(fnd_file.log, 'Number of records preformatted '|| to_char(l_numrecs));
134
135 --
136 -- Close the in/outdatafiles
137 --
138 fnd_file.put_line(fnd_file.log, 'Closing p_indatafilename '|| p_indatafilename);
139 utl_file.fclose(l_indatafileptr);
140 fnd_file.put_line(fnd_file.log, 'Closing p_outdatafilename '|| p_outdatafilename);
141 utl_file.fclose(l_outdatafileptr);
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 utl_file.fclose_all;
146 fnd_message.set_name('AK', 'AK_INVALID_FILE_OPERATION');
147 errbuf := fnd_message.get;
148 retcode := 2;
149
150 END Preformat;
151
152 END AP_CREDIT_CARD_TRXN_LOADER_PKG;