1 PACKAGE PJI_RM_SUM_AVL AUTHID CURRENT_USER AS
2 /* $Header: PJISR04S.pls 115.7 2003/04/02 23:26:40 svermett noship $ */
3
4 --exception to raise
5 RAISE_USER_DEF_EXCEPTION EXCEPTION;
6 PRAGMA EXCEPTION_INIT(RAISE_USER_DEF_EXCEPTION, -502);
7
8 TYPE V_TYPE_TAB IS TABLE OF VARCHAR2(30)
9 INDEX BY BINARY_INTEGER;
10 TYPE N_TYPE_TAB IS TABLE OF NUMBER(15)
11 INDEX BY BINARY_INTEGER;
12
13 PROCEDURE INIT_PCKG_GLOBAL_VARS;
14
15 PROCEDURE POP_ROLL_WEEK_OFFSET;
16
17 PROCEDURE CALCULATE_BUCKET_VALUE
18 (
19 p_res_cnt IN NUMBER
20 ,x_bckt_1 OUT NOCOPY NUMBER
21 ,x_bckt_2 OUT NOCOPY NUMBER
22 ,x_bckt_3 OUT NOCOPY NUMBER
23 ,x_bckt_4 OUT NOCOPY NUMBER
24 ,x_bckt_5 OUT NOCOPY NUMBER
25 );
26
27 PROCEDURE CALC_CS_RES_CNT_VALUE
28 (
29 p_res_cnt_tbl IN OUT NOCOPY N_TYPE_TAB
30 );
31
32 PROCEDURE DEL_GLOBAL_RS_AVL3_TBL;
33
34 PROCEDURE BULK_INSERT_RS_AVL3
35 (
36 p_exp_organization_id_in_tbl IN N_TYPE_TAB,
37 p_exp_org_id_in_tbl IN N_TYPE_TAB,
38 p_period_type_id_in_tbl IN N_TYPE_TAB,
39 p_time_id_in_tbl IN N_TYPE_TAB,
40 p_person_id_in_tbl IN N_TYPE_TAB,
41 p_calendar_type_in_tbl IN V_TYPE_TAB,
42 p_threshold_in_tbl IN N_TYPE_TAB,
43 p_as_of_date_in_tbl IN N_TYPE_TAB,
44 p_bckt_1_cs_in_tbl IN N_TYPE_TAB,
45 p_bckt_2_cs_in_tbl IN N_TYPE_TAB,
46 p_bckt_3_cs_in_tbl IN N_TYPE_TAB,
47 p_bckt_4_cs_in_tbl IN N_TYPE_TAB,
48 p_bckt_5_cs_in_tbl IN N_TYPE_TAB,
49 p_bckt_1_cm_in_tbl IN N_TYPE_TAB,
50 p_bckt_2_cm_in_tbl IN N_TYPE_TAB,
51 p_bckt_3_cm_in_tbl IN N_TYPE_TAB,
52 p_bckt_4_cm_in_tbl IN N_TYPE_TAB,
53 p_bckt_5_cm_in_tbl IN N_TYPE_TAB,
54 p_total_res_cnt_in_tbl IN N_TYPE_TAB,
55 p_run_mode IN VARCHAR2,
56 p_blind_insert_flag IN VARCHAR2
57 );
58
59 PROCEDURE PREPARE_TO_INS_INTO_AVL3
60 (
61 p_exp_organization_id IN PJI_RM_AGGR_AVL2.expenditure_organization_id%TYPE,
62 p_exp_org_id IN PJI_RM_AGGR_AVL2.expenditure_org_id%TYPE,
63 p_person_id IN PJI_RM_AGGR_AVL2.person_id%TYPE,
64 p_time_id IN PJI_RM_AGGR_AVL2.time_id%TYPE,
65 p_curr_pd IN NUMBER,
66 p_as_of_date IN NUMBER,
67 p_pd_org_st_date IN NUMBER,
68 p_period_type_id IN NUMBER,
69 p_calendar_type IN VARCHAR2,
70 p_res_cnt_tbl IN N_TYPE_TAB,
71 p_run_mode IN VARCHAR2,
72 p_blind_insert_flag IN VARCHAR2,
73 x_zero_bkt_cnt_flag OUT NOCOPY VARCHAR2
74 );
75
76 PROCEDURE DEL_GLOBAL_RS_AVL4_TBL;
77
78 PROCEDURE BULK_INSERT_RS_AVL4
79 (
80 p_exp_organization_id_in_tbl IN N_TYPE_TAB,
81 p_exp_org_id_in_tbl IN N_TYPE_TAB,
82 p_period_type_id_in_tbl IN N_TYPE_TAB,
83 p_time_id_in_tbl IN N_TYPE_TAB,
84 p_person_id_in_tbl IN N_TYPE_TAB,
85 p_calendar_type_in_tbl IN V_TYPE_TAB,
86 p_threshold_in_tbl IN N_TYPE_TAB,
87 p_as_of_date_in_tbl IN N_TYPE_TAB,
88 p_availability_in_tbl IN N_TYPE_TAB,
89 p_total_res_cnt_in_tbl IN N_TYPE_TAB,
90 p_run_mode IN VARCHAR2,
91 p_blind_insert_flag IN VARCHAR2
92 );
93
94 PROCEDURE PREPARE_TO_INS_INTO_AVL4
95 (
96 p_exp_organization_id IN PJI_RM_AGGR_AVL2.expenditure_organization_id%TYPE,
97 p_exp_org_id IN PJI_RM_AGGR_AVL2.expenditure_org_id%TYPE,
98 p_person_id IN PJI_RM_AGGR_AVL2.person_id%TYPE,
99 p_time_id IN PJI_RM_AGGR_AVL2.time_id%TYPE,
100 p_curr_pd IN NUMBER,
101 p_as_of_date IN NUMBER,
102 p_pd_org_st_date IN NUMBER,
103 p_period_type_id IN NUMBER,
104 p_calendar_type IN VARCHAR2,
105 p_res_cnt_tbl IN N_TYPE_TAB,
106 p_run_mode IN VARCHAR2,
107 p_blind_insert_flag IN VARCHAR2,
108 x_zero_bkt_cnt_flag OUT NOCOPY VARCHAR2
109 );
110
111 PROCEDURE CALCULATE_RES_AVL
112 (
113 p_worker_id IN NUMBER,
114 p_person_id IN NUMBER,
115 p_run_mode IN VARCHAR2,
116 x_return_status OUT NOCOPY VARCHAR2
117 );
118
119 /**************************************************************************
120 THE PART BELOW IS THE DRIVER PART FOR CALCULATIONS. IT ACTS LIKE AN
121 OVERALL MANAGER WHO MONITORS THE RESOURCE AVAILABILITY CALCULATIONS
122 **************************************************************************/
123
124 PROCEDURE CALC_CURR_RES_COUNT;
125
126 PROCEDURE MERGE_ORG_AVL_DUR
127 (
128 p_worker_id IN NUMBER
129 );
130
131 PROCEDURE MERGE_CURR_ORG_AVL
132 (
133 p_worker_id IN NUMBER
134 );
135
136 PROCEDURE UPDATE_RES_STATUS;
137
138 PROCEDURE RES_CALC_CLEANUP
139 (
140 p_worker_id IN NUMBER
141 );
142
143 PROCEDURE START_RES_AVL_CALC_R1
144 (
145 p_worker_id IN NUMBER
146 );
147
148 PROCEDURE UPDATE_RES_STA_FOR_RUN2
149 (
150 p_worker_id IN NUMBER
151 );
152
153 PROCEDURE START_RES_AVL_CALC_R2
154 (
155 p_worker_id IN NUMBER
156 );
157
158 PROCEDURE INS_INTO_RES_STATUS
159 (
160 p_worker_id IN NUMBER
161 );
162
163 PROCEDURE REFRESH_AV_ORGO_F_MV
164 (
165 p_worker_id IN NUMBER
166 );
167
168 PROCEDURE REFRESH_CA_ORGO_F_MV
169 (
170 p_worker_id IN NUMBER
171 );
172
173 END PJI_RM_SUM_AVL;
[Home] [Help]