[Home] [Help]
Skip to content
PACKAGE BODY: APPS.IBE_M_PRICING_PVT
Source
1 PACKAGE BODY Ibe_M_Pricing_Pvt AS
2 /* $Header: IBEVMPLB.pls 120.1.12020000.2 2012/10/29 06:02:01 amaheshw ship $ */
3
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- Ibe_M_Pricing_Pvt
10 --
11 -- PURPOSE
12 --
13 --
14 -- NOTES
15 --
16 -- HISTORY
17 -- 08/10/03 JQU Created
18 -- 10/Jan/11 amaheshw Bug 10217112
19 -- 29/Oct/12 ytian Bug 14789352
20 -- **************************************************************************
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_M_PRICING_PVT';
23 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVMPLB.pls';
24
25 -- bug 14789352
26 procedure parseInputTable (p_inTable IN QP_UTIL_PUB.PRICE_LIST_TBL,
27 p_Type IN VARCHAR2,
28 p_keyString IN VARCHAR2,
29 p_number IN NUMBER,
30 x_QueryString OUT NOCOPY VARCHAR2)
31 IS
32
33 l_id_str varchar2(2000);
34 begin
35
36
37
38 delete from IBE_TEMP_TABLE where key =p_keyString;
39
40 FOR i IN 1..p_inTable.COUNT LOOP
41 l_id_str := p_inTable(i).price_list_id;
42
43 INSERT into IBE_TEMP_TABLE (KEY, NUM_VAL) VALUES (p_keyString,to_number(l_id_str));
44
45 end loop;
46
47
48 x_QueryString := 'SELECT NUM_VAL FROM IBE_TEMP_TABLE WHERE KEY = :'||p_number||'';
49
50
51 exception
52 WHEN OTHERS then
53 -- printDebug('Exception.....'||sqlerrm,'parseInput');
54 Raise;
55 end parseInputTable;
56
57
58 --
59 -- Search price lists given the currency code and search criteria.
60 --
61 PROCEDURE Search_Price_List
62 (
63 p_currency_code IN VARCHAR2,
64 p_search_by IN NUMBER,
65 p_search_value IN VARCHAR2,
66 x_pricelist_csr OUT NOCOPY pricing_cur_type
67 )
68 IS
69 l_pricelist_id_tbl QP_UTIL_PUB.PRICE_LIST_TBL;
70 idx BINARY_INTEGER := 0;
71 l_stmt VARCHAR2(1000);
72 l_count NUMBER;
73 /* Commented and added by amaheshw on 10.jan.11 Bug 10217112
74 l_tmp_str VARCHAR2(2000);
75 l_query_str VARCHAR2(2000);
76 */
77 l_tmp_str VARCHAR2(4000);
78 l_query_str VARCHAR2(4000);
79 l_key_str VARCHAR2(15);
80 l_delete_status VARCHAR2(10);
81 -- l_pricelist_id_jtf_tbl JTF_NUMBER_TABLE;
82 BEGIN
83 l_tmp_str := '';
84 l_query_str := '';
85 l_key_str := 'PRC_LIST_IDS';
86
87 QP_UTIL_PUB.GET_PRICE_LIST(l_currency_code=>p_currency_code,
88 l_pricing_effective_date=>sysdate,
89 l_agreement_id=>null,
90 l_price_list_tbl=>l_pricelist_id_tbl);
91
92 l_count := l_pricelist_id_tbl.COUNT;
93
94 -- IF (l_count < 1000) THEN
95 /* bug 14789352
96 FOR i IN 1..l_pricelist_id_tbl.COUNT LOOP
97 IF (i = 1) THEN
98 l_tmp_str := l_tmp_str || l_pricelist_id_tbl(i).price_list_id;
99 ELSE
100 l_tmp_str := l_tmp_str || ',' || l_pricelist_id_tbl(i).price_list_id;
101 END IF;
102 END LOOP;
103
104 IBE_LEAD_IMPORT_PVT.parseInput(l_tmp_str, 'NUM',l_key_str,1,l_query_str);
105 */
106 parseInputTable(l_pricelist_id_tbl, 'NUM',l_key_str,1,l_query_str);
107 l_stmt := ' SELECT list_header_id, name, description, currency_code '||
108 ' FROM QP_LIST_HEADERS_V ' ||
109 ' WHERE list_header_id in ( ' || l_query_str || ')';
110
111 IF (p_search_by = 1) THEN
112 l_stmt := l_stmt || ' AND UPPER(name) like UPPER(:2) ';
113 ELSIF (p_search_by = 2) THEN
114 l_stmt := l_stmt || ' AND UPPER(description) like UPPER(:2) ';
115 END IF;
116
117 l_stmt := l_stmt || ' ORDER BY 2 ';
118 OPEN x_pricelist_csr FOR l_stmt using l_key_str,p_search_value;
119 /* ELSE
120 -- loop through PL/SQL table to construct JTF table
121
122 l_pricelist_id_jtf_tbl := JTF_NUMBER_TABLE();
123
124 FOR i in 1..l_pricelist_id_tbl.COUNT LOOP
125 l_pricelist_id_jtf_tbl.EXTEND();
126
127 l_pricelist_id_jtf_tbl(i) := l_pricelist_id_tbl(i);
128 END LOOP;
129
130 l_stmt := ' SELECT list_header_id, name, description, currency_code '||
131 ' FROM QP_LIST_HEADERS_V '||
132 ' WHERE list_header_id in ' ||
133 ' (SELECT T.column_value FROM TABLE(:price_list_id_tbl AS '||
134 ' JTF_NUMBER_TABLE) T WHERE T.column_value > 0 AND ' ||
135 ' T.column_value < 9.99E125)';
136
137 IF (p_search_by = 1) THEN
138 l_stmt := l_stmt || ' AND name like :1 ';
139
140 ELSIF (p_search_by = 2) THEN
141 l_stmt := l_stmt || ' AND description like :1 ' ;
142
143 END IF;
144
145 OPEN x_pricelist_csr FOR l_stmt USING l_pricelist_id_jtf_tbl;
146
147 END IF;
148 */
149
150 EXCEPTION
151 WHEN FND_API.G_EXC_ERROR THEN
152 IBE_UTIL.debug(' ERROR G_EXC_ERROR =' || FND_API.G_RET_STS_ERROR);
153
154 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
155 IBE_UTIL.debug(' ERROR G_RET_STS_UNEXP_ERROR =' || FND_API.G_RET_STS_UNEXP_ERROR);
156 WHEN OTHERS THEN
157 IBE_UTIL.debug(' ERROR G_RET_STS_UNEXP_ERROR =' || FND_API.G_RET_STS_UNEXP_ERROR);
158
159 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
160 FND_MESSAGE.Set_Token('ROUTINE', 'Search_Price_List');
161 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
162 FND_MESSAGE.Set_Token('REASON', SQLERRM);
163 FND_MSG_PUB.Add;
164
165 END Search_Price_List;
166 PROCEDURE Search_Currency
167 (
168 p_search_by IN VARCHAR2,
169 p_search_value IN VARCHAR2,
170 x_currency_csr OUT NOCOPY currency_cur_type
171 )
172 IS
173 l_query_statement VARCHAR2(1000);
174
175 BEGIN
176 QP_UTIL_PUB.GET_PRICE_LIST_CURRENCY(p_price_list_id => null,x_sql_string => l_query_statement);
177 if p_search_by = 'currName' then
178 open x_currency_csr FOR 'SELECT currency_code, name FROM('||l_query_statement||')WHERE LOWER(name) like :p_search_value' USING p_search_value;
179 else
180 open x_currency_csr FOR 'SELECT currency_code, name FROM(' ||l_query_statement|| ')WHERE LOWER(currency_code) like :p_search_value' USING p_search_value;
181 end if;
182 END Search_Currency;
183
184 END Ibe_M_Pricing_Pvt;