eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.QP_PRICE_BOOK_HEADERS_PKG

Source


1 package body QP_PRICE_BOOK_HEADERS_PKG as
2 /* $Header: QPXUPBHB.pls 120.0 2005/10/27 04:07:52 nirmkuma noship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_PRICE_BOOK_HEADER_ID in NUMBER,
6   X_PRICE_BOOK_TYPE_CODE in VARCHAR2,
7   X_CURRENCY_CODE in VARCHAR2,
8   X_EFFECTIVE_DATE in DATE,
9   X_CUSTOMER_ID in VARCHAR2,
10   X_PUB_STATUS_CODE in VARCHAR2,
11   X_ITEM_CATEGORY in NUMBER,
12   X_PRICE_BASED_ON in VARCHAR2,
13   X_PL_AGR_BSA_ID in NUMBER,
14   X_PRICING_PERSPECTIVE_CODE in VARCHAR2,
15   X_ITEM_QUANTITY in NUMBER,
16   X_REQUEST_ID in NUMBER,
17   X_REQUEST_TYPE_CODE in VARCHAR2,
18   X_PB_INPUT_HEADER_ID in NUMBER,
19   X_PRICE_BOOK_NAME in VARCHAR2,
20   X_PL_AGR_BSA_NAME in VARCHAR2,
21   X_LANGUAGE in VARCHAR2,
22   X_CREATION_DATE in DATE,
23   X_CREATED_BY in NUMBER,
24   X_LAST_UPDATE_DATE in DATE,
25   X_LAST_UPDATED_BY in NUMBER,
26   X_LAST_UPDATE_LOGIN in NUMBER
27 ) is
28   cursor C is select ROWID from QP_PRICE_BOOK_HEADERS_ALL_B
29     where PRICE_BOOK_HEADER_ID = X_PRICE_BOOK_HEADER_ID
30     ;
31 begin
32   insert into QP_PRICE_BOOK_HEADERS_ALL_B (
33     PRICE_BOOK_TYPE_CODE,
34     CURRENCY_CODE,
35     EFFECTIVE_DATE,
36     CUSTOMER_ID,
37     PUB_STATUS_CODE,
38     ITEM_CATEGORY,
39     PRICE_BASED_ON,
40     PL_AGR_BSA_ID,
41     PRICING_PERSPECTIVE_CODE,
42     ITEM_QUANTITY,
43     REQUEST_ID,
44     REQUEST_TYPE_CODE,
45     PB_INPUT_HEADER_ID,
46     PRICE_BOOK_HEADER_ID ,
47     CREATION_DATE,
48     CREATED_BY,
49     LAST_UPDATE_DATE,
50     LAST_UPDATED_BY,
51     LAST_UPDATE_LOGIN
52   ) values (
53     X_PRICE_BOOK_TYPE_CODE,
54     X_CURRENCY_CODE,
55     X_EFFECTIVE_DATE,
56     X_CUSTOMER_ID,
57     X_PUB_STATUS_CODE,
58     X_ITEM_CATEGORY,
59     X_PRICE_BASED_ON,
60     X_PL_AGR_BSA_ID,
61     X_PRICING_PERSPECTIVE_CODE,
62     X_ITEM_QUANTITY,
63     X_REQUEST_ID,
64     X_REQUEST_TYPE_CODE,
65     X_PB_INPUT_HEADER_ID,
66     X_PRICE_BOOK_HEADER_ID ,
67     X_CREATION_DATE,
68     X_CREATED_BY,
69     X_LAST_UPDATE_DATE,
70     X_LAST_UPDATED_BY,
71     X_LAST_UPDATE_LOGIN
72   );
73 
74   insert into QP_PRICE_BOOK_HEADERS_TL (
75     PRICE_BOOK_HEADER_ID,
76     PRICE_BOOK_NAME,
77     PL_AGR_BSA_NAME,
78     CREATION_DATE,
79     CREATED_BY,
80     LAST_UPDATE_DATE,
81     LAST_UPDATED_BY,
82     LAST_UPDATE_LOGIN,
83     LANGUAGE,
84     SOURCE_LANG
85   ) select
86     X_PRICE_BOOK_HEADER_ID,
87     X_PRICE_BOOK_NAME,
88     X_PL_AGR_BSA_NAME,
89     X_CREATION_DATE,
90     X_CREATED_BY,
91     X_LAST_UPDATE_DATE,
92     X_LAST_UPDATED_BY,
93     X_LAST_UPDATE_LOGIN,
94     L.LANGUAGE_CODE,
95     userenv('LANG')
96   from FND_LANGUAGES L
97   where L.INSTALLED_FLAG in ('I', 'B')
98   and not exists
99     (select NULL
100     from QP_PRICE_BOOK_HEADERS_TL T
101     where T.PRICE_BOOK_HEADER_ID = X_PRICE_BOOK_HEADER_ID
102     and T.LANGUAGE = L.LANGUAGE_CODE);
103 
104   open c;
105   fetch c into X_ROWID;
106   if (c%notfound) then
107     close c;
108     raise no_data_found;
109   end if;
110   close c;
111 
112 end INSERT_ROW;
113 
114 procedure LOCK_ROW (
115   X_PRICE_BOOK_HEADER_ID in NUMBER,
116   X_PRICE_BOOK_TYPE_CODE in VARCHAR2,
117   X_CURRENCY_CODE in VARCHAR2,
118   X_EFFECTIVE_DATE in DATE,
119   X_CUSTOMER_ID in VARCHAR2,
120   X_PUB_STATUS_CODE in VARCHAR2,
121   X_ITEM_CATEGORY in NUMBER,
122   X_PRICE_BASED_ON in VARCHAR2,
123   X_PL_AGR_BSA_ID in NUMBER,
124   X_PRICING_PERSPECTIVE_CODE in VARCHAR2,
125   X_ITEM_QUANTITY in NUMBER,
126   X_REQUEST_ID in NUMBER,
127   X_REQUEST_TYPE_CODE in VARCHAR2,
128   X_PB_INPUT_HEADER_ID in NUMBER,
129   X_PRICE_BOOK_NAME in VARCHAR2,
130    X_PL_AGR_BSA_NAME in VARCHAR2,
131   X_LANGUAGE in VARCHAR2
132 ) is
133   cursor c is select
134       PRICE_BOOK_TYPE_CODE,
135       CURRENCY_CODE,
136       EFFECTIVE_DATE,
137       CUSTOMER_ID,
138       PUB_STATUS_CODE,
139       ITEM_CATEGORY,
140       PRICE_BASED_ON,
141       PL_AGR_BSA_ID,
142       PRICING_PERSPECTIVE_CODE,
143       ITEM_QUANTITY,
144       REQUEST_ID,
145       REQUEST_TYPE_CODE,
146       PB_INPUT_HEADER_ID
147     from QP_PRICE_BOOK_HEADERS_ALL_B
148     where PRICE_BOOK_HEADER_ID = X_PRICE_BOOK_HEADER_ID
149     for update of PRICE_BOOK_HEADER_ID nowait;
150   recinfo c%rowtype;
151 
152   cursor c1 is select
153        PRICE_BOOK_NAME,
154       PL_AGR_BSA_NAME,
155     --  PRICE_BOOK_HEADER_ID,
156       LANGUAGE,
157       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
158     from QP_PRICE_BOOK_HEADERS_TL
159     where PRICE_BOOK_HEADER_ID = X_PRICE_BOOK_HEADER_ID
160     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
161     for update of PRICE_BOOK_HEADER_ID nowait;
162 begin
163   open c;
164   fetch c into recinfo;
165   if (c%notfound) then
166     close c;
167     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
168     app_exception.raise_exception;
169   end if;
170   close c;
171   if (    (recinfo.PRICE_BOOK_TYPE_CODE = X_PRICE_BOOK_TYPE_CODE)
172       AND (recinfo.CURRENCY_CODE = X_CURRENCY_CODE)
173       AND ((recinfo.EFFECTIVE_DATE = X_EFFECTIVE_DATE)
174            OR ((recinfo.EFFECTIVE_DATE is null) AND (X_EFFECTIVE_DATE is null)))
175       AND (recinfo.CUSTOMER_ID = X_CUSTOMER_ID)
176       AND ((recinfo.PUB_STATUS_CODE = X_PUB_STATUS_CODE)
177            OR ((recinfo.PUB_STATUS_CODE is null) AND (X_PUB_STATUS_CODE is null)))
178       AND ((recinfo.ITEM_CATEGORY = X_ITEM_CATEGORY)
179            OR ((recinfo.ITEM_CATEGORY is null) AND (X_ITEM_CATEGORY is null)))
180       AND ((recinfo.PRICE_BASED_ON = X_PRICE_BASED_ON)
181            OR ((recinfo.PRICE_BASED_ON is null) AND (X_PRICE_BASED_ON is null)))
182       AND ((recinfo.PL_AGR_BSA_ID = X_PL_AGR_BSA_ID)
183            OR ((recinfo.PL_AGR_BSA_ID is null) AND (X_PL_AGR_BSA_ID is null)))
184       AND (recinfo.PRICING_PERSPECTIVE_CODE = X_PRICING_PERSPECTIVE_CODE)
185       AND ((recinfo.ITEM_QUANTITY = X_ITEM_QUANTITY)
186            OR ((recinfo.ITEM_QUANTITY is null) AND (X_ITEM_QUANTITY is null)))
187       AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
188            OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
189       AND (recinfo.REQUEST_TYPE_CODE = X_REQUEST_TYPE_CODE)
190       AND (recinfo.PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID)
191   ) then
192     null;
193   else
194     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
195     app_exception.raise_exception;
196   end if;
197 
198   for tlinfo in c1 loop
199     if (tlinfo.BASELANG = 'Y') then
200          if (tlinfo.PRICE_BOOK_NAME = X_PRICE_BOOK_NAME) then
201         null;
202       else
203         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
204         app_exception.raise_exception;
205       end if;
206     end if;
207   end loop;
208   return;
209 end LOCK_ROW;
210 
211 procedure UPDATE_ROW (
212   X_PRICE_BOOK_HEADER_ID in NUMBER,
213   X_PRICE_BOOK_TYPE_CODE in VARCHAR2,
214   X_CURRENCY_CODE in VARCHAR2,
215   X_EFFECTIVE_DATE in DATE,
216   X_CUSTOMER_ID in VARCHAR2,
217   X_PUB_STATUS_CODE in VARCHAR2,
218   X_ITEM_CATEGORY in NUMBER,
219   X_PRICE_BASED_ON in VARCHAR2,
220   X_PL_AGR_BSA_ID in NUMBER,
221   X_PRICING_PERSPECTIVE_CODE in VARCHAR2,
222   X_ITEM_QUANTITY in NUMBER,
223   X_REQUEST_ID in NUMBER,
224   X_REQUEST_TYPE_CODE in VARCHAR2,
225   X_PB_INPUT_HEADER_ID in NUMBER,
226   X_PRICE_BOOK_NAME in VARCHAR2,
227    X_PL_AGR_BSA_NAME in VARCHAR2,
228   X_LANGUAGE in VARCHAR2,
229   X_LAST_UPDATE_DATE in DATE,
230   X_LAST_UPDATED_BY in NUMBER,
231   X_LAST_UPDATE_LOGIN in NUMBER
232 ) is
233 begin
234   update QP_PRICE_BOOK_HEADERS_ALL_B set
235     PRICE_BOOK_TYPE_CODE = X_PRICE_BOOK_TYPE_CODE,
236     CURRENCY_CODE = X_CURRENCY_CODE,
237     EFFECTIVE_DATE = X_EFFECTIVE_DATE,
238     CUSTOMER_ID = X_CUSTOMER_ID,
239     PUB_STATUS_CODE = X_PUB_STATUS_CODE,
240     ITEM_CATEGORY = X_ITEM_CATEGORY,
241     PRICE_BASED_ON = X_PRICE_BASED_ON,
242     PL_AGR_BSA_ID = X_PL_AGR_BSA_ID,
243     PRICING_PERSPECTIVE_CODE = X_PRICING_PERSPECTIVE_CODE,
244     ITEM_QUANTITY = X_ITEM_QUANTITY,
245     REQUEST_ID = X_REQUEST_ID,
246     REQUEST_TYPE_CODE = X_REQUEST_TYPE_CODE,
247     PB_INPUT_HEADER_ID = X_PB_INPUT_HEADER_ID,
248     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
249     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
250     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
251   where PRICE_BOOK_HEADER_ID = X_PRICE_BOOK_HEADER_ID;
252 
253   if (sql%notfound) then
254     raise no_data_found;
255   end if;
256 
257   update QP_PRICE_BOOK_HEADERS_TL set
258     PRICE_BOOK_NAME = X_PRICE_BOOK_NAME,
259     PL_AGR_BSA_NAME = X_PL_AGR_BSA_NAME,
260     PRICE_BOOK_HEADER_ID = X_PRICE_BOOK_HEADER_ID,
261     LANGUAGE = X_LANGUAGE,
262     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
263     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
264     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
265     SOURCE_LANG = userenv('LANG')
266   where PRICE_BOOK_HEADER_ID = X_PRICE_BOOK_HEADER_ID
267   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
268 
269   if (sql%notfound) then
270     raise no_data_found;
271   end if;
272 end UPDATE_ROW;
273 
274 procedure DELETE_ROW (
275   X_PRICE_BOOK_HEADER_ID in NUMBER
276 ) is
277 begin
278   delete from QP_PRICE_BOOK_HEADERS_TL
279   where PRICE_BOOK_HEADER_ID = X_PRICE_BOOK_HEADER_ID;
280 
281   if (sql%notfound) then
282     raise no_data_found;
283   end if;
284 
285   delete from QP_PRICE_BOOK_HEADERS_ALL_B
286   where PRICE_BOOK_HEADER_ID = X_PRICE_BOOK_HEADER_ID;
287 
288   if (sql%notfound) then
289     raise no_data_found;
290   end if;
291 end DELETE_ROW;
292 
293 procedure ADD_LANGUAGE
294 is
295 begin
296   delete from QP_PRICE_BOOK_HEADERS_TL T
297   where not exists
298     (select NULL
299     from QP_PRICE_BOOK_HEADERS_ALL_B B
300     where B.PRICE_BOOK_HEADER_ID = T.PRICE_BOOK_HEADER_ID
301     );
302 
303   update QP_PRICE_BOOK_HEADERS_TL T set (
304        PRICE_BOOK_NAME,
305        PL_AGR_BSA_NAME
306     ) = (select
307           B.PRICE_BOOK_NAME,
308           B.PL_AGR_BSA_NAME
309     from QP_PRICE_BOOK_HEADERS_TL B
310     where B.PRICE_BOOK_HEADER_ID = T.PRICE_BOOK_HEADER_ID
311     and B.LANGUAGE = T.SOURCE_LANG)
312   where (
313       T.PRICE_BOOK_HEADER_ID,
314       T.LANGUAGE
315   ) in (select
316       SUBT.PRICE_BOOK_HEADER_ID,
317       SUBT.LANGUAGE
318     from QP_PRICE_BOOK_HEADERS_TL SUBB, QP_PRICE_BOOK_HEADERS_TL SUBT
319     where SUBB.PRICE_BOOK_HEADER_ID = SUBT.PRICE_BOOK_HEADER_ID
320     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
321     and (SUBB.PRICE_BOOK_NAME <> SUBT.PRICE_BOOK_NAME
322       or SUBB.PL_AGR_BSA_NAME <> SUBT.PL_AGR_BSA_NAME
323       or (SUBB.PL_AGR_BSA_NAME is null and SUBT.PL_AGR_BSA_NAME is not null)
324       or (SUBB.PL_AGR_BSA_NAME is not null and SUBT.PL_AGR_BSA_NAME is null)
325   ));
326 
327   insert into QP_PRICE_BOOK_HEADERS_TL (
328     PRICE_BOOK_HEADER_ID,
329     PRICE_BOOK_NAME,
330     PL_AGR_BSA_NAME,
331     CREATION_DATE,
332     CREATED_BY,
333     LAST_UPDATE_DATE,
334     LAST_UPDATED_BY,
335     LAST_UPDATE_LOGIN,
336     LANGUAGE,
337     SOURCE_LANG
338   ) select /*+ ORDERED */
339     B.PRICE_BOOK_HEADER_ID,
340     B.PRICE_BOOK_NAME,
341     B.PL_AGR_BSA_NAME,
342     B.CREATION_DATE,
343     B.CREATED_BY,
344     B.LAST_UPDATE_DATE,
345     B.LAST_UPDATED_BY,
346     B.LAST_UPDATE_LOGIN,
347     L.LANGUAGE_CODE,
348     B.SOURCE_LANG
349   from QP_PRICE_BOOK_HEADERS_TL B, FND_LANGUAGES L
350   where L.INSTALLED_FLAG in ('I', 'B')
351   and B.LANGUAGE = userenv('LANG')
352   and not exists
353     (select NULL
354     from QP_PRICE_BOOK_HEADERS_TL T
355     where T.PRICE_BOOK_HEADER_ID = B.PRICE_BOOK_HEADER_ID
356     and T.LANGUAGE = L.LANGUAGE_CODE);
357 end ADD_LANGUAGE;
358 
359 end QP_PRICE_BOOK_HEADERS_PKG;