[Home] [Help]
PACKAGE: APPS.OZF_RESALE_COMMON_PVT
Source
1 PACKAGE OZF_RESALE_COMMON_PVT AUTHID CURRENT_USER AS
2 /* $Header: ozfvrscs.pls 120.11.12020000.4 2012/09/14 11:21:49 nepanda ship $ */
3 -------------------------------------------------------------------------------
4 -- PACKAGE:
5 -- OZF_RESALE_COMMON_PVT
6 --
7 -- PURPOSE:
8 -- Private API for common resale functionality across all IDSM batches.
9 --
10 -- HISTORY:
11 -- 02-Oct-2003 Jim Wu Created
12 -- 28-Feb-2004 Sarvanan Error Handling, Formating, Changes to error logging
13 -- and Changes for Workflow.
14 -- 28-May-2007 ateotia Bug# 5997978 fixed.
15 -- 15-Apr-2009 ateotia Bug# 8414563 fixed.
16 -- 06-May-2009 ateotia Bug# 8489216 fixed.
17 -- Added the logic for End Customer/Bill_To/Ship_To
18 -- Party creation.
19 -------------------------------------------------------------------------------
20
21 -- Default NUMBER of records fetch per call
22 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
23
24 G_ID_TYPE_BATCH CONSTANT VARCHAR2(30) := 'BATCH';
25 G_ID_TYPE_LINE CONSTANT VARCHAR2(30) := 'LINE';
26 G_ID_TYPE_IFACE CONSTANT VARCHAR2(30) := 'IFACE';
27
28 G_BATCH_NEW CONSTANT VARCHAR2(30) := 'NEW';
29 G_BATCH_OPEN CONSTANT VARCHAR2(30) := 'OPEN';
30 G_BATCH_PROCESSING CONSTANT VARCHAR2(30) := 'PROCESSING';
31 G_BATCH_PROCESSED CONSTANT VARCHAR2(30) := 'PROCESSED';
32 G_BATCH_REJECTED CONSTANT VARCHAR2(30) := 'REJECTED';
33 G_BATCH_DISPUTED CONSTANT VARCHAR2(30) := 'DISPUTED';
34 G_BATCH_PENDING_PAYMENT CONSTANT VARCHAR2(30) := 'PENDING_PAYMENT';
35 G_BATCH_CLOSED CONSTANT VARCHAR2(30) := 'CLOSED';
36
37 G_BATCH_ADJ_NEW CONSTANT VARCHAR2(30) := 'NEW';
38 G_BATCH_ADJ_OPEN CONSTANT VARCHAR2(30) := 'OPEN';
39 G_BATCH_ADJ_PROCESSED CONSTANT VARCHAR2(30) := 'PROCESSED';
40 G_BATCH_ADJ_DUPLICATED CONSTANT VARCHAR2(30) := 'DUPLICATED';
41 G_BATCH_ADJ_DISPUTED CONSTANT VARCHAR2(30) := 'DISPUTED';
42 G_BATCH_ADJ_CLOSED CONSTANT VARCHAR2(30) := 'CLOSED';
43 -- For Bug#8867381
44 G_BATCH_PENDING_CLOSE CONSTANT VARCHAR2(30) := 'PENDING_CLOSE';
45
46 -- For Bug#9614703
47 G_BATCH_PENDING_ACCRUALS CONSTANT VARCHAR2(30) := 'PENDING_ACCRUALS';
48
49 G_TP_ACCRUAL CONSTANT VARCHAR2(30) := 'TP_ACCRUAL';
50 G_CHARGEBACK CONSTANT VARCHAR2(30) := 'CHARGEBACK';
51 G_TRACING CONSTANT VARCHAR2(30) := 'TRACING';
52 G_SPECIAL_PRICING CONSTANT VARCHAR2(30) := 'SHIP_DEBIT';
53
54 G_BATCH_REF_TYPE CONSTANT VARCHAR2(30) := 'BATCH';
55 G_BATCH_OBJECT_CLASS CONSTANT VARCHAR2(30) := 'BATCH';
56
57 G_INVALD_DISPUTE_CODE CONSTANT VARCHAR2(30) := 'INVLD';
58
59 -- Start RSATYAVA Bug#10085365 fix
60 TYPE dup_cursor is REF CURSOR;
61 -- End RSATYAVA Bug#10085365 fix
62
63 -- julou bug 6317120. this assignment becomes invalid if MO: Operating Unit is not set. Get org_id from table.
64 --G_ORG_ID NUMBER := TO_NUMBER(NVL(SUBSTRB(USERENV('CLIENT_INFO'),1,10),-99));
65 CURSOR gc_batch_org_id(p_id NUMBER) IS
66 SELECT org_id
67 FROM ozf_resale_batches
68 WHERE resale_batch_id = p_id;
69
70 CURSOR gc_line_org_id(p_id NUMBER) IS
71 SELECT org_id
72 FROM ozf_resale_lines
73 WHERE resale_line_id = p_id;
74
75 CURSOR gc_iface_org_id(p_id NUMBER) IS
76 SELECT org_id
77 FROM ozf_resale_lines_int
78 WHERE resale_line_int_id = p_id;
79
80 -- Added by vanitha
81 TYPE party_rec_type IS RECORD
82 (
83 Partner_Party_ID NUMBER,
84 Name VARCHAR2(360),
85 Address VARCHAR2(240),
86 City VARCHAR2(60),
87 State VARCHAR2(60),
88 Postal_Code VARCHAR2(60),
89 Country VARCHAR2(60),
90 Site_Use_Code VARCHAR2(60),
91 Party_ID NUMBER,
92 Party_Site_ID NUMBER,
93 Party_Site_Use_ID NUMBER
94 );
95 --
96 CURSOR g_interface_rec_csr(p_id in NUMBER) IS
97 SELECT *
98 FROM ozf_resale_lines_int
99 WHERE resale_line_int_id = p_id;
100
101 CURSOR g_header_id_csr IS
102 SELECT ozf_resale_headers_all_s.nextval
103 FROM dual;
104
105 CURSOR g_line_id_csr IS
106 SELECT ozf_resale_lines_all_s.nextval
107 FROM dual;
108
109 CURSOR g_map_id_csr IS
110 SELECT ozf_resale_batch_line_map_s.nextval
111 FROM dual;
112
113 CURSOR g_log_id_csr IS
114 SELECT ozf_resale_logs_all_s.nextval
115 FROM dual;
116
117 CURSOR g_adjustment_id_csr IS
118 SELECT ozf_resale_adjustments_all_s.nextval
119 FROM dual;
120
121 CURSOR g_inventory_tracking_csr IS
122 SELECT inventory_tracking_flag
123 FROM ozf_sys_parameters;
124
125 TYPE interface_lines_tbl_type IS TABLE OF g_interface_rec_csr%rowtype INDEX BY BINARY_INTEGER;
126
127 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
128 TYPE varchar_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
129 TYPE date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
130 TYPE long_varchar_tbl_type IS TABLE OF VARCHAR2(300) INDEX BY BINARY_INTEGER;
131
132 CURSOR g_batch_type_csr (p_id NUMBER) IS
133 SELECT batch_type
134 FROM ozf_resale_batches
135 WHERE resale_batch_id = p_id;
136
137 CURSOR g_total_amount_csr(p_id in NUMBER) IS
138 -- [BEGIN OF BUG 4376520 FIXING]
139 /*
140 SELECT sum(calculated_amount)
141 , sum(total_claimed_amount)
142 , sum(total_accepted_amount)
143 , sum(total_allowed_amount)
144 --, sum(total_accepted_amount - total_claimed_amount)
145 , sum(total_claimed_amount - total_allowed_amount)-- disputed amount
146 */
147 SELECT NVL(sum(calculated_amount), 0)
148 , NVL(sum(total_claimed_amount), 0)
149 , NVL(sum(total_accepted_amount), 0)
150 , NVL(sum(total_allowed_amount), 0)
151 -- BUG 4731894 (+)
152 --, NVL(sum(total_claimed_amount - total_allowed_amount), 0) -- disputed amount
153 , NVL(sum(NVL(total_claimed_amount, 0) - NVL(total_allowed_amount, 0)), 0) -- disputed amount
154 -- BUG 4731894 (-)
155 -- [END OF BUG 4376520 FIXING]
156 FROM ozf_resale_lines_int
157 WHERE resale_batch_id = p_id
158 -- [BEGIN OF BUG 4376520 FIXING]
159 AND NVL(tracing_flag, 'F') <> 'T'
160 -- [END OF BUG 4376520 FIXING]
161 AND status_code <> 'DUPLICATED'; -- BUG 4930718
162
163 CURSOR g_disputed_line_count_csr (p_id NUMBER)IS
164 SELECT count(1)
165 FROM ozf_resale_lines_int
166 WHERE status_code = G_BATCH_ADJ_DISPUTED
167 AND resale_batch_id = p_id;
168
169 CURSOR g_exchange_rate_type_csr IS
170 SELECT exchange_rate_type
171 FROM ozf_sys_parameters;
172
173 -- Start: bug # 5997978 fixed
174 CURSOR g_resale_batch_org_id_csr(cv_batch_id NUMBER) IS
175 SELECT org_id
176 FROM ozf_resale_batches_all
177 WHERE resale_batch_id = cv_batch_id;
178
179 CURSOR g_resale_header_org_id_csr(cv_header_id NUMBER) IS
180 SELECT org_id
181 FROM ozf_resale_headers_all
182 WHERE resale_header_id = cv_header_id;
183 -- End: bug # 5997978 fixed
184
185 --Bug# 8414563 fixed by ateotia(+)
186 CURSOR g_duplicated_line_count_csr (p_resale_batch_id IN NUMBER) IS
187 SELECT count(1)
188 FROM ozf_resale_lines_int_all
189 WHERE status_code = G_BATCH_ADJ_DUPLICATED
190 AND resale_batch_id = p_resale_batch_id;
191
192 CURSOR g_tracing_flag_csr (p_resale_line_int_id IN NUMBER) IS
193 SELECT tracing_flag
194 FROM ozf_resale_lines_int_all
195 WHERE resale_line_int_id = p_resale_line_int_id;
196 --Bug# 8414563 fixed by ateotia(-)
197
198 ---------------------------------------------------------------------
199 -- PROCEDURE
200 -- Insert_Resale_Log
201 --
202 -- PURPOSE
203 -- This procedure inserts a error log
204 --
205 -- PARAMETERS
206 --
207 --
208 -- NOTES
209 ---------------------------------------------------------------------
210 PROCEDURE Insert_Resale_Log (
211 p_id_value IN VARCHAR2,
212 p_id_type IN VARCHAR2,
213 p_error_code IN VARCHAR2,
214 p_error_message IN VARCHAR2 := NULL,
215 p_column_name IN VARCHAR2,
216 p_column_value IN VARCHAR2,
217 x_return_status OUT NOCOPY VARCHAR2 );
218
219 ---------------------------------------------------------------------
220 -- PROCEDURE
221 -- Bulk_Insert_Resale_Log
222 --
223 -- PURPOSE
224 -- This procecure inserts error log for multiple resale interface lines using
225 -- bulk insert function
226 --
227 -- PARAMETERS
228 --
229 --
230 -- NOTES
231 ---------------------------------------------------------------------
232 PROCEDURE Bulk_Insert_Resale_Log (
233 p_id_value IN number_tbl_type,
234 p_id_type IN VARCHAR2,
235 p_error_code IN varchar_tbl_type,
236 p_column_name IN varchar_tbl_type,
237 p_column_value IN long_varchar_tbl_type,
238 p_batch_id IN NUMBER, -- bug # 5997978 fixed
239 x_return_status OUT NOCOPY VARCHAR2
240 );
241
242 ---------------------------------------------------------------------
243 -- PROCEDURE
244 -- Bulk_Dispute_Line
245 --
246 -- PURPOSE
247 -- This procedure sets the statuses of interface lines that have disputes
248 --
249 -- PARAMETERS
250 --
251 --
252 -- NOTES
253 ---------------------------------------------------------------------
254 PROCEDURE Bulk_Dispute_Line (
255 p_batch_id IN NUMBER,
256 p_line_status IN VARCHAR2,
257 x_return_status OUT NOCOPY VARCHAR2
258 );
259
260 ---------------------------------------------------------------------
261 -- PROCEDURE
262 -- Update_Header_Calculations
263 --
264 -- PURPOSE
265 -- ThIS procedure updates the results of chargeback processing
266 --
267 -- PARAMETERS
268 --
269 --
270 -- NOTES
271 ---------------------------------------------------------------------
272 PROCEDURE Update_Batch_Calculations (
273 p_api_version IN NUMBER
274 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
275 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
276 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
277 ,p_resale_batch_id IN NUMBER
278 ,x_return_status OUT NOCOPY VARCHAR2
279 ,x_msg_data OUT NOCOPY VARCHAR2
280 ,x_msg_count OUT NOCOPY NUMBER
281 );
282
283 ---------------------------------------------------------------------
284 -- PROCEDURE
285 -- Update_Line_Calculations
286 --
287 -- PURPOSE
288 --
289 -- PARAMETERS
290 -- x_return_status out VARCHAR2
291 --
292 -- NOTES
293 --
294 ---------------------------------------------------------------------
295 PROCEDURE Update_Line_Calculations(
296 p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE,
297 p_unit_price IN NUMBER,
298 p_line_quantity IN NUMBER,
299 p_allowed_amount IN NUMBER,
300 x_return_status OUT NOCOPY VARCHAR2
301 );
302
303 ---------------------------------------------------------------------
304 -- PROCEDURE
305 -- Validate_Batch
306 --
307 -- PURPOSE
308 -- This procedure validates the batch information
309 -- make sure that we can process this batch.
310 --
311 -- PARAMETERS
312 --
313 --
314 -- NOTES
315 ---------------------------------------------------------------------
316 PROCEDURE Validate_Batch(
317 p_api_version IN NUMBER
318 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
319 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
320 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
321 ,p_resale_batch_id IN NUMBER
322 ,x_batch_status OUT NOCOPY VARCHAR2
323 ,x_return_status OUT NOCOPY VARCHAR2
324 ,x_msg_data OUT NOCOPY VARCHAR2
325 ,x_msg_count OUT NOCOPY NUMBER
326 );
327
328 ---------------------------------------------------------------------
329 -- PROCEDURE
330 -- Validate_Order_Record
331 --
332 -- PURPOSE
333 -- This procedure validates the order information
334 -- I will only validate cust_account_id, currency_code and uom
335 --
336 -- PARAMETERS
337 --
338 --
339 -- NOTES
340 ---------------------------------------------------------------------
341 PROCEDURE Validate_Order_Record(
342 p_api_version IN NUMBER
343 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
344 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
345 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
346 ,p_resale_batch_id IN NUMBER
347 ,x_return_status OUT NOCOPY VARCHAR2
348 ,x_msg_data OUT NOCOPY VARCHAR2
349 ,x_msg_count OUT NOCOPY NUMBER
350 );
351
352 ---------------------------------------------------------------------
353 -- PROCEDURE
354 -- Update_Duplicates
355 --
356 -- PURPOSE
357 -- This procedure updates the duplicates
358 --
359 -- PARAMETERS
360 --
361 --
362 -- NOTES
363 ---------------------------------------------------------------------
364 PROCEDURE Update_Duplicates (
365 p_api_version IN NUMBER
366 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
367 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
368 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
369 ,p_resale_batch_id IN NUMBER
370 ,p_resale_batch_type IN VARCHAR2
371 ,p_batch_status IN VARCHAR2
372 ,x_batch_status OUT NOCOPY VARCHAR2
373 ,x_return_status OUT NOCOPY VARCHAR2
374 ,x_msg_data OUT NOCOPY VARCHAR2
375 ,x_msg_count OUT NOCOPY NUMBER
376 );
377
378 ---------------------------------------------------------------------
379 -- PROCEDURE
380 -- Check_Duplicate_Line
381 --
382 -- PURPOSE
383 -- This procedure tries to see whether the current line and adjustments have been sent before.
384 --
385 -- PARAMETERS
386 --
387 --
388 -- NOTES
389 ---------------------------------------------------------------------
390 PROCEDURE Check_Duplicate_Line(
391 p_api_version_number IN NUMBER
392 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
393 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
394 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
395 ,p_resale_line_int_id IN NUMBER
396 ,p_direct_customer_flag IN VARCHAR2
397 ,p_claimed_amount IN NUMBER
398 ,p_batch_type IN VARCHAR2
399 ,x_dup_line_id OUT NOCOPY NUMBER
400 ,x_dup_adjustment_id OUT NOCOPY NUMBER
401 ,x_reprocessing OUT NOCOPY BOOLEAN
402 ,x_return_status OUT NOCOPY VARCHAR2
403 ,x_msg_count OUT NOCOPY NUMBER
404 ,x_msg_data OUT NOCOPY VARCHAR2
405 );
406
407 ---------------------------------------------------------------------
408 -- PROCEDURE
409 -- Create_Utilization
410 --
411 -- PURPOSE
412 -- ThIS procedure prepare the record FOR utilization
413 --
414 -- PARAMETERS
415 --
416 --
417 -- NOTES
418 -- 27/10/2009 muthsubr Added p_use_fund_staging_tables for bug#8867381.
419 --
420 ---------------------------------------------------------------------
421 PROCEDURE Create_Utilization(
422 p_api_version IN NUMBER
423 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
424 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
425 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
426 ,p_line_int_rec IN g_interface_rec_csr%ROWTYPE
427 ,p_fund_id IN NUMBER
428 ,p_line_id IN NUMBER
429 ,p_cust_account_id IN NUMBER
430 ,p_approver_id IN NUMBER
431 ,x_return_status OUT NOCOPY VARCHAR2
432 ,x_msg_data OUT NOCOPY VARCHAR2
433 ,x_msg_count OUT NOCOPY NUMBER
434 );
435
436
437 ---------------------------------------------------------------------
438 -- PROCEDURE
439 -- Create_Utilization
440 --
441 -- PURPOSE
442 -- ThIS procedure prepare the record FOR utilization
443 --
444 -- PARAMETERS
445 -- p_use_fund_staging_tables for staging table impln of chargeback flow
446 -- and px_ozf_act_budgets_tbl for PLSQL table impln of storing budgets value.
447 -- and px_ozf_funds_old_rectype for taking the funds old values
448 -- and px_ozf_funds_new_rectype for taking the new values after updation.
449 --
450 --
451 -- NOTES
452 -- 27/10/2009 muthsubr Added px_ozf_act_budgets_tbl, p_use_fund_staging_tables
453 -- px_ozf_funds_old_rectype, px_ozf_funds_new_rectype
454 -- for bug#8867381.
455 ---------------------------------------------------------------------
456 PROCEDURE Create_Utilization(
457 p_api_version IN NUMBER
458 ,p_init_msg_LIST IN VARCHAR2 := FND_API.G_FALSE
459 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
460 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
461 ,p_line_int_rec IN g_interface_rec_csr%ROWTYPE
462 ,p_fund_id IN NUMBER
463 ,p_fund_int_rec IN ozf_funds_pvt.fund_rec_int_type DEFAULT NULL
464 ,p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F'
465 ,p_line_id IN NUMBER
466 ,p_cust_account_id IN NUMBER
467 ,p_approver_id IN NUMBER
468 ,x_return_status OUT NOCOPY VARCHAR2
469 ,x_msg_data OUT NOCOPY VARCHAR2
470 ,x_msg_count OUT NOCOPY NUMBER
471 ,p_batch_type IN VARCHAR2
472 ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
473 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
474 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
475 );
476
477 --------------------------------------------------------------------
478 -- PROCEDURE
479 -- Create_Adj_And_Utilization
480 --
481 -- PURPOSE
482 -- This procedure adjustment and utilization
483 --
484 -- PARAMETERS
485 -- p_use_fund_staging_tables for staging table impln of chargeback flow
486 --
487 -- NOTES
488 -- 27/10/2009 muthsubr Added p_use_fund_staging_tables for bug#8867381.
489 --06/11/2011 BKUNJAN Added OUT parameter x_utilization_id for ER 13333298
490 ---------------------------------------------------------------------
491 PROCEDURE Create_Adj_And_Utilization(
492 p_api_version IN NUMBER
493 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
494 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
495 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
496 ,p_price_adj_rec IN ozf_resale_adjustments_all%rowtype
497 ,p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
498 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type
499 ,p_to_create_utilization IN BOOLEAN
500 ,x_return_status OUT NOCOPY VARCHAR2
501 ,x_msg_data OUT NOCOPY VARCHAR2
502 ,x_msg_count OUT NOCOPY NUMBER
503 ,x_utilization_id OUT NOCOPY NUMBER
504 );
505
506
507 --------------------------------------------------------------------
508 -- PROCEDURE
509 -- Create_Adj_And_Utilization
510 --
511 -- PURPOSE
512 -- This procedure adjustment and utilization
513 --
514 -- PARAMETERS
515 -- p_use_fund_staging_tables for staging table impln of chargeback flow
516 -- and px_ozf_act_budgets_tbl for PLSQL table impln of storing budgets value.
517 -- and px_ozf_funds_old_rectype for taking the funds old values
518 -- and px_ozf_funds_new_rectype for taking the new values after updation.
519 --
520 -- NOTES
521 -- 27/10/2009 muthsubr Added px_ozf_act_budgets_tbl, p_use_fund_staging_tables
522 -- px_ozf_funds_old_rectype, px_ozf_funds_new_rectype
523 -- for bug#8867381.
524 -- 29/11/2010 muthsubr Altered px_ozf_funds_new_rectype to px_ozf_funds_new_tbl
525 -- for JMS ER Bug#9614703.
526 --06/11/2011 BKUNJAN Added OUT parameter x_utilization_id for ER 13333298
527 ---------------------------------------------------------------------
528 PROCEDURE Create_Adj_And_Utilization(
529 p_api_version IN NUMBER
530 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
531 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
532 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
533 ,p_price_adj_rec IN ozf_resale_adjustments_all%rowtype
534 ,p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
535 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type
536 ,p_to_create_utilization IN BOOLEAN
537 ,x_return_status OUT NOCOPY VARCHAR2
538 ,x_msg_data OUT NOCOPY VARCHAR2
539 ,x_msg_count OUT NOCOPY NUMBER
540 ,x_utilization_id OUT NOCOPY NUMBER
541 ,p_batch_type IN VARCHAR2
542 ,p_use_fund_staging_tables IN VARCHAR2 DEFAULT 'F'
543 ,px_ozf_act_budgets_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_act_budgets_table
544 ,px_ozf_funds_old_rectype IN OZF_UTILITY_PVT.ozf_funds_all_b_rectype
545 ,px_ozf_funds_new_tbl IN OUT NOCOPY OZF_UTILITY_PVT.ozf_funds_table
546 );
547
548 ---------------------------------------------------------------------
549 -- PROCEDURE
550 -- Create_Sales_Transaction
551 --
552 -- PURPOSE
553 -- This procedure inserts a record in ozf sales transaction table
554 --
555 -- PARAMETERS
556 -- p_line_int_rec IN g_interface_rec_csr%rowtype,
557 -- x_headerid out NUMBER
558 -- x_return_status out VARCHAR2
559 --
560 -- NOTES
561 --
562 ---------------------------------------------------------------------
563 PROCEDURE Create_Sales_Transaction(
564 p_api_version IN NUMBER
565 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
566 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
567 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
568 ,p_line_int_rec IN g_interface_rec_csr%rowtype
569 ,p_header_id IN NUMBER
570 ,p_line_id IN NUMBER
571 ,x_sales_transaction_id OUT NOCOPY NUMBER
572 ,x_return_status OUT NOCOPY VARCHAR2
573 ,x_msg_data OUT NOCOPY VARCHAR2
574 ,x_msg_count OUT NOCOPY NUMBER
575 );
576
577 ---------------------------------------------------------------------
578 -- PROCEDURE
579 -- Insert_Resale_Header
580 --
581 -- PURPOSE
582 -- This procedure inserts a record in resale header table
583 --
584 -- PARAMETERS
585 -- p_line_int_rec IN g_interface_rec_csr%rowtype,
586 -- x_headerid out NUMBER
587 -- x_return_status out VARCHAR2
588 --
589 -- NOTES
590 --
591 ---------------------------------------------------------------------
592 PROCEDURE Insert_Resale_Header(
593 p_api_version IN NUMBER
594 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
595 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
596 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
597 ,p_line_int_rec IN g_interface_rec_csr%rowtype
598 ,x_header_id OUT NOCOPY NUMBER
599 ,x_return_status OUT NOCOPY VARCHAR2
600 ,x_msg_data OUT NOCOPY VARCHAR2
601 ,x_msg_count OUT NOCOPY NUMBER
602 );
603
604 ---------------------------------------------------------------------
605 -- PROCEDURE
606 -- Insert_Resale_Line
607 --
608 -- PURPOSE
609 -- This procedure inserts a record in resale line table
610 --
611 -- PARAMETERS
612 -- p_line_int_rec IN g_interface_rec_csr%rowtype,
613 -- x_return_status out VARCHAR2
614 --
615 -- NOTES
616 --
617 ---------------------------------------------------------------------
618 PROCEDURE Insert_Resale_Line(
619 p_api_version IN NUMBER
620 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
621 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
622 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
623 ,p_line_int_rec IN g_interface_rec_csr%rowtype
624 ,p_header_id IN NUMBER
625 ,x_line_id OUT NOCOPY NUMBER
626 ,x_return_status OUT NOCOPY VARCHAR2
627 ,x_msg_data OUT NOCOPY VARCHAR2
628 ,x_msg_count OUT NOCOPY NUMBER
629 );
630
631 ---------------------------------------------------------------------
632 -- PROCEDURE
633 -- Insert_Resale_Line_Mapping
634 --
635 -- PURPOSE
636 -- This procedure inserts a record in resale_batch_line_mapping table
637 --
638 -- PARAMETERS
639 -- p_line_int_rec IN g_interface_rec_csr%rowtype,
640 -- x_return_status out VARCHAR2
641 --
642 -- NOTES
643 --
644 ---------------------------------------------------------------------
645 PROCEDURE Insert_Resale_Line_Mapping(
646 p_api_version IN NUMBER
647 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
648 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
649 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
650 ,p_resale_batch_id IN NUMBER
651 ,p_line_id IN NUMBER
652 ,x_return_status OUT NOCOPY VARCHAR2
653 ,x_msg_data OUT NOCOPY VARCHAR2
654 ,x_msg_count OUT NOCOPY NUMBER
655 );
656
657 ---------------------------------------------------------------------
658 -- PROCEDURE
659 -- Delete_Log
660 --
661 -- PURPOSE
662 -- This procedure delets the log for all open lines of batch
663 --
664 -- PARAMETERS
665 -- p_resale_batch_id in number
666 --
667 -- NOTES
668 -----------------------------------------------------------------------
669 PROCEDURE Delete_Log(
670 p_api_version IN NUMBER
671 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
672 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
673 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
674 ,p_resale_batch_id IN NUMBER
675 ,x_return_status OUT NOCOPY VARCHAR2
676 ,x_msg_data OUT NOCOPY VARCHAR2
677 ,x_msg_count OUT NOCOPY NUMBER
678 );
679
680 ---------------------------------------------------------------------
681 -- PROCEDURE
682 -- Create_Party
683 --
684 -- PURPOSE
685 -- This procedure creates party, party site, party site use and relationship
686 --
687 -- PARAMETERS
688 -- px_party_rec IN OUT party_rec_type
689 -- x_return_status out VARCHAR2
690 --
691 -- NOTES
692 --
693 ---------------------------------------------------------------------
694 PROCEDURE Create_Party
695 ( p_api_version IN NUMBER
696 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
697 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
698 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
699 ,px_party_rec IN OUT NOCOPY party_rec_type
700 ,x_return_status OUT NOCOPY VARCHAR2
701 ,x_msg_count OUT NOCOPY NUMBER
702 ,x_msg_data OUT NOCOPY VARCHAR2
703 );
704
705 ---------------------------------------------------------------------
706 -- PROCEDURE
707 -- Build_Global_Resale_Rec
708 --
709 -- PURPOSE
710 -- Build Global Resale Record for Pricing Simulation
711 --
712 -- PARAMETERS
713 -- p_caller_type IN VARCHAR2
714 -- p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE
715 -- p_resale_line_rec IN OZF_RESALE_LINES%ROWTYPE
716 --
717 -- NOTES
718 --
719 ---------------------------------------------------------------------
720 PROCEDURE Build_Global_Resale_Rec(
721 p_api_version IN NUMBER
722 ,p_init_msg_list IN VARCHAR2
723 ,p_commit IN VARCHAR2
724 ,p_validation_level IN NUMBER
725 ,p_caller_type IN VARCHAR2
726 ,p_line_index IN NUMBER
727 ,p_resale_line_int_rec IN OZF_RESALE_COMMON_PVT.g_interface_rec_csr%ROWTYPE
728 ,p_resale_header_rec IN OZF_RESALE_HEADERS%ROWTYPE
729 ,p_resale_line_rec IN OZF_RESALE_LINES%ROWTYPE
730 ,x_return_status OUT NOCOPY VARCHAR2
731 ,x_msg_count OUT NOCOPY NUMBER
732 ,x_msg_data OUT NOCOPY VARCHAR2
733 );
734
735 -------------------------------------------------------------------------------
736 -- PROCEDURE
737 -- Derive_Orig_Parties
738 --
739 -- PURPOSE
740 -- This procedure derives Bill_To, Ship_To and End_Cust Party information.
741 --
742 -- PARAMETERS
743 -- p_resale_batch_id IN NUMBER
744 -- p_partner_party_id IN NUMBER
745 -- x_return_status OUT NOCOPY VARCHAR2
746 --
747 -- NOTES
748 --
749 --
750 -- HISTORY
751 -- 06-May-2009 ateotia Created.
752 -- Bug# 8489216 fixed.
753 -------------------------------------------------------------------------------
754 PROCEDURE Derive_Orig_Parties (
755 p_api_version IN NUMBER
756 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
757 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
758 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
759 ,p_resale_batch_id IN NUMBER
760 ,p_partner_party_id IN NUMBER
761 ,x_return_status OUT NOCOPY VARCHAR2
762 ,x_msg_data OUT NOCOPY VARCHAR2
763 ,x_msg_count OUT NOCOPY NUMBER
764 );
765
766 -------------------------------------------------------------------------------
767 -- PROCEDURE
768 -- Derive_Bill_To_Party
769 --
770 -- PURPOSE
771 -- This procedure derives Bill_To Party, Party_Site, Party_Site_Use and
772 -- Relationship.
773 --
774 -- PARAMETERS
775 -- p_resale_batch_id IN NUMBER
776 -- p_partner_party_id IN NUMBER
777 -- x_return_status OUT NOCOPY VARCHAR2
778 --
779 -- NOTES
780 --
781 --
782 -- HISTORY
783 -- 06-May-2009 ateotia Created.
784 -- Bug# 8489216 fixed.
785 -------------------------------------------------------------------------------
786 PROCEDURE Derive_Bill_To_Party (
787 p_api_version IN NUMBER
788 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
789 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
790 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
791 ,p_resale_batch_id IN NUMBER
792 ,p_partner_party_id IN NUMBER
793 ,x_return_status OUT NOCOPY VARCHAR2
794 ,x_msg_data OUT NOCOPY VARCHAR2
795 ,x_msg_count OUT NOCOPY NUMBER
796 );
797
798 -------------------------------------------------------------------------------
799 -- PROCEDURE
800 -- Derive_Ship_To_Party
801 --
802 -- PURPOSE
803 -- This procedure derives Ship_To Party, Party_Site, Party_Site_Use and
804 -- Relationship.
805 --
806 -- PARAMETERS
807 -- p_resale_batch_id IN NUMBER
808 -- p_partner_party_id IN NUMBER
809 -- x_return_status OUT NOCOPY VARCHAR2
810 --
811 -- NOTES
812 --
813 --
814 -- HISTORY
815 -- 06-May-2009 ateotia Created.
816 -- Bug# 8489216 fixed.
817 -------------------------------------------------------------------------------
818 PROCEDURE Derive_Ship_To_Party (
819 p_api_version IN NUMBER
820 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
821 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
822 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
823 ,p_resale_batch_id IN NUMBER
824 ,p_partner_party_id IN NUMBER
825 ,x_return_status OUT NOCOPY VARCHAR2
826 ,x_msg_data OUT NOCOPY VARCHAR2
827 ,x_msg_count OUT NOCOPY NUMBER
828 );
829
830 -------------------------------------------------------------------------------
831 -- PROCEDURE
832 -- Derive_End_Cust_Party
833 --
834 -- PURPOSE
835 -- This procedure derives End Customer Party, Party_Site, Party_Site_Use and
836 -- Relationship.
837 --
838 -- PARAMETERS
839 -- p_resale_batch_id IN NUMBER
840 -- p_partner_party_id IN NUMBER
841 -- x_return_status OUT NOCOPY VARCHAR2
842 --
843 -- NOTES
844 --
845 --
846 -- HISTORY
847 -- 06-May-2009 ateotia Created.
848 -- Bug# 8489216 fixed.
849 -------------------------------------------------------------------------------
850 PROCEDURE Derive_End_Cust_Party (
851 p_api_version IN NUMBER
852 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
853 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
854 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
855 ,p_resale_batch_id IN NUMBER
856 ,p_partner_party_id IN NUMBER
857 ,x_return_status OUT NOCOPY VARCHAR2
858 ,x_msg_data OUT NOCOPY VARCHAR2
859 ,x_msg_count OUT NOCOPY NUMBER
860 );
861
862
863 --======================================================================
864 -- PROCEDURE
865 -- update_main_tables
866 --
867 -- PURPOSE
868 -- This API is called via concurrent program to update the
869 -- main tables ozf_funds_all_b, ozf_act_budgets
870 -- with the records from staging tables ozf_funds_all_b_int,
871 -- ozf_act_budgets_int and fix for the bug#8867381.
872 --
873 -- HISTORY
874 -- 30-OCT-2009 muthsubr Created.
875 --======================================================================
876 PROCEDURE update_main_tables (x_errbuf OUT NOCOPY VARCHAR2,
877 x_retcode OUT NOCOPY NUMBER,
878 p_batch_type IN VARCHAR2,
879 p_batch_id IN NUMBER
880 );
881
882 --======================================================================
883 -- PROCEDURE
884 -- Create_Draft_Utilization
885 --
886 -- PURPOSE
887 -- This API will populate the draft tables when TPA is run in DRAFT mode
888 --
889 -- HISTORY
890 -- 7/31/2012 nepanda Created For Bug 14194884 - TPA In Draft Mode ER
891 --======================================================================
892 PROCEDURE Create_Draft_Utilization(
893 p_api_version IN NUMBER
894 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
895 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
896 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
897 ,p_price_adj_rec IN ozf_resale_adjustments_all%rowtype
898 ,p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type
899 ,p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type
900 ,p_resale_line_int_rec IN OUT NOCOPY g_interface_rec_csr%rowtype
901 ,p_draft_run_id IN NUMBER
902 ,p_caller_type IN VARCHAR2
903 ,px_draft_accrual_tbl IN OUT NOCOPY OZF_TP_ACCRUAL_PVT.ozf_draft_accrual_tbl_type
904 ,x_return_status OUT NOCOPY VARCHAR2
905 ,x_msg_data OUT NOCOPY VARCHAR2
906 ,x_msg_count OUT NOCOPY NUMBER
907 ,x_draft_utilization_id OUT NOCOPY NUMBER
908 );
909
910 END OZF_RESALE_COMMON_PVT;