[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_COM_C
Source
1 PACKAGE BODY POA_DBI_COM_C AS
2 /* $Header: poadbicomcrb.pls 120.2 2008/02/25 10:07:20 nchava noship $ */
3
4 /* ***************************************************************************
5 * Procedure Name : proc_commodity_check *
6 * Description : Procedure to determine whether the commodity exists *
7 * or not *
8 * File Name : poadbicomcrb.pls *
9 * Visibility : Public *
10 * Parameters/Mode : None *
11 * History : 14-Nov-2006 ANKGOYAL Initial Creation *
12 * *
13 *************************************************************************** */
14
15
16 PROCEDURE proc_commodity_check (
17 errbuf OUT NOCOPY VARCHAR2,
18 retcode OUT NOCOPY NUMBER
19 )
20 IS
21 l_start_time DATE;
22 l_login NUMBER;
23 l_user NUMBER;
24 v_commodity_code varchar2(3000);
25 v_check_flag INTEGER :=0;
26 v_nextval NUMBER;
27
28 BEGIN
29 -- Get the value of new commodity code into the variable v_commodity_code
30 v_commodity_code := fnd_message.get_string('POA','POA_DBI_DEFAULT_COMMODITY');
31 bis_collection_utilities.log('Retrieved commodity code as "' || v_commodity_code||'"',0);
32 ----Extract 40 characters and Trim the spaces
33 v_commodity_code := trim(substr(v_commodity_code,1,40));
34 bis_collection_utilities.log('Truncated commodity code is "'|| v_commodity_code||'"',0);
35 -- Check if the commodity exists or not
36 SELECT COUNT(*) INTO v_check_flag FROM
37 po_commodities_b WHERE commodity_code=v_commodity_code;
38 IF (v_check_flag=0) THEN
39
40 -- Get the values for the WHO columns
41 l_start_time := SYSDATE;
42 l_login := fnd_global.login_id;
43 l_user := fnd_global.user_id;
44
45 --Get the commodity id in the variable
46 SELECT po_commodities_s.NEXTVAL INTO v_nextval FROM dual;
47 --Insert into the table po_commodities_b
48 INSERT INTO po_commodities_b (
49 commodity_id,
50 commodity_code,
51 active_flag,
52 creation_date,
53 last_update_date,
54 created_by,
55 last_updated_by,
56 last_update_login )
57 VALUES
58 (
59 v_nextval,
60 v_commodity_code,
61 'Y',
62 l_start_time,
63 l_start_time,
64 l_user,
65 l_user,
66 l_login
67 );
68
69 -- Insert into the table po_commodities_tl
70 INSERT INTO po_commodities_tl (
71 commodity_id,
72 language,
73 source_lang,
74 name,
75 description,
76 creation_date,
77 created_by,
78 last_update_date,
79 last_updated_by,
80 last_update_login)
81 (
82 SELECT
83 v_nextval commodity_id,
84 lang.language_code language,
85 userenv('LANG') source_lang,
86 v_commodity_code name,
87 v_commodity_code description,
88 l_start_time creation_date,
89 l_user created_by,
90 l_start_time last_update_date,
91 l_user last_updated_by,
92 l_login last_update_login
93 FROM
94 fnd_languages lang
95 WHERE
96 lang.installed_flag IN ('I','B')
97 );
98
99 COMMIT;
100 bis_collection_utilities.log('Commodity Created',0);
101
102 END IF;
103 -- Calling procedure to assign all unassigned categories of the
104 -- Purchasing category set to the default commodity
105
106 proc_category_commodity_update(errbuf,retcode);
107
108 EXCEPTION
109
110 WHEN OTHERS THEN
111 Errbuf:= Sqlerrm;
112 Retcode:=sqlcode;
113 ROLLBACK;
114 POA_LOG.debug_line('proc_commodity_check' || Sqlerrm || sqlcode || sysdate);
115 RAISE_APPLICATION_ERROR(-20000,'Stack Dump Follows =>', true);
116
117 END proc_commodity_check;
118
119
120 /****************************************************************************
121 * End of Procedure : proc_commodity_check
122 *****************************************************************************/
123
124
125 /****************************************************************************
126 * Procedure Name : proc_category_commodity_update *
127 * Description : Procedureto to update the categories *
128 * File Name : poadbicomcrb.pls *
129 * Visibility : Public *
130 * Parameters/Mode : None *
131 * History : 14-Nov-2006 ANKGOYAL Initial Creation *
132 * *
133 ****************************************************************************/
134 /* Procedureto to update the categories */
135
136 PROCEDURE proc_category_commodity_update (errbuf OUT NOCOPY VARCHAR2,
137 retcode OUT NOCOPY NUMBER)
138 IS
139 l_start_time DATE;
140 l_login NUMBER;
141 l_user NUMBER;
142 -- Variable for storing the new commodity code.
143 v_commodity_code varchar2(3000);
144 -- Variable for storing the new commodity id
145 v_commodity_id po_commodities_b.commodity_id%type;
146
147 BEGIN
148 -- Get the values in the WHO columns
149 l_start_time := sysdate;
150 l_login := fnd_global.login_id;
151 l_user := fnd_global.user_id;
152 --Get the value of new commodity code into the variable v_commodity_code
153 v_commodity_code := fnd_message.get_string('POA','POA_DBI_DEFAULT_COMMODITY');
154 --Extract 40 characters and Trim the spaces
155 v_commodity_code := trim(substr(v_commodity_code,1,40));
156 --Store the commodity id into the variable
157 SELECT commodity_id INTO v_commodity_id from po_commodities_b
158 WHERE commodity_code=v_commodity_code;
159
160 -- Insert Record in the table
161 INSERT INTO po_commodity_categories (
162 commodity_id,
163 category_id,
164 creation_date,
165 created_by,
166 last_update_date,
167 last_updated_by,
168 last_update_login)
169 (
170 SELECT
171 v_commodity_id commodity_id,
172 mcat.category_id category_id,
173 l_start_time creation_date,
174 l_user created_by,
175 l_start_time last_update_date,
176 l_user last_updated_by,
177 l_login last_update_login
178 FROM
179 mtl_category_set_valid_cats mcat
180 WHERE
181 mcat.category_set_id = 2 -- Consider only categories of the purchasing category set
182 AND NOT EXISTS ( SELECT 1 FROM po_commodity_categories pcc
183 WHERE pcc.category_id=mcat.category_id)
184 );
185
186
187 bis_collection_utilities.log(SQL%ROWCOUNT||' Categories associated with '
188 ||v_commodity_code,0);
189 COMMIT;
190
191 EXCEPTION
192 WHEN OTHERS THEN
193 Errbuf:= Sqlerrm;
194 Retcode:=sqlcode;
195 ROLLBACK;
196 POA_LOG.debug_line('commodity_check' || Sqlerrm || sqlcode || sysdate);
197 RAISE_APPLICATION_ERROR(-20000,'Stack Dump Follows =>', true);
198
199 END proc_category_commodity_update;
200
201 /****************************************************************************
202 * End of Procedure : proc_category_commodity_update
203 *****************************************************************************/
204
205 END POA_DBI_COM_C;
206
207 /****************************************************************************
208 * End of Package : poa_dbi_com_c
209 *****************************************************************************/
210