1 PACKAGE jai_inv_items_pkg
2 /* $Header: jai_inv_items.pls 120.4 2012/05/23 09:48:22 zxin ship $ */
3 AUTHID CURRENT_USER AS
4 /* */
5 /* ------------------------------------------------------------------------------------------------------------------------------------------------
6 FILENAME: jai_items_pkg_s.pls CHANGE HISTORY:
7 SlNo. DD/MM/YYYY Author and Details of Modifications
8 -------------------------------------------------------------------------------------------------------------------------------------------------
9 1. 22/04/2005 Brathod for Bug # 4299606 (Item DFF Elimination), File Version 116.1
10
11 Issue:- Item DFF Needs to be eliminated
12 Package fie is renamed to JAI_INV_ITEMS_PKG.PLS
13 -----------------------------------------------
14 2. 24/05/2005 Brathod, For Bug# 4389149 (Item Code Hook - API), File Version 116.1
15 Issue:-
16 Code hook (API) needs to developed that will be called when item is either
17 copied, assigned, deleted or imported in the base item form.
18 Fix:-
19 To support this functionality a procedure PROPAGATE_ITEM_ACTION is developed
20 which accepts the base action as well as pl/sql table as inventory items to be processed.
21 The procedure will be able to process items in bulk so that only one call from base item
22 form can propagate changes in many items. Each row in plsql table PT_ITEM_DATA will be
23 a comman (,) seperated string depending upon the PV_ACTION_TYPE argument.
24 (For more details regarding possible values for each argument and format of the string
25 for each PV_ACTION_TYPE argument please refere the bug# 4389149)
26
27 3. 08-Jun-2005 Version 116.2 jai_inv_items -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
28 as required for CASE COMPLAINCE.
29
30 4. 15-Jul-2005 Brathod, For Bug# 4496223 Version 117.2
31 Issue: -
32 The Code hook API for IL Item currently accepts datatype of the type
33 table_item which is pl-sql table of varchar2(100). But as the code hook
34 needs to be called by base application it should not have any depedancy on IL
35 Product. Use of this data type introduces the dependancy of IL.
36 Solution:-
37 To avoid this the datatype should be independent of product. The pl-sql table
38 type aregument is removed from PROPAGATE_ITEM_ACTION procedure and added
39 the following four simple arguments
40 1. pn_organization_id - NUMBER - Destination Organization
41 2. pn_inventory_item_id - NUMBER - Destination Inventory Item
42 3. pn_source_organization_id - NUMBER - Source Organization
43 4. pn_source_inventory_item_id - NUMBER - Source Inventory Item
44 of TABLE_ITEMS.
45
46 5. 28-JAN-2010 CSahoo for bug#9191274, File Version: 120.2.12000000.2
47 ISSUE: VAT ITEM ATTRIBUTES NOT ASSIGNED AUTOMATICALLY FOR STAR ITEM, AFTER CONFIGURATI
48 Fix: Added a parameter pn_regime_code to the procedure copy_items.
49
50
51 Future Dependencies For the release Of this Object:-
52 (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/
53 A datamodel change )
54
55 -------------------------------------------------------------------------------------------------------------------------------------------------
56 Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
57 Of File On Bug/Patchset
58 jai_items_pkg_s.sql
59 --------------------------------------------------------------------------------------------------------------------------------------------------
60 115.0 4245134 IN60105D2 VAT Objects 115.0 Brathod 17-Mar-2005 Technical Dependacny
61 +4245089
62 --------------------------------------------------------------------------------------------------------------------------------------------------*/
63 TYPE type_rec_jmsi_attribs IS RECORD
64 (
65 item_class JAI_INV_ITM_SETUPS.item_class%TYPE
66 , modvat_flag JAI_INV_ITM_SETUPS.modvat_flag%TYPE
67 , item_tariff JAI_INV_ITM_SETUPS.item_tariff%TYPE
68 , item_folio JAI_INV_ITM_SETUPS.item_folio%TYPE
69 , excise_flag JAI_INV_ITM_SETUPS.excise_flag%TYPE
70 , trading_flag JAI_INV_ITM_SETUPS.item_trading_flag%TYPE
71 , count NUMBER
72 );
73
74 TYPE type_rec_rgm_itm_attribs IS RECORD
75 (
76 attribute_code JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_CODE%TYPE
77 ,attribute_value JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_VALUE%TYPE
78 );
79
80 TYPE type_tab_attributes IS TABLE OF type_rec_rgm_itm_attribs
81 INDEX BY BINARY_INTEGER;
82
83 gr_jmsi_attribs type_rec_jmsi_attribs;
84 gt_attributes type_tab_attributes;
85
86 /* Added by Brathod for bug#4389149 */
87
88 -- TYPE table_items IS TABLE OF VARCHAR2 (100) -- Bug# 4496223
89 -- INDEX BY BINARY_INTEGER;
90
91 /* End of Bug# 4389149 */
92
93 /* API to find out attribute of particular inventory item */
94 PROCEDURE jai_get_attrib
95 ( p_regime_code IN JAI_RGM_ITM_TEMPLATES.REGIME_CODE%TYPE ,
96 p_organization_id IN JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE ,
97 p_inventory_item_id IN JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE ,
98 p_attribute_code IN JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_CODE%TYPE,
99 p_attribute_value OUT NOCOPY JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_VALUE%TYPE,
100 p_process_flag OUT NOCOPY VARCHAR2 ,
101 p_process_msg OUT NOCOPY VARCHAR2
102 );
103 /* This function will create template and retrun the newly created template_id */
104 FUNCTION jai_create_template( p_regime_code JAI_RGM_ITM_TEMPLATES.REGIME_CODE%TYPE
105 , p_template_name JAI_RGM_ITM_TEMPLATES.TEMPLATE_NAME%TYPE
106 , p_description JAI_RGM_ITM_TEMPLATES.DESCRIPTION%TYPE DEFAULT NULL
107 )
108 RETURN NUMBER;
109 /* Procedure to assign template to particular / all inventory items in organization */
110 PROCEDURE jai_assign_template( p_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
111 , p_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
112 , p_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
113 ) ;
114 /* Procedure to create item specific registration */
115 PROCEDURE jai_create_item_regns ( p_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE
116 ,p_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
117 ,p_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
118 ,p_tab_attributes jai_inv_items_pkg.GT_ATTRIBUTES%TYPE
119 );
120 /* Procedure will create either item/template attributes based on attributes passed as pl-sql table */
121 PROCEDURE jai_create_attribs ( p_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
122 ,p_rgm_item_regns_id JAI_RGM_ITM_REGNS.RGM_ITEM_REGNS_ID%TYPE
123 ,p_tab_attributes jai_inv_items_pkg.GT_ATTRIBUTES%TYPE
124 );
125 /* Synchronization procedure for JAI_INV_ITM_SETUPS table */
126 PROCEDURE jai_synchronize_jmsi (p_synchronization_number JAI_INV_ITM_SETUPS.SYNCHRONIZATION_NUMBER%TYPE DEFAULT NULL
127 );
128
129
130 /* Added by Brathod for bug#4389149 */
131 /* India Localization code hook for Base item copy/delete/assignment/import action */
132 PROCEDURE propagate_item_action
133 (
134 pv_action_type IN VARCHAR2
135 --, pt_item_data IN dbms_utility.uncl_array -- TABLE_ITEMS, Bug# 4496223
136 , pn_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
137 , pn_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
138 , pn_source_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
139 , pn_source_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
140 , pn_set_process_id IN NUMBER
141 , pv_called_from IN VARCHAR2
142 );
143 PROCEDURE copy_items
144 ( pn_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
145 ,pn_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
146 ,pn_source_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
147 ,pn_source_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
148 ,pn_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE DEFAULT 'EXCISE' --added for bug#9191274
149 );
150
151 PROCEDURE delete_items
152 ( pn_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
153 ,pn_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
154 );
155
156 /* End of Bug# 4389149 */
157
158 END jai_inv_items_pkg;