1 PACKAGE PFT_PROFCAL_VALIDX_PUB AS
2 /* $Header: PFTPVIDXS.pls 120.1 2006/05/25 10:29:10 ssthiaga noship $ */
3
4 ---------------------------------------------
5 -- Package Constants
6 ---------------------------------------------
7 g_block CONSTANT VARCHAR2(80) := 'FEM.PLSQL.PFT_PROFCAL_VALIDX_PUB';
8 g_pft CONSTANT VARCHAR2(3) := 'PFT';
9 g_fem CONSTANT VARCHAR2(3) := 'FEM';
10 g_calling_api_version CONSTANT NUMBER := 1.0;
11 g_complete_normal CONSTANT VARCHAR2(30) := 'COMPLETE:NORMAL';
12
13 g_false CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
14 g_true CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
15 g_success CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
16 g_error CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
17 g_unexp CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
18 g_api_version CONSTANT NUMBER := 1.0;
19
20 --------Message Constants--------------
21 G_ENG_INVALID_LEDGER_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_INVALID_LEDGER_ERR';
22 G_ENG_INVALID_GVSC_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_INVALID_GVSC_ERR';
23 G_ENG_DS_WHERE_CLAUSE_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_DS_WHERE_CLAS_ERR';
24 G_PL_OP_UPD_ROWS_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_PL_OP_UPD_ROWS_ERR';
25 G_PL_IP_UPD_ROWS_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_PL_IP_UPD_ROWS_ERR';
26 G_ENG_INVALID_OBJ_DEFN_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_INVALID_OBJ_DEFN_ERR';
27 G_PL_UPD_EXEC_STEP_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_PL_UPD_EXEC_STEP_ERR';
28 G_ENG_SINGLE_RULE_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_SINGLE_RULE_ERR';
29 G_ENG_MULTI_PROC_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_MULTI_PROC_ERR';
30 G_ENG_COND_PRED_CLAUSE_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_COND_PRED_CLAUSE_ERR';
31 G_PL_REG_CHAIN_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_PL_REG_CHAIN_ERR';
32 G_PL_REG_UPD_COL_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_PL_REG_UPD_COL_ERR';
33 G_ENG_NO_OP_ROWS_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_PC_NO_OP_ROWS_ERR';
34 G_ENG_RCNT_NO_FORMULA_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_RCNT_NO_FORMULA_ERR';
35 G_ENG_PPTILE_NO_FORMULA_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_PTILE_NO_FORMULA_ERR';
36 G_ENG_PROD_NO_FORMULA_ERR CONSTANT VARCHAR2(30) := 'PFT_PPROF_PROD_NO_FORMULA_ERR';
37 --------------------------------------------
38 -- Variable Types
39 ---------------------------------------------
40 id NUMBER(9);
41 pct NUMBER(3,2);
42 flag VARCHAR2(1);
43 currency_code VARCHAR2(15);
44 varchar2_std VARCHAR2(30);
45 varchar2_150 VARCHAR2(150);
46 varchar2_240 VARCHAR2(240);
47 varchar2_1000 VARCHAR2(1000);
48 varchar2_10000 VARCHAR2(10000);
49
50 ---------------------------------------------
51 -- Package Types
52 ---------------------------------------------
53
54 TYPE cv_curs IS REF CURSOR;
55 TYPE rowid_tbl_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
56 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
57 TYPE date_tbl_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
58 TYPE flag_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
59 TYPE varchar2_std_tbl_type IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
60 TYPE varchar2_150_tbl_type IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
61 TYPE varchar2_1000_tbl_type IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
62
63 TYPE product_list IS RECORD (
64 product_id NUMBER,
65 account NUMBER,
66 factor_weight NUMBER
67 );
68
69 TYPE product_rec IS TABLE OF product_list INDEX BY BINARY_INTEGER;
70
71 l_product_rec product_rec;
72
73 TYPE NumTab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
74
75 l_customer_id NumTab;
76 l_child_id NumTab;
77 l_region_code_tab NumTab;
78 l_region_pct_tab NumTab;
79
80 TYPE region_cnt_list IS RECORD (
81 customer_id NUMBER,
82 region_code NUMBER,
83 region_pct NUMBER,
84 val_index NUMBER
85 );
86
87 TYPE region_cnt_rec IS TABLE OF region_cnt_list INDEX BY BINARY_INTEGER;
88
89 l_region_cnt_cust region_cnt_rec;
90
91 /*============================================================================+
92 | Procedure
93 | Process_Request
94 |
95 | DESCRIPTION
96 | The procedure calculates the value index of the customer
97 | based on the region counting and profit percentile steps
98 |
99 | scope - public
100 |
101 +===========================================================================*/
102
103 PROCEDURE Process_Single_Rule ( p_rule_obj_id IN NUMBER
104 ,p_cal_period_id IN NUMBER
105 ,p_dataset_io_obj_def_id IN NUMBER
106 ,p_output_dataset_code IN NUMBER
107 ,p_effective_date IN VARCHAR2
108 ,p_ledger_id IN NUMBER
109 ,p_source_system_code IN NUMBER
110 ,p_value_index_formula_id IN NUMBER
111 ,p_rule_obj_def_id IN NUMBER
112 ,p_region_counting_flag IN VARCHAR2
113 ,p_proft_percentile_flag IN VARCHAR2
114 ,p_customer_level IN NUMBER
115 ,p_cond_obj_id IN NUMBER
116 ,p_output_column IN VARCHAR2
117 ,p_exec_state IN VARCHAR2
118 ,x_return_status OUT NOCOPY VARCHAR2
119 );
120
121 END PFT_PROFCAL_VALIDX_PUB;