1 PACKAGE EGO_ITEM_USER_ATTRS_CP_PUB AUTHID DEFINER AS
2 /* $Header: EGOCIUAS.pls 120.12.12020000.1 2012/06/26 16:33:43 appldev ship $ */
3
4
5
6 ----------------------
7 -- Global Variables --
8 ----------------------
9
10 /*
11 * PROCESS_STATUS constants
12 * ------------------------
13 * The following constants are used in the PROCESS_STATUS column of the table
14 * EGO_ITEM_USER_ATTRS_INTERFACE to describe the processing status of each row.
15 *
16 * G_PS_TO_BE_PROCESSED: row should be processed
17 * G_PS_IN_PROCESS: row is being processed
18 * G_PS_GENERIC_ERROR: some row in the same logical Attribute Group as this row
19 encountered an error (all error statuses described below
20 * are set to this status at the completion of processing)
21 * G_PS_SUCCESS: row processed succcessfully
22 *
23 * In addition to the four basic error statuses above, there are several internal
24 * statuses that may appear in the interface table at times (for instance, while
25 * a data set is being processed, or if the process encountered a fatal error)
26 *
27 * G_PS_BAD_ORG_ID: some row in the same logical Attribute Group as this row
28 * contains an Org ID that is not a Master Org ID in MTL_PARAMETERS
29 * G_PS_BAD_ORG_CODE: some row in the same logical Attribute Group as this row
30 * contains an Org Code that isn't a Master Org Code in MTL_PARAMETERS
31 * G_PS_BAD_ITEM_ID: some row in the same logical Attribute Group as this row
32 * contains an Item ID that isn't in MTL_SYSTEM_ITEMS_B for the
33 * passed-in Organization
34 * G_PS_BAD_ITEM_NUMBER: some row in the same logical Attribute Group as this row
35 * contains an Item Number that isn't a valid "concatenated
36 * segments" value for the passed-in Organization
37 * G_PS_BAD_REVISION_ID: some row in the same logical Attribute Group as this row
38 * contains a revision ID that isn't in MTL_ITEM_REVISIONS
39 * for the passed-in Item and Organization
40 * G_PS_BAD_REVISION_CODE: some row in the same logical Attribute Group as this row
41 * contains a revision Code that isn't in MTL_ITEM_REVISIONS
42 * for the passed-in Item and Organization
43 * G_PS_BAD_CATALOG_GROUP_ID: some row in the same logical Attribute Group as this row
44 * contains a catalog group ID that isn't in MTL_SYSTEM_ITEMS_B
45 * for the passed-in Item and Organization
46 * G_PS_ITM_CHANGE_POLICY_EXISTS: the Attribute Group of which this row is a part is under
47 * Change control for the Catalog Category and Lifecycle Phase
48 * to which this Item belongs
49 * G_PS_REV_CHANGE_POLICY_EXISTS: the Attribute Group of which this row is a part is under
50 * Change control for the Catalog Category and Lifecycle Phase
51 * to which this Revision belongs
52 */
53
54 G_PS_TO_BE_PROCESSED CONSTANT NUMBER := 1;
55 G_PS_IN_PROCESS CONSTANT NUMBER := 2;
56 G_PS_GENERIC_ERROR CONSTANT NUMBER := 3;
57 G_PS_SUCCESS CONSTANT NUMBER := 4;
58 G_PS_BAD_ORG_ID CONSTANT NUMBER := 15;
59 G_PS_BAD_ORG_CODE CONSTANT NUMBER := 16;
60 G_PS_BAD_ITEM_ID CONSTANT NUMBER := 17;
61 G_PS_BAD_ITEM_NUMBER CONSTANT NUMBER := 18;
62 G_PS_BAD_REVISION_ID CONSTANT NUMBER := 19;
63 G_PS_BAD_REVISION_CODE CONSTANT NUMBER := 20;
64 G_PS_BAD_CATALOG_GROUP_ID CONSTANT NUMBER := 21;
65 -- bug 3762809
66 -- gave way to G_PS_CHG_POLICY_CO_REQUIRED and G_PS_CHG_POLICY_NOT_ALLOWED
67 -- G_PS_CHANGE_POLICY_IN_PLACE CONSTANT NUMBER := 12;
68 G_PS_CHG_POLICY_CO_REQUIRED CONSTANT NUMBER := 5;
69 -- bug 4679902 (process status for policy "NOT ALLOWED" = 24)
70 G_PS_CHG_POLICY_NOT_ALLOWED CONSTANT NUMBER := 24;
71 G_PS_BAD_ATTR_GROUP_ID CONSTANT NUMBER := 22;
72 G_PS_BAD_ATTR_GROUP_NAME CONSTANT NUMBER := 23;
73 G_PS_DATA_LEVEL_INCORRECT CONSTANT NUMBER := 25;
74
75 G_PS_BAD_DATA_LEVEL CONSTANT NUMBER := 26;
76 G_PS_BAD_SUPPLIER CONSTANT NUMBER := 27;
77 G_PS_BAD_SUPPLIER_SITE CONSTANT NUMBER := 28;
78 G_PS_BAD_SUPPLIER_SITE_ORG CONSTANT NUMBER := 29;
79
80 -- for user defined attributes, all the process statuses are already in use i.e.
81 -- 0, 1, 2, 3, 4, 5, 6, and 8 and above are in use
82 -- 7 has a conflict with other interface tables, user may think that record is successful
83 -- so, the only option left is to use numbers with decimal < 8
84 G_PS_STYLE_VARIANT_IN_PROCESS CONSTANT NUMBER := 3.05;
85 G_PS_VAR_VSET_CHG_NOT_ALLOWED CONSTANT NUMBER := 3.15;
86 G_PS_BAD_STYLE_VAR_VALUE_SET CONSTANT NUMBER := 3.25;
87 G_PS_BAD_SKU_VAR_VALUE CONSTANT NUMBER := 3.35;
88 G_PS_SKU_VAR_VALUE_NOT_UPD CONSTANT NUMBER := 3.45;
89 G_PS_INH_ATTR_FOR_SKU_NOT_UPD CONSTANT NUMBER := 3.55;
90
91 -- PIM for Telco Validations
92 -- G_COM_VALDN_FAIL CONSTANT NUMBER := 3.65;
93
94 /*
95 * RETCODE supplementary constant
96 * ------------------------------
97 * An additional constant to indicate that the concurrent program completed
98 * successfully but that at least one of the rows processed failed validations.
99 */
100
101 G_RETCODE_SUCCESS_WITH_WARNING CONSTANT VARCHAR(1) := 'W';
102
103
104
105
106
107 ----------------
108 -- Procedures --
109 ----------------
110
111 /*
112 * Get_Item_Security_Predicate
113 * ---------------------------
114 */
115 PROCEDURE Get_Item_Security_Predicate (
116 p_object_name IN VARCHAR2
117 ,p_party_id IN VARCHAR2
118 ,p_privilege_name IN VARCHAR2
119 ,p_table_alias IN VARCHAR2
120 ,x_security_predicate OUT NOCOPY VARCHAR2
121 );
122
123
124
125 /*
126 * Process_Item_User_Attrs_Data
127 * ----------------------------
128 * This procedure processes all interface table rows
129 * corresponding to the passed-in data set ID. ERRBUF and RETCODE are standard
130 * parameters for concurrent programs, and we ignore them.
131 * p_debug_level: number from 0-3, with 0 for no debug
132 * information and 3 for exhaustive debugs
133 * p_purge_successful_lines: 'T' or 'F', indicating
134 * whether or not to delete all rows in this data set
135 * that are successfully processed
136 * p_initialize_error_handler: flag indicating whether
137 * or not we initialize the ERROR_HANDLER package
138 */
139 PROCEDURE Process_Item_User_Attrs_Data
140 (
141 ERRBUF OUT NOCOPY VARCHAR2
142 ,RETCODE OUT NOCOPY VARCHAR2
143 ,p_data_set_id IN NUMBER
144 ,p_debug_level IN NUMBER DEFAULT 0
145 ,p_purge_successful_lines IN VARCHAR2 DEFAULT FND_API.G_FALSE
146 ,p_initialize_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
147 ,p_validate_only IN VARCHAR2 DEFAULT FND_API.G_FALSE
148 ,p_ignore_security_for_validate IN VARCHAR2 DEFAULT FND_API.G_FALSE
149 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE /* Added to fix Bug#7422423*/
150 ,p_is_id_validations_reqd IN VARCHAR2 DEFAULT FND_API.G_TRUE /* Fix for bug#9660659 */
151 );
152
153
154
155 /*
156 * Get_Related_Class_Codes
157 * -----------------------
158 * A procedure for INTERNAL USE ONLY;
159 * util procedure to get a comma-delimited list of parent
160 * Catalog Category IDs for a passed-in Catalog Category ID
161 */
162 PROCEDURE Get_Related_Class_Codes (
163 p_classification_code IN VARCHAR2
164 ,x_related_class_codes_list OUT NOCOPY VARCHAR2
165 );
166
167
168
169 /*
170 * Impl_Item_Attr_Change_Line
171 * --------------------------
172 * A procedure for INTERNAL USE ONLY;
173 * wrapper for ENG to implement Change
174 * Lines for Items
175 */
176 PROCEDURE Impl_Item_Attr_Change_Line (
177 p_api_version IN NUMBER
178 ,p_change_id IN NUMBER
179 ,p_change_line_id IN NUMBER
180 ,p_old_revision_id IN NUMBER DEFAULT NULL
181 ,p_new_revision_id IN NUMBER DEFAULT NULL
182 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
183 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
184 ,x_return_status OUT NOCOPY VARCHAR2
185 ,x_errorcode OUT NOCOPY NUMBER
186 ,x_msg_count OUT NOCOPY NUMBER
187 ,x_msg_data OUT NOCOPY VARCHAR2
188 );
189
190
191 ----------------------------------------------------------------------
192 /*
193 * Copy_data_to_Intf
194 * --------------------------
195 * A procedure for ITEMS use
196 * which copies data from production/interface table to interface table
197 * The inherited attribute groups are filtered at the source sql only.
198 *
199 */
200 PROCEDURE Copy_data_to_Intf
201 (
202 p_api_version IN NUMBER
203 ,p_commit IN VARCHAR2
204 ,p_copy_from_intf_table IN VARCHAR2 -- T/F
205 ,p_source_entity_sql IN VARCHAR2
206 ,p_source_attr_groups_sql IN VARCHAR2
207 ,p_dest_process_status IN VARCHAR2
208 ,p_dest_data_set_id IN VARCHAR2
209 ,p_dest_transaction_type IN VARCHAR2
210 ,p_cleanup_row_identifiers IN VARCHAR2 DEFAULT FND_API.G_TRUE -- T/F
211 ,x_return_status OUT NOCOPY VARCHAR2
212 ,x_msg_count OUT NOCOPY NUMBER
213 ,x_msg_data OUT NOCOPY VARCHAR2
214 );
215
216 END EGO_ITEM_USER_ATTRS_CP_PUB;
217