1 package body iby_pmtschemes_pkg as
2 /*$Header: ibypmscb.pls 120.2 2005/10/30 05:48:54 appldev ship $*/
3
4 /*
5 ** Procedure: checkPmtSchemes.
6 ** Purpose: get payment scheme id based on scheme name,
7 ** from fnd_lookup table, raise exception
8 ** if scheme doesn't exist
9 */
10 procedure checkPmtScheme( i_pmtschemename in
11 iby_pmtschemes.pmtschemename%type,
12 io_pmtschemeid in out nocopy
13 iby_pmtschemes.pmtschemeid%type)
14 is
15
16 /*
17 l_pmtschemeid iby_pmtschemes.pmtschemeid%type;
18 cursor c_get_pmtschemeid(ci_pmtschemename
19 iby_pmtschemes.pmtschemename%type)
20 is
21 SELECT lookup_code
22 FROM fnd_lookups
23 WHERE lookup_type = 'IBY_PMTSCHEMES'
24 AND meaning = ci_pmtschemename;
25 */
26
27 begin
28 -- new code, hardcoded version
29
30 IF (i_pmtschemename = 'SSL') THEN
31 io_pmtschemeid := 2;
32 ELSIF (i_pmtschemename = 'BANKACCOUNT') THEN
33 io_pmtschemeid := 4;
34 ELSIF (i_pmtschemename = 'PURCHASECARD') THEN
35 io_pmtschemeid := 5;
36 ELSIF (i_pmtschemename = 'FINANCING') THEN
37 io_pmtschemeid := 6;
38 ELSIF (i_pmtschemename = 'BANKPAYMENT') THEN
39 io_pmtschemeid := 7;
40 ELSIF (i_pmtschemename = 'PINLESSDEBITCARD') THEN
41 io_pmtschemeid := 8;
42 ELSIF (i_pmtschemename = 'SET') THEN -- keep SET here for now
43 io_pmtschemeid := 1;
44 ELSE
45 -- FI no longer supported
46 raise_application_error(-20000, 'IBY_20570#', FALSE);
47 END IF;
48
49 /*
50 ** close the cursor if it is already open.
51 */
52 /*
53 if ( c_get_pmtschemeid%isopen ) then
54 close c_get_pmtschemeid;
55 end if;
56 */
57 /*
58 ** open the cursor and check if the corresponding name exists in the
59 ** database.
60 */
61 /*
62 open c_get_pmtschemeid(i_pmtschemename);
63 fetch c_get_pmtschemeid into l_pmtschemeid;
64 if ( c_get_pmtschemeid%notfound ) then
65 raise_application_error(-20000, 'IBY_20570#', FALSE);
66 --raise_application_error(-20570, 'Payment Scheme is not
67 --Defined',FALSE);
68 end if;
69 io_pmtschemeid := l_pmtschemeid;
70 close c_get_pmtschemeid;
71 */
72 --commit;
73 end checkPmtScheme;
74
75 /* Procedure: getPmtSchemeName
76 ** Purpose: return pmt scheme name based on bepid
77 ** for single entry, whatever in the table
78 ** for double entry, return 'BOTH' ('SSL' and 'BANKACCOUNT')
79 */
80 procedure getPmtSchemeName(i_bepid in iby_pmtschemes.bepid%type,
81 o_pmtschemeName out nocopy JTF_VARCHAR2_TABLE_100)
82 --o_pmtschemename out nocopy iby_pmtschemes.pmtschemename%type)
83 IS
84
85 CURSOR c_get_pmtschemeName(ci_bepid in iby_pmtschemes.bepid%type)
86 IS
87 SELECT pmtschemename
88 FROM iby_pmtschemes
89 WHERE bepid = i_bepid;
90
91 l_index number;
92 l_pmtschemename varchar(30);
93
94 BEGIN
95 if (c_get_pmtschemeName%isopen) then
96 close c_get_pmtschemeName;
97 end if;
98
99 open c_get_pmtschemeName(i_bepid);
100
101 if (c_get_pmtschemeName%notfound) then
102 -- should never happen, if called from iby_bepinfo_pkg
103 -- no row matches, invalid bepid or object version number
104 close c_get_pmtschemeName;
105 raise_application_error(-20000, 'IBY_20521#', FALSE);
106 end if;
107
108 o_pmtschemeName := JTF_VARCHAR2_TABLE_100();
109 --o_pmtschemeName.extend(c_get_pmtschemeName%count);
110
111 --dbms_output.put_line('rowcount is ' || c_get_pmtschemeName%rowcount);
112
113 l_index := 1;
114
115 Loop
116 o_pmtschemeName.extend(1);
117 --dbms_output.put_line('l_index is ' || l_index);
118 fetch c_get_pmtschemename into l_pmtschemename;
119 --dbms_output.put_line('l_pmtschemename is ' || l_pmtschemename);
120 o_pmtschemename(l_index) := l_pmtschemename;
121 l_index := l_index + 1;
122 exit when c_get_pmtschemename%notfound;
123 END LOOP;
124 close c_get_pmtschemeName;
125
126 end getPmtSchemeName;
127
128
129
130 /* Procedure: createPmtScheme
131 ** Purpose: replace whatever previous existing pmtscheme with new ones
132 ** for a given bepid
133 */
134 procedure createPmtScheme(i_bepid in iby_pmtschemes.bepid%type,
135 i_pmtschemeName in JTF_VARCHAR2_TABLE_100)
136 IS
137
138 l_count number;
139 l_pmtschemeid number;
140
141 BEGIN
142 DELETE FROM iby_pmtschemes
143 WHERE bepid = i_bepid;
144
145 for l_count in 1..i_pmtschemeName.count LOOP
146 -- get pmtschemeid
147 checkPmtScheme( i_pmtschemename(l_count), l_pmtschemeid);
148
149 -- insertion
150 INSERT INTO iby_pmtschemes
151 (bepid, pmtschemeid, pmtschemename,
152 last_update_date, last_updated_by, creation_date,
153 created_by, last_update_login, object_version_number)
154 VALUES (i_bepid, l_pmtschemeid, i_pmtschemename(l_count),
155 sysdate, fnd_global.user_id, sysdate,
156 fnd_global.user_id, fnd_global.login_id, 1);
157 END LOOP;
158
159 end createPmtScheme;
160
161 end iby_pmtschemes_pkg;
[Home] [Help]