1 PACKAGE pn_var_trx_pkg AUTHID CURRENT_USER AS
2 -- $Header: PNVRTRXS.pls 120.0 2007/10/03 14:30:19 rthumma noship $
3
4 /* calculation methods */
5 G_CALC_CUMULATIVE VARCHAR2(30) := 'C';
6 G_CALC_NON_CUMULATIVE VARCHAR2(30) := 'N';
7 G_CALC_YTD VARCHAR2(30) := 'Y';
8 G_CALC_TRUE_UP VARCHAR2(30) := 'T';
9
10 /* proration rules */
11 G_PRORUL_NP VARCHAR2(30) := 'NP';
12 G_PRORUL_STD VARCHAR2(30) := 'STD';
13 G_PRORUL_FY VARCHAR2(30) := 'FY';
14 G_PRORUL_LY VARCHAR2(30) := 'LY';
15 G_PRORUL_FLY VARCHAR2(30) := 'FLY';
16 G_PRORUL_CYP VARCHAR2(30) := 'CYP';
17 G_PRORUL_CYNP VARCHAR2(30) := 'CYNP';
18
19 /* sales volume status */
20 G_SALESVOL_STATUS_APPROVED VARCHAR2(30) := 'APPROVED';
21 G_SALESVOL_STATUS_DRAFT VARCHAR2(30) := 'DRAFT';
22 G_SALESVOL_STATUS_ON_HOLD VARCHAR2(30) := 'ON_HOLD';
23
24 /* period status */
25 G_PERIOD_ACTIVE_STATUS VARCHAR2(30) := 'ACTIVE';
26 G_PERIOD_REVERSED_STATUS VARCHAR2(30) := 'REVERSED';
27
28 /* data structures */
29 TYPE TRX_HRD_T IS TABLE OF pn_var_trx_headers_all%ROWTYPE INDEX BY BINARY_INTEGER;
30 TYPE TRX_DTL_T IS TABLE OF pn_var_trx_details_all%ROWTYPE INDEX BY BINARY_INTEGER;
31
32 /* functions */
33 FUNCTION exists_trx_hdr( p_vr_id IN NUMBER
34 ,p_period_id IN NUMBER
35 ,p_line_item_id IN NUMBER
36 ,p_grp_date_id IN NUMBER
37 ,p_calc_prd_st_dt IN DATE
38 ,p_calc_prd_end_dt IN DATE)
39 RETURN NUMBER;
40
41 FUNCTION exists_trx_dtl( p_trx_hdr_id IN NUMBER
42 ,p_bkpt_dtl_id IN NUMBER)
43 RETURN NUMBER;
44
45 /* procedures */
46 PROCEDURE insert_trx_hdr( p_trx_header_id IN OUT NOCOPY NUMBER
47 ,p_var_rent_id IN NUMBER
48 ,p_period_id IN NUMBER
49 ,p_line_item_id IN NUMBER
50 ,p_grp_date_id IN NUMBER
51 ,p_calc_prd_start_date IN DATE
52 ,p_calc_prd_end_date IN DATE
53 ,p_var_rent_summ_id IN NUMBER
54 ,p_line_item_group_id IN NUMBER
55 ,p_reset_group_id IN NUMBER
56 ,p_proration_factor IN NUMBER
57 ,p_reporting_group_sales IN NUMBER
58 ,p_prorated_group_sales IN NUMBER
59 ,p_ytd_sales IN NUMBER
60 ,p_fy_proration_sales IN NUMBER
61 ,p_ly_proration_sales IN NUMBER
62 ,p_percent_rent_due IN NUMBER
63 ,p_ytd_percent_rent IN NUMBER
64 ,p_calculated_rent IN NUMBER
65 ,p_prorated_rent_due IN NUMBER
66 ,p_invoice_flag IN VARCHAR2
67 ,p_org_id IN NUMBER
68 ,p_last_update_date IN DATE
69 ,p_last_updated_by IN NUMBER
70 ,p_creation_date IN DATE
71 ,p_created_by IN NUMBER
72 ,p_last_update_login IN NUMBER);
73
74 PROCEDURE insert_trx_dtl( p_trx_detail_id IN OUT NOCOPY NUMBER
75 ,p_trx_header_id IN NUMBER
76 ,p_bkpt_detail_id IN NUMBER
77 ,p_bkpt_rate IN NUMBER
78 ,p_prorated_grp_vol_start IN NUMBER
79 ,p_prorated_grp_vol_end IN NUMBER
80 ,p_fy_pr_grp_vol_start IN NUMBER
81 ,p_fy_pr_grp_vol_end IN NUMBER
82 ,p_ly_pr_grp_vol_start IN NUMBER
83 ,p_ly_pr_grp_vol_end IN NUMBER
84 ,p_pr_grp_blended_vol_start IN NUMBER
85 ,p_pr_grp_blended_vol_end IN NUMBER
86 ,p_ytd_group_vol_start IN NUMBER
87 ,p_ytd_group_vol_end IN NUMBER
88 ,p_blended_period_vol_start IN NUMBER
89 ,p_blended_period_vol_end IN NUMBER
90 ,p_org_id IN NUMBER
91 ,p_last_update_date IN DATE
92 ,p_last_updated_by IN NUMBER
93 ,p_creation_date IN DATE
94 ,p_created_by IN NUMBER
95 ,p_last_update_login IN NUMBER);
96
97 /* ----------------------------------------------------------------------
98 ----- PROCEDURES TO CREATE TRX HEADERS, DETAILS, POPULATE BKPTS -----
99 ---------------------------------------------------------------------- */
100
101 PROCEDURE populate_line_grp_id(p_var_rent_id IN NUMBER);
102
103 PROCEDURE populate_reset_grp_id(p_var_rent_id IN NUMBER);
104
105 PROCEDURE populate_ly_pro_vol( p_var_rent_id IN NUMBER
106 ,p_proration_rule IN VARCHAR2
107 ,p_vr_commencement_dt IN DATE
108 ,p_vr_termination_dt IN DATE);
109
110 PROCEDURE populate_fy_pro_vol( p_var_rent_id IN NUMBER
111 ,p_proration_rule IN VARCHAR2
112 ,p_vr_commencement_dt IN DATE
113 ,p_vr_termination_dt IN DATE);
114
115 PROCEDURE populate_blended_grp_vol( p_var_rent_id IN NUMBER
116 ,p_proration_rule IN VARCHAR2);
117
118 PROCEDURE populate_ytd_pro_vol( p_var_rent_id IN NUMBER
119 ,p_proration_rule IN VARCHAR2);
120
121 PROCEDURE populate_blended_period_vol( p_var_rent_id IN NUMBER
122 ,p_proration_rule IN VARCHAR2
123 ,p_calc_method IN VARCHAR2);
124
125 PROCEDURE delete_transactions( p_var_rent_id IN NUMBER
126 ,p_period_id IN NUMBER
127 ,p_line_item_id IN NUMBER);
128
129 /* -- procedure to be called from outside this package -- */
130 PROCEDURE populate_transactions(p_var_rent_id IN NUMBER);
131
132 /* ----------------------------------------------------------------------
133 -------------------- PROCEDURES TO POPULATE SALES --------------------
134 ---------------------------------------------------------------------- */
135 PROCEDURE get_calc_prd_sales( p_var_rent_id IN NUMBER
136 ,p_period_id IN NUMBER
137 ,p_line_item_id IN NUMBER
138 ,p_grp_date_id IN NUMBER
139 ,p_start_date IN DATE
140 ,p_end_date IN DATE
141 ,x_pro_sales OUT NOCOPY NUMBER
142 ,x_sales OUT NOCOPY NUMBER);
143
144 FUNCTION get_calc_prd_sales( p_var_rent_id IN NUMBER
145 ,p_period_id IN NUMBER
146 ,p_line_item_id IN NUMBER
147 ,p_grp_date_id IN NUMBER
148 ,p_start_date IN DATE
149 ,p_end_date IN DATE)
150 RETURN NUMBER;
151
152 PROCEDURE populate_ly_pro_sales( p_var_rent_id IN NUMBER
153 ,p_proration_rule IN VARCHAR2
154 ,p_vr_commencement_dt IN DATE
155 ,p_vr_termination_dt IN DATE);
156
157 PROCEDURE populate_fy_pro_sales( p_var_rent_id IN NUMBER
158 ,p_proration_rule IN VARCHAR2
159 ,p_vr_commencement_dt IN DATE
160 ,p_vr_termination_dt IN DATE);
161
162 PROCEDURE populate_ytd_sales( p_var_rent_id IN NUMBER
163 ,p_proration_rule IN VARCHAR2);
164
165 PROCEDURE populate_sales (p_var_rent_id IN NUMBER);
166 /* ----------------------------------------------------------------------
167 ----- PROCEDURES TO POPULATE SALES DATA FOR FORCASTED SALES -----
168 ---------------------------------------------------------------------- */
169 PROCEDURE get_calc_prd_sales_for( p_var_rent_id IN NUMBER
170 ,p_period_id IN NUMBER
171 ,p_line_item_id IN NUMBER
172 ,p_grp_date_id IN NUMBER
173 ,p_start_date IN DATE
174 ,p_end_date IN DATE
175 ,x_pro_sales OUT NOCOPY NUMBER
176 ,x_sales OUT NOCOPY NUMBER);
177
178 FUNCTION get_calc_prd_sales_for( p_var_rent_id IN NUMBER
179 ,p_period_id IN NUMBER
180 ,p_line_item_id IN NUMBER
181 ,p_grp_date_id IN NUMBER
182 ,p_start_date IN DATE
183 ,p_end_date IN DATE)
184 RETURN NUMBER;
185
186 PROCEDURE populate_ytd_sales_for( p_var_rent_id IN NUMBER
187 ,p_calc_method IN VARCHAR2);
188
189 PROCEDURE populate_sales_for( p_var_rent_id IN NUMBER);
190 /* ----------------------------------------------------------------------
191 ----- PROCEDURES TO POPULATE DEDUCTIONS-------------------------------
192 ---------------------------------------------------------------------- */
193 PROCEDURE get_calc_prd_dedc( p_var_rent_id IN NUMBER
194 ,p_period_id IN NUMBER
195 ,p_line_item_id IN NUMBER
196 ,p_grp_date_id IN NUMBER
197 ,p_start_date IN DATE
198 ,p_end_date IN DATE
199 ,x_pro_dedc OUT NOCOPY NUMBER
200 ,x_dedc OUT NOCOPY NUMBER);
201 FUNCTION get_calc_prd_dedc( p_var_rent_id IN NUMBER
202 ,p_period_id IN NUMBER
203 ,p_line_item_id IN NUMBER
204 ,p_grp_date_id IN NUMBER
205 ,p_start_date IN DATE
206 ,p_end_date IN DATE)
207
208 RETURN NUMBER;
209 PROCEDURE populate_ly_pro_dedc( p_var_rent_id IN NUMBER
210 ,p_proration_rule IN VARCHAR2
211 ,p_vr_commencement_dt IN DATE
212 ,p_vr_termination_dt IN DATE);
213 PROCEDURE populate_fy_pro_dedc( p_var_rent_id IN NUMBER
214 ,p_proration_rule IN VARCHAR2
215 ,p_vr_commencement_dt IN DATE
216 ,p_vr_termination_dt IN DATE);
217 PROCEDURE populate_ytd_deductions( p_var_rent_id IN NUMBER
218 ,p_proration_rule IN VARCHAR2);
219 PROCEDURE populate_deductions(p_var_rent_id IN NUMBER);
220
221 END pn_var_trx_pkg;
[Home] [Help]