1 PACKAGE jai_inv_items_pkg
2 /* $Header: jai_inv_items.pls 120.2 2005/07/22 15:31:50 avallabh ship $ */
3 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
47 Future Dependencies For the release Of this Object:-
48 (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/
49 A datamodel change )
50
51 -------------------------------------------------------------------------------------------------------------------------------------------------
52 Current Version Current Bug Dependent Dependency On Files Version Author Date Remarks
53 Of File On Bug/Patchset
54 jai_items_pkg_s.sql
55 --------------------------------------------------------------------------------------------------------------------------------------------------
56 115.0 4245134 IN60105D2 VAT Objects 115.0 Brathod 17-Mar-2005 Technical Dependacny
57 +4245089
58 --------------------------------------------------------------------------------------------------------------------------------------------------*/
59 TYPE type_rec_jmsi_attribs IS RECORD
60 (
61 item_class JAI_INV_ITM_SETUPS.item_class%TYPE
62 , modvat_flag JAI_INV_ITM_SETUPS.modvat_flag%TYPE
63 , item_tariff JAI_INV_ITM_SETUPS.item_tariff%TYPE
64 , item_folio JAI_INV_ITM_SETUPS.item_folio%TYPE
65 , excise_flag JAI_INV_ITM_SETUPS.excise_flag%TYPE
66 , trading_flag JAI_INV_ITM_SETUPS.item_trading_flag%TYPE
67 , count NUMBER
68 );
69
70 TYPE type_rec_rgm_itm_attribs IS RECORD
71 (
72 attribute_code JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_CODE%TYPE
73 ,attribute_value JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_VALUE%TYPE
74 );
75
76 TYPE type_tab_attributes IS TABLE OF type_rec_rgm_itm_attribs
77 INDEX BY BINARY_INTEGER;
78
79 gr_jmsi_attribs type_rec_jmsi_attribs;
80 gt_attributes type_tab_attributes;
81
82 /* Added by Brathod for bug#4389149 */
83
84 -- TYPE table_items IS TABLE OF VARCHAR2 (100) -- Bug# 4496223
85 -- INDEX BY BINARY_INTEGER;
86
87 /* End of Bug# 4389149 */
88
89 /* API to find out attribute of particular inventory item */
90 PROCEDURE jai_get_attrib
91 ( p_regime_code IN JAI_RGM_ITM_TEMPLATES.REGIME_CODE%TYPE ,
92 p_organization_id IN JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE ,
93 p_inventory_item_id IN JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE ,
94 p_attribute_code IN JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_CODE%TYPE,
95 p_attribute_value OUT NOCOPY JAI_RGM_ITM_TMPL_ATTRS.ATTRIBUTE_VALUE%TYPE,
96 p_process_flag OUT NOCOPY VARCHAR2 ,
97 p_process_msg OUT NOCOPY VARCHAR2
98 );
102 , p_description JAI_RGM_ITM_TEMPLATES.DESCRIPTION%TYPE DEFAULT NULL
99 /* This function will create template and retrun the newly created template_id */
100 FUNCTION jai_create_template( p_regime_code JAI_RGM_ITM_TEMPLATES.REGIME_CODE%TYPE
101 , p_template_name JAI_RGM_ITM_TEMPLATES.TEMPLATE_NAME%TYPE
103 )
104 RETURN NUMBER;
105 /* Procedure to assign template to particular / all inventory items in organization */
106 PROCEDURE jai_assign_template( p_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
107 , p_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
108 , p_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE DEFAULT NULL
109 ) ;
110 /* Procedure to create item specific registration */
111 PROCEDURE jai_create_item_regns ( p_regime_code JAI_RGM_ITM_REGNS.REGIME_CODE%TYPE
112 ,p_organization_id JAI_RGM_ITM_REGNS.ORGANIZATION_ID%TYPE
113 ,p_inventory_item_id JAI_RGM_ITM_REGNS.INVENTORY_ITEM_ID%TYPE
114 ,p_tab_attributes jai_inv_items_pkg.GT_ATTRIBUTES%TYPE
115 );
116 /* Procedure will create either item/template attributes based on attributes passed as pl-sql table */
117 PROCEDURE jai_create_attribs ( p_template_id JAI_RGM_ITM_TEMPLATES.TEMPLATE_ID%TYPE
118 ,p_rgm_item_regns_id JAI_RGM_ITM_REGNS.RGM_ITEM_REGNS_ID%TYPE
119 ,p_tab_attributes jai_inv_items_pkg.GT_ATTRIBUTES%TYPE
120 );
121 /* Synchronization procedure for JAI_INV_ITM_SETUPS table */
122 PROCEDURE jai_synchronize_jmsi (p_synchronization_number JAI_INV_ITM_SETUPS.SYNCHRONIZATION_NUMBER%TYPE DEFAULT NULL
123 );
124
125
126 /* Added by Brathod for bug#4389149 */
127 /* India Localization code hook for Base item copy/delete/assignment/import action */
128 PROCEDURE propagate_item_action
129 (
130 pv_action_type IN VARCHAR2
131 --, pt_item_data IN dbms_utility.uncl_array -- TABLE_ITEMS, Bug# 4496223
132 , pn_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
133 , pn_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
134 , pn_source_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
135 , pn_source_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
136 , pn_set_process_id IN NUMBER
137 , pv_called_from IN VARCHAR2
138 );
139 PROCEDURE copy_items
140 ( pn_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
141 ,pn_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
142 ,pn_source_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
143 ,pn_source_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
144 );
145
146 PROCEDURE delete_items
147 ( pn_organization_id MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
148 ,pn_inventory_item_id MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
149 );
150
151 /* End of Bug# 4389149 */
152
153 END jai_inv_items_pkg;