[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.4 2007/02/26 17:25:43 sacsethi 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
122 Future Dependencies For the release Of this Object:-
123 (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/
124 A datamodel change )
125
126 -------------------------------------------------------------------------------------------------------------------------------------------------
127 Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
128 Of File On Bug/Patchset
129 jai_inv_items_pkg_b.sql
130 --------------------------------------------------------------------------------------------------------------------------------------------------
131 115.0 4245134 IN60105D2 VAT Objects 115.0 Brathod 17-Mar-2005 Technical Dependacny
132 +4245089
133 --------------------------------------------------------------------------------------------------------------------------------------------------*/
134
135 IF p_regime_code IS NULL THEN
136 p_process_flag := jai_constants.expected_error;
137 p_process_msg := 'Regime cannot be null';
138 return ;
139 END IF;
140
141 IF p_organization_id IS NULL THEN
142 p_process_flag := jai_constants.expected_error;
143 p_process_msg := 'Organization cannot be null';
144 return ;
145 END IF;
146
147 IF p_inventory_item_id IS NULL THEN
148 p_process_flag := jai_constants.expected_error;
149 p_process_msg := 'Item cannot be null';
150 return ;
151 END IF;
152
153 OPEN cur_get_item_attrib;
154 FETCH cur_get_item_attrib INTO lv_attribute_value ,LN_RGM_ITEM_REGNS_ID ,LN_TEMPLATE_ID ;
155 CLOSE cur_get_item_attrib;
156
157 -- Added by sacsethi for bug 5631784 on 31-01-2007
158
159 IF LN_RGM_ITEM_REGNS_ID IS NULL AND LN_TEMPLATE_ID IS NULL THEN
160 OPEN C_GET_ITEM_NAME;
161 FETCH C_GET_ITEM_NAME INTO LV_ITEM_NAME;
162 CLOSE C_GET_ITEM_NAME;
163
164 p_process_flag := jai_constants.expected_error;
165 p_process_msg := 'Cannot find item classification for "'||p_regime_code||'" regime and "'|| lv_item_name || '" item of '||p_organization_id ||' organization(id)';
166 RETURN ;
167 END IF ;
168 -- END 5631784
169
170
171 IF lv_attribute_value IS NULL THEN
172 p_process_flag := jai_constants.expected_error;
173 p_process_msg := 'Given item is either not registered with template or does not have the given attribute';
174 ELSE
175 p_attribute_value := lv_attribute_value;
176 p_process_flag := jai_constants.successful;
177 p_process_msg := null;
178 END IF;
179
180 EXCEPTION
181 WHEN OTHERS THEN
182 p_attribute_value := null;
183 p_process_flag := jai_constants.unexpected_error;
184 p_process_msg := substr (sqlerrm,1,999) ;
185
186 END jai_get_attrib;
187
188 /*------------------------------------------- CREATE TEMPLATE -------------------------------------------*/
189
190 FUNCTION jai_create_template( p_regime_code JAI_RGM_ITM_TEMPLATES.REGIME_CODE%TYPE
191 , p_template_name JAI_RGM_ITM_TEMPLATES.TEMPLATE_NAME%TYPE
192 , p_description JAI_RGM_ITM_TEMPLATES.DESCRIPTION%TYPE DEFAULT NULL
193 )
194 RETURN NUMBER
195 AS
196
197 ln_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE DEFAULT NULL;
198 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_create_template';
199 BEGIN -- Create Template
200
201 --SELECT JAI_RGM_ITM_TEMPLATES_S.NEXTVAL INTO ln_template_id FROM DUAL;
202
203 INSERT INTO JAI_RGM_ITM_TEMPLATES
204 (
205 template_id
206 , template_name
207 , description
208 , regime_code
209 , creation_date
210 , created_by
211 , last_update_date
212 , last_updated_by
213 , last_update_login
214 )
215 VALUES
216 (
217 --ln_template_id
218 JAI_RGM_ITM_TEMPLATES_S.NEXTVAL /* Modified by Ramananda for removal of SQL LITERALs */
219 , p_template_name
220 , p_description
221 , p_regime_code
222 , sysdate
223 , fnd_global.user_id
224 , sysdate
228 RETURNING template_id INTO ln_template_id;
225 , fnd_global.user_id
226 , fnd_global.login_id
227 )
229
230 RETURN ln_template_id ;
231
232 EXCEPTION
233 WHEN OTHERS THEN
234 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
235 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
236 app_exception.raise_exception;
237 END jai_create_template;
238 /*---------------------------------------------- ASSIGN TEMPLATE ----------------------------------------*/
239
240 PROCEDURE jai_assign_template( p_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
241 , p_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
242 , p_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
243 )
244 AS
245
246 CURSOR cur_get_items
247 IS
248 SELECT inventory_item_id
249 FROM JAI_INV_ITM_SETUPS
250 WHERE organization_id = p_organization_id
251 AND inventory_item_id = p_inventory_item_id;
252
253 CURSOR cur_chk_templ_org
254 IS
255 SELECT templ_org_regns_id
256 FROM JAI_RGM_TMPL_ORG_REGNS torg
257 WHERE torg.organization_id = p_organization_id
258 AND torg.template_id = p_template_id;
259
260 ln_templ_org_regns_id JAI_RGM_TMPL_ORG_REGNS.TEMPL_ORG_REGNS_ID%TYPE DEFAULT NULL;
261 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_assign_template';
262
263 /* -------------------------------------- Local Procedure ---------------------------------------*/
264 PROCEDURE assign_template (p_templ_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
265 ,p_org_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
266 ,p_inv_itm_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
267 ,p_templ_org_regns_id IN OUT NOCOPY JAI_RGM_TMPL_ORG_REGNS.TEMPL_ORG_REGNS_ID%TYPE
268 )
269 AS
270
271 CURSOR cur_chk_templ_itm_regns
272 IS
273 SELECT templ_itm_regns_id
274 FROM JAI_RGM_TMPL_ITM_REGNS tirg
275 WHERE tirg.templ_org_regns_id = p_templ_org_regns_id
276 AND tirg.inventory_item_id = p_inv_itm_id;
277
278 ln_templ_itm_regns_id JAI_RGM_TMPL_ITM_REGNS.TEMPL_ITM_REGNS_ID%TYPE DEFAULT NULL;
279
280 BEGIN
281
282 IF p_templ_org_regns_id IS NULL THEN
283
284 SELECT JAI_RGM_TMPL_ORG_REGNS_S.NEXTVAL
285 INTO p_templ_org_regns_id
286 FROM DUAL;
287 /* Create template organization association */
288 INSERT INTO JAI_RGM_TMPL_ORG_REGNS
289 ( templ_org_regns_id
290 , template_id
291 , organization_id
292 , creation_date
293 , created_by
294 , last_update_date
295 , last_updated_by
296 , last_update_login
297 )
298 VALUES
299 (
300 p_templ_org_regns_id
301 , p_templ_id
302 , p_org_id
303 , sysdate
304 , fnd_global.user_id
305 , sysdate
306 , fnd_global.user_id
307 , fnd_global.login_id
308 );
309 END IF;
310
311 OPEN cur_chk_templ_itm_regns;
312 FETCH cur_chk_templ_itm_regns INTO ln_templ_itm_regns_id;
313 CLOSE cur_chk_templ_itm_regns;
314 /* Create template item association */
315 IF ln_templ_itm_regns_id IS NULL THEN
316 INSERT INTO JAI_RGM_TMPL_ITM_REGNS
317 (
318 templ_itm_regns_id
319 , templ_org_regns_id
320 , inventory_item_id
321 , creation_date
322 , created_by
323 , last_update_date
324 , last_updated_by
325 , last_update_login
326 )
327 VALUES (
328 JAI_RGM_TMPL_ITM_REGNS_S.nextval
329 , p_templ_org_regns_id
330 , p_inv_itm_id
331 , sysdate
332 , fnd_global.user_id
333 , sysdate
334 , fnd_global.user_id
335 , fnd_global.login_id
336 );
337 END IF;
338
339 END assign_template;
340
341 /* -------------------------------- End of Local Procedure -------------------------------*/
342
343 BEGIN
344
345 OPEN cur_chk_templ_org ;
346 FETCH cur_chk_templ_org INTO ln_templ_org_regns_id;
347 CLOSE cur_chk_templ_org;
348
349 IF p_inventory_item_id IS NOT NULL THEN
350 /* Call the local procedure to create assignment */
351 assign_template( p_templ_id => p_template_id
352 ,p_org_id => p_organization_id
353 ,p_inv_itm_id => p_inventory_item_id
354 ,p_templ_org_regns_id => ln_templ_org_regns_id
355 );
356 ELSE
357 FOR c_items IN cur_get_items
358 LOOP
359 /* Call the local procedure to create assignment */
360 assign_template( p_templ_id => p_template_id
364 );
361 ,p_org_id => p_organization_id
362 ,p_inv_itm_id => c_items.inventory_item_id
363 ,p_templ_org_regns_id => ln_templ_org_regns_id
365 END LOOP; /* c_items */
366 END IF;
367
368 EXCEPTION
369 WHEN OTHERS THEN
370 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
371 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
372 app_exception.raise_exception;
373 END jai_assign_template;
374
375 /* ------------------------------- CREATE ITEM SPECIFIC REGISTRATION ---------------------------------*/
376 PROCEDURE jai_create_item_regns ( p_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE
377 ,p_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
378 ,p_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
379 ,p_tab_attributes jai_inv_items_pkg.GT_ATTRIBUTES%TYPE
380 )
381 AS
382 CURSOR cur_chk_rgm_item_regns
383 IS
384 SELECT rgm_item_regns_id
385 FROM JAI_RGM_ITM_REGNS rirg
386 WHERE rirg.regime_code = p_regime_code
387 AND rirg.organization_id = p_organization_id
388 AND rirg.inventory_item_id = p_inventory_item_id;
389
390 ln_rgm_item_regns_id JAI_RGM_ITM_REGNS.RGM_ITEM_REGNS_ID%TYPE DEFAULT NULL;
391 ltab_attribs jai_inv_items_pkg.GT_ATTRIBUTES%TYPE;
392 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_create_item_regns';
393
394 BEGIN
395 /* Chekc if item specific registration already exists */
396 OPEN cur_chk_rgm_item_regns;
397 FETCH cur_chk_rgm_item_regns INTO ln_rgm_item_regns_id;
398 CLOSE cur_chk_rgm_item_regns;
399
400 IF ln_rgm_item_regns_id IS NULL THEN
401 /*
402 * Item specific registration does not exist so create one by inserting a row in
403 * JAI_RGM_ITM_REGNS
404 */
405
406 --SELECT JAI_RGM_ITM_REGNS_S.NEXTVAL INTO ln_rgm_item_regns_id FROM DUAL;
407
408 INSERT INTO JAI_RGM_ITM_REGNS
409 ( rgm_item_regns_id
410 ,regime_code
411 ,organization_id
412 ,inventory_item_id
413 ,creation_date
414 ,created_by
415 ,last_update_date
416 ,last_updated_by
417 ,last_update_login
418 )
419 VALUES(
420 --ln_rgm_item_regns_id
421 JAI_RGM_ITM_REGNS_S.NEXTVAL /* Modified by Ramananda for removal of SQL LITERALs */
422 , p_regime_code
423 , p_organization_id
424 , p_inventory_item_id
425 , sysdate
426 , fnd_global.user_id
427 , sysdate
428 , fnd_global.user_id
429 , fnd_global.login_id
430 ) returning rgm_item_regns_id into ln_rgm_item_regns_id;
431 END IF;
432
433 jai_inv_items_pkg.jai_create_attribs( p_template_id => ''
434 ,p_rgm_item_regns_id => ln_rgm_item_regns_id
435 ,p_tab_attributes => p_tab_attributes
436 );
437
438 EXCEPTION
439 WHEN OTHERS THEN
440 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
441 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
442 app_exception.raise_exception;
443 END jai_create_item_regns ;
444 /* -------------------------------------- CREATE TEMPLATE ATTRIBUTES ------------------------------*/
445 PROCEDURE jai_create_attribs ( p_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
446 ,p_rgm_item_regns_id JAI_RGM_ITM_REGNS.RGM_ITEM_REGNS_ID%TYPE
447 ,p_tab_attributes jai_inv_items_pkg.GT_ATTRIBUTES%TYPE
448 )
449 IS
450 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_create_attribs';
451 BEGIN
452 /*
453 * For each row in pl-sql table create row in jai_item_templ_attribs
454 */
455 FOR ln_attrib IN 1..p_tab_attributes.COUNT LOOP
456 INSERT INTO JAI_RGM_ITM_TMPL_ATTRS(
457 ITM_TEMPL_ATTRIBUTE_ID,
458 TEMPLATE_ID,
459 RGM_ITEM_REGNS_ID,
460 ATTRIBUTE_CODE,
461 ATTRIBUTE_VALUE,
462 CREATION_DATE,
463 CREATED_BY,
464 LAST_UPDATE_DATE,
465 LAST_UPDATE_LOGIN,
466 LAST_UPDATED_BY
467 ) VALUES (JAI_RGM_ITM_TMPL_ATTRS_S.nextval
468 ,p_template_id
469 ,p_rgm_item_regns_id
470 ,p_tab_attributes(ln_attrib).attribute_code
471 ,p_tab_attributes(ln_attrib).attribute_value
472 ,SYSDATE, fnd_global.user_id , SYSDATE, fnd_global.login_id, fnd_global.user_id
473 );
474 END LOOP;
475 EXCEPTION
476 WHEN OTHERS THEN
477 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
478 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
479 app_exception.raise_exception;
480 END jai_create_attribs;
481
482 /* ------------------------------------------- SYNCHRONIZATION ---------------------------------------*/
483
484 procedure jai_synchronize_jmsi
485 (
489 lv_new_excise_flag JAI_INV_ITM_SETUPS.excise_flag%type;
486 p_synchronization_number JAI_INV_ITM_SETUPS.synchronization_number%type default null
487 )
488 is
490 lv_new_item_class JAI_INV_ITM_SETUPS.item_class%type;
491 lv_new_modvat_flag JAI_INV_ITM_SETUPS.modvat_flag%type;
492 lv_new_item_tariff JAI_INV_ITM_SETUPS.item_tariff%type;
493 lv_new_item_folio JAI_INV_ITM_SETUPS.item_folio%type;
494 lv_new_item_trading_flag JAI_INV_ITM_SETUPS.item_trading_flag%type;
495 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.jai_synchronize_jmsi';
496
497 cursor cur_get_jmsi_row(cpn_synchronization_number number ) is
498 select
499 jmsi.rowid,
500 jmsi.excise_flag ,
501 jmsi.item_class ,
502 jmsi.modvat_flag ,
503 jmsi.item_tariff ,
504 jmsi.item_folio ,
505 jmsi.item_trading_flag,
506 jmsi.organization_id,
507 jmsi.inventory_item_id
508 from JAI_INV_ITM_SETUPS jmsi
509 where (
510 (cpn_synchronization_number is null)
511 or
512 ( (cpn_synchronization_number is not null ) and (synchronization_number = cpn_synchronization_number ) )
513 );
514
515 cursor cur_get_itm_attribs
516 (
517 cpn_organization_id jai_rgm_item_attrib_v.organization_id%type
518 ,cpn_inventory_item_id jai_rgm_item_attrib_v.inventory_item_id%type
519 )
520 is
521 select attribute_code,
522 attribute_value,
523 last_updated_by
524 from jai_rgm_item_attrib_v
525 where organization_id = cpn_organization_id
526 and inventory_item_id = cpn_inventory_item_id
527 AND regime_code = jai_constants.excise_regime ;
528
529 begin
530 /* Get all the rows to be synchronized */
531 for rec_jmsi in cur_get_jmsi_row(p_synchronization_number)
532 loop
533
534 lv_new_excise_flag := null;
535 lv_new_item_class := null;
536 lv_new_modvat_flag := null;
537 lv_new_item_tariff := null;
538 lv_new_item_folio := null;
539 lv_new_item_trading_flag := null;
540
541 /* Get attributes for each organization and inventory item */
542 for rec_attribs in
543 cur_get_itm_attribs (cpn_organization_id => rec_jmsi.organization_id,
544 cpn_inventory_item_id => rec_jmsi.inventory_item_id
545 )
546 loop
547
548 if rec_attribs.attribute_code ='EXCISABLE' then
549 lv_new_excise_flag := rec_attribs.attribute_value;
550 elsif rec_attribs.attribute_code ='ITEM CLASS' then
551 lv_new_item_class := rec_attribs.attribute_value;
552 elsif rec_attribs.attribute_code ='MODVATABLE' then
553 lv_new_modvat_flag := rec_attribs.attribute_value;
554 elsif rec_attribs.attribute_code ='ITEM TARIFF' then
555 lv_new_item_tariff := rec_attribs.attribute_value;
556 elsif rec_attribs.attribute_code ='ITEM FOLIO' then
557 lv_new_item_folio := rec_attribs.attribute_value;
558 elsif rec_attribs.attribute_code ='TRADABLE' then
559 lv_new_item_trading_flag := rec_attribs.attribute_value;
560 end if;
561
562 end loop; /* Attributes */
563
564 /* Update JAI_INV_ITM_SETUPS if atleast one attribute is changed */
565
566 if nvl(lv_new_excise_flag, 'NULL') <> nvl(rec_jmsi.excise_flag, 'NULL') or
567 nvl(lv_new_item_class, 'NULL') <> nvl(rec_jmsi.item_class, 'NULL') or
568 nvl(lv_new_modvat_flag, 'NULL') <> nvl(rec_jmsi.modvat_flag, 'NULL') or
569 nvl(lv_new_item_tariff, 'NULL') <> nvl(rec_jmsi.item_tariff, 'NULL') or
570 nvl(lv_new_item_folio, 'NULL') <> nvl(rec_jmsi.item_folio, 'NULL') or
571 nvl(lv_new_item_trading_flag, 'NULL') <> nvl(rec_jmsi.item_trading_flag, 'NULL')
572 then
573
574 update JAI_INV_ITM_SETUPS
575 set excise_flag = lv_new_excise_flag
576 , item_class = lv_new_item_class
577 , modvat_flag = lv_new_modvat_flag
578 , item_tariff = lv_new_item_tariff
579 , item_folio = lv_new_item_folio
580 , item_trading_flag = lv_new_item_trading_flag
581 , last_update_date = sysdate
582 , last_updated_by = 0405051/* p_synchronization_number*/
583 where rowid = rec_jmsi.rowid;
584
585 end if; /* update */
586
587 end loop; /* rec_jmsi */
588 EXCEPTION
589 WHEN OTHERS THEN
590 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
591 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
592 app_exception.raise_exception;
593 end jai_synchronize_jmsi;
594 /*-----------------------------------------------------------------------------------*/
595 /* Added by Brathod for bug#4389149 */
596 /* India Localization code hook for Base item copy/delete/assignment/import action */
597 PROCEDURE propagate_item_action
598 (
599 pv_action_type IN VARCHAR2
600 , pn_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
604 , pn_set_process_id IN NUMBER
601 , pn_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
602 , pn_source_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
603 , pn_source_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
605 , pv_called_from IN VARCHAR2
606 )
607 IS
608 CURSOR cur_get_items_from_interface
609 IS
610 SELECT intf.organization_id organization_id
611 ,intf.inventory_item_id inventory_item_id
612 ,master_org.master_organization_id source_organization_id
613 FROM mtl_system_items_interface intf
614 ,mtl_parameters master_org
615 WHERE intf.process_flag = 7
616 AND intf.transaction_type = 'CREATE'
617 AND intf.request_id = fnd_global.conc_request_id
618 AND intf.set_process_id = pn_set_process_id
619 AND intf.organization_id = master_org.organization_id
620 AND intf.organization_id <> master_org.master_organization_id ;
621
622 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.propagate_item_action';
623
624 BEGIN
625 /*
626 pv_action_type "IMPORT" indicates to import items from interface.
627 In this case pt_item_data (PL/SQL) table will be null and desired data will
628 be fetched from interface table
629 */
630
631 IF pv_action_type IN ('IMPORT') THEN
632 FOR rec_itms IN cur_get_items_from_interface
633 LOOP
634
635 IF rec_itms.organization_id IS NULL THEN
636 fnd_message.set_name('JA','JAI_DEST_ORG_CANT_BE_NULL');
637 app_exception.raise_exception;
638 END IF;
639 IF rec_itms.inventory_item_id IS NULL THEN
640 fnd_message.set_name ('JA','JAI_ITEM_CANT_BE_NULL');
641 app_exception.raise_exception;
642 END IF;
643 IF rec_itms.source_organization_id IS NULL THEN
644 fnd_message.set_name('JA','JAI_SOURCE_ORG_CANT_BE_NULL');
645 app_exception.raise_exception;
646 END IF;
647
648 jai_inv_items_pkg.copy_items
649 ( pn_organization_id => rec_itms.organization_id
650 ,pn_inventory_item_id => rec_itms.inventory_item_id
651 ,pn_source_organization_id => rec_itms.source_organization_id
652 ,pn_source_inventory_item_id => rec_itms.inventory_item_id
653 );
654 END LOOP;
655 ELSE /* pv_action_type is either COPY, ASSIGN or DELETE */
656
657 IF pv_action_type IN ('COPY','ASSIGN') THEN
658
659 IF pn_organization_id IS NULL
660 OR pn_inventory_item_id IS NULL
661 OR pn_source_organization_id IS NULL
662 OR pn_source_inventory_item_id IS NULL THEN
663 fnd_message.set_name('JA','JAI_IL_API_ARGS_NOT_PROPER');
664 app_exception.raise_exception;
665 END IF;
666
667 jai_inv_items_pkg.copy_items
668 ( pn_organization_id => pn_organization_id
669 ,pn_inventory_item_id => pn_inventory_item_id
670 ,pn_source_organization_id => pn_source_organization_id
671 ,pn_source_inventory_item_id => pn_source_inventory_item_id
672 );
673
674 ELSIF pv_action_type IN ('DELETE') THEN
675
676 IF pn_organization_id IS NULL
677 OR pn_inventory_item_id IS NULL THEN
678 fnd_message.set_name('JA','JAI_IL_API_ARGS_NOT_PROPER');
679 app_exception.raise_exception;
680 END IF;
681
682 jai_inv_items_pkg.delete_items
683 (
684 pn_organization_id => pn_organization_id
685 ,pn_inventory_item_id => pn_inventory_item_id
686 );
687 END IF;
688 END IF;
689 EXCEPTION
690 WHEN OTHERS THEN
691 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
692 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||SQLERRM );
693 app_exception.raise_exception;
694 END propagate_item_action;
695 /*----------------------------------------------------------------------------------*/
696 PROCEDURE copy_items
697 ( pn_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
698 ,pn_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
699 ,pn_source_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
700 ,pn_source_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
701 )
702 IS
703 ln_items NUMBER := 0;
704 ln_item_regns_id NUMBER ;
705 ln_dest_item_regns_id NUMBER ;
706 ln_template_id NUMBER ;
707 ln_dest_template_id NUMBER ;
708
709 ln_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE ;
710 ln_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE ;
711 ln_source_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE ;
712 ln_source_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE ;
713
714 lt_attribs JAI_INV_ITEMS_PKG.GT_ATTRIBUTES%TYPE;
715 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.copy_items';
716
717 CURSOR cur_get_item_regns
721 )
718 (cpv_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE
719 ,cpn_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
720 ,cpn_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
722 IS
723 SELECT rgm_item_regns_id
724 FROM JAI_RGM_ITM_REGNS
725 WHERE regime_code = cpv_regime_code
726 AND organization_id = cpn_organization_id
727 AND inventory_item_id = cpn_inventory_item_id;
728
729 CURSOR cur_get_rec_item_attrib (cpv_itm_templ_flg VARCHAR2
730 ,cpn_itm_templ_id NUMBER
731 )
732 IS
733 SELECT template_id
734 ,rgm_item_regns_id
735 ,attribute_code
736 ,attribute_value
737 FROM JAI_RGM_ITM_TMPL_ATTRS
738 WHERE (( template_id = cpn_itm_templ_id AND cpv_itm_templ_flg = 'T')
739 OR
740 ( rgm_item_regns_id = cpn_itm_templ_id AND cpv_itm_templ_flg = 'I')
741 );
742
743 CURSOR cur_get_template_id
744 (cpv_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE
745 ,cpn_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
746 ,cpn_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
747 )
748 IS
749 SELECT torg.template_id
750 FROM JAI_RGM_TMPL_ITM_REGNS tirg
751 ,JAI_RGM_TMPL_ORG_REGNS torg
752 ,JAI_RGM_ITM_TEMPLATES rgtmp
753 WHERE tirg.templ_org_regns_id = torg.templ_org_regns_id
754 AND torg.template_id = rgtmp.template_id
755 AND torg.organization_id = cpn_organization_id
756 AND tirg.inventory_item_id = cpn_inventory_item_id
757 AND rgtmp.regime_code = cpv_regime_code;
758
759
760 BEGIN
761
762 ln_organization_id := pn_organization_id;
763 ln_inventory_item_id := pn_inventory_item_id;
764 ln_source_organization_id := pn_source_organization_id;
765 ln_source_inventory_item_id := pn_source_inventory_item_id;
766
767
768 /* Check if destination item already exists */
769
770 OPEN cur_get_item_regns (cpv_regime_code => jai_constants.excise_regime
771 ,cpn_organization_id => ln_organization_id
772 ,cpn_inventory_item_id => ln_inventory_item_id
773 );
774
775 FETCH cur_get_item_regns INTO ln_dest_item_regns_id;
776
777 CLOSE cur_get_item_regns;
778
779 IF ln_dest_item_regns_id IS NOT NULL THEN
780 fnd_message.set_name('JA', 'JAI_DUP_ITEM');
781 app_exception.raise_exception;
782 END IF;
783
784 /* Check if item specific registration exists for the source item */
785
786 OPEN cur_get_item_regns (cpv_regime_code => jai_constants.excise_regime
787 ,cpn_organization_id => ln_source_organization_id
788 ,cpn_inventory_item_id => ln_source_inventory_item_id
789 );
790 FETCH cur_get_item_regns INTO ln_item_regns_id;
791 CLOSE cur_get_item_regns;
792
793
794 IF ln_item_regns_id IS NOT NULL THEN
795 /*
796 Item specific registration exists, so create item specific registration for
797 new item by copying the attributes of source item
798 */
799 lt_attribs.delete; -- Flush plsql table
800
801 FOR cur_attribs IN cur_get_rec_item_attrib (cpv_itm_templ_flg => 'I'
802 ,cpn_itm_templ_id => ln_item_regns_id
803 )
804 LOOP
805 lt_attribs(lt_attribs.count+1).attribute_code := cur_attribs.attribute_code;
806 lt_attribs(lt_attribs.count).attribute_value := cur_attribs.attribute_value;
807 END LOOP;
808
809 jai_inv_items_pkg.jai_create_item_regns
810 ( p_regime_code => jai_constants.excise_regime
811 ,p_organization_id => ln_organization_id
812 ,p_inventory_item_id => ln_inventory_item_id
813 ,p_tab_attributes => lt_attribs
814 );
815 END IF; /* End of Item Specific Registration*/
816
817 /* Check if source item is alredy registred with some template */
818 OPEN cur_get_template_id
819 (cpv_regime_code => jai_constants.excise_regime
820 ,cpn_organization_id => ln_organization_id
821 ,cpn_inventory_item_id => ln_inventory_item_id
822 );
823 FETCH cur_get_template_id INTO ln_dest_template_id;
824 CLOSE cur_get_template_id ;
825
826 IF ln_dest_template_id IS NOT NULL THEN
827 /* Item is alredy registered so nothing to do */
828 RETURN;
829 END IF;
830
831 /* Check if source item is assigned to template */
832 OPEN cur_get_template_id
833 (cpv_regime_code => jai_constants.excise_regime
834 ,cpn_organization_id => ln_source_organization_id
835 ,cpn_inventory_item_id => ln_source_inventory_item_id
836 );
837 FETCH cur_get_template_id INTO ln_template_id;
838 CLOSE cur_get_template_id ;
839
840 IF ln_template_id IS NOT NULL THEN
841 /*
845 jai_inv_items_pkg.jai_assign_template (p_template_id => ln_template_id
842 Source item is assigned to a template and so new item should also
843 be assigned to the same template
844 */
846 ,p_organization_id => ln_organization_id
847 ,p_inventory_item_id => ln_inventory_item_id
848 );
849
850 END IF; /* End of Template Assignment */
851 /*
852 For Excise regime create a copy record in the JAI_INV_ITM_SETUPS
853 from the source item
854 */
855 INSERT INTO JAI_INV_ITM_SETUPS
856 (
857 inventory_item_id
858 , organization_id
859 , item_class
860 , modvat_flag
861 , item_tariff
862 , item_folio
863 , excise_flag
864 , creation_date
865 , created_by
866 , last_update_date
867 , last_updated_by
868 , last_update_login
869 , item_trading_flag
870 , synchronization_number
871 )
872 (SELECT
873 ln_inventory_item_id
874 , ln_organization_id
875 , item_class
876 , modvat_flag
877 , item_tariff
878 , item_folio
879 , excise_flag
880 , sysdate
881 , fnd_global.user_id
882 , sysdate
883 , fnd_global.user_id
884 , fnd_global.login_id
885 , item_trading_flag
886 , NULL
887 FROM JAI_INV_ITM_SETUPS
888 WHERE organization_id = ln_source_organization_id
889 AND inventory_item_id = ln_source_inventory_item_id
890 );
891 EXCEPTION
892 WHEN OTHERS THEN
893 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
894 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||SQLERRM );
895 app_exception.raise_exception;
896 END copy_items;
897 /* ------------------------------- END OF PROCEDURE COPY_ITEMS ------------------------*/
898
899 PROCEDURE delete_items ( pn_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
900 ,pn_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
901 )
902 IS
903 ln_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE ;
904 ln_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE ;
905 ln_rgm_item_regns_id JAI_RGM_ITM_REGNS.RGM_ITEM_REGNS_ID%TYPE;
906 ln_templ_org_regns_id JAI_RGM_TMPL_ORG_REGNS.TEMPL_ORG_REGNS_ID%TYPE;
907 ln_templ_itm_regns NUMBER := NULL;
908 lv_object_name CONSTANT VARCHAR2 (61) := 'jai_inv_items_pkg.delete_items';
909
910 CURSOR cur_chk_templ_itm_regns (cpn_templ_org_regns_id JAI_RGM_TMPL_ITM_REGNS.TEMPL_ORG_REGNS_ID%TYPE)
911 IS
912 SELECT 1
913 FROM JAI_RGM_TMPL_ITM_REGNS
914 WHERE templ_org_regns_id = cpn_templ_org_regns_id;
915
916 BEGIN
917 ln_organization_id := pn_organization_id;
918 ln_inventory_item_id := pn_inventory_item_id;
919
920 DELETE FROM JAI_RGM_ITM_REGNS
921 WHERE inventory_item_id = ln_inventory_item_id
922 AND organization_id = ln_organization_id
923 AND regime_code = jai_constants.excise_regime
924 RETURNING rgm_item_regns_id INTO ln_rgm_item_regns_id;
925
926 IF ln_rgm_item_regns_id IS NOT NULL THEN
927 DELETE FROM JAI_RGM_ITM_TMPL_ATTRS
928 WHERE rgm_item_regns_id = ln_rgm_item_regns_id;
929 END IF;
930
931 DELETE FROM JAI_RGM_TMPL_ITM_REGNS
932 WHERE templ_itm_regns_id IN (SELECT templ_itm_regns_id
933 FROM JAI_RGM_TMPL_ITM_REGNS tirg
934 ,JAI_RGM_TMPL_ORG_REGNS torg
935 ,JAI_RGM_ITM_TEMPLATES rgtmp
936 WHERE tirg.templ_org_regns_id = torg.templ_org_regns_id
937 AND torg.template_id = rgtmp.template_id
938 AND tirg.inventory_item_id = ln_inventory_item_id
939 AND torg.organization_id = ln_organization_id
940 AND rgtmp.regime_code = jai_constants.excise_regime
941 )
942 RETURNING templ_org_regns_id INTO ln_templ_org_regns_id ;
943
944 /*
945 Check if any item is registered with templ_org_regns_id. If no such items found
946 then delete the template organizatin registration also
947 */
948 OPEN cur_chk_templ_itm_regns (cpn_templ_org_regns_id => ln_templ_org_regns_id);
949 FETCH cur_chk_templ_itm_regns INTO ln_templ_itm_regns ;
950 CLOSE cur_chk_templ_itm_regns ;
951
952 IF ln_templ_itm_regns IS NULL THEN
953 DELETE FROM JAI_RGM_TMPL_ORG_REGNS
954 WHERE templ_org_regns_id = ln_templ_org_regns_id;
955 END IF;
956
957 DELETE FROM JAI_INV_ITM_SETUPS
958 WHERE organization_id = ln_organization_id
959 AND inventory_item_id = ln_inventory_item_id;
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 FND_MESSAGE.SET_NAME ('JA','JAI_EXCEPTION_OCCURED');
964 FND_MESSAGE.SET_TOKEN ('JAI_PROCESS_MSG',lv_object_name ||'.Err:'||sqlerrm);
965 app_exception.raise_exception;
966 END delete_items;
967 /* -----------------------END OF PROCEDURE DELETE_ITEMS ----------------------------*/
968 /* End of Bug# 4389149 */
969
970 end jai_inv_items_pkg;