1 PACKAGE EGO_ITEM_USER_ATTRS_CP_PUB AUTHID DEFINER AS
2 /* $Header: EGOCIUAS.pls 120.8 2007/10/03 14:20:00 dsakalle 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 /*
92 * RETCODE supplementary constant
93 * ------------------------------
94 * An additional constant to indicate that the concurrent program completed
95 * successfully but that at least one of the rows processed failed validations.
96 */
97
98 G_RETCODE_SUCCESS_WITH_WARNING CONSTANT VARCHAR(1) := 'W';
99
100
101
102
103
104 ----------------
105 -- Procedures --
106 ----------------
107
108 /*
109 * Get_Item_Security_Predicate
110 * ---------------------------
111 */
112 PROCEDURE Get_Item_Security_Predicate (
113 p_object_name IN VARCHAR2
114 ,p_party_id IN VARCHAR2
115 ,p_privilege_name IN VARCHAR2
116 ,p_table_alias IN VARCHAR2
117 ,x_security_predicate OUT NOCOPY VARCHAR2
118 );
119
120
121
122 /*
123 * Process_Item_User_Attrs_Data
124 * ----------------------------
125 * This procedure processes all interface table rows
126 * corresponding to the passed-in data set ID. ERRBUF and RETCODE are standard
127 * parameters for concurrent programs, and we ignore them.
128 * p_debug_level: number from 0-3, with 0 for no debug
129 * information and 3 for exhaustive debugs
130 * p_purge_successful_lines: 'T' or 'F', indicating
131 * whether or not to delete all rows in this data set
132 * that are successfully processed
133 * p_initialize_error_handler: flag indicating whether
134 * or not we initialize the ERROR_HANDLER package
135 */
136 PROCEDURE Process_Item_User_Attrs_Data
137 (
138 ERRBUF OUT NOCOPY VARCHAR2
139 ,RETCODE OUT NOCOPY VARCHAR2
140 ,p_data_set_id IN NUMBER
141 ,p_debug_level IN NUMBER DEFAULT 0
142 ,p_purge_successful_lines IN VARCHAR2 DEFAULT FND_API.G_FALSE
143 ,p_initialize_error_handler IN VARCHAR2 DEFAULT FND_API.G_TRUE
144 ,p_validate_only IN VARCHAR2 DEFAULT FND_API.G_FALSE
145 ,p_ignore_security_for_validate IN VARCHAR2 DEFAULT FND_API.G_FALSE
146 );
147
148
149
150 /*
151 * Get_Related_Class_Codes
152 * -----------------------
153 * A procedure for INTERNAL USE ONLY;
154 * util procedure to get a comma-delimited list of parent
155 * Catalog Category IDs for a passed-in Catalog Category ID
156 */
157 PROCEDURE Get_Related_Class_Codes (
158 p_classification_code IN VARCHAR2
159 ,x_related_class_codes_list OUT NOCOPY VARCHAR2
160 );
161
162
163
164 /*
165 * Impl_Item_Attr_Change_Line
166 * --------------------------
167 * A procedure for INTERNAL USE ONLY;
168 * wrapper for ENG to implement Change
169 * Lines for Items
170 */
171 PROCEDURE Impl_Item_Attr_Change_Line (
172 p_api_version IN NUMBER
173 ,p_change_id IN NUMBER
174 ,p_change_line_id IN NUMBER
175 ,p_old_revision_id IN NUMBER DEFAULT NULL
176 ,p_new_revision_id IN NUMBER DEFAULT NULL
177 ,p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
178 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
179 ,x_return_status OUT NOCOPY VARCHAR2
180 ,x_errorcode OUT NOCOPY NUMBER
181 ,x_msg_count OUT NOCOPY NUMBER
182 ,x_msg_data OUT NOCOPY VARCHAR2
183 );
184
185
186 ----------------------------------------------------------------------
187 /*
188 * Copy_data_to_Intf
189 * --------------------------
190 * A procedure for ITEMS use
191 * which copies data from production/interface table to interface table
192 * The inherited attribute groups are filtered at the source sql only.
193 *
194 */
195 PROCEDURE Copy_data_to_Intf
196 (
197 p_api_version IN NUMBER
198 ,p_commit IN VARCHAR2
199 ,p_copy_from_intf_table IN VARCHAR2 -- T/F
200 ,p_source_entity_sql IN VARCHAR2
201 ,p_source_attr_groups_sql IN VARCHAR2
202 ,p_dest_process_status IN VARCHAR2
203 ,p_dest_data_set_id IN VARCHAR2
204 ,p_dest_transaction_type IN VARCHAR2
205 ,p_cleanup_row_identifiers IN VARCHAR2 DEFAULT FND_API.G_TRUE -- T/F
206 ,x_return_status OUT NOCOPY VARCHAR2
207 ,x_msg_count OUT NOCOPY NUMBER
208 ,x_msg_data OUT NOCOPY VARCHAR2
209 );
210
211 END EGO_ITEM_USER_ATTRS_CP_PUB;
212
[Home] [Help]