1 PACKAGE CST_RevenueCogsMatch_PVT AS
2 /* $Header: CSTRCMVS.pls 120.4.12010000.2 2008/10/27 20:01:22 hyu ship $ */
3
4 -- COGS Event Types
5 -- These will also be seeded lookups in mfg_lookups
6 SO_ISSUE CONSTANT NUMBER := 1;
7 RMA_RECEIPT CONSTANT NUMBER := 2;
8 COGS_RECOGNITION_EVENT CONSTANT NUMBER := 3;
9 COGS_REC_PERCENT_ADJUSTMENT CONSTANT NUMBER := 4;
10 COGS_REC_QTY_ADJUSTMENT CONSTANT NUMBER := 5;
11 RMA_RECEIPT_PLACEHOLDER CONSTANT NUMBER := 6;
12
13 -- Accounting Line Types for COGS and Deferred COGS
14 COGS_LINE_TYPE CONSTANT NUMBER := 35;
15 DEF_COGS_LINE_TYPE CONSTANT NUMBER := 36;
16
17 -- Other constants
18 C_max_bulk_fetch_size CONSTANT NUMBER := 1000;
19
20 -- Table of Numbers used to store NUMBER columns that are passed
21 -- around during event processing.
22 TYPE number_table IS TABLE OF NUMBER
23 INDEX BY BINARY_INTEGER;
24
25 -- Table used to store DATE columns
26 TYPE date_table IS TABLE OF DATE
27 INDEX BY BINARY_INTEGER;
28
29 -- Table used to store FLAG columns
30 TYPE flag_table IS TABLE OF VARCHAR2(1)
31 INDEX BY BINARY_INTEGER;
32
33 -- Table used to store VARCHAR2(15) columns
34 TYPE char15_table IS TABLE OF VARCHAR2(15)
35 INDEX BY BINARY_INTEGER;
36
37 -- Table used to store VARCHAR2(3) columns
38 TYPE char3_table IS TABLE OF VARCHAR2(3)
39 INDEX BY BINARY_INTEGER;
40
41
42 -----------------------------------------------------------------------------
43 -- Start of comments --
44 -- --
45 -- PROCEDURE --
46 -- Match_RevenueCOGS This API is the outer wrapper for the concurrent --
47 -- request that matches COGS to revenue for OM lines. --
48 -- It is run in four phases, each followed by a --
49 -- commit: --
50 -- 1) Record any sales order issues and RMA receipts --
51 -- that have not yet been inserted into CRCML and --
52 -- CCE. --
53 -- 2) Process incoming revenue events and insert --
54 -- revenue recognition per period by OM line into --
55 -- CRRL. --
56 -- 3) Compare CRRL to CCE (via CRCML) and create new --
57 -- COGS recognition events where they don't match. --
58 -- 4) Cost all of the Cogs Recogntion Events that were --
59 -- just created in bulk. --
60 -- --
61 -- --
62 -- VERSION 1.0 --
63 -- --
64 -- PARAMETERS --
65 -- --
66 -- P_LOW_DATE Lower bound for the date range. --
67 -- P_HIGH_DATE Upper bound for the date range. --
68 -- P_PHASE Set to a number, this parameter indicates that only --
69 -- that phase # should be run. Otherwise all phases --
70 -- should be run. --
71 -- --
72 -- HISTORY: --
73 -- 04/20/05 Bryan Kuntz Created --
74 -- End of comments --
75 -----------------------------------------------------------------------------
76 PROCEDURE Match_RevenueCogs(
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_dummy_out OUT NOCOPY NUMBER,
79 p_api_version IN NUMBER,
80 p_phase IN NUMBER,
81 p_low_date IN VARCHAR2,
82 p_high_date IN VARCHAR2,
83 p_ledger_id IN NUMBER DEFAULT NULL--BUG#5726230
84 ,p_neg_req_id IN NUMBER DEFAULT NULL--HYU
85 );
86
87
88 -----------------------------------------------------------------------------
89 -- Start of comments --
90 -- --
91 -- PROCEDURE --
92 -- Insert_SoIssues This procedure handles the insertion of sales order --
93 -- issue transactions in batch into the matching data --
94 -- model. Most sales orders will be inserted into the --
95 -- matching data model by the Cost Processor. Any that --
96 -- are not processed at that time (e.g. - OPM orgs) --
97 -- will be inserted here. --
98 -- --
99 -- VERSION 1.0 --
100 -- --
101 -- PARAMETERS --
102 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
103 -- WHO columns --
104 -- --
105 -- HISTORY: --
106 -- 04/22/05 Bryan Kuntz Created using cursor --
107 -- End of comments --
108 -----------------------------------------------------------------------------
109 PROCEDURE Insert_SoIssues(
110 x_return_status OUT NOCOPY VARCHAR2,
111 p_request_id IN NUMBER,
112 p_user_id IN NUMBER,
113 p_login_id IN NUMBER,
114 p_pgm_app_id IN NUMBER,
115 p_pgm_id IN NUMBER
116 );
117
118
119 -----------------------------------------------------------------------------
120 -- Start of comments --
121 -- --
122 -- PROCEDURE --
123 -- Insert_RmaReceipts This procedure handles the insertion of RMA --
124 -- receipt transactions in batch into the matching --
125 -- data model. Most RMA receipts will be inserted --
126 -- by the Cost Processor. This bulk procedure will --
127 -- pick up the rest. --
128 -- --
129 -- VERSION 1.0 --
130 -- --
131 -- PARAMETERS --
132 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
133 -- WHO columns --
134 -- --
135 -- HISTORY: --
136 -- 05/06/05 Bryan Kuntz Created --
137 -- End of comments --
138 -----------------------------------------------------------------------------
139 PROCEDURE Insert_RmaReceipts(
140 x_return_status OUT NOCOPY VARCHAR2,
141 p_request_id IN NUMBER,
142 p_user_id IN NUMBER,
143 p_login_id IN NUMBER,
144 p_pgm_app_id IN NUMBER,
145 p_pgm_id IN NUMBER
146 );
147
148
149 -----------------------------------------------------------------------------
150 -- Start of comments --
151 -- --
152 -- PROCEDURE --
153 -- Create_CogsRecognitionEvents --
154 -- This procedure is the main procedure for phase 3 of the program --
155 -- to Match COGS to Revenue. It compares the latest Revenue % with --
156 -- the latest COGS percentage and, where different, creates new --
157 -- COGS recognition events to bring the COGS percentage up to date. --
158 -- --
159 -- VERSION 1.0 --
160 -- --
161 -- PARAMETERS --
162 -- X_RETURN_STATUS Success/Error/Unexplained error - 'S','E', or 'U' --
163 -- WHO columns --
164 -- --
165 -- HISTORY: --
166 -- 04/28/05 Bryan Kuntz Created --
167 -- End of comments --
168 -----------------------------------------------------------------------------
169 PROCEDURE Create_CogsRecognitionEvents(
170 x_return_status OUT NOCOPY VARCHAR2,
171 p_request_id IN NUMBER,
172 p_user_id IN NUMBER,
173 p_login_id IN NUMBER,
174 p_pgm_app_id IN NUMBER,
175 p_pgm_id IN NUMBER,
176 p_ledger_id IN NUMBER DEFAULT NULL --BUG#5726230
177 ,p_neg_req_id IN NUMBER DEFAULT NULL --BUG#7387575
178 );
179
180
181 -----------------------------------------------------------------------------
182 -- Start of comments --
183 -- --
184 -- PROCEDURE --
185 -- Insert_OneSoIssue This procedure is very similar to the --
186 -- Insert_SoIssues() procedure above. It differs in that the --
187 -- above procedure handles bulk inserts and is called during --
188 -- one of the phases of the concurrent request, while this --
189 -- version inserts one sales order at a time into the data --
190 -- model, and is called from the Cost Processor. --
191 -- --
192 -- This procedure should only get called for issues out of --
193 -- asset subinventories. --
194 -- --
195 -- VERSION 1.0 --
196 -- --
197 -- PARAMETERS --
198 -- P_COGS_OM_LINE_ID Line_ID of the sales order issue from OM table --
199 -- P_COGS_ACCT_ID GL Code Combination for the COGS account --
200 -- P_DEF_COGS_ACCT_ID GCC for the deferred COGS account --
201 -- P_MMT_TXN_ID Transaction ID from MMT table --
202 -- P_ORGANIZATION_ID Organization ID --
203 -- P_ITEM_ID Inventory Item ID --
204 -- P_TRANSACTION_DATE Event Date --
205 -- P_COGS_GROUP_ID Cost Group ID --
206 -- P_QUANTITY Sales Order Issue Quantity as a POSITIVE value --
207 -- --
208 -- HISTORY: --
209 -- 05/13/05 Bryan Kuntz Created --
210 -- End of comments --
211 -----------------------------------------------------------------------------
212 PROCEDURE Insert_OneSoIssue(
213 p_api_version IN NUMBER,
214 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
215 p_commit IN VARCHAR2 := FND_API.G_FALSE,
216 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
217 p_user_id IN NUMBER,
218 p_login_id IN NUMBER,
219 p_request_id IN NUMBER,
220 p_pgm_app_id IN NUMBER,
221 p_pgm_id IN NUMBER,
222 x_return_status OUT NOCOPY VARCHAR2,
223 p_cogs_om_line_id IN NUMBER,
224 p_cogs_acct_id IN NUMBER,
225 p_def_cogs_acct_id IN NUMBER,
226 p_mmt_txn_id IN NUMBER,
227 p_organization_id IN NUMBER,
228 p_item_id IN NUMBER,
229 p_transaction_date IN DATE,
230 p_cost_group_id IN NUMBER,
231 p_quantity IN NUMBER
232 );
233
234
235 -----------------------------------------------------------------------------
236 -- Start of comments --
237 -- --
238 -- PROCEDURE --
239 -- Insert_OneRmaReceipt This procedure is very similar to the --
240 -- Insert_RmaReceipts() procedure above. It differs in that the --
241 -- above procedure handles bulk inserts and is called during one --
242 -- of the phases of the concurrent request, while this version --
243 -- inserts one RMA receipt at a time into the data model, and is --
244 -- called from the Cost Processor. --
245 -- --
246 -- VERSION 1.0 --
247 -- --
248 -- PARAMETERS --
249 -- P_RMA_OM_LINE_ID Line_ID of the RMA from OM table --
250 -- P_COGS_OM_LINE_ID Line_ID of the Original Sales Order Issue --
251 -- referrred to by this RMA Receipt. --
252 -- P_MMT_TXN_ID Transaction ID from MMT table --
253 -- P_ORGANIZATION_ID Organization ID --
254 -- P_ITEM_ID Inventory Item ID --
255 -- P_TRANSACTION_DATE Event Date --
256 -- P_QUANTITY Event Quantity --
257 -- X_COGS_PERCENTAGE Returns the % at which this RMA will be applied --
258 -- to COGS. --
259 -- --
260 -- HISTORY: --
261 -- 05/13/05 Bryan Kuntz Created --
262 -- End of comments --
263 -----------------------------------------------------------------------------
264 PROCEDURE Insert_OneRmaReceipt(
265 p_api_version IN NUMBER,
266 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
267 p_commit IN VARCHAR2 := FND_API.G_FALSE,
268 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
269 p_user_id IN NUMBER,
273 p_pgm_id IN NUMBER,
270 p_login_id IN NUMBER,
271 p_request_id IN NUMBER,
272 p_pgm_app_id IN NUMBER,
274 x_return_status OUT NOCOPY VARCHAR2,
275 x_msg_count OUT NOCOPY NUMBER,
276 x_msg_data OUT NOCOPY VARCHAR2,
277 p_rma_om_line_id IN NUMBER,
278 p_cogs_om_line_id IN NUMBER,
279 p_mmt_txn_id IN NUMBER,
280 p_organization_id IN NUMBER,
281 p_item_id IN NUMBER,
282 p_transaction_date IN DATE,
283 p_quantity IN NUMBER,
284 x_event_id OUT NOCOPY NUMBER,
285 x_cogs_percentage OUT NOCOPY NUMBER
286 );
287
288
289 -----------------------------------------------------------------------------
290 -- Start of comments --
291 -- --
292 -- PROCEDURE --
293 -- Record_SoIssueCost --
294 -- This procedure is called by the distribution processors to --
295 -- record the outgoing cost of the item at the time of the sales --
296 -- order issue. The logic is standard across cost methods so --
297 -- it can be called for all perpetual and PAC types. --
298 -- --
299 -- VERSION 1.0 --
300 -- --
301 -- PARAMETERS --
302 -- P_COGS_OM_LINE_ID OM Line_ID of the Sales Order issue --
303 -- P_PAC_COST_TYPE_ID Periodic Cost Type, Leave NULL for perpetual --
304 -- P_UNIT_MATERIAL_COST --
305 -- P_UNIT_MOH_COST --
306 -- P_UNIT_RESOURCE_COST --
307 -- P_UNIT_OP_COST --
308 -- P_UNIT_OVERHEAD_COST --
309 -- P_UNIT_COST --
310 -- P_TXN_QUANTITY --
311 -- --
312 -- HISTORY: --
313 -- 05/16/05 Bryan Kuntz Created --
314 -- End of comments --
315 -----------------------------------------------------------------------------
316 PROCEDURE Record_SoIssueCost(
317 p_api_version IN NUMBER,
318 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
319 p_user_id IN NUMBER,
320 p_login_id IN NUMBER,
321 p_request_id IN NUMBER,
322 p_pgm_app_id IN NUMBER,
323 p_pgm_id IN NUMBER,
324 x_return_status OUT NOCOPY VARCHAR2,
325 x_msg_count OUT NOCOPY NUMBER,
326 x_msg_data OUT NOCOPY VARCHAR2,
327 p_cogs_om_line_id IN NUMBER,
328 p_pac_cost_type_id IN NUMBER,
329 p_unit_material_cost IN NUMBER,
330 p_unit_moh_cost IN NUMBER,
331 p_unit_resource_cost IN NUMBER,
332 p_unit_op_cost IN NUMBER,
333 p_unit_overhead_cost IN NUMBER,
334 p_unit_cost IN NUMBER,
335 p_txn_quantity IN NUMBER
336 );
337
338
339 -----------------------------------------------------------------------------
340 -- Start of comments --
341 -- --
342 -- PROCEDURE --
343 -- Process_RmaReceipt --
344 -- This procedure is called by the distribution processors for --
345 -- all perpetual cost methods to create the accounting entries --
346 -- for RMAs that are linked to forward flow sales orders with --
347 -- COGS deferral. --
348 -- --
349 -- VERSION 1.0 --
350 -- --
351 -- PARAMETERS --
352 -- P_RMA_OM_LINE_ID OM Line ID of the RMA Receipt --
353 -- P_COGS_OM_LINE_ID OM Line_ID of the Sales Order Issue, not the RMA --
354 -- P_COST_TYPE_ID Cost Type if Periodic, Cost Method if perpetual --
355 -- P_TXN_QUANTITY RMA Receipt quantity --
356 -- P_COGS_PERCENTAGE Latest COGS Percentage reported for this OM line --
357 -- --
361 -----------------------------------------------------------------------------
358 -- HISTORY: --
359 -- 05/16/05 Bryan Kuntz Created --
360 -- End of comments --
362 PROCEDURE Process_RmaReceipt(
363 x_return_status OUT NOCOPY VARCHAR2,
364 x_msg_count OUT NOCOPY NUMBER,
365 x_msg_data OUT NOCOPY VARCHAR2,
366 p_rma_om_line_id IN NUMBER,
367 p_cogs_om_line_id IN NUMBER,
368 p_cost_type_id IN NUMBER,
369 p_txn_quantity IN NUMBER,
370 p_cogs_percentage IN NUMBER,
371 p_organization_id IN NUMBER,
372 p_transaction_id IN NUMBER,
373 p_item_id IN NUMBER,
374 p_sob_id IN NUMBER,
375 p_txn_date IN DATE,
376 p_txn_src_id IN NUMBER,
377 p_src_type_id IN NUMBER,
378 p_pri_curr IN VARCHAR2,
379 p_alt_curr IN VARCHAR2,
380 p_conv_date IN DATE,
381 p_conv_rate IN NUMBER,
382 p_conv_type IN VARCHAR2,
383 p_user_id IN NUMBER,
384 p_login_id IN NUMBER,
385 p_req_id IN NUMBER,
386 p_prg_appl_id IN NUMBER,
387 p_prg_id IN NUMBER
388 );
389
390
391 -----------------------------------------------------------------------------
392 -- Start of comments --
393 -- --
394 -- PROCEDURE --
395 -- Process_CogsRecognitionTxn --
396 -- This procedure is called by the distribution processors for --
397 -- all perpetual cost methods to create the accounting entries --
398 -- for COGS Recognition Events. --
399 -- --
400 -- VERSION 1.0 --
401 -- --
402 -- PARAMETERS --
403 -- P_COGS_OM_LINE_ID OM Line_ID of the Sales Order issue --
404 -- All other parameters are pretty standard for Cost Processing --
405 -- --
406 -- HISTORY: --
407 -- 05/17/05 Bryan Kuntz Created --
408 -- End of comments --
409 -----------------------------------------------------------------------------
410 PROCEDURE Process_CogsRecognitionTxn(
411 x_return_status OUT NOCOPY VARCHAR2,
412 x_msg_count OUT NOCOPY NUMBER,
413 x_msg_data OUT NOCOPY VARCHAR2,
414 p_cogs_om_line_id IN NUMBER,
415 p_transaction_id IN NUMBER,
416 p_txn_quantity IN NUMBER,
417 p_organization_id IN NUMBER,
418 p_item_id IN NUMBER,
419 p_sob_id IN NUMBER,
420 p_txn_date IN DATE,
421 p_txn_src_id IN NUMBER,
422 p_src_type_id IN NUMBER,
423 p_pri_curr IN VARCHAR2,
424 p_alt_curr IN VARCHAR2,
425 p_conv_date IN DATE,
426 p_conv_rate IN NUMBER,
427 p_conv_type IN VARCHAR2,
428 p_user_id IN NUMBER,
429 p_login_id IN NUMBER,
430 p_req_id IN NUMBER,
431 p_prg_appl_id IN NUMBER,
432 p_prg_id IN NUMBER
433 );
434
435 -----------------------------------------------------------------------------
436 -- Start of comments --
437 -- --
438 -- PROCEDURE --
439 -- Cost_BulkCogsRecTxns --
440 -- This procedure is called in phase 4 of the concurrent request --
441 -- to create the accounting distributions for all of the COGS --
442 -- Recognition Events that were created during this run of the --
443 -- concurrent request. --
444 -- --
445 -- VERSION 1.0 --
446 -- --
447 -- PARAMETERS --
448 -- Standard return status and Who columns --
452 -- End of comments --
449 -- --
450 -- HISTORY: --
451 -- 05/17/05 Bryan Kuntz Created --
453 -----------------------------------------------------------------------------
454 PROCEDURE Cost_BulkCogsRecTxns(
455 x_return_status OUT NOCOPY VARCHAR2,
456 p_request_id IN NUMBER,
457 p_user_id IN NUMBER,
458 p_login_id IN NUMBER,
459 p_pgm_app_id IN NUMBER,
460 p_pgm_id IN NUMBER,
461 p_ledger_id IN NUMBER DEFAULT NULL --BUG5726230
462 ,p_neg_req_id IN NUMBER DEFAULT NULL --BUG7387575
463 );
464
465 -----------------------------------------------------------------------------
466 -- Start of comments --
467 -- --
468 -- PROCEDURE --
469 -- Insert_PacSoIssue This is the PAC version of Insert_OneSoIssue(). --
470 -- It creates a new row in CRCML for the given OM Line ID and --
471 -- PAC cost type ID. The purpose is to record the SO issue --
472 -- cost so that future related events (e.g. COGS Recognition) --
473 -- can query this row and create accting with these amounts. --
474 -- --
475 -- VERSION 1.0 --
476 -- --
477 -- PARAMETERS --
478 -- P_TRANSACTION_ID MMT Transaction ID --
479 -- P_LAYER_ID PAC Cost Layer ID (CPIC, CPICD) --
480 -- P_COST_TYPE_ID PAC Cost Type ID --
481 -- P_COST_GROUP_ID PAC Cost Group ID --
482 -- --
483 -- HISTORY: --
484 -- 06/27/05 Bryan Kuntz Created --
485 -- End of comments --
486 -----------------------------------------------------------------------------
487 PROCEDURE Insert_PacSoIssue(
488 p_api_version IN NUMBER,
489 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
490 p_commit IN VARCHAR2 := FND_API.G_FALSE,
491 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
492 x_return_status OUT NOCOPY VARCHAR2,
493 x_msg_count OUT NOCOPY NUMBER,
494 x_msg_data OUT NOCOPY VARCHAR2,
495 p_transaction_id IN NUMBER,
496 p_layer_id IN NUMBER,
497 p_cost_type_id IN NUMBER,
498 p_cost_group_id IN NUMBER,
499 p_user_id IN NUMBER,
500 p_login_id IN NUMBER,
501 p_request_id IN NUMBER,
502 p_pgm_app_id IN NUMBER,
503 p_pgm_id IN NUMBER
504 );
505
506 -----------------------------------------------------------------------------
507 -- Start of comments --
508 -- --
509 -- PROCEDURE --
510 -- Process_PacRmaReceipt The PAC equivalent of Process_OneRmaRecipt() --
511 -- This procedure creates the distributions for RMAs that refer --
512 -- to an original sales order for which COGS was deferred. It --
513 -- creates credits to Deferred COGS and COGS as appropriate. --
514 -- --
515 -- VERSION 1.0 --
516 -- --
517 -- PARAMETERS --
518 -- P_AE_TXN_REC Transaction Record used throughout PAC processor --
519 -- P_AE_CURR_REC Currency Record used throughout PAC processor --
520 -- P_DR_FLAG Debit = True / Credit = False --
521 -- P_COGS_OM_LINE_ID OM Line ID of the sales order to which this RMA refers
522 -- L_AE_LINE_TBL Table where the distributions are built --
523 -- --
524 -- HISTORY: --
525 -- 06/28/05 Bryan Kuntz Created --
526 -- End of comments --
527 -----------------------------------------------------------------------------
528 PROCEDURE Process_PacRmaReceipt(
529 p_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
530 p_ae_curr_rec IN CSTPALTY.cst_ae_curr_rec_type,
531 p_dr_flag IN BOOLEAN,
532 p_cogs_om_line_id IN NUMBER,
533 l_ae_line_tbl IN OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
534 x_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
535 );
536
540 -- PROCEDURE --
537 -----------------------------------------------------------------------------
538 -- Start of comments --
539 -- --
541 -- Process_PacCogsRecTxn PAC equivalent of Process_CogsRecognitionTxn() --
542 -- This procedure is called from the PAC distribution processor --
543 -- to create the accounting entries for COGS Recognition events --
544 -- --
545 -- VERSION 1.0 --
546 -- --
547 -- PARAMETERS --
548 -- P_AE_TXN_REC Transaction Record used throughout PAC processor --
549 -- P_AE_CURR_REC Currency Record used throughout PAC processor --
550 -- L_AE_LINE_TBL Table where the distributions are built --
551 -- --
552 -- HISTORY: --
553 -- 06/28/05 Bryan Kuntz Created --
554 -- End of comments --
555 -----------------------------------------------------------------------------
556 PROCEDURE Process_PacCogsRecTxn(
557 p_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
558 p_ae_curr_rec IN CSTPALTY.cst_ae_curr_rec_type,
559 l_ae_line_tbl IN OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
560 x_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
561 );
562
563
564 -----------------------------------------------------------------------------
565 -- Start of comments --
566 -- --
567 -- PROCEDURE --
568 -- Print_MessageStack --
569 -- This procedure is called from Match_RevenueCogs() to spit out --
570 -- the contents of the message stack to the log file. --
571 -- --
572 -- VERSION 1.0 --
573 -- --
574 -- PARAMETERS --
575 -- none --
576 -- --
577 -- HISTORY: --
578 -- 05/17/05 Bryan Kuntz Created --
579 -- End of comments --
580 -----------------------------------------------------------------------------
581 PROCEDURE Print_MessageStack;
582
583 /*
584 In the procedure definitions, there will be notes like "Only used in perpetual" and "Only used in PAC"
585 This is due because the same three procedures will be used for both the Perpetual and Periodic
586 Revenue/COGS Matching Report. Therefore, instead of writing the three procedures twice with slightly
587 different paramenters, the 3 procedures will be written with a common set of parameters and the specific
588 concurrent program will take care of setting them apporpriately
589 */
590
591 /*===========================================================================*/
592 -- API name : Generate_DefCOGSXml
593 -- Type : Private
594 -- Function : Generate XML Data for Deferred COGS Report
595 -- Report
596 -- Pre-reqs : None.
597 -- Parameters :
598 -- in : p_cost_method in number
599 -- : p_ledger_id in number (Only used in perpetual)
600 -- : p_pac_legal_entity in number (Only used in PAC)
601 -- : p_pac_cost_type in number (Only used in PAC)
602 -- : p_pac_cost_group in number (Only used in PAC)
603 -- : p_period_name in varchar2
604 -- : p_sales_order_date_low in varchar2
605 -- : p_sales_order_date_high in varchar2
606 -- : p_all_lines in varchar2
607 -- : p_api_version in number
608 --
609 -- out :
610 -- : errcode OUT varchar2
611 -- : errno OUT number
612 --
613 -- Version : Current version 1.0
614 -- : Initial version 1.0
615 -- History : 6/24/2005 David Gottlieb Created
616 -- Notes : This Procedure is called by the Deferred COGS Report
617 -- This is the wrapper procedure that calls the other
618 -- procedures to generate XML data according to report parameters.
619 -- End of comments
620 /*===========================================================================*/
621
622 procedure Generate_DefCOGSXml (
623 errcode out nocopy varchar2,
624 err_code out nocopy number,
625 p_cost_method in number,
626 p_ledger_id in number,
627 p_pac_legal_entity in number,
628 p_pac_cost_type in number,
632 p_sales_order_date_high in varchar2,
629 p_pac_cost_group in number,
630 p_period_name in varchar2,
631 p_sales_order_date_low in varchar2,
633 p_all_lines in varchar2,
634 p_api_version in number);
635
636 /*===========================================================================*/
637 -- API name : add_parameters
638 -- Type : Private
639 -- Function : Generate XML data for Parameters and append it to
640 -- output
641 -- Pre-reqs : None.
642 -- Parameters :
643 -- in : p_api_version in number
644 -- : p_init_msg_list in varchar2
645 -- : p_validation_level in number
646 -- : p_cost_method in number
647 -- : p_operating_unit in number
648 -- : p_ledger_id in number
649 -- : p_pac_legal_entity in number
650 -- : p_pac_cost_type in number
651 -- : p_pac_cost_group in number
652 -- : p_period_name in varchar2
653 -- : p_sales_order_date_low in varchar2
654 -- : p_sales_order_date_high in varchar2
655 -- : p_all_lines in varchar2
656 --
657 -- out :
658 -- : x_return_status out nocopy varchar2
659 -- : x_msg_count out nocopy number
660 -- : x_msg_data out nocopy varchar2
661 --
662 -- in out :
663 -- : x_xml_doc in out nocopy clob
664 --
665 -- Version : Current version 1.0
666 -- : Initial version 1.0
667 -- History : 6/24/2005 David Gottlieb Created
668 -- Notes : This Procedure is called by Generate_DefCOSXml
669 -- procedure. The procedure generates XML data for the
670 -- report parameters and appends it to the report
671 -- output.
672 -- End of comments
673 /*===========================================================================*/
674
675 procedure Add_Parameters (
676 p_api_version in number,
677 p_init_msg_list in varchar2,
678 p_validation_level in number,
679 x_return_status out nocopy varchar2,
680 x_msg_count out nocopy number,
681 x_msg_data out nocopy varchar2,
682 i_cost_method in number,
683 i_operating_unit in number,
684 i_ledger_id in number,
685 i_pac_legal_entity in number,
686 i_pac_cost_type in number,
687 i_pac_cost_group in number,
688 i_period_name in varchar2,
689 i_sales_order_date_low in varchar2,
690 i_sales_order_date_high in varchar2,
691 i_all_lines in varchar2,
692 x_xml_doc in out nocopy clob);
693
694 /*===========================================================================*/
695 -- API name : Add_DefCOGSData
696 -- Type : Private
697 -- Function : Generate XML data from sql query and append it to
698 -- output
699 -- Pre-reqs : None.
700 -- Parameters :
701 -- in : p_api_version in number
702 -- : p_init_msg_list in varchar2
703 -- : p_validation_level in number
704 -- : i_cost_method in number
705 -- : i_operating_unit in number
706 -- : i_ledger_id in number
707 -- : i_pac_legal_entity in number
708 -- : i_pac_cost_type in number
709 -- : i_pac_cost_group in number
710 -- : i_period_name in varchar2
711 -- : i_sales_order_date_low in date
712 -- : i_sales_order_date_high in date
713 -- : i_set_of_books_id in number
714 -- : i_all_lines in varchar2
715 --
716 -- out : x_return_status out nocopy varchar2
717 -- : x_msg_count out nocopy number
718 -- : x_msg_data out nocopy varchar2
719 --
720 -- in out : x_xml_doc in out nocopy clob
721 --
722 -- Version : Current version 1.0
723 -- : Initial version 1.0
724 -- History : 6/24/2005 David Gottlieb Created
725 -- Notes : This Procedure is called by Generate_DefCOGSXml
726 -- procedure. The procedure generates XML data from
727 -- sql query and appends it to the report output.
728 -- End of comments
729 /*===========================================================================*/
730
731 procedure Add_DefCOGSData (
732 p_api_version in number,
733 p_init_msg_list in varchar2,
734 p_validation_level in number,
735 x_return_status out nocopy varchar2,
736 x_msg_count out nocopy number,
737 x_msg_data out nocopy varchar2,
738 i_cost_method in number,
739 i_operating_unit in number,
740 i_ledger_id in number,
741 i_pac_legal_entity in number,
742 i_pac_cost_type in number,
743 i_pac_cost_group in number,
744 i_period_name in varchar2,
745 i_sales_order_date_low in date,
746 i_sales_order_date_high in date,
747 i_all_lines in varchar2,
748 x_xml_doc in out nocopy clob);
749
750
751 ------------------------------------------------
752 -- Master ordonnancer for multi thread execution
753 ------------------------------------------------
754 PROCEDURE ordonnancer
755 (errbuf OUT NOCOPY VARCHAR2
756 ,retcode OUT NOCOPY VARCHAR2
757 ,p_batch_size IN NUMBER DEFAULT 1000
758 ,p_nb_worker IN NUMBER DEFAULT 4
759 ,p_api_version IN NUMBER
760 ,p_phase IN NUMBER
761 ,p_low_date IN VARCHAR2
762 ,p_high_date IN VARCHAR2
763 ,p_ledger_id IN NUMBER DEFAULT NULL);
764
765
766 END CST_RevenueCogsMatch_PVT;
[Home] [Help]