1 PACKAGE ego_item_associations_pub AUTHID CURRENT_USER AS
2 /* $Header: EGOPIASS.pls 120.7 2011/07/14 13:07:14 nendrapu ship $ */
3
4 G_ITEM_LEVEL CONSTANT NUMBER := 43101;
5 G_ITEM_SUPPLIER_LEVEL CONSTANT NUMBER := 43103;
6 G_ITEM_SUPPLIERSITE_LEVEL CONSTANT NUMBER := 43104;
7 G_ITEM_SUPPLIERSITE_ORG_LEVEL CONSTANT NUMBER := 43105;
8
9 G_ITEM_LEVEL_NAME CONSTANT VARCHAR2(20) := 'ITEM_LEVEL';
10 G_ITEM_SUP_LEVEL_NAME CONSTANT VARCHAR2(20) := 'ITEM_SUP';
11 G_ITEM_SUP_SITE_LEVEL_NAME CONSTANT VARCHAR2(20) := 'ITEM_SUP_SITE';
12 G_ITEM_SUP_SITE_ORG_LEVEL_NAME CONSTANT VARCHAR2(20) := 'ITEM_SUP_SITE_ORG';
13
14 G_ASSIGN_PACK_SUPPLIER CONSTANT VARCHAR2(50) := 'EGO_ASSIGN_PACK_SUPPLIER';
15 G_ASSIGN_PACK_SUP_SITE CONSTANT VARCHAR2(50) := 'EGO_ASSIGN_PACK_SUP_SITE';
16 G_ASSIGN_PACK_SS_ORG CONSTANT VARCHAR2(50) := 'EGO_ASSIGN_PACK_SS_ORG';
17 G_ASSIGN_STYLE_SUP_SUPSITE CONSTANT VARCHAR2(50) := 'EGO_DEFAULT_STYLE_SUP_AND_SUP_SITE';
18 --G_ASSIGN_STYLE_SUP_SITE CONSTANT VARCHAR2(50) := 'EGO_DEFAULT_STYLE_SUP_SITE';
19 G_ASSIGN_STYLE_SS_ORG CONSTANT VARCHAR2(50) := 'EGO_DEFAULT_STYLE_SUP_SITE_ORG';
20
21 G_ACTIVE CONSTANT NUMBER := 1;
22
23 G_RET_STS_WARNING CONSTANT VARCHAR2(1) := 'W';
24
25 G_VALID_LEVEL_INPUT CONSTANT NUMBER := 70; -- Input Validation + Business Validation
26 G_VALID_LEVEL_BUSINESS CONSTANT NUMBER := 50; -- Business Validation
27 G_DEFAULT_STATUS_CODE CONSTANT NUMBER := 1; -- Active
28 G_DEFAULT_PRIMARY_FLAG CONSTANT VARCHAR2(1) := 'N'; -- Not a Primary
29 G_PRIMARY CONSTANT VARCHAR2(1) := 'Y';
30
31
32 G_CREATE CONSTANT VARCHAR2(10) := 'CREATE';
33 G_UPDATE CONSTANT VARCHAR2(10) := 'UPDATE';
34 G_DELETE CONSTANT VARCHAR2(10) := 'DELETE';
35 G_SYNC CONSTANT VARCHAR2(10) := 'SYNC';
36
37 G_VALID_LEVEL_INPUT CONSTANT NUMBER := 70; -- Input Validation + Business Validation
38 G_VALID_LEVEL_BUSINESS CONSTANT NUMBER := 50; -- Business Validation
39 G_SUPPLIER_OBJ_NAME CONSTANT VARCHAR2(20) := 'PO_VENDORS';
40 G_SUPPLIER_SITE_OBJ_NAME CONSTANT VARCHAR2(20) := 'PO_VENDOR_SITES_ALL';
41 G_PACK_STR_NAME CONSTANT VARCHAR2(10) := 'PIM_PBOM_S';
42
43
44 -- Standard Process Flags
45 G_REC_BEFORE_MATCH CONSTANT NUMBER := 0;
46 G_REC_TO_BE_PROCESSED CONSTANT NUMBER := 1;
47 G_REC_IN_PROCESS CONSTANT NUMBER := 2;
48 G_REC_ERROR CONSTANT NUMBER := 3;
49 G_REC_VAL_ID_CONVERTED CONSTANT NUMBER := 4;
50 G_REC_SUCCESS CONSTANT NUMBER := 7;
51
52 -- Error Process Flags
53
54 G_REC_UNEXPECTED_ERROR CONSTANT NUMBER := 999;
55 G_REC_MISSING_REQ_VALUE CONSTANT NUMBER := 1001;
56 G_REC_INVALID_TRAN_TYPE CONSTANT NUMBER := 1002;
57 G_REC_INVALID_ORG CONSTANT NUMBER := 1003;
58 G_REC_INVALID_MASTER_ORG CONSTANT NUMBER := 1004;
59 G_REC_INVALID_ITEM CONSTANT NUMBER := 1005;
60 G_REC_INVALID_PK1_VALUE CONSTANT NUMBER := 1006;
61 G_REC_INVALID_PK2_VALUE CONSTANT NUMBER := 1007;
62 G_REC_INVALID_ASSOC_TYPE CONSTANT NUMBER := 1008;
63 G_REC_INVALID_STATUS CONSTANT NUMBER := 1009;
64 G_REC_INVALID_PRIMARY CONSTANT NUMBER := 1010;
65 G_REC_ORG_NO_ACCESS CONSTANT NUMBER := 1101;
66 G_REC_ASSOCIATION_NOT_EXISTS CONSTANT NUMBER := 1102;
67 G_REC_ALREADY_ASSIGNED CONSTANT NUMBER := 1103;
68 G_REC_ASSOC_SITE_NOT_EXISTS CONSTANT NUMBER := 1104;
69 G_REC_ASSOC_ITEM_NOT_IN_ORG CONSTANT NUMBER := 1105;
70 G_REC_PARENT_NOT_ASSIGNED CONSTANT NUMBER := 1106;
71 G_REC_PRIMARY_NOT_ACTIVE CONSTANT NUMBER := 1107;
72 G_REC_DUPLICATE CONSTANT NUMBER := 1108;
73 G_REC_NO_CREATE_ASSOC_PRIV CONSTANT NUMBER := 1109;
74 G_REC_NO_EDIT_ASSOC_PRIV CONSTANT NUMBER := 1110;
75 G_REC_NO_SUPPL_ACCESS_PRIV CONSTANT NUMBER := 1111;
76 G_REC_SUPPLIER_NOT_ASSIGNED CONSTANT NUMBER := 1112;
77 G_REC_SITE_NOT_ASSIGNED CONSTANT NUMBER := 1113;
78 G_REC_DUPLICATE_PRIMARY CONSTANT NUMBER := 1114;
79 G_REC_PARENT_NOT_ACTIVE CONSTANT NUMBER := 1115;
80 G_REC_NO_EDIT_ITEM_ORG_PRIV CONSTANT NUMBER := 1116;
81
82 -- Warning Process Flag
83
84 G_REC_STATUS_PROPAGATED CONSTANT NUMBER := 2001;
85
86 -- Table of Numbers (For Organizations and Items) - Used in Private
87
88 TYPE VARCHAR2_TBL_TYPE IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
89
90 -- Start of comments
91 -- API name : pre_process
92 -- Type : Private.
93 -- Function :
94 -- Pre-reqs : None
95 -- Parameters :
96 -- IN : p_api_version IN NUMBER Required
97 -- p_batch_id IN NUMBER Required
98 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
99 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
100 -- Version : Initial version 1.0
101 -- Notes : Converts the processing independent values to Ids
102 -- a) Master Org Code and Master Org Id for ITEM_SUP and ITEM_SUP_SITE
103 -- b) Org Code and Org Id for ITEM_SUP_SITE_ORG
104 -- c) Convert Pk1_Name and Pk2_Name
105 -- d) Convert existing item numbers to item ids and vice versa
106 -- e) Convert transaction type SYNC to CREATE/UPDATE
107 -- End of comments
108 PROCEDURE pre_process
109 (
110 p_api_version IN NUMBER
111 ,p_batch_id IN NUMBER
112 ,x_return_status OUT NOCOPY VARCHAR2
113 ,x_msg_count OUT NOCOPY NUMBER
114 ,x_msg_data OUT NOCOPY VARCHAR2
115 );
116
117 -- Start of comments
118 -- API name : import_item_associations
119 -- Type : Public.
120 -- Function : Imports the item associations into the systems.
121 -- Pre-reqs :
122 -- i) Rows needs to be populated in EGO.EGO_ITEM_ASSOCIATIONS_INTF if the data is not from temp tables.
123 -- ii) Errors will be grouped based on concurrent program's request id.
124 -- Parameters :
125 -- IN : p_api_version IN NUMBER Required
126 -- IN OUT : x_batch_id IN OUT NOCOPY Optional
127 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
128 -- x_msg_count OUT NOCOPY NUMBER
129 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
130 -- Version : Current version 1.0
131 -- Initial version 1.0
132 -- Notes :
133 -- x_batch_id IN OUT NOCOPY Optional if p_data_from_temp_table is not set
134 -- Returns batch_id of the batch if its not passed.
135 -- x_return_status OUT NOCOPY VARCHAR2 Return status of the program
136 -- S - Success, E - Error, U - Unexpected Error
137 -- x_msg_data OUT NOCOPY VARCHAR2 Error Message Data if the message stack has one message else null.
138 -- End of comments
139 PROCEDURE import_item_associations
140 (
141 p_api_version IN NUMBER
142 ,x_batch_id IN OUT NOCOPY VARCHAR2
143 ,x_return_status OUT NOCOPY VARCHAR2
144 ,x_msg_count OUT NOCOPY NUMBER
145 ,x_msg_data OUT NOCOPY VARCHAR2
146 );
147
148 -- Start of comments
149 -- API name : import_item_associations
150 -- Type : private.
151 -- Function : Imports the item associations in the excel import flow.
152 -- Pre-reqs :
153 -- i) Rows needs to be populated in EGO.EGO_ITEM_ASSOCIATIONS_INTF if the data is not from temp tables.
154 -- ii) Errors will be grouped based on concurrent program's request id.
155 -- Parameters :
156 -- IN : p_api_version IN NUMBER Required
157 -- IN OUT : x_batch_id IN OUT NOCOPY Optional
158 -- OUT : x_errbuf OUT NOCOPY VARCHAR2
159 -- x_retcode OUT NOCOPY VARCHAR2
160 -- Version : Current version 1.0
161 -- Initial version 1.0
162 -- Notes :
163 -- x_errbuf Returns the single error message if it is else null.
164 -- x_retcode 0 - Success, 1 - Warning, 2 - Error
165 -- End of comments
166 PROCEDURE import_item_associations
167 (
168 p_api_version IN NUMBER
169 ,x_batch_id IN OUT NOCOPY VARCHAR2
170 ,x_errbuf OUT NOCOPY VARCHAR2
171 ,x_retcode OUT NOCOPY VARCHAR2
172 );
173
174
175 -- Start of comments
176 -- API name : copy_associations_to_items
177 -- Type : Private.
178 -- Function : Insert interface rows for associations of the items
179 -- for which the copy_item_id is src_item_id.
180 -- Pre-reqs : None
181 -- Parameters :
182 -- IN : p_api_version IN NUMBER Required
183 -- p_batch_id IN NUMBER Required
184 -- p_src_item_id IN NUMBER Required
185 -- p_data_level_names IN VARCHAR2_TBL_TYPE Required
186 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
187 -- x_msg_count OUT NOCOPY NUMBER
188 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
189 -- Version : Initial version 1.0
190 -- Notes : Note text
191 --
192 -- End of comments
193 PROCEDURE copy_associations_to_items
194 (
195 p_api_version IN NUMBER
196 ,p_batch_id IN NUMBER
197 ,p_src_item_id IN NUMBER
198 ,p_data_level_names IN VARCHAR2_TBL_TYPE
199 ,x_return_status OUT NOCOPY VARCHAR2
200 ,x_msg_count OUT NOCOPY NUMBER
201 ,x_msg_data OUT NOCOPY VARCHAR2
202 );
203
204 -- Start of comments
205 -- API name : copy_from_style_to_SKUs
206 -- Type : Private.
207 -- Function : Insert interface rows for associations of the style items
208 -- to the corresponding SKUs.
209 -- Pre-reqs : None
210 -- Parameters :
211 -- IN : p_api_version IN NUMBER Required
212 -- p_batch_id IN NUMBER Required
213 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
214 -- x_msg_count OUT NOCOPY NUMBER
215 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
216 -- Version : Initial version 1.0
217 -- Notes : Note text
218 --
219 -- End of comments
220 PROCEDURE copy_from_style_to_SKUs
221 (
222 p_api_version IN NUMBER
223 ,p_batch_id IN NUMBER
224 ,x_return_status OUT NOCOPY VARCHAR2
225 ,x_msg_count OUT NOCOPY NUMBER
226 ,x_msg_data OUT NOCOPY VARCHAR2
227 ,p_msii_miri_process_flag IN NUMBER DEFAULT 1 -- Bug 12635842
228 );
229
230 -- Start of comments
231 -- API name : copy_to_packs
232 -- Type : Private.
233 -- Function : Insert interface rows for associations of the pack items
234 -- to the corresponding pack hierarchy.
235 -- Pre-reqs : None
236 -- Parameters :
237 -- IN : p_api_version IN NUMBER Required
238 -- p_batch_id IN NUMBER Required
239 -- OUT : x_return_status OUT NOCOPY VARCHAR2(1)
240 -- x_msg_count OUT NOCOPY NUMBER
241 -- x_msg_data OUT NOCOPY VARCHAR2(2000)
242 -- Version : Initial version 1.0
243 -- Notes : Note text
244 --
245 -- End of comments
246 PROCEDURE copy_to_packs
247 (
248 p_api_version IN NUMBER
249 ,p_batch_id IN NUMBER
250 ,x_return_status OUT NOCOPY VARCHAR2
251 ,x_msg_count OUT NOCOPY NUMBER
252 ,x_msg_data OUT NOCOPY VARCHAR2
253 );
254
255
256 END ego_item_associations_pub;
[Home] [Help]