1 PACKAGE CST_PAC_ITERATION_PROCESS_PVT AS
2 -- $Header: CSTVIIPS.pls 120.14.12010000.2 2008/10/30 23:27:23 vjavli ship $
3
4 -- API Name : Iteration_Process
5 -- Type : Private
6 -- Function :
7 -- Pre-reqs : None
8 -- Parameters :
9 -- IN : p_init_msg_list IN VARCHAR2
10 -- p_validation_level IN NUMBER
11 -- p_legal_entity_id IN NUMBER
12 -- p_cost_type_id IN NUMBER
13 -- p_cost_method IN NUMBER
14 -- p_iteration_proc_flag IN VARCHAR2
15 -- p_period_id IN NUMBER
16 -- p_start_date IN DATE
17 -- p_end_date IN DATE
18 -- p_inventory_item_id IN NUMBER
19 -- p_inventory_item_number IN VARCHAR2(1025)
20 -- p_tolerance IN NUMBER
21 -- p_iteration_num IN NUMBER
22 -- p_run_options IN NUMBER
23 -- p_pac_rates_id IN NUMBER
24 -- p_uom_control IN NUMBER
25 -- p_user_id IN NUMBER
26 -- p_login_id IN NUMBER
27 -- p_req_id IN NUMBER
28 -- p_prg_id IN NUMBER
29 -- p_prg_appid IN NUMBER
30 -- OUT : x_return_status OUT VARCHAR2(1)
31 -- x_msg_count OUT NUMBER
32 -- x_msg_data OUT VARCHAR2(2000)
33 -- Version : Current Version : 1.0
34 -- Initial version 1.0
35 -- Notes :
36 -- +========================================================================+
37
38 -- +========================================================================+
39 -- GLOBAL CONSTANTS
40 -- +========================================================================+
41 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_PAC_ITERATION_PROCESS_PVT';
42
43 -- +========================================================================+
44 -- PL/SQL table g_cst_group_tbl
45 -- This table contains all the valid Cost Groups for the user entered
46 -- Legal Entity
47 -- period new quantity is the total quantity of all cost owned transactions
48 -- upto interorg receipts across Cost groups i.e. upto txn category 8
49 -- +========================================================================+
50 TYPE cst_group_rec_type IS RECORD
51 ( cost_group_id NUMBER
52 , cost_group VARCHAR2(10)
53 , master_organization_id NUMBER
54 , period_new_quantity NUMBER
55 );
56
57 TYPE g_cst_group_table_type IS TABLE OF cst_group_rec_type
58 INDEX BY PLS_INTEGER;
59
60 G_CST_GROUP_TBL g_cst_group_table_type;
61
62 -- +========================================================================+
63 -- PL/SQL table g_cst_group_org_tbl
64 -- This table contains all the valid organizations in each Cost Group
65 -- +========================================================================+
66 TYPE cst_group_org_rec_type IS RECORD
67 ( cost_group_id NUMBER
68 , organization_id NUMBER
69 );
70
71 TYPE g_cst_group_org_table_type IS TABLE OF cst_group_org_rec_type
72 INDEX BY PLS_INTEGER;
73
74 G_CST_GROUP_ORG_TBL g_cst_group_org_table_type;
75
76 -- +========================================================================+
77 -- PL/SQL table g_pwac_new_cost_tbl
78 -- This table contains :
79 -- balance before interorg txns, qty before interorg txns,
80 -- period new balance of all interorg receipts, final new cost of the
81 -- cost element and level type
82 -- for a given inventory item id, cost group id and pac period id
83 -- This table is used get the final new cost which is assigned to all the
84 -- corresponding group 2 (cost derived) transactions in a given item id,
85 -- cost group and pac period.
86 -- period_new_quantity is same for all the cost elements, level in a given
87 -- item,cost group and pac period
88 -- This pl/sql table is flushed for each optimal cost group
89 -- +========================================================================+
90 TYPE pwac_new_cost_rec_type IS RECORD
91 ( final_new_cost NUMBER
92 , period_new_balance NUMBER
93 , period_new_quantity NUMBER
94 , period_bal_bef_intorg NUMBER
95 , period_qty_bef_intorg NUMBER
96 );
97
98 TYPE g_pwac_new_cost_table_type IS TABLE OF pwac_new_cost_rec_type INDEX BY PLS_INTEGER;
99 TYPE g_pwac_cost_table_type IS TABLE OF g_pwac_new_cost_table_type INDEX BY PLS_INTEGER;
100 G_PWAC_NEW_COST_TBL g_pwac_cost_table_type;
101
102
103 -- +========================================================================+
104 -- PL/SQL table g_cg_pwac_cost_tbl
105 -- This table contains final new cost of the cost element and level type
106 -- for a given inventory item id, cost group id and pac period id
107 -- This table is used to update cpicd.item_cost, item_balance for each
108 -- cost group
109 -- The table has the same record structure as that of pwac_new_cost_rec_type
110 -- This pl/sql table is flushed after Nth iteration, after updating cpicd
111 -- The pl/sql table is flushed in the procedure Update_Cpicd_With_New_Values
112 -- +========================================================================+
113
114 G_CG_PWAC_COST_TBL g_pwac_new_cost_table_type;
115
116 -- +========================================================================+
117 -- This pl/sql table is used to store interorg items in the current BOM level
118 -- considering the highest BOM level across cost groups
119 -- This pl/sql table is flushed before each BOM level
120 -- Item Id itself is the index
121 -- +========================================================================+
122 TYPE interorg_item_level_rec_type IS RECORD
123 (inventory_item_id NUMBER
124 ,bom_high_level_code NUMBER
125 );
126
127 TYPE g_interorg_item_level_tab_type IS TABLE OF interorg_item_level_rec_type
128 INDEX BY PLS_INTEGER;
129
130 G_INTERORG_ITEM_LEVEL_TBL g_interorg_item_level_tab_type;
131
132 G_TOL_ACHIEVED_MESSAGE VARCHAR2(255);
133 G_TOL_NOT_ACHIEVED_MESSAGE VARCHAR2(255);
134
135 -- +========================================================================+
136 -- PROCEDURES AND FUNCTIONS
137 -- +========================================================================+
138
139 -- +========================================================================+
140 -- FUNCTION: Check_Cst_Group Local Utility
141 -- PARAMETERS:
142 -- p_cost_group_id user input
143 -- COMMENT:
144 -- Take p_cost_group_id and look in the PL/SQL table g_cst_group_tbl.
145 -- A return value 'Y' means that the cost group id belongs to user entered
146 -- legal entity and therefore its a valid cost group.
147 -- A return value 'N' means that the cost group is not valid since it is not
148 -- belong to Legal Entity
149 -- USAGE: This function is used within the SQL
150 -- PRE-COND: none
151 -- EXCEPTIONS: none
152 -- +========================================================================+
153 FUNCTION Check_Cst_Group
154 ( p_cost_group_id IN NUMBER
155 )
156 RETURN VARCHAR2;
157
158 -- +========================================================================+
159 -- FUNCTION: Check_Cst_Group_Org Local Utility
160 -- PARAMETERS:
161 -- p_organization_id
162 -- COMMENT:
163 -- Take p_organization_id and look in the PL/SQL table l_cst_group_org_tbl.
164 -- A return value 'Y' means that the organization id belongs to one of the
165 -- valid cost group in legal entity
166 -- A return value 'N' means that the organization id is NOT belong to
167 -- valid cost group
168 -- USAGE: This function is used within the SQL
169 -- PRE-COND: none
170 -- EXCEPTIONS: none
171 -- +========================================================================+
172 FUNCTION Check_Cst_Group_Org
173 ( p_organization_id IN NUMBER
174 )
175 RETURN VARCHAR2;
176
177
178 -- +========================================================================+
179 -- FUNCTION: Get_Cost_Group Local Utility
180 -- PARAMETERS:
181 -- p_organization_id IN NUMBER
182 -- COMMENT:
183 -- Get Cost Group of the corresponding p_organization_id
184 -- USAGE: This function is used in the sql cursor
185 -- PRE-COND: none
186 -- EXCEPTIONS: none
187 -- +========================================================================+
188 FUNCTION Get_Cost_Group
189 ( p_organization_id IN NUMBER
190 )
191 RETURN NUMBER;
192
193 -- +========================================================================+
194 -- PROCEDURE: Initialize
195 -- PARAMETERS:
196 -- p_legal_entity_id IN NUMBER
197 -- COMMENT:
198 -- This procedure is to initialize Global PL/SQL tables
199 -- G_CST_GROUP_TBL to store valid Cost Groups in Legal Entity
200 -- G_CST_GROUP_ORG_TBL to store valid organizations in those cost groups
201 -- This procedure is called by the API CST_INTERORG_TRANSFER_PROC
202 -- PRE-COND: none
203 -- EXCEPTIONS: none
204 -- +========================================================================+
205 PROCEDURE Initialize
206 ( p_legal_entity_id IN NUMBER
207 );
208
209
210 -- +========================================================================+
211 -- PROCEDURE: Set_Phase5_Status
212 -- PARAMETERS:
213 -- p_legal_entity_id NUMBER Legal Entity
214 -- p_cost_group_id NUMBER Valid Cost Group in LE
215 -- p_period_id NUMBER PAC Period Id
216 -- p_phase_status NUMBER
217 -- Not Applicable(0)
218 -- Un Processed (1)
219 -- Running (2)
220 -- Error (3)
221 -- Complete (4)
222 -- COMMENT:
223 -- This procedure sets the phase 5 status to Un Processed (1)
224 -- at the end of final iteration or when the tolerance is achieved
225 --
226 -- USAGE: This procedure is invoked from api:iteration_process
227 -- PRE-COND: none
228 -- EXCEPTIONS: none
229 -- +========================================================================+
230 PROCEDURE Set_Phase5_Status(p_legal_entity_id IN NUMBER
231 ,p_period_id IN NUMBER
232 ,p_period_end_date IN DATE
233 ,p_phase_status IN NUMBER
234 );
235
236
237 -- +========================================================================+
238 -- PROCEDURE: Set_Process_Status
239 -- PARAMETERS:
240 -- p_legal_entity_id NUMBER Legal Entity
241 -- p_period_id NUMBER PAC Period Id
242 -- p_period_end_date DATE
243 -- p_phase_status NUMBER
244 -- Not Applicable(0)
245 -- Un Processed (1)
246 -- Running (2)
247 -- Error (3)
248 -- Complete (4)
249 -- Resume (5) used when non-tolerance items exists
250 -- COMMENT:
251 -- This procedure sets the Interorg Transfer Cost Processor - iteration
252 -- process phase status. The phase will be 7. When the iteration process
253 -- is invoked through main program, the phase status will be set to 1
254 -- to start with indicating that the status is in Un Processed.
255 -- When the iteration process begins, the phase status will be set to 2
256 -- indicating that the status is in Running for all the valid cost groups
257 -- in the Legal Entity
258 -- If the iteration process completed with error the status is 3
259 -- If the iteration process completed where all the items achieved
260 -- tolerance, then the status is set to 4 - Complete.
261 -- If the iteration process completed where some of the items are left over
262 -- with no tolerance achieved AND the resume option is Iteration for non
263 -- tolerance items, then the status is set to 5 indicating that the
264 -- status is in Resume where the process is not completed yet.
265 -- If the iteration process completed where some of the items are left over
266 -- with no tolerance achieved AND the resume option is Final Iteration, then
267 -- the status is set to 4 - Complete indicating that the Iteration Process
268 -- is completed.
269 --
270 -- USAGE: This procedure is invoked from api:iteration_process
271 -- PRE-COND: none
272 -- EXCEPTIONS: none
273 -- +========================================================================+
274 PROCEDURE Set_Process_Status( p_legal_entity_id IN NUMBER
275 , p_period_id IN NUMBER
276 , p_period_end_date IN DATE
277 , p_phase_status IN NUMBER
278 );
279
280 -- +========================================================================+
281 -- PROCEDURE: Populate_Temp_Tables
282 -- PARAMETERS:
283 -- p_cost_group_id IN NUMBER
284 -- p_period_id IN NUMBER
285 -- p_period_start_date IN DATE
286 -- p_period_end_date IN DATE
287 -- COMMENT:
288 -- This procedure is called by the Iterative PAC Worker
289 -- PRE-COND: none
290 -- EXCEPTIONS: none
291 -- +==========================================================================+
292 PROCEDURE Populate_Temp_Tables
293 ( p_cost_group_id IN NUMBER
294 , p_period_id IN NUMBER
295 , p_period_start_date IN DATE
296 , p_period_end_date IN DATE
297 );
298 -- +========================================================================+
299 -- PROCEDURE: Retrieve_Interorg_Items
300 -- PARAMETERS:
301 -- p_period_id IN NUMBER
302 -- p_cost_group_id IN NUMBER
303 -- p_period_start_date IN DATE
304 -- p_period_end_date IN DATE
305 -- COMMENT:
306 -- This procedure is called by the API iteration_process
307 -- PRE-COND: none
308 -- EXCEPTIONS: none
309 -- +==========================================================================+
310 PROCEDURE Retrieve_Interorg_Items
311 ( p_period_id IN NUMBER
312 , p_cost_group_id IN NUMBER
313 , p_period_start_date IN DATE
314 , p_period_end_date IN DATE
315 );
316
317 -- +========================================================================+
318 -- PROCEDURE: Process_Optimal_Sequence
319 -- PARAMETERS:
320 -- p_period_id IN NUMBER
321 -- COMMENT:
322 -- This procedure is called by the Absorption Cost Process
323 -- PRE-COND: none
324 -- EXCEPTIONS: none
325 -- +==========================================================================+
326 PROCEDURE Process_Optimal_Sequence
327 ( p_period_id IN NUMBER
328 );
329
330 -- +========================================================================+
331 -- PROCEDURE: Iteration_Process PRIVATE UTILITY
332 -- PARAMETERS:
333 -- p_init_msg_list IN VARCHAR2
334 -- p_validation_level IN NUMBER
335 -- x_return_status OUT VARCHAR2(1)
336 -- x_msg_count OUT NUMBER
337 -- x_msg_data OUT VARCHAR2(2000)
338 -- p_legal_entity_id IN NUMBER
339 -- p_cost_type_id IN NUMBER
340 -- p_cost_method IN NUMBER
341 -- p_iteration_proc_flag IN VARCHAR2(1)
342 -- p_period_id IN NUMBER
343 -- p_start_date IN DATE
344 -- p_end_date IN DATE
345 -- p_inventory_item_id IN NUMBER
346 -- p_inventory_item_number IN VARHCHAR2(1025)
347 -- p_tolerance IN NUMBER
348 -- p_iteration_num IN NUMBER
349 -- p_run_options IN NUMBER
350 -- p_pac_rates_id IN NUMBER
351 -- p_uom_control IN NUMBER
352 -- COMMENT:
353 -- This procedure is called by the Interorg Transfer Cost Process worker
354 -- after completing the necessary process in phase 5 of standard PAC
355 -- feature
356 -- PRE-COND: none
357 -- EXCEPTIONS: none
358 -- +==========================================================================+
359 PROCEDURE Iteration_Process
360 ( p_init_msg_list IN VARCHAR2
361 , p_validation_level IN NUMBER
362 , p_legal_entity_id IN NUMBER
363 , p_cost_type_id IN NUMBER
364 , p_cost_method IN NUMBER
365 , p_iteration_proc_flag IN VARCHAR2
366 , p_period_id IN NUMBER
367 , p_start_date IN DATE
368 , p_end_date IN DATE
369 , p_inventory_item_id IN NUMBER
370 , p_inventory_item_number IN VARCHAR2
371 , p_tolerance IN NUMBER
372 , p_iteration_num IN NUMBER
373 , p_run_options IN NUMBER
374 , p_pac_rates_id IN NUMBER
375 , p_uom_control IN NUMBER
376 , p_user_id IN NUMBER
377 , p_login_id IN NUMBER
378 , p_req_id IN NUMBER
379 , p_prg_id IN NUMBER
380 , p_prg_appid IN NUMBER
381 );
382
383
384 END CST_PAC_ITERATION_PROCESS_PVT;
[Home] [Help]