1 PACKAGE BODY PV_PRTNR_PMNT_TYPES_PVT as
2 /* $Header: pvxvptsb.pls 120.4.12000000.2 2007/06/12 19:48:38 dhii ship $ */
3 -- Start of Comments
4 -- Package name: PV_PRTNR_PMNT_TYPES_PVT
5 -- Purpose :
6 -- History :
7 -- 03-MAR-2003 sveerave changed lookup table from fnd_lookup_values to pv_lookups
8 -- for bug fix# 2829104.
9 -- jkylee added mode_type = 'PAYMENT' for PV_PROGRAM_PAYMENT_MODE for release12
10 -- 31-MAR-2005 pukken support for Wire Transfer Enhancement 4137727
11 -- kvattiku Aug 31, 2005 Made changes to VerifyPaymentTypes api. Takes and extra input parameter
12 -- p_credit_card_exists which would be used to validate if credit card is
13 -- enabled in payments view.
14 -- Also made changes to Get_prtnr_payment_types api. It wont include CREDIT_CARD
15 -- in the x_payment_type_tbl that gets passed to VerifyPaymentTypes. Its added to
16 -- the x_payment_type_tbl_out only if its enabled in oe_payment_types_vl and exists
17 -- in iby_fndcpt_all_pmt_channels_v (doing this as we create transaction extension
18 -- for just CREDIT_CARD type.
19 -- NOTE :
20 -- Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA
21 -- All rights reserved.
22 -- End of Comments
23
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'PV_PRTNR_PMNT_TYPES_PVT';
25 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxvptsb.pls';
26
27 PROCEDURE VerifyPaymentTypes
28 (
29 p_payment_type_tbl_in IN JTF_VARCHAR2_TABLE_200
30 , p_invoice IN VARCHAR2
31 , p_credit_card_exists IN VARCHAR2
32 , x_payment_type_tbl_out OUT NOCOPY JTF_VARCHAR2_TABLE_200
33 ) IS
34
35 l_oe_query_str VARCHAR2(3000);
36
37 --kvattiku Aug 31, 05
38 l_credit_card_code VARCHAR2(30);
39 l_credit_card_meaning VARCHAR2(30);
40 l_credit_card VARCHAR2(60);
41
42 TYPE csr_type IS REF CURSOR;
43 oe_csr csr_type ;
44
45 CURSOR x_enrq_param_cur( pymnt_type_tbl JTF_VARCHAR2_TABLE_200) IS
46 SELECT * FROM TABLE( CAST (pymnt_type_tbl AS JTF_VARCHAR2_TABLE_200)) order by column_value;
47
48 CURSOR c_get_credit_card_type IS
49 SELECT payment_type_code, name
50 FROM oe_payment_types_vl
51 WHERE payment_type_code = 'CREDIT_CARD'
52 AND NVL(start_date_active, sysdate) <= sysdate
53 AND NVL(end_date_active,sysdate+1) >= sysdate
54 AND enabled_flag='Y'
55 AND EXISTS(
56 SELECT payment_channel_code
57 FROM iby_fndcpt_all_pmt_channels_v
58 WHERE payment_channel_code = 'CREDIT_CARD'
59 );
60
61 BEGIN
62
63 x_payment_type_tbl_out := JTF_VARCHAR2_TABLE_200();
64
65 l_oe_query_str := 'SELECT name ||''%''||payment_type_code FROM oe_payment_types_vl WHERE payment_type_code IN ( SELECT * FROM TABLE (CAST(:1' || 'AS JTF_VARCHAR2_TABLE_200)))';
66 l_oe_query_str := l_oe_query_str || 'AND NVL(start_date_active, SYSDATE) <= SYSDATE AND NVL(end_date_active,sysdate+1)>=sysdate and enabled_flag=''Y''';
67
68 OPEN oe_csr FOR l_oe_query_str USING p_payment_type_tbl_in;
69 FETCH oe_csr BULK COLLECT INTO x_payment_type_tbl_out;
70 CLOSE oe_csr;
71
72 --kvattiku: Add the Invoice as its a new payment type
73 IF p_invoice IS NOT NULL THEN
74 x_payment_type_tbl_out.extend;
75 x_payment_type_tbl_out(x_payment_type_tbl_out.count) := p_invoice;
76 END IF;
77
78 --kvattiku Aug 31, 05
79 IF (p_credit_card_exists = 'Y') THEN
80 OPEN c_get_credit_card_type;
81 FETCH c_get_credit_card_type INTO l_credit_card_code, l_credit_card_meaning;
82 CLOSE c_get_credit_card_type;
83 END IF;
84
85 IF (l_credit_card_code = 'CREDIT_CARD') THEN
86 l_credit_card := l_credit_card_meaning || '%' || l_credit_card_code;
87 x_payment_type_tbl_out.extend;
88 x_payment_type_tbl_out(x_payment_type_tbl_out.count) := l_credit_card;
89 END IF;
90
91 OPEN x_enrq_param_cur(x_payment_type_tbl_out) ;
92 FETCH x_enrq_param_cur BULK COLLECT INTO x_payment_type_tbl_out ;
93 CLOSE x_enrq_param_cur;
94
95 END VerifyPaymentTypes;
96
97 PROCEDURE Get_prtnr_payment_types(
98 p_partner_party_id IN NUMBER
99 ,x_payment_type_tbl OUT NOCOPY JTF_VARCHAR2_TABLE_200
100 ,x_is_po_number_enabled OUT NOCOPY VARCHAR2
101 )
102 IS
103 l_api_name CONSTANT VARCHAR2(45) := 'Get_prtnr_payment_types';
104 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
105
106
107 CURSOR c_pmnt_geo_hierarchy_ids IS
108 SELECT geo_hierarchy_id
109 FROM PV_PROGRAM_PAYMENT_MODE
110 where program_id is null
111 and MODE_TYPE='PAYMENT'
112 group by geo_hierarchy_id;
113
114 --kvattiku: Aug 05, 05
115 CURSOR c_get_pmnt_modes(l_geo_hierarchy_Id NUMBER) IS
116 SELECT meaning, lookup_code, mode_of_payment
117 from pv_lookups l , PV_PROGRAM_PAYMENT_MODE p
118 where l.lookup_type(+) = 'PV_PAYMENT_TYPE'
119 and l.enabled_flag(+) = 'Y'
120 and l.lookup_code(+) = p.mode_of_payment
121 and NVL(l.start_date_active, SYSDATE) <= SYSDATE
122 and NVL(l.end_date_active, SYSDATE) >= SYSDATE
123 and p.program_id is null
124 and p.geo_hierarchy_id = l_geo_hierarchy_Id
125 order by meaning;
126
127
128 CURSOR c_get_all_pmnt_modes IS
129 --kvattiku: Aug 05, 05 Modified to check for only the active codes
130 SELECT meaning, lookup_code
131 from pv_lookups
132 where lookup_type = 'PV_PAYMENT_TYPE'
133 and enabled_flag = 'Y'
134 and NVL(start_date_active, SYSDATE) <= SYSDATE
135 and NVL(end_date_active, SYSDATE) >= SYSDATE
136 order by meaning;
137
138 l_geo_hierarchy_ids_tbl JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
139 l_geo_hierarchy_id NUMBER;
140 l_get_all_pmnt_types boolean := false;
141 l_msg_count number;
142 l_msg_data varchar2(200);
143 l_return_status VARCHAR2(1);
144
145 l_invoice VARCHAR2(200);
146 l_credit_card_exists VARCHAR2(1);
147 l_is_po_num_enabled VARCHAR2(1);
148 l_payment_type_tbl JTF_VARCHAR2_TABLE_200:= JTF_VARCHAR2_TABLE_200();
149
150
151 BEGIN
152
153 x_payment_type_tbl := JTF_VARCHAR2_TABLE_200();
154
155 for x in c_pmnt_geo_hierarchy_ids loop
156 l_geo_hierarchy_ids_tbl.extend;
157 l_geo_hierarchy_ids_tbl(l_geo_hierarchy_ids_tbl.count) := x.geo_hierarchy_id;
158 end loop;
159
160
161 IF l_geo_hierarchy_ids_tbl.count > 0 THEN
162
163 PV_Partner_Geo_Match_PVT.get_Matched_Geo_Hierarchy_Id(
164 p_api_version_number => 1.0
165 ,p_init_msg_list => FND_API.G_TRUE
166 ,x_return_status => l_return_status
167 ,x_msg_count => l_msg_count
168 ,x_msg_data => l_msg_Data
169 ,p_partner_party_id => p_partner_party_id
170 ,p_geo_hierarchy_id => l_geo_hierarchy_ids_tbl
171 ,x_geo_hierarchy_id => l_geo_hierarchy_id
172 );
173
174 IF l_return_Status <> FND_API.G_RET_STS_SUCCESS or l_geo_hierarchy_id is null THEN
175 l_get_all_pmnt_types := TRUE;
176 END IF;
177 ELSE
178 l_get_all_pmnt_types := TRUE;
179 END IF;
180
181
182 IF l_get_all_pmnt_types THEN
183 l_is_po_num_enabled := 'Y';
184 FOR x in c_get_all_pmnt_modes LOOP
185 --kvattiku: Replaced Purchase order with Invoice as PO is no longer a payment type
186 IF x.lookup_code='INVOICE' THEN
187 l_invoice :=x.meaning||'%'||x.lookup_code;
188 ELSIF x.lookup_code <> 'CREDIT_CARD' THEN
189 l_payment_type_tbl.extend;
190 l_payment_type_tbl(l_payment_type_tbl.count) := x.lookup_code;
191 ELSIF x.lookup_code = 'CREDIT_CARD' THEN
192 l_credit_card_exists := 'Y';
193 END IF;
194 END LOOP;
195 ELSE
196 FOR x in c_get_pmnt_modes(l_geo_hierarchy_Id) LOOP
197 --kvattiku: Replaced Purchase order with Invoice as PO is no longer a payment type
198 IF x.lookup_code='INVOICE' THEN
199 l_invoice :=x.meaning||'%'||x.lookup_code;
200 ELSIF x.mode_of_payment ='PO_NUM_ENABLED' THEN
201 l_is_po_num_enabled := 'Y';
202 ELSIF x.mode_of_payment ='PO_NUM_DISABLED' THEN
203 l_is_po_num_enabled := 'N';
204 ELSIF x.lookup_code <> 'CREDIT_CARD' THEN
205 l_payment_type_tbl.extend;
206 l_payment_type_tbl(l_payment_type_tbl.count) := x.lookup_code;
207 ELSIF x.lookup_code = 'CREDIT_CARD' THEN
208 l_credit_card_exists := 'Y';
209 END IF;
210 END LOOP;
211 END IF;
212
213 VerifyPaymentTypes(l_payment_type_tbl, l_invoice, l_credit_card_exists, x_payment_type_tbl);
214 x_is_po_number_enabled := l_is_po_num_enabled;
215
216 END Get_prtnr_payment_types;
217
218
219 END PV_PRTNR_PMNT_TYPES_PVT;
[Home] [Help]