1 PACKAGE EGO_GTIN_PVT AS
2 /* $Header: EGOUCCPS.pls 120.8 2007/03/27 16:59:27 dsakalle ship $ */
3
4 --Global Variables
5 TYPE REF_CURSOR_TYPE IS REF CURSOR;
6
7 /* Public API for getting the Publication Status
8 **
9 */
10
11 FUNCTION Get_Publication_Status
12 ( p_inventory_item_id IN NUMBER
13 , p_org_id IN NUMBER
14 , p_address_id IN NUMBER
15 ) RETURN VARCHAR2;
16
17
18 FUNCTION Get_Publication_Status
19 ( p_inventory_item_id IN NUMBER
20 , p_org_id IN NUMBER
21 , p_gln IN VARCHAR2
22 , p_customer_id IN NUMBER
23 , p_address_id IN NUMBER
24 ) RETURN VARCHAR2;
25
26 FUNCTION Get_Publication_Status_Code
27 ( p_inventory_item_id IN NUMBER
28 , p_org_id IN NUMBER
29 , p_address_id IN NUMBER
30 ) RETURN VARCHAR2;
31
32
33 FUNCTION Get_Publication_Status_Code
34 ( p_inventory_item_id IN NUMBER
35 , p_org_id IN NUMBER
36 , p_gln IN VARCHAR2
37 , p_customer_id IN NUMBER
38 , p_address_id IN NUMBER
39 ) RETURN VARCHAR2;
40
41
42
43 FUNCTION Is_Not_Published
44 ( p_inventory_item_id IN NUMBER
45 , p_org_id IN NUMBER
46 , p_gln IN VARCHAR2
47 , p_customer_id IN NUMBER
48 , p_address_id IN NUMBER
49 ) RETURN BOOLEAN;
50
51 FUNCTION Is_Publication_In_Prog
52 ( p_inventory_item_id IN NUMBER
53 , p_org_id IN NUMBER
54 , p_gln IN VARCHAR2
55 , p_customer_id IN NUMBER
56 , p_address_id IN NUMBER
57 ) RETURN BOOLEAN;
58
59 FUNCTION Is_Published
60 ( p_inventory_item_id IN NUMBER
61 , p_org_id IN NUMBER
62 , p_gln IN VARCHAR2
63 , p_customer_id IN NUMBER
64 , p_address_id IN NUMBER
65 ) RETURN BOOLEAN;
66
67
68 FUNCTION Is_Re_Publish_Needed
69 ( p_inventory_item_id IN NUMBER
70 , p_org_id IN NUMBER
71 , p_gln IN VARCHAR2
72 , p_customer_id IN NUMBER
73 , p_address_id IN NUMBER
74 ) RETURN BOOLEAN;
75
76 FUNCTION Is_Withdrawn
77 ( p_inventory_item_id IN NUMBER
78 , p_org_id IN NUMBER
79 , p_gln IN VARCHAR2
80 , p_customer_id IN NUMBER
81 , p_address_id IN NUMBER
82 ) RETURN BOOLEAN;
83
84 FUNCTION Is_Rejected
85 ( p_inventory_item_id IN NUMBER
86 , p_org_id IN NUMBER
87 , p_gln IN VARCHAR2
88 , p_customer_id IN NUMBER
89 , p_address_id IN NUMBER
90 ) RETURN BOOLEAN;
91
92 FUNCTION Is_Delisted
93 ( p_inventory_item_id IN NUMBER
94 , p_org_id IN NUMBER
95 ) RETURN BOOLEAN;
96
97
98
99 /* Public API for getting the Registration Status
100 **
101 */
102
103 FUNCTION Get_Registration_Status
104 ( p_inventory_item_id IN NUMBER
105 , p_org_id IN NUMBER
106 ) RETURN VARCHAR2;
107
108 FUNCTION Get_Registration_Status_Code
109 ( p_inventory_item_id IN NUMBER
110 , p_org_id IN NUMBER
111 ) RETURN VARCHAR2;
112
113
114 FUNCTION Is_Not_Registered
115 ( p_inventory_item_id IN NUMBER
116 , p_org_id IN NUMBER
117 ) RETURN BOOLEAN;
118
119 FUNCTION Is_Registration_In_Prog
120 ( p_inventory_item_id IN NUMBER
121 , p_org_id IN NUMBER
122 ) RETURN BOOLEAN;
123
124 FUNCTION Is_Registered
125 ( p_inventory_item_id IN NUMBER
126 , p_org_id IN NUMBER
127 ) RETURN BOOLEAN;
128
129
130 FUNCTION Is_Re_Register_Needed
131 ( p_inventory_item_id IN NUMBER
132 , p_org_id IN NUMBER
133 ) RETURN BOOLEAN;
134
135
136 /*
137 * This API will return 'Y' if the item with p_inventory_item_id, p_org_id has ever been published
138 * to any customers before. Else, this API will return 'N'
139 */
140 FUNCTION Is_Globally_Published
141 ( p_inventory_item_id IN NUMBER
142 , p_org_id IN NUMBER
143 ) RETURN VARCHAR2;
144
145 /* Bug 5523228 - API validates the Unit wt and wt uom against Trade Item Descriptor */
146 FUNCTION Validate_Unit_Wt_Uom
147 ( p_inventory_item_id IN NUMBER
148 , p_org_id IN NUMBER
149 ) RETURN VARCHAR2;
150
151 /*
152 * Added by Nisar and is called from IOI Category update to handle update of UDex Catalog.
153 */
154 PROCEDURE PROCESS_CAT_ASSIGNMENT ( p_inventory_item_id NUMBER,
155 p_organization_id NUMBER);
156
157 /*
158 ** Added by Devendra - This method will be called from Items IOI.
159 * This procedure will validate the MSI attributes for UCCnet and will call PROCESS_ATTRIBUTE_UPDATES
160 */
161 PROCEDURE PROCESS_UCCNET_ATTRIBUTES (P_Prog_AppId NUMBER DEFAULT -1,
162 P_Prog_Id NUMBER DEFAULT -1,
163 P_Request_Id NUMBER DEFAULT -1,
164 P_User_Id NUMBER DEFAULT -1,
165 P_Login_Id NUMBER DEFAULT -1,
166 P_Set_id NUMBER DEFAULT -999,
167 P_Suppress_Rollup VARCHAR2 DEFAULT 'N'
168 );
169
170
171
172 /*
173 ** Added by Devendra - for updation of REGISTRATION_LAST_UPDATE_DATE and TP_NEUTRAL_LAST_UPDATE_DATE
174 */
175 PROCEDURE PROCESS_ATTRIBUTE_UPDATES (p_inventory_item_id NUMBER,
176 p_organization_id NUMBER,
177 p_attribute_names EGO_VARCHAR_TBL_TYPE,
178 p_commit VARCHAR2 := FND_API.G_FALSE,
179 x_return_status OUT NOCOPY VARCHAR2,
180 x_msg_count OUT NOCOPY NUMBER,
181 x_msg_data OUT NOCOPY VARCHAR2);
182
183 /*
184 ** Added by Devendra - This method will update the REGISTRATION_UPDATE_DATE and TP_NEUTRAL_UPDATE_DATE
185 ** for an item. If parameter p_update_reg is supplied as 'Y' then REGISTRATION_UPDATE_DATE and
186 ** TP_NEUTRAL_UPDATE_DATE will be updated else only TP_NEUTRAL_UPDATE_DATE will be updated.
187 */
188 PROCEDURE UPDATE_REG_PUB_UPDATE_DATES (p_inventory_item_id NUMBER,
189 p_organization_id NUMBER,
190 p_update_reg VARCHAR2 := 'N',
191 p_commit VARCHAR2 := FND_API.G_FALSE,
192 x_return_status OUT NOCOPY VARCHAR2,
193 x_msg_count OUT NOCOPY NUMBER,
194 x_msg_data OUT NOCOPY VARCHAR2);
195
196
197 /*
198 ** Added by Amay - for propagation of attributes up the hierarchy
199 */
200 PROCEDURE Item_Propagate_Attributes
201 ( p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
202 , p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
203 , p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
204 , p_attr_diffs IN EGO_USER_ATTR_DIFF_TABLE
205 , p_transaction_type IN VARCHAR2
206 , p_attr_group_id IN NUMBER DEFAULT NULL
207 , x_error_message OUT NOCOPY VARCHAR2
208 );
209
210 /*
211 ** Added by Amay - for setting of attributes (called by BOM_COMPUTE_FUNCTIONS)
212 */
213 PROCEDURE Update_Attribute
214 ( p_inventory_item_id IN NUMBER
215 , p_organization_id IN NUMBER
216 , p_attr_name IN VARCHAR2
217 , p_attr_group_type IN VARCHAR2 DEFAULT NULL
218 , p_attr_group_name IN VARCHAR2 DEFAULT NULL
219 , p_attr_new_value_str IN VARCHAR2 DEFAULT NULL
220 , p_attr_new_value_num IN NUMBER DEFAULT NULL
221 , p_attr_new_value_date IN DATE DEFAULT NULL
222 , p_attr_new_value_uom IN VARCHAR2 DEFAULT NULL
223 , p_debug_level IN NUMBER DEFAULT 0
224 , x_return_status OUT NOCOPY VARCHAR2
225 , x_errorcode OUT NOCOPY NUMBER
226 , x_msg_count OUT NOCOPY NUMBER
227 , x_msg_data OUT NOCOPY VARCHAR2
228 );
229
230 /*
231 ** Added by Amay - for setting of attributes (called by BOM_COMPUTE_FUNCTIONS)
232 */
233 PROCEDURE Update_Attributes
234 ( p_pk_column_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
235 , p_class_code_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
236 , p_data_level_name_value_pairs IN EGO_COL_NAME_VALUE_PAIR_ARRAY
237 , p_attr_diffs IN EGO_USER_ATTR_DIFF_TABLE
238 , p_transaction_type IN VARCHAR2
239 , p_attr_group_id IN NUMBER DEFAULT NULL
240 , x_error_message OUT NOCOPY VARCHAR2
241 );
242
243 /*
244 ** Added by Amay - for getting of attribute diff objects (called by BOM_ROLLUP_PUB)
245 */
246 PROCEDURE Get_Attr_Diffs
247 ( p_inventory_item_id IN NUMBER
248 , p_org_id IN NUMBER
249 , p_attr_group_id IN NUMBER DEFAULT NULL
250 , p_application_id IN NUMBER DEFAULT NULL
251 , p_attr_group_type IN VARCHAR2 DEFAULT NULL
252 , p_attr_group_name IN VARCHAR2 DEFAULT NULL
253 , px_attr_diffs IN OUT NOCOPY EGO_USER_ATTR_DIFF_TABLE
254 , px_pk_column_name_value_pairs OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
255 , px_class_code_name_value_pairs OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
256 , px_data_level_name_value_pairs OUT NOCOPY EGO_COL_NAME_VALUE_PAIR_ARRAY
257 , x_error_message OUT NOCOPY VARCHAR2
258 );
259
260 /*
261 ** Added by Amay - for setting of special case attribute TOP_GTIN
262 */
263 PROCEDURE Set_Top_GTIN_Flag
264 ( p_inventory_item_id IN NUMBER
265 , p_organization_id IN NUMBER
266 , p_top_gtin_flag IN VARCHAR2
267 , x_return_status OUT NOCOPY VARCHAR2
268 );
269
270 Function Is_Attribute_Group_Associated
271 ( p_application_id IN NUMBER
272 , p_attr_group_type IN VARCHAR2
273 , p_attr_group_name IN VARCHAR2
274 , p_inventory_item_id IN NUMBER
275 , p_organization_id IN NUMBER
276 )
277 RETURN BOOLEAN;
278
279 PROCEDURE Seed_Uccnet_Attributes_Pages;
280
281 FUNCTION Is_In_Sync_customer
282 ( p_inventory_item_id IN NUMBER
283 , p_org_id IN NUMBER
284 , p_address_id IN NUMBER
285 , p_explode_group_id IN NUMBER
286 ) RETURN VARCHAR2;
287
288 /*
289 * This method validates SBDH attributes. p_address_id is mandatory and not null
290 * returns a data object containing all errors
291 */
292 PROCEDURE Validate_SBDH_Attributes(p_inventory_item_id NUMBER,
293 p_organization_id NUMBER,
294 p_address_id NUMBER,
295 p_errors OUT NOCOPY REF_CURSOR_TYPE);
296
297 /*
298 * This method validates SBDH attributes. p_address_id is mandatory and not null
299 * returns 'F' if some validation fails
300 */
301 FUNCTION Is_SBDH_Attributes_Valid(p_inventory_item_id NUMBER,
302 p_organization_id NUMBER,
303 p_address_id NUMBER) RETURN VARCHAR2;
304
305 /*
306 * This procedure is added as a part of fix for bug: 3983838
307 * This procedure is called from User Defined attributes EO i.e. EgoMtlSyItemsExtVLEOImpl
308 * If any Extension GDSN attributes are updated, we update the TP_NEUTRAL_UPDATE_DATE or
309 * LAST_UPDATE_DATE of EGO_ITEM_TP_ATTRS_EXT_B, depending upon whether the Attibute group
310 * is TP-Dependant or not.
311 */
312 PROCEDURE PROCESS_EXTN_ATTRIBUTE_UPDATES (p_inventory_item_id NUMBER,
313 p_organization_id NUMBER,
314 p_attribute_names EGO_VARCHAR_TBL_TYPE,
315 p_attr_group_name VARCHAR2,
316 p_commit VARCHAR2 := FND_API.G_FALSE,
317 x_return_status OUT NOCOPY VARCHAR2,
318 x_msg_count OUT NOCOPY NUMBER,
319 x_msg_data OUT NOCOPY VARCHAR2);
320
321 PROCEDURE PROCESS_GTID_UPDATE (p_inventory_item_id NUMBER,
322 p_organization_id NUMBER,
323 p_trade_item_desc VARCHAR2,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2);
327
328 END EGO_GTIN_PVT;