[Home] [Help]
PACKAGE BODY: APPS.PON_PRICE_ELEMENTS_PKG
Source
1 PACKAGE BODY PON_PRICE_ELEMENTS_PKG as
2 /* $Header: PONPETPB.pls 120.2 2006/04/13 13:09:47 sapandey noship $ */
3
4 -------------------------------------------------------------------------------
5 -------------------------- PACKAGE BODY --------------------------------------
6 -------------------------------------------------------------------------------
7
8 PROCEDURE insert_price_element(p_type_id IN NUMBER,
9 p_code IN VARCHAR2,
10 p_name IN VARCHAR2,
11 p_description IN VARCHAR2,
12 p_enabledFlag IN VARCHAR2,
13 p_partyId IN NUMBER,
14 p_source_language IN VARCHAR2,
15 p_pricingBasis IN VARCHAR2,
16 p_contactId IN NUMBER,
17 p_result OUT NOCOPY NUMBER,
18 p_err_code OUT NOCOPY VARCHAR2,
19 p_err_msg OUT NOCOPY VARCHAR2) IS
20
21 x_err_loc integer;
22 x_type_id number;
23 x_system_flag varchar2(1) := 'N';
24
25 BEGIN
26
27 x_err_loc := 100;
28
29 p_result := 0;
30
31
32 insert into pon_price_element_types_tl(PRICE_ELEMENT_TYPE_ID,
33 TRADING_PARTNER_ID,
34 NAME,
35 DESCRIPTION,
36 LANGUAGE,
37 SOURCE_LANG,
38 CREATION_DATE,
39 CREATED_BY,
40 LAST_UPDATE_DATE,
41 LAST_UPDATED_BY)
42 select p_type_id,
43 p_partyId,
44 p_name,
45 p_description,
46 a.language_code,
47 p_source_language,
48 sysdate,
49 p_contactId,
50 sysdate,
51 p_contactId
52 from fnd_languages a
53 where a.installed_flag in ('I', 'B');
54
55 x_err_loc := 300;
56
57
58 EXCEPTION
59 WHEN OTHERS THEN
60 p_result := 2;
61 p_err_msg := SQLERRM;
62 p_err_code := SQLCODE;
63
64 RAISE_APPLICATION_ERROR(-20000, 'Exception at PON_PRICE_ELEMENTS_PKG.insert_price_element('|| x_err_loc || '): ' || p_err_code || ' : ' || p_err_msg);
65
66
67 END;
68
69
70
71 PROCEDURE update_price_element(p_typeId IN NUMBER,
72 p_code IN VARCHAR2,
73 p_name IN VARCHAR2,
74 p_description IN VARCHAR2,
75 p_enabledFlag IN VARCHAR2,
76 p_partyId IN NUMBER,
77 p_language IN VARCHAR2,
78 p_pricingBasis IN VARCHAR2,
79 p_contactId IN NUMBER,
80 p_lastUpdate IN DATE,
81 p_result OUT NOCOPY NUMBER,
82 p_err_code OUT NOCOPY VARCHAR2,
83 p_err_msg OUT NOCOPY VARCHAR2) IS
84
85 x_updated varchar2(1) := 'N';
86 x_zero_rows exception;
87 x_err_loc integer;
88
89 BEGIN
90
91 x_err_loc := 100;
92 p_result := 0;
93
94
95 begin
96 select 'Y'
97 into x_updated
98 from pon_price_element_types
99 where price_element_type_id = p_typeId
100 and last_update_date <> p_lastUpdate;
101
102 p_result := 1;
103 p_err_code := 'PON_AUC_PRC_ELMNT_UPDT';
104
105 exception
106 when no_data_found then
107 x_err_loc := 101;
108 end;
109
110 if(p_result = 0) then
111 x_err_loc := 200;
112
113
114 update pon_price_element_types_tl
115 set
116 name = p_name,
117 description = p_description,
118 source_lang = p_language,
119 last_updated_by = p_contactId,
120 last_update_date = sysdate
121 where
122 price_element_type_id = p_typeId and
123 language = p_language;
124
125 x_err_loc := 400;
126
127 end if;
128
129 EXCEPTION
130
131 WHEN OTHERS THEN
132 p_result := 2;
133 p_err_msg := SQLERRM;
134 p_err_code := SQLCODE;
135 RAISE_APPLICATION_ERROR(-20000, 'Exception at PON_PRICE_ELEMENTS_PKG.update_price_element('|| x_err_loc || '): ' || p_err_code || ' : ' || p_err_msg);
136
137
138 END;
139
140 -- ======================================================================
141 -- PROCEDURE : ADD_LANGUAGE
142 -- COMMENT : Used to popluate the PON_PRICE_ELEMENT_TYPES_TL table when
143 -- a new language is added. It is called from sql/PONNLINS.sql
144 -- ======================================================================
145 PROCEDURE ADD_LANGUAGE
146 IS
147
148 BEGIN
149
150 --
151 -- Get the current language and try to insert a row
152 -- in the PON_PRICE_ELEMENT_TYPES_TL table if not
153 -- existing
154 --
155
156 INSERT INTO
157 PON_PRICE_ELEMENT_TYPES_TL (
158 PRICE_ELEMENT_TYPE_ID,
159 TRADING_PARTNER_ID,
160 NAME,
161 DESCRIPTION,
162 LANGUAGE,
163 SOURCE_LANG,
164 CREATION_DATE,
165 CREATED_BY,
166 LAST_UPDATE_DATE,
167 LAST_UPDATED_BY
168 )
169 SELECT
170 pe.PRICE_ELEMENT_TYPE_ID,
171 pe.TRADING_PARTNER_ID,
172 pe.NAME,
173 pe.DESCRIPTION,
174 lang.language_code,
175 pe.SOURCE_LANG,
176 sysdate,
177 pe.CREATED_BY,
178 sysdate,
179 pe.LAST_UPDATED_BY
180 FROM PON_PRICE_ELEMENT_TYPES_TL pe,
181 FND_LANGUAGES lang
182 WHERE pe.LANGUAGE = USERENV('LANG')
183 AND lang.INSTALLED_FLAG IN ('I','B')
184 AND NOT EXISTS (SELECT 'x'
185 FROM PON_PRICE_ELEMENT_TYPES_TL pe2
186 WHERE pe2.PRICE_ELEMENT_TYPE_ID = pe.PRICE_ELEMENT_TYPE_ID
187 AND pe2.language = lang.language_code);
188
189 END ADD_LANGUAGE;
190
191 END PON_PRICE_ELEMENTS_PKG;