1 PACKAGE INV_ITEM_ATTRIBUTES_PKG AS
2 --$Header: INVGIAPS.pls 120.1 2005/07/07 03:25:31 myerrams noship $
3 --+=======================================================================+
4 --| Copyright (c) 1999 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVGIAPS.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Item attribute processor for the Item Attribute copy form |
13 --| |
14 --| HISTORY |
15 --| 9/19/2000 DHerring Created |
16 --| 11/21/2002 VMa Added NOCOPY to OUT parameters of |
17 --| find_org_list, get_type_struct, |
18 --| call_item_update for performance |
19 --| 02/10/2004 vjavli GSCC standard compliance; added NOCOPY |
20 --| 20/AUG/2004 nesoni Bug# 3770547. Procedure |
21 --| populate_temp_table definition has been |
22 --| modified to incorporate |
23 --| attribute_category as additional IN |
24 --| parameter. |
25 --| 10/DEC/2004 nesoni Bug# 4025750. Procedure |
26 --| populate_temp_table modified to |
27 --| incorporate CopyDffToNull as additional|
28 --| IN parameter. |
29 --+======================================================================*/
30
31 --===============================================
32 -- CONSTANTS for concurrent program return values
33 --===============================================
34 -- Return values for RETCODE parameter (standard for concurrent programs):
35 RETCODE_SUCCESS VARCHAR2(10) := '0';
36 RETCODE_WARNING VARCHAR2(10) := '1';
37 RETCODE_ERROR VARCHAR2(10) := '2';
38
39
40 --=================
41 -- TYPES
42 --=================
43
44 TYPE att_rec_type IS
45 RECORD (temp_column_name VARCHAR2(240)
46 ,item_column_name VARCHAR2(240)
47 ,column_type NUMBER
48 ,foreign_key_name VARCHAR2(240)
49 ,foreign_key_column VARCHAR2(240)
50 ,reference_key_column VARCHAR2(240)
51 ,display_column VARCHAR2(240)
52 ,chosen_value VARCHAR2(240)
53 ,selected_value VARCHAR2(240)
54 ,lookup_table VARCHAR2(240)
55 ,lookup_type VARCHAR2(240)
56 ,lookup_type_value VARCHAR2(240)
57 ,lookup_column VARCHAR2(240));
58
59 TYPE att_tbl_type IS TABLE OF att_rec_type
60 INDEX BY BINARY_INTEGER;
61
62 TYPE sel_rec_type IS
63 RECORD (organization_id NUMBER
64 ,item_id NUMBER);
65
66 TYPE sel_tbl_type IS TABLE OF sel_rec_type
67 INDEX BY BINARY_INTEGER;
68
69 TYPE cho_rec_type IS
70 RECORD (organization_id NUMBER
71 ,item_id NUMBER
72 ,attribute01 VARCHAR2(240)
73 ,attribute02 VARCHAR2(240)
74 ,attribute03 VARCHAR2(240)
75 ,attribute04 VARCHAR2(240));
76
77
78 TYPE org_tbl_type IS TABLE OF hr_all_organization_units.organization_id%TYPE;
79
80 --=========================
81 -- PROCEDURES AND FUNCTIONS
82 --=========================
83
84 --=========================================================================
85 -- PROCEDURE : find_org_list PUBLIC
86 -- PARAMETERS :
87 -- COMMENT :
88 -- PRE-COND :
89 --=========================================================================
90 PROCEDURE find_org_list
91 ( p_org_tab OUT NOCOPY INV_ORGHIERARCHY_PVT.orgid_tbl_type
92 );
93
94 --=========================================================================
95 -- PROCEDURE : set_type_struct PUBLIC
96 -- PARAMETERS :
97 -- COMMENT :
98 -- PRE-COND :
99 --=========================================================================
100 PROCEDURE set_type_struct
101 (p_att_tab IN ATT_TBL_TYPE
102 ,p_cho_rec IN CHO_REC_TYPE
103 ,p_sel_tab IN SEL_TBL_TYPE);
104
105 --=========================================================================
106 -- PROCEDURE : get_type_struct PUBLIC
107 -- PARAMETERS :
108 -- COMMENT :
109 -- PRE-COND :
110 --=========================================================================
111 PROCEDURE get_type_struct
112 (p_att_tab OUT NOCOPY ATT_TBL_TYPE
113 ,p_cho_rec OUT NOCOPY CHO_REC_TYPE
114 ,p_sel_tab OUT NOCOPY SEL_TBL_TYPE);
115
116 --=========================================================================
117 -- PROCEDURE : populate_type_struct PUBLIC
118 -- PARAMETERS :
119 -- COMMENT :
120 -- PRE-COND :
121 --=========================================================================
122 PROCEDURE populate_type_struct(p_att_tab IN ATT_TBL_TYPE);
123
124 --=========================================================================
125 -- PROCEDURE : populate_temp_table PUBLIC
126 -- PARAMETERS :
127 -- COMMENT :
128 -- PRE-COND :
129 --=========================================================================
130 /* Bug: 3770547
131 One more filter parameter AttributeCategory added to find items that need to be populated*/
132 /* Bug: 4025750
133 One more filter parameter p_copy_dff_to_null added to find items that need to be populated*/
134
135 PROCEDURE populate_temp_table
136 (p_item_id IN NUMBER
137 ,p_org_code_list IN INV_ORGHIERARCHY_PVT.orgid_tbl_type
138 ,p_cat_id IN NUMBER
139 ,p_cat_set_id IN NUMBER
140 ,p_item_low IN VARCHAR2
141 ,p_item_high IN VARCHAR2
142 ,p_sts_code IN VARCHAR2
143 ,p_attribute_category IN VARCHAR2
144 ,p_copy_dff_to_null IN VARCHAR2);
145
146 --=========================================================================
147 -- PROCEDURE : clear_temp_table PUBLIC
148 -- PARAMETERS :
149 -- COMMENT : clear MTL_ITEM_ATTRIBUTES_TEMP
150 -- simple command to purge all records in temp table
151 -- this may not seem necessary as a temp table loses
152 -- it's data at the eand of each session.
153 -- However the session will last until the form is
154 -- dismissed and the user may query several times
155 -- before dismissing the form.
156 -- Each time there is a new query the temp table
157 -- needs to be purged.
158
159 -- PRE-COND : This procedure prior to poulating the temp table
160 --=========================================================================
161 PROCEDURE clear_temp_table;
162
163 --=========================================================================
164 -- PROCEDURE : call_item_update PUBLIC
165 -- PARAMETERS :
166 -- COMMENT : Blanket Update records in the MTL_SYSTEM_ITEMS table
167 -- PRE-COND : This procedure will be called from the form
168 --=========================================================================
169 PROCEDURE call_item_update(
170 p_att_tab IN INV_ITEM_ATTRIBUTES_PKG.att_tbl_type
171 ,p_sel_tab IN INV_ITEM_ATTRIBUTES_PKG.sel_tbl_type
172 ,p_inventory_item_id OUT NOCOPY NUMBER
173 ,p_organization_id OUT NOCOPY NUMBER
174 ,p_return_status OUT NOCOPY VARCHAR2
175 ,p_error_tab OUT NOCOPY INV_Item_GRP.Error_tbl_type
176 );
177
178 --=========================================================================
179 -- PROCEDURE : batch item update PUBLIC
180 -- PARAMETERS: x_errbuf error buffer
181 -- x_retcode 0 success, 1 warning, 2 error
182 -- p_att_tab pl/sql table of records
183 -- p_sel_tab pl/sql table of records
184 -- COMMENT : Called from a concurrent program if used
185 -- this procedure allows the user to work in a
186 -- no modal fashion.
187 -- Blanket Update records in the MTL_SYSTEM_ITEMS table
188 -- The struct att_tab contains the columns that
189 -- are to be updated and the default values they are
190 -- to be updated to.
191 -- The struct sel_tab contains the unique id
192 -- of the records that are to be updated
193 -- The procedure constructs the record p_item_rec
194 -- with the default values
195 -- It then loops through the selected records
196 -- and calls the published item update api
197 -- for each unique record.
198 -- PRE-COND : This procedure will be called from the form
199 --=========================================================================
200 PROCEDURE batch_item_update(
201 x_errbuff OUT NOCOPY VARCHAR2
202 , x_retcode OUT NOCOPY NUMBER
203 , p_seq_id IN NUMBER
204 );
205
206 --=========================================================================
207 -- PROCEDURE : populate_temp_tables PUBLIC
208 -- PARAMETERS :
209 -- COMMENT : This procedure is called just before
210 -- the call to the concurrent program
211 -- which will update the item attributes.
212 --
213 -- PRE-COND : This procedure will be called from the form
214 --=========================================================================
215 PROCEDURE populate_temp_tables(
216 p_att_tab IN INV_ITEM_ATTRIBUTES_PKG.att_tbl_type
217 ,x_seq_id OUT NOCOPY NUMBER
218 );
219
220 --========================================================================
221 -- PROCEDURE : Set_Unit_Test_Mode PUBLIC
222 -- COMMENT : This procedure sets the unit test mode that prevents the
223 -- program from attempting to submit concurrent requests and
224 -- enables it to run it from SQL*Plus. The Item Interface will
225 -- not be run.
226 --=========================================================================
227 PROCEDURE Set_Unit_Test;
228
229 END INV_ITEM_ATTRIBUTES_PKG;
[Home] [Help]