1 PACKAGE EGO_ITEM_BULKLOAD_PKG AS
2 /* $Header: EGOIBLKS.pls 120.2 2007/04/09 13:56:56 gnanda ship $ */
3
4
5 -- ===============================================
6 -- CONSTANTS for concurrent program return values
7 -- ===============================================
8
9 ---------------------------------------
10 -- Package Name
11 ---------------------------------------
12 G_PACKAGE_NAME CONSTANT VARCHAR2(30) := 'EGO_ITEM_BULKLOAD_PKG';
13
14 ------------------------------------------------------------------------------
15 -- Return values for RETCODE parameter (standard for concurrent programs)
16 ------------------------------------------------------------------------------
17 RETCODE_SUCCESS NUMBER := 0;
18 RETCODE_WARNING NUMBER := 1;
19 RETCODE_ERROR NUMBER := 2;
20
21 ----------------------------------------------------
22 -- List of PROCESS_STATUS
23 ----------------------------------------------------
24
25 --------------------------------------------------------------------------
26 -- ProcessStatus : To Be Processed
27 -- the status when the record is loaded into Mtl_System_Items_Interface
28 --------------------------------------------------------------------------
29 G_PS_TO_BE_PROCESSED NUMBER := 1;
30
31 --------------------------------------------------------------------------
32 -- ProcessStatus : Error
33 --------------------------------------------------------------------------
34 G_PS_ERROR NUMBER := 3;
35 G_PS_IMPORT_FAILURE NUMBER := 4;
36
37 --------------------------------------------------------------------------
38 -- ProcessStatus : Success
39 --------------------------------------------------------------------------
40 G_PS_SUCCESS NUMBER := 7;
41
42 --------------------------------------------------------------------------
43 -- Caller Identifiers
44 --------------------------------------------------------------------------
45 G_ITEM VARCHAR2(50) := 'ITEM';
46 G_BOM VARCHAR2(50) := 'BOM';
47
48 -- =========================
49 -- PROCEDURES AND FUNCTIONS
50 -- =========================
51
52 -----------------------------------------------------------------------
53 -- Fix for Bug# 3970069.
54 -- Insert into MTL_INTERFACE_ERRORS through autonomous transaction
55 -- commit. Earlier for any exception during Java Conc Program's
56 -- AM.commit(), the errors wouldnot get logged. By following Autonomous
57 -- Transaction block, that issue gets resolved.
58 -----------------------------------------------------------------------
59 PROCEDURE Insert_Mtl_Intf_Err( p_transaction_id IN VARCHAR2
60 , p_bo_identifier IN VARCHAR2
61 , p_error_entity_code IN VARCHAR2
62 , p_error_table_name IN VARCHAR2
63 , p_error_msg IN VARCHAR2
64 );
65
66
67 --------------------------------------------------------------------
68 -- Fix for Bug# 3864813
69 -- Process Net Weight (i.e. Unit Weights) for the Items based on the
70 -- Trade Item Descriptor value.
71 --
72 -- NOTE: Net Weight can only have a value if Trade Item Descriptor
73 -- is "Base Unit Or Each", else it will be NULL (i.e. it will be
74 -- derived value).
75 --------------------------------------------------------------------
76 PROCEDURE process_netweights
77 (
78 p_resultfmt_usage_id IN NUMBER,
79 p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE ,
80 x_errbuff OUT NOCOPY VARCHAR2,
81 x_retcode OUT NOCOPY VARCHAR2
82 );
83
84 ----------------------------------------------------------
85 -- Process Item and Item Revision Interface Lines
86 --
87 -- Main API called by the Concurrent Program.
88 ----------------------------------------------------------
89 PROCEDURE process_item_interface_lines
90 (p_resultfmt_usage_id IN NUMBER,
91 p_user_id IN NUMBER,
92 p_conc_request_id IN NUMBER,
93 p_language_code IN VARCHAR2,
94 p_caller_identifier IN VARCHAR2 DEFAULT EGO_ITEM_BULKLOAD_PKG.G_ITEM,
95 p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE ,
96 x_errbuff OUT NOCOPY VARCHAR2,
97 x_retcode OUT NOCOPY VARCHAR2,
98 p_start_upload IN VARCHAR2 DEFAULT FND_API.G_TRUE ,
99 p_data_set_id IN NUMBER DEFAULT NULL
100 );
101 -- Start OF comments
102 -- API name : Process Item Interfance Lines
103 -- TYPE : Public (called by Concurrent Program Wrapper)
104 -- Pre-reqs : None
105 -- FUNCTION : Process and Load Item interfance lines.
106 -- Loads Item Attr Values + Item User-Defined Attr Values
107 -- Errors are populated in MTL_INTERFACE_ERRORS
108 --
109 -- Parameters:
110 -- IN :
111 -- p_resultfmt_usage_id IN NUMBER
112 -- Similar to job number. Maps one-to-one with Data_Set_Id,
113 -- i.e. job number.
114 --
115 -- p_user_id IN NUMBER
116 -- Used for Security validation and populating Created By.
117 --
118 -- p_conc_request_id IN NUMBER
119 -- Concurrent Request ID.
120 --
121 -- p_language_code IN VARCHAR2
122 -- Language Code.
123 --
124 -- p_caller_identifier IN VARCHAR2
125 -- Can be called from Item -or- BOM code.
126 --
127 -- p_commit IN VARCHAR2
128 -- To commit or not to commit.
129 --
130 --
131 -- OUT :
132 -- x_retcode OUT NOCOPY VARCHAR2,
133 -- x_errbuff OUT NOCOPY VARCHAR2
134
135
136
137 -- Bug: 3778006
138 -------------------------------------------------------------------------
139 -- Function to get description generation method for catalog category --
140 -------------------------------------------------------------------------
141 FUNCTION get_desc_gen_method(p_catalog_group_id NUMBER) RETURN VARCHAR2;
142
143 /*
144 ** Added by Ssingal - This procedure gets the Trade ItemDescriptor for a given
145 ** Inventory_item_id and organization Id.
146 ** Bug Fix 4001661
147 */
148
149 PROCEDURE get_Trade_Item_Descriptor (
150 p_inventory_item_id IN VARCHAR2,
151 p_organization_id IN VARCHAR2,
152 x_tradeItemDescriptor OUT NOCOPY VARCHAR2
153 );
154
155 /*
156 ** Added by Ssingal - For clearing the attribute values for an Item
157 ** taking into consideration the Trade Item Descriptor
158 ** Bug Fix 4001661
159 */
160
161 PROCEDURE Clear_Gtin_Attrs
162 (
163 p_resultfmt_usage_id IN NUMBER,
164 p_commit IN VARCHAR2 DEFAULT FND_API.G_TRUE ,
165 x_errbuff OUT NOCOPY VARCHAR2,
166 x_ret_code OUT NOCOPY VARCHAR2
167 );
168
169
170 -- ============================================================================
171 -- API Name : Populate_Seq_Gen_Item_Nums
172 -- Description : This procedure will be called from IOI
173 -- (after org and catalog category details are resolved)
174 -- to populate the item numbers for all the sequence generated items.
175 -- ============================================================================
176
177 PROCEDURE Populate_Seq_Gen_Item_Nums
178 (p_set_id IN NUMBER
179 ,p_org_id IN NUMBER
180 ,p_all_org IN NUMBER
181 ,p_rec_status IN NUMBER
182 ,x_return_status OUT NOCOPY VARCHAR2
183 ,x_msg_count OUT NOCOPY NUMBER
184 ,x_msg_data OUT NOCOPY VARCHAR2);
185
186
187 -- ============================================================================
188 -- API Name : load_intersections_interface
189 -- Description : this procedure would look at the bulkload interface table
190 -- populated by excel and then populate the intersections
191 -- interface table with supplier/supplier site etc. intersections
192 -- ============================================================================
193
194 PROCEDURE load_intersections_interface
195 (
196 p_resultfmt_usage_id IN NUMBER,
197 p_set_process_id IN NUMBER,
198 x_set_process_id OUT NOCOPY NUMBER,
199 x_errbuff OUT NOCOPY VARCHAR2,
200 x_retcode OUT NOCOPY VARCHAR2
201 );
202
203
204 END EGO_ITEM_BULKLOAD_PKG;