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