[Home] [Help]
PACKAGE BODY: APPS.JAI_INV_ITEMS_PKG
Source
1 PACKAGE BODY jai_inv_items_pkg AS
2 /* $Header: jai_inv_items.plb 120.6 2012/05/23 08:40:50 zxin ship $ */
3 PROCEDURE jai_get_attrib
4 ( p_regime_code IN JAI_RGM_ITM_TEMPLATES.REGIME_CODE%TYPE ,
5 p_organization_id IN JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE ,
6 p_inventory_item_id IN JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE ,
7 p_attribute_code IN JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_CODE%TYPE,
8 p_attribute_value OUT NOCOPY JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_VALUE%TYPE,
9 p_process_flag OUT NOCOPY VARCHAR2 ,
10 p_process_msg OUT NOCOPY VARCHAR2
11 )
12 IS
13 /**********************************************************************************
14 || This procedure will return the attribute_value (as OUT parameter)
15 || given the regime_code, organization_id, inventory_item_id and attribute_code.
16 || If the precess is successful it will return proces_flag ='SS' else it may return
17 || process_flag='EE' (Expected Error) or process_flag = 'UE' (Unexpected Error)
18 || and the process_msg will return the error message.
19 || -------------------------------------------------------------------------------
20 || Recommended variable declaration in calling module
21 || process_msg VARCHAR2 (1000) ;
22 || process_flag VARCHAR2 (2);
23 ************************************************************************************/
24
25 LV_ATTRIBUTE_VALUE JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_VALUE%TYPE DEFAULT NULL ;
26 -- Added by sacsethi for bug 5631784 on 31-01-2007
27 LN_RGM_ITEM_REGNS_ID JAI_RGM_ITM_TMPL_ATTRS.RGM_ITEM_REGNS_ID%TYPE DEFAULT NULL ;
28 LN_TEMPLATE_ID JAI_RGM_ITM_TMPL_ATTRS.TEMPLATE_ID%TYPE DEFAULT NULL ;
29 LV_ITEM_NAME MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE ;
30 --END 5631784
31
32 /* Added by Brathod from bug# 4299606 */
33 CURSOR cur_get_item_attrib
34 IS
35 SELECT attribute_value , template_id , rgm_item_regns_id
36 FROM jai_rgm_item_attrib_v
37 WHERE attribute_code = p_attribute_code
38 AND inventory_item_id = p_inventory_item_id
39 AND organization_id = p_organization_id
40 AND regime_code = p_regime_code;
41 -- Added by sacsethi for bug 5631784 on 31-01-2007
42
43 CURSOR C_GET_ITEM_NAME
44 IS
45 SELECT CONCATENATED_SEGMENTS
46 FROM MTL_SYSTEM_ITEMS_KFV
47 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
48 AND INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
49 --END 5631784
50 BEGIN
51
52 /* ------------------------------------------------------------------------------------------------------------------------------------------------
53 FILENAME: jai_items_pkg_b.sql CHANGE HISTORY:
54 SlNo. DD/MM/YYYY Author and Details of Modifications
55 -------------------------------------------------------------------------------------------------------------------------------------------------
56 1. 22/04/2005 Brathod for Bug # 4299606 (Item DFF Elimination), File Version 116.1
57
58 Issue:- Item DFF Needs to be eliminated
59
60 Package fie is renamed to JAI_INV_ITEMS_PKG.PLB
61 -----------------------------------------------
62 2. 24/05/2005 Brathod, For Bug# 4389149 (Item Code Hook - API), File Version 116.1
63 Issue:-
64 Code hook (API) needs to developed that will be called when item is either
65 copied, assigned, deleted or imported in the base item form.
66 Fix:-
67 To support this functionality a procedure PROPAGATE_ITEM_ACTION is developed
68 which accepts the base action as well as pl/sql table as inventory items to be processed.
69 The procedure will be able to process items in bulk so that only one call from base item
70 form can propagate changes in many items. Each row in plsql table PT_ITEM_DATA will be
71 a comman (,) seperated string depending upon the PV_ACTION_TYPE argument.
72 (For more details regarding possible values for each argument and format of the string
73 for each PV_ACTION_TYPE argument please refere the bug# 4389149)
74
75
76 3. 08-Jun-2005 Version 116.3 jai_inv_items -Object is Modified to refer to New DB Entity names in place of
77 Old DB Entity Names as required for CASE COMPLAINCE.
78 4. 13-Jun-2005 File Version: 116.4
79 Ramananda for bug#4428980. Removal of SQL LITERALs is done
80
81 5. 13-Jun-2005 File Version: 116.2
82 Ramananda for bug#4428980. Removal of SQL LITERALs is done
83
84 6. 15-Jul-2005 Brathod, For Bug# 4496223 Version 117.2
85 Issue: -
86 The Code hook API for IL Item currently accepts datatype of the type
87 table_item which is pl-sql table of varchar2(100). But as the code hook
88 needs to be called by base application it should not have any depedancy on IL
89 Product. Use of this data type introduces the dependancy of IL.
90 Solution:-
91 To avoid this the datatype should be independent of product. The pl-sql table
92 type aregument is removed from PROPAGATE_ITEM_ACTION procedure and added
93 the following four simple arguments
94 1. pn_organization_id - NUMBER - Destination Organization
95 2. pn_inventory_item_id - NUMBER - Destination Inventory Item
96 3. pn_source_organization_id - NUMBER - Source Organization
97 4. pn_source_inventory_item_id - NUMBER - Source Inventory Item
98
99 7. 16-Aug-2005 Brathod, For Bug#4554851, File Version 120.3
100 Issue :- Item Classification form allows multiple template assign ment
101 for same organization item combination
102 Solution:- Added a regime_code condition in cursor cur_get_itm_attribs
103 to fetch only attribute value for EXCISE regime.
104 Dependency
105 ----------
106 JAIITMCL.fmb (120.5)
107
108 8. 31-01-2007 SACSETHI , FOR BUG#5631784 , File Version #120.4
109
110 FORWARD PORTING BUG FROM 11I BUG 4742259
111 NEW ENH: TAX COLLECTION AT SOURCE IN RECEIVABLES
112
113 Changes -
114
115 Object Type Object Name Change Description
116 -----------------------------------------------------------------------------------------------------
117 VARIABLE LN_RGM_ITEM_REGNS_ID NEW FOR TCS TO CHECK ITEMS REGIME ID
118 VARIABLE LN_TEMPLATE_ID NEW FOR TEMPLATE_ID
119 VARIABLE LV_ITEM_NAME NEW ITEM NAME USED TO SEND TO CALLING OBJECT
120
121 9. 28-Jan-2010 CSahoo for bug#9191274, File Version 120.3.12000000.3
122 ISSUE: VAT ITEM ATTRIBUTES NOT ASSIGNED AUTOMATICALLY FOR STAR ITEM, AFTER CONFIGURATI
123 FIX: Added a parameter pn_regime_code to the procedure copy_items. Further modified the code to
124 copy the VAT attributes also.
125
126
127 Future Dependencies For the release Of this Object:-
128 (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
129 A datamodel change )
130
131 -------------------------------------------------------------------------------------------------------------------------------------------------
132 Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
133 Of File On Bug/Patchset
134 jai_inv_items_pkg_b.sql
135 --------------------------------------------------------------------------------------------------------------------------------------------------
136 115.0 4245134 IN60105D2 VAT Objects 115.0 Brathod 17-Mar-2005 Technical Dependacny
137 +4245089
138 --------------------------------------------------------------------------------------------------------------------------------------------------*/
139
140 IF p_regime_code IS NULL THEN
141 p_process_flag := jai_constants.expected_error;
142 p_process_msg := 'Regime cannot be null';
143 return ;
144 END IF;
145
146 IF p_organization_id IS NULL THEN
147 p_process_flag := jai_constants.expected_error;
148 p_process_msg := 'Organization cannot be null';
149 return ;
150 END IF;
151
152 IF p_inventory_item_id IS NULL THEN
153 p_process_flag := jai_constants.expected_error;
154 p_process_msg := 'Item cannot be null';
155 return ;
156 END IF;
157
158 OPEN cur_get_item_attrib;
159 FETCH cur_get_item_attrib INTO lv_attribute_value ,LN_RGM_ITEM_REGNS_ID ,LN_TEMPLATE_ID ;
160 CLOSE cur_get_item_attrib;
161
162 -- Added by sacsethi for bug 5631784 on 31-01-2007
163
164 IF LN_RGM_ITEM_REGNS_ID IS NULL AND LN_TEMPLATE_ID IS NULL THEN
165 OPEN C_GET_ITEM_NAME;
166 FETCH C_GET_ITEM_NAME INTO LV_ITEM_NAME;
167 CLOSE C_GET_ITEM_NAME;
168
169 p_process_flag := jai_constants.expected_error;
170 p_process_msg := 'Cannot find item classification for "'||p_regime_code||'" regime and "'|| lv_item_name || '" item of '||p_organization_id ||' organization(id)';
171 RETURN ;
172 END IF ;
173 -- END 5631784
174
175
176 IF lv_attribute_value IS NULL THEN
177 p_process_flag := jai_constants.expected_error;
178 p_process_msg := 'Given item is either not registered with template or does not have the given attribute';
179 ELSE
180 p_attribute_value := lv_attribute_value;
181 p_process_flag := jai_constants.successful;
182 p_process_msg := null;
183 END IF;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 p_attribute_value := null;
188 p_process_flag := jai_constants.unexpected_error;
189 p_process_msg := substr (sqlerrm,1,999) ;
190
191 END jai_get_attrib;
192
193 /*------------------------------------------- CREATE TEMPLATE -------------------------------------------*/
194
195 FUNCTION jai_create_template( p_regime_code JAI_RGM_ITM_TEMPLATES.REGIME_CODE%TYPE
196 , p_template_name JAI_RGM_ITM_TEMPLATES.TEMPLATE_NAME%TYPE
197 , p_description JAI_RGM_ITM_TEMPLATES.DESCRIPTION%TYPE DEFAULT NULL
198 )
199 RETURN NUMBER
200 AS
201
202 ln_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE DEFAULT NULL;
203 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_create_template';
204 BEGIN -- Create Template
205
206 --SELECT JAI_RGM_ITM_TEMPLATES_S.NEXTVAL INTO ln_template_id FROM DUAL;
207
208 INSERT INTO JAI_RGM_ITM_TEMPLATES
209 (
210 template_id
211 , template_name
212 , description
213 , regime_code
214 , creation_date
215 , created_by
216 , last_update_date
217 , last_updated_by
218 , last_update_login
219 )
220 VALUES
221 (
222 --ln_template_id
223 JAI_RGM_ITM_TEMPLATES_S.NEXTVAL /* Modified by Ramananda for removal of SQL LITERALs */
224 , p_template_name
225 , p_description
226 , p_regime_code
227 , sysdate
228 , fnd_global.user_id
229 , sysdate
230 , fnd_global.user_id
231 , fnd_global.login_id
232 )
233 RETURNING template_id INTO ln_template_id;
234
235 RETURN ln_template_id ;
236
237 EXCEPTION
238 WHEN OTHERS THEN
239 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
240 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
241 app_exception.raise_exception;
242 END jai_create_template;
243 /*---------------------------------------------- ASSIGN TEMPLATE ----------------------------------------*/
244
245 PROCEDURE jai_assign_template( p_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
246 , p_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
247 , p_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
248 )
249 AS
250
251 CURSOR cur_get_items
252 IS
253 SELECT inventory_item_id
254 FROM JAI_INV_ITM_SETUPS
255 WHERE organization_id = p_organization_id
256 AND inventory_item_id = p_inventory_item_id;
257
258 CURSOR cur_chk_templ_org
259 IS
260 SELECT templ_org_regns_id
261 FROM JAI_RGM_TMPL_ORG_REGNS torg
262 WHERE torg.organization_id = p_organization_id
263 AND torg.template_id = p_template_id;
264
265 ln_templ_org_regns_id JAI_RGM_TMPL_ORG_REGNS.TEMPL_ORG_REGNS_ID%TYPE DEFAULT NULL;
266 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_assign_template';
267
268 /* -------------------------------------- Local Procedure ---------------------------------------*/
269 PROCEDURE assign_template (p_templ_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
270 ,p_org_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
271 ,p_inv_itm_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
272 ,p_templ_org_regns_id IN OUT NOCOPY JAI_RGM_TMPL_ORG_REGNS.TEMPL_ORG_REGNS_ID%TYPE
273 )
274 AS
275
276 CURSOR cur_chk_templ_itm_regns
277 IS
278 SELECT templ_itm_regns_id
279 FROM JAI_RGM_TMPL_ITM_REGNS tirg
280 WHERE tirg.templ_org_regns_id = p_templ_org_regns_id
281 AND tirg.inventory_item_id = p_inv_itm_id;
282
283 ln_templ_itm_regns_id JAI_RGM_TMPL_ITM_REGNS.TEMPL_ITM_REGNS_ID%TYPE DEFAULT NULL;
284
285 BEGIN
286
287 IF p_templ_org_regns_id IS NULL THEN
288
289 SELECT JAI_RGM_TMPL_ORG_REGNS_S.NEXTVAL
290 INTO p_templ_org_regns_id
291 FROM DUAL;
292 /* Create template organization association */
293 INSERT INTO JAI_RGM_TMPL_ORG_REGNS
294 ( templ_org_regns_id
295 , template_id
296 , organization_id
297 , creation_date
298 , created_by
299 , last_update_date
300 , last_updated_by
301 , last_update_login
302 )
303 VALUES
304 (
305 p_templ_org_regns_id
306 , p_templ_id
307 , p_org_id
308 , sysdate
309 , fnd_global.user_id
310 , sysdate
311 , fnd_global.user_id
312 , fnd_global.login_id
313 );
314 END IF;
315
316 OPEN cur_chk_templ_itm_regns;
317 FETCH cur_chk_templ_itm_regns INTO ln_templ_itm_regns_id;
318 CLOSE cur_chk_templ_itm_regns;
319 /* Create template item association */
320 IF ln_templ_itm_regns_id IS NULL THEN
321 INSERT INTO JAI_RGM_TMPL_ITM_REGNS
322 (
323 templ_itm_regns_id
324 , templ_org_regns_id
325 , inventory_item_id
326 , creation_date
327 , created_by
328 , last_update_date
329 , last_updated_by
330 , last_update_login
331 )
332 VALUES (
333 JAI_RGM_TMPL_ITM_REGNS_S.nextval
334 , p_templ_org_regns_id
335 , p_inv_itm_id
336 , sysdate
337 , fnd_global.user_id
338 , sysdate
339 , fnd_global.user_id
340 , fnd_global.login_id
341 );
342 END IF;
343
344 END assign_template;
345
346 /* -------------------------------- End of Local Procedure -------------------------------*/
347
348 BEGIN
349
350 OPEN cur_chk_templ_org ;
351 FETCH cur_chk_templ_org INTO ln_templ_org_regns_id;
352 CLOSE cur_chk_templ_org;
353
354 IF p_inventory_item_id IS NOT NULL THEN
355 /* Call the local procedure to create assignment */
356 assign_template( p_templ_id => p_template_id
357 ,p_org_id => p_organization_id
358 ,p_inv_itm_id => p_inventory_item_id
359 ,p_templ_org_regns_id => ln_templ_org_regns_id
360 );
361 ELSE
362 FOR c_items IN cur_get_items
363 LOOP
364 /* Call the local procedure to create assignment */
365 assign_template( p_templ_id => p_template_id
366 ,p_org_id => p_organization_id
367 ,p_inv_itm_id => c_items.inventory_item_id
368 ,p_templ_org_regns_id => ln_templ_org_regns_id
369 );
370 END LOOP; /* c_items */
371 END IF;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
376 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
377 app_exception.raise_exception;
378 END jai_assign_template;
379
380 /* ------------------------------- CREATE ITEM SPECIFIC REGISTRATION ---------------------------------*/
381 PROCEDURE jai_create_item_regns ( p_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE
382 ,p_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
383 ,p_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
384 ,p_tab_attributes jai_inv_items_pkg.GT_ATTRIBUTES%TYPE
385 )
386 AS
387 CURSOR cur_chk_rgm_item_regns
388 IS
389 SELECT rgm_item_regns_id
390 FROM JAI_RGM_ITM_REGNS rirg
391 WHERE rirg.regime_code = p_regime_code
392 AND rirg.organization_id = p_organization_id
393 AND rirg.inventory_item_id = p_inventory_item_id;
394
395 ln_rgm_item_regns_id JAI_RGM_ITM_REGNS.RGM_ITEM_REGNS_ID%TYPE DEFAULT NULL;
396 ltab_attribs jai_inv_items_pkg.GT_ATTRIBUTES%TYPE;
397 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_create_item_regns';
398
399 BEGIN
400 /* Chekc if item specific registration already exists */
401 OPEN cur_chk_rgm_item_regns;
402 FETCH cur_chk_rgm_item_regns INTO ln_rgm_item_regns_id;
403 CLOSE cur_chk_rgm_item_regns;
404
405 IF ln_rgm_item_regns_id IS NULL THEN
406 /*
407 * Item specific registration does not exist so create one by inserting a row in
408 * JAI_RGM_ITM_REGNS
409 */
410
411 --SELECT JAI_RGM_ITM_REGNS_S.NEXTVAL INTO ln_rgm_item_regns_id FROM DUAL;
412
413 INSERT INTO JAI_RGM_ITM_REGNS
414 ( rgm_item_regns_id
415 ,regime_code
416 ,organization_id
417 ,inventory_item_id
418 ,creation_date
419 ,created_by
420 ,last_update_date
421 ,last_updated_by
422 ,last_update_login
423 )
424 VALUES(
425 --ln_rgm_item_regns_id
426 JAI_RGM_ITM_REGNS_S.NEXTVAL /* Modified by Ramananda for removal of SQL LITERALs */
427 , p_regime_code
428 , p_organization_id
429 , p_inventory_item_id
430 , sysdate
431 , fnd_global.user_id
432 , sysdate
433 , fnd_global.user_id
434 , fnd_global.login_id
435 ) returning rgm_item_regns_id into ln_rgm_item_regns_id;
436 END IF;
437
438 jai_inv_items_pkg.jai_create_attribs( p_template_id => ''
439 ,p_rgm_item_regns_id => ln_rgm_item_regns_id
440 ,p_tab_attributes => p_tab_attributes
441 );
442
443 EXCEPTION
444 WHEN OTHERS THEN
445 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
446 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
447 app_exception.raise_exception;
448 END jai_create_item_regns ;
449 /* -------------------------------------- CREATE TEMPLATE ATTRIBUTES ------------------------------*/
450 PROCEDURE jai_create_attribs ( p_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
451 ,p_rgm_item_regns_id JAI_RGM_ITM_REGNS.RGM_ITEM_REGNS_ID%TYPE
452 ,p_tab_attributes jai_inv_items_pkg.GT_ATTRIBUTES%TYPE
453 )
454 IS
455 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_create_attribs';
456 BEGIN
457 /*
458 * For each row in pl-sql table create row in jai_item_templ_attribs
459 */
460 FOR ln_attrib IN 1..p_tab_attributes.COUNT LOOP
461 INSERT INTO JAI_RGM_ITM_TMPL_ATTRS(
462 ITM_TEMPL_ATTRIBUTE_ID,
463 TEMPLATE_ID,
464 RGM_ITEM_REGNS_ID,
465 ATTRIBUTE_CODE,
466 ATTRIBUTE_VALUE,
467 CREATION_DATE,
468 CREATED_BY,
469 LAST_UPDATE_DATE,
470 LAST_UPDATE_LOGIN,
471 LAST_UPDATED_BY
472 ) VALUES (JAI_RGM_ITM_TMPL_ATTRS_S.nextval
473 ,p_template_id
474 ,p_rgm_item_regns_id
475 ,p_tab_attributes(ln_attrib).attribute_code
476 ,p_tab_attributes(ln_attrib).attribute_value
477 ,SYSDATE, fnd_global.user_id , SYSDATE, fnd_global.login_id, fnd_global.user_id
478 );
479 END LOOP;
480 EXCEPTION
481 WHEN OTHERS THEN
482 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
483 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
484 app_exception.raise_exception;
485 END jai_create_attribs;
486
487 /* ------------------------------------------- SYNCHRONIZATION ---------------------------------------*/
488
489 procedure jai_synchronize_jmsi
490 (
491 p_synchronization_number JAI_INV_ITM_SETUPS.synchronization_number%type default null
492 )
493 is
494 lv_new_excise_flag JAI_INV_ITM_SETUPS.excise_flag%type;
495 lv_new_item_class JAI_INV_ITM_SETUPS.item_class%type;
496 lv_new_modvat_flag JAI_INV_ITM_SETUPS.modvat_flag%type;
497 lv_new_item_tariff JAI_INV_ITM_SETUPS.item_tariff%type;
498 lv_new_item_folio JAI_INV_ITM_SETUPS.item_folio%type;
499 lv_new_item_trading_flag JAI_INV_ITM_SETUPS.item_trading_flag%type;
500 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_synchronize_jmsi';
501
502 cursor cur_get_jmsi_row(cpn_synchronization_number number ) is
503 select
504 jmsi.rowid,
505 jmsi.excise_flag ,
506 jmsi.item_class ,
507 jmsi.modvat_flag ,
508 jmsi.item_tariff ,
509 jmsi.item_folio ,
510 jmsi.item_trading_flag,
511 jmsi.organization_id,
512 jmsi.inventory_item_id
513 from JAI_INV_ITM_SETUPS jmsi
514 where (
515 (cpn_synchronization_number is null)
516 or
517 ( (cpn_synchronization_number is not null ) and (synchronization_number = cpn_synchronization_number ) )
518 );
519
520 cursor cur_get_itm_attribs
521 (
522 cpn_organization_id jai_rgm_item_attrib_v.organization_id%type
523 ,cpn_inventory_item_id jai_rgm_item_attrib_v.inventory_item_id%type
524 )
525 is
526 select attribute_code,
527 attribute_value,
528 last_updated_by
529 from jai_rgm_item_attrib_v
530 where organization_id = cpn_organization_id
531 and inventory_item_id = cpn_inventory_item_id
532 AND regime_code = jai_constants.excise_regime ;
533
534 begin
535 /* Get all the rows to be synchronized */
536 for rec_jmsi in cur_get_jmsi_row(p_synchronization_number)
537 loop
538
539 lv_new_excise_flag := null;
540 lv_new_item_class := null;
541 lv_new_modvat_flag := null;
542 lv_new_item_tariff := null;
543 lv_new_item_folio := null;
544 lv_new_item_trading_flag := null;
545
546 /* Get attributes for each organization and inventory item */
547 for rec_attribs in
548 cur_get_itm_attribs (cpn_organization_id => rec_jmsi.organization_id,
549 cpn_inventory_item_id => rec_jmsi.inventory_item_id
550 )
551 loop
552
553 if rec_attribs.attribute_code ='EXCISABLE' then
554 lv_new_excise_flag := rec_attribs.attribute_value;
555 elsif rec_attribs.attribute_code ='ITEM CLASS' then
556 lv_new_item_class := rec_attribs.attribute_value;
557 elsif rec_attribs.attribute_code ='MODVATABLE' then
558 lv_new_modvat_flag := rec_attribs.attribute_value;
559 elsif rec_attribs.attribute_code ='ITEM TARIFF' then
560 lv_new_item_tariff := rec_attribs.attribute_value;
561 elsif rec_attribs.attribute_code ='ITEM FOLIO' then
562 lv_new_item_folio := rec_attribs.attribute_value;
563 elsif rec_attribs.attribute_code ='TRADABLE' then
564 lv_new_item_trading_flag := rec_attribs.attribute_value;
565 end if;
566
567 end loop; /* Attributes */
568
569 /* Update JAI_INV_ITM_SETUPS if atleast one attribute is changed */
570
571 if nvl(lv_new_excise_flag, 'NULL') <> nvl(rec_jmsi.excise_flag, 'NULL') or
572 nvl(lv_new_item_class, 'NULL') <> nvl(rec_jmsi.item_class, 'NULL') or
573 nvl(lv_new_modvat_flag, 'NULL') <> nvl(rec_jmsi.modvat_flag, 'NULL') or
574 nvl(lv_new_item_tariff, 'NULL') <> nvl(rec_jmsi.item_tariff, 'NULL') or
575 nvl(lv_new_item_folio, 'NULL') <> nvl(rec_jmsi.item_folio, 'NULL') or
576 nvl(lv_new_item_trading_flag, 'NULL') <> nvl(rec_jmsi.item_trading_flag, 'NULL')
577 then
578
579 update JAI_INV_ITM_SETUPS
580 set excise_flag = lv_new_excise_flag
581 , item_class = lv_new_item_class
582 , modvat_flag = lv_new_modvat_flag
583 , item_tariff = lv_new_item_tariff
584 , item_folio = lv_new_item_folio
585 , item_trading_flag = lv_new_item_trading_flag
586 , last_update_date = sysdate
587 , last_updated_by = 0405051/* p_synchronization_number*/
588 where rowid = rec_jmsi.rowid;
589
590 end if; /* update */
591
592 end loop; /* rec_jmsi */
593 EXCEPTION
594 WHEN OTHERS THEN
595 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
596 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
597 app_exception.raise_exception;
598 end jai_synchronize_jmsi;
599 /*-----------------------------------------------------------------------------------*/
600 /* Added by Brathod for bug#4389149 */
601 /* India Localization code hook for Base item copy/delete/assignment/import action */
602 PROCEDURE propagate_item_action
603 (
604 pv_action_type IN VARCHAR2
605 , pn_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
606 , pn_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
607 , pn_source_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
608 , pn_source_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
609 , pn_set_process_id IN NUMBER
610 , pv_called_from IN VARCHAR2
611 )
612 IS
613 CURSOR cur_get_items_from_interface
614 IS
615 SELECT intf.organization_id organization_id
616 ,intf.inventory_item_id inventory_item_id
617 ,master_org.master_organization_id source_organization_id
618 FROM mtl_system_items_interface intf
619 ,mtl_parameters master_org
620 WHERE intf.process_flag = 7
621 AND intf.transaction_type = 'CREATE'
622 AND intf.request_id = fnd_global.conc_request_id
623 AND intf.set_process_id = pn_set_process_id
624 AND intf.organization_id = master_org.organization_id
625 AND intf.organization_id <> master_org.master_organization_id ;
626
627 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.propagate_item_action';
628
629 BEGIN
630 /*
631 pv_action_type "IMPORT" indicates to import items from interface.
632 In this case pt_item_data (PL/SQL) table will be null and desired data will
633 be fetched from interface table
634 */
635
636 IF pv_action_type IN ('IMPORT') THEN
637 FOR rec_itms IN cur_get_items_from_interface
638 LOOP
639
640 IF rec_itms.organization_id IS NULL THEN
641 fnd_message.set_name('JA','JAI_DEST_ORG_CANT_BE_NULL');
642 app_exception.raise_exception;
643 END IF;
644 IF rec_itms.inventory_item_id IS NULL THEN
645 fnd_message.set_name ('JA','JAI_ITEM_CANT_BE_NULL');
646 app_exception.raise_exception;
647 END IF;
648 IF rec_itms.source_organization_id IS NULL THEN
649 fnd_message.set_name('JA','JAI_SOURCE_ORG_CANT_BE_NULL');
650 app_exception.raise_exception;
651 END IF;
652
653 jai_inv_items_pkg.copy_items
654 ( pn_organization_id => rec_itms.organization_id
655 ,pn_inventory_item_id => rec_itms.inventory_item_id
656 ,pn_source_organization_id => rec_itms.source_organization_id
657 ,pn_source_inventory_item_id => rec_itms.inventory_item_id
658 );
659 END LOOP;
660 ELSE /* pv_action_type is either COPY, ASSIGN or DELETE */
661
662 IF pv_action_type IN ('COPY','ASSIGN') THEN
663
664 IF pn_organization_id IS NULL
665 OR pn_inventory_item_id IS NULL
666 OR pn_source_organization_id IS NULL
667 OR pn_source_inventory_item_id IS NULL THEN
668 fnd_message.set_name('JA','JAI_IL_API_ARGS_NOT_PROPER');
669 app_exception.raise_exception;
670 END IF;
671
672 jai_inv_items_pkg.copy_items
673 ( pn_organization_id => pn_organization_id
674 ,pn_inventory_item_id => pn_inventory_item_id
675 ,pn_source_organization_id => pn_source_organization_id
676 ,pn_source_inventory_item_id => pn_source_inventory_item_id
677 );
678
679 ELSIF pv_action_type IN ('DELETE') THEN
680
681 IF pn_organization_id IS NULL
682 OR pn_inventory_item_id IS NULL THEN
683 fnd_message.set_name('JA','JAI_IL_API_ARGS_NOT_PROPER');
684 app_exception.raise_exception;
685 END IF;
686
687 jai_inv_items_pkg.delete_items
688 (
689 pn_organization_id => pn_organization_id
690 ,pn_inventory_item_id => pn_inventory_item_id
691 );
692 END IF;
693 END IF;
694 EXCEPTION
695 WHEN OTHERS THEN
696 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
697 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||SQLERRM );
698 app_exception.raise_exception;
699 END propagate_item_action;
700 /*----------------------------------------------------------------------------------*/
701 PROCEDURE copy_items
702 ( pn_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
703 ,pn_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
704 ,pn_source_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
705 ,pn_source_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
706 ,pn_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE DEFAULT 'EXCISE' --added for bug#9191274
707 )
708 IS
709 ln_items NUMBER := 0;
710 ln_item_regns_id NUMBER ;
711 ln_dest_item_regns_id NUMBER ;
712 ln_template_id NUMBER ;
713 ln_dest_template_id NUMBER ;
714
715 ln_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE ;
716 ln_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE ;
717 ln_source_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE ;
718 ln_source_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE ;
719
720 lt_attribs JAI_INV_ITEMS_PKG.GT_ATTRIBUTES%TYPE;
721 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.copy_items';
722 lv_regime_code VARCHAR2(15);
723
724 CURSOR cur_get_item_regns
725 (cpv_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE
726 ,cpn_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
727 ,cpn_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
728 )
729 IS
730 SELECT rgm_item_regns_id
731 FROM JAI_RGM_ITM_REGNS
732 WHERE regime_code = cpv_regime_code
733 AND organization_id = cpn_organization_id
734 AND inventory_item_id = cpn_inventory_item_id;
735
736 CURSOR cur_get_rec_item_attrib (cpv_itm_templ_flg VARCHAR2
737 ,cpn_itm_templ_id NUMBER
738 )
739 IS
740 SELECT template_id
741 ,rgm_item_regns_id
742 ,attribute_code
743 ,attribute_value
744 FROM JAI_RGM_ITM_TMPL_ATTRS
745 WHERE (( template_id = cpn_itm_templ_id AND cpv_itm_templ_flg = 'T')
746 OR
747 ( rgm_item_regns_id = cpn_itm_templ_id AND cpv_itm_templ_flg = 'I')
748 );
749
750 CURSOR cur_get_template_id
751 (cpv_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE
752 ,cpn_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
753 ,cpn_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
754 )
755 IS
756 SELECT torg.template_id
757 FROM JAI_RGM_TMPL_ITM_REGNS tirg
758 ,JAI_RGM_TMPL_ORG_REGNS torg
759 ,JAI_RGM_ITM_TEMPLATES rgtmp
760 WHERE tirg.templ_org_regns_id = torg.templ_org_regns_id
761 AND torg.template_id = rgtmp.template_id
762 AND torg.organization_id = cpn_organization_id
763 AND tirg.inventory_item_id = cpn_inventory_item_id
764 AND rgtmp.regime_code = cpv_regime_code;
765
766
767 BEGIN
768
769 ln_organization_id := pn_organization_id;
770 ln_inventory_item_id := pn_inventory_item_id;
771 ln_source_organization_id := pn_source_organization_id;
772 ln_source_inventory_item_id := pn_source_inventory_item_id;
773 lv_regime_code := pn_regime_code; --added for bug#9191274
774
775
776 /* Check if destination item already exists */
777
778 OPEN cur_get_item_regns (cpv_regime_code => lv_regime_code --replaced jai_constants.excise_regime for bug#9191274
779 ,cpn_organization_id => ln_organization_id
780 ,cpn_inventory_item_id => ln_inventory_item_id
781 );
782
783 FETCH cur_get_item_regns INTO ln_dest_item_regns_id;
784
785 CLOSE cur_get_item_regns;
786
787 IF ln_dest_item_regns_id IS NOT NULL THEN
788 fnd_message.set_name('JA', 'JAI_DUP_ITEM');
789 app_exception.raise_exception;
790 END IF;
791
792 /* Check if item specific registration exists for the source item */
793
794 OPEN cur_get_item_regns (cpv_regime_code => lv_regime_code --replaced jai_constants.excise_regime for bug#9191274
795 ,cpn_organization_id => ln_source_organization_id
796 ,cpn_inventory_item_id => ln_source_inventory_item_id
797 );
798 FETCH cur_get_item_regns INTO ln_item_regns_id;
799 CLOSE cur_get_item_regns;
800
801
802 IF ln_item_regns_id IS NOT NULL THEN
803 /*
804 Item specific registration exists, so create item specific registration for
805 new item by copying the attributes of source item
806 */
807 lt_attribs.delete; -- Flush plsql table
808
809 FOR cur_attribs IN cur_get_rec_item_attrib (cpv_itm_templ_flg => 'I'
810 ,cpn_itm_templ_id => ln_item_regns_id
811 )
812 LOOP
813 lt_attribs(lt_attribs.count+1).attribute_code := cur_attribs.attribute_code;
814 lt_attribs(lt_attribs.count).attribute_value := cur_attribs.attribute_value;
815 END LOOP;
816
817 jai_inv_items_pkg.jai_create_item_regns
818 ( p_regime_code => lv_regime_code --replaced jai_constants.excise_regime for bug#9191274
819 ,p_organization_id => ln_organization_id
820 ,p_inventory_item_id => ln_inventory_item_id
821 ,p_tab_attributes => lt_attribs
822 );
823 END IF; /* End of Item Specific Registration*/
824
825 /* Check if source item is alredy registred with some template */
826 OPEN cur_get_template_id
827 (cpv_regime_code => lv_regime_code --replaced jai_constants.excise_regime for bug#9191274
828 ,cpn_organization_id => ln_organization_id
829 ,cpn_inventory_item_id => ln_inventory_item_id
830 );
831 FETCH cur_get_template_id INTO ln_dest_template_id;
832 CLOSE cur_get_template_id ;
833
834 IF ln_dest_template_id IS NOT NULL THEN
835 /* Item is alredy registered so nothing to do */
836 RETURN;
837 END IF;
838
839 /* Check if source item is assigned to template */
840 OPEN cur_get_template_id
841 (cpv_regime_code => lv_regime_code --replaced jai_constants.excise_regime for bug#9191274
842 ,cpn_organization_id => ln_source_organization_id
843 ,cpn_inventory_item_id => ln_source_inventory_item_id
844 );
845 FETCH cur_get_template_id INTO ln_template_id;
846 CLOSE cur_get_template_id ;
847
848 IF ln_template_id IS NOT NULL THEN
849 /*
850 Source item is assigned to a template and so new item should also
851 be assigned to the same template
852 */
853 jai_inv_items_pkg.jai_assign_template (p_template_id => ln_template_id
854 ,p_organization_id => ln_organization_id
855 ,p_inventory_item_id => ln_inventory_item_id
856 );
857
858 END IF; /* End of Template Assignment */
859 /*
860 For Excise regime create a copy record in the JAI_INV_ITM_SETUPS
861 from the source item
862 */
863 --added the IF condition for bug#9191274
864 IF lv_regime_code = jai_constants.excise_regime THEN
865 INSERT INTO JAI_INV_ITM_SETUPS
866 (
867 inventory_item_id
868 , organization_id
869 , item_class
870 , modvat_flag
871 , item_tariff
872 , item_folio
873 , excise_flag
874 , creation_date
875 , created_by
876 , last_update_date
877 , last_updated_by
878 , last_update_login
879 , item_trading_flag
880 , synchronization_number
881 )
882 (SELECT
883 ln_inventory_item_id
884 , ln_organization_id
885 , item_class
886 , modvat_flag
887 , item_tariff
888 , item_folio
889 , excise_flag
890 , sysdate
891 , fnd_global.user_id
892 , sysdate
893 , fnd_global.user_id
894 , fnd_global.login_id
895 , item_trading_flag
896 , NULL
897 FROM JAI_INV_ITM_SETUPS
898 WHERE organization_id = ln_source_organization_id
899 AND inventory_item_id = ln_source_inventory_item_id
900 );
901 END IF;
902 EXCEPTION
903 WHEN OTHERS THEN
904 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
905 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||SQLERRM );
906 app_exception.raise_exception;
907 END copy_items;
908 /* ------------------------------- END OF PROCEDURE COPY_ITEMS ------------------------*/
909
910 PROCEDURE delete_items ( pn_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
911 ,pn_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
912 )
913 IS
914 ln_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE ;
915 ln_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE ;
916 ln_rgm_item_regns_id JAI_RGM_ITM_REGNS.RGM_ITEM_REGNS_ID%TYPE;
917 ln_templ_org_regns_id JAI_RGM_TMPL_ORG_REGNS.TEMPL_ORG_REGNS_ID%TYPE;
918 ln_templ_itm_regns NUMBER := NULL;
919 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.delete_items';
920
921 CURSOR cur_chk_templ_itm_regns (cpn_templ_org_regns_id JAI_RGM_TMPL_ITM_REGNS.TEMPL_ORG_REGNS_ID%TYPE)
922 IS
923 SELECT 1
924 FROM JAI_RGM_TMPL_ITM_REGNS
925 WHERE templ_org_regns_id = cpn_templ_org_regns_id;
926
927 BEGIN
928 ln_organization_id := pn_organization_id;
929 ln_inventory_item_id := pn_inventory_item_id;
930
931 DELETE FROM JAI_RGM_ITM_REGNS
932 WHERE inventory_item_id = ln_inventory_item_id
933 AND organization_id = ln_organization_id
934 AND regime_code = jai_constants.excise_regime
935 RETURNING rgm_item_regns_id INTO ln_rgm_item_regns_id;
936
937 IF ln_rgm_item_regns_id IS NOT NULL THEN
938 DELETE FROM JAI_RGM_ITM_TMPL_ATTRS
939 WHERE rgm_item_regns_id = ln_rgm_item_regns_id;
940 END IF;
941
942 DELETE FROM JAI_RGM_TMPL_ITM_REGNS
943 WHERE templ_itm_regns_id IN (SELECT templ_itm_regns_id
944 FROM JAI_RGM_TMPL_ITM_REGNS tirg
945 ,JAI_RGM_TMPL_ORG_REGNS torg
946 ,JAI_RGM_ITM_TEMPLATES rgtmp
947 WHERE tirg.templ_org_regns_id = torg.templ_org_regns_id
948 AND torg.template_id = rgtmp.template_id
949 AND tirg.inventory_item_id = ln_inventory_item_id
950 AND torg.organization_id = ln_organization_id
951 AND rgtmp.regime_code = jai_constants.excise_regime
952 )
953 RETURNING templ_org_regns_id INTO ln_templ_org_regns_id ;
954
955 /*
956 Check if any item is registered with templ_org_regns_id. If no such items found
957 then delete the template organizatin registration also
958 */
959 OPEN cur_chk_templ_itm_regns (cpn_templ_org_regns_id => ln_templ_org_regns_id);
960 FETCH cur_chk_templ_itm_regns INTO ln_templ_itm_regns ;
961 CLOSE cur_chk_templ_itm_regns ;
962
963 IF ln_templ_itm_regns IS NULL THEN
964 DELETE FROM JAI_RGM_TMPL_ORG_REGNS
965 WHERE templ_org_regns_id = ln_templ_org_regns_id;
966 END IF;
967
968 DELETE FROM JAI_INV_ITM_SETUPS
969 WHERE organization_id = ln_organization_id
970 AND inventory_item_id = ln_inventory_item_id;
971
972 EXCEPTION
973 WHEN OTHERS THEN
974 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
975 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
976 app_exception.raise_exception;
977 END delete_items;
978 /* -----------------------END OF PROCEDURE DELETE_ITEMS ----------------------------*/
979 /* End of Bug# 4389149 */
980
981 end jai_inv_items_pkg;