[Home] [Help]
PACKAGE: APPS.OZF_OFFR_ELIG_PROD_DENORM_PVT
Source
1 PACKAGE OZF_OFFR_ELIG_PROD_DENORM_PVT AUTHID CURRENT_USER AS
2 /* $Header: ozfvodes.pls 120.4 2006/03/30 13:37:02 gramanat ship $ */
3
4
5 TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 TYPE char_tbl_type IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
7
8 ---------------------------------------------------------------------
9 -- FUNCTION
10 -- get_sql
11 --
12 -- PURPOSE
13 -- Retrieves SQL statment for given context, attribute.
14 --
15 -- PARAMETERS
16 -- p_context: product or qualifier context
17 -- p_attribute: context attribute
18 -- p_attr_value: context attribute value
19 -- p_type: PROD for product; ELIG for eligibity
20 --
21 -- NOTES
22 -- This functions returns SQL statement for the given context, attribute and attribute value.
23 ---------------------------------------------------------------------
24 FUNCTION get_sql(
25 p_context IN VARCHAR2,
26 p_attribute IN VARCHAR2,
27 p_attr_value_from IN VARCHAR2,
28 p_attr_value_to IN VARCHAR2,
29 p_comparison IN VARCHAR2,
30 p_type IN VARCHAR2,
31 p_qualifier_id IN NUMBER := NULL,
32 p_qualifier_group IN NUMBER := NULL
33 )
34 RETURN VARCHAR2;
35
36
37 ---------------------------------------------------------------------
38 -- PROCEDURE
39 -- refresh_parties
40 --
41 -- PURPOSE
42 -- Refreshes offer and party denorm table ams_offer_parties.
43 --
44 -- PARAMETERS
45 -- p_list_header_id: list_header_id of the offer
46 --
47 -- DESCRIPTION
48 -- This procedure calls get_sql, builds SQL statment for parties and refresh ams_offer_parties
49 ---------------------------------------------------------------------
50 PROCEDURE refresh_parties(
51 p_api_version IN NUMBER,
52 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
53 p_commit IN VARCHAR2 := FND_API.g_false,
54
55 p_list_header_id IN NUMBER,
56 p_calling_from_den IN VARCHAR2,
57
58 x_return_status OUT NOCOPY VARCHAR2,
59 x_msg_count OUT NOCOPY NUMBER,
60 x_msg_data OUT NOCOPY VARCHAR2,
61
62 x_party_stmt OUT NOCOPY VARCHAR2,
63 p_qnum IN NUMBER := NULL
64 );
65
66 PROCEDURE refresh_volume_products(
67 p_api_version IN NUMBER,
68 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
69 p_commit IN VARCHAR2 := FND_API.g_false,
70
71 p_list_header_id IN NUMBER,
72 p_calling_from_den IN VARCHAR2,
73
74 x_return_status OUT NOCOPY VARCHAR2,
75 x_msg_count OUT NOCOPY NUMBER,
76 x_msg_data OUT NOCOPY VARCHAR2,
77
78 x_product_stmt OUT NOCOPY VARCHAR2,
79 p_lline_id IN NUMBER := NULL
80 );
81
82 PROCEDURE refresh_lumpsum_parties(
83 p_api_version IN NUMBER,
84 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
85 p_commit IN VARCHAR2 := FND_API.g_false,
86
87 p_list_header_id IN NUMBER,
88 p_calling_from_den IN VARCHAR2,
89
90 x_return_status OUT NOCOPY VARCHAR2,
91 x_msg_count OUT NOCOPY NUMBER,
92 x_msg_data OUT NOCOPY VARCHAR2,
93
94 x_party_stmt OUT NOCOPY VARCHAR2
95 );
96
97 PROCEDURE get_actual_values(
98 p_uom_code IN VARCHAR2,
99 p_offer_id IN NUMBER,
100 p_org_id IN NUMBER,
101 p_dis_as_exp IN VARCHAR2,
102 p_curr_code IN VARCHAR2,
103 x_actual_units OUT NOCOPY NUMBER,
104 x_actual_revenue OUT NOCOPY NUMBER,
105 x_actual_costs OUT NOCOPY NUMBER,
106 xy_actual_revenue OUT NOCOPY NUMBER,
107 xy_actual_costs OUT NOCOPY NUMBER,
108 x_return_status OUT NOCOPY VARCHAR2,
109 x_msg_count OUT NOCOPY NUMBER,
110 x_msg_data OUT NOCOPY VARCHAR2
111 );
112
113 PROCEDURE get_forecast_values (
114 p_forecast_uom_code IN VARCHAR2,
115 p_offer_id IN NUMBER,
116 p_org_id IN NUMBER,
117 p_dis_as_exp IN VARCHAR2,
118 x_forecast_units OUT NOCOPY NUMBER,
119 x_forecast_revenue OUT NOCOPY NUMBER,
120 x_forecast_costs OUT NOCOPY NUMBER,
121 xy_forecast_revenue OUT NOCOPY NUMBER,
122 xy_forecast_costs OUT NOCOPY NUMBER,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_msg_count OUT NOCOPY NUMBER,
125 x_msg_data OUT NOCOPY VARCHAR2
126 );
127
128 --------------------------------------------------------------------
129 -- PROCEDURE
130 -- refresh_products
131 --
132 -- PURPOSE
133 -- Refreshes offer and product denorm table ams_offer_products.
134 --
135 -- PARAMETERS
136 -- p_list_header_id: list_header_id of the offer
137 -- DESCRIPTION
138 -- This procedure calls get_sql, builds SQL statment for product and refresh ams_offer_products
139 ----------------------------------------------------------------------
140 PROCEDURE refresh_products(
141 p_api_version IN NUMBER,
142 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
143 p_commit IN VARCHAR2 := FND_API.g_false,
144
145 p_list_header_id IN NUMBER,
146 p_calling_from_den IN VARCHAR2,
147
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_msg_count OUT NOCOPY NUMBER,
150 x_msg_data OUT NOCOPY VARCHAR2,
151
152 x_product_stmt OUT NOCOPY VARCHAR2,
153 p_lline_id IN NUMBER := NULL
154 );
155
156 -------------------------------------------------------------------
157 -- PROCEDURE
158 -- refresh_offers
159 --
160 -- PURPOSE
161 -- Refresh denorm tables ams_offer_products and ams_offer_parties.
162 --
163 -- PARAMETERS
164 -- p_increment_flag: indicates where full or incremental denorm
165 -- p_latest_comp_date: indicates the last concurrent program run date
166 -- NOTES
167 -- This is the main procedure. It calls refresh refresh_parties and ams_products
168 -- to update ams_offer_parties and ams_offer_produces, respectively.
169 --------------------------------------------------------------------
170 PROCEDURE refresh_offers(
171 ERRBUF OUT NOCOPY VARCHAR2,
172 RETCODE OUT NOCOPY VARCHAR2,
173 x_return_status OUT NOCOPY VARCHAR2,
174 p_increment_flag IN VARCHAR2 := 'N',
175 p_latest_comp_date IN DATE,
176 p_offer_id IN NUMBER
177 );
178
179 -------------------------------------------------------------------
180 -- PROCEDURE
181 -- find_party_elig
182 --
183 -- PURPOSE
184 -- Find eligible offer for given party and offers.
185 --
186 -- PARAMETERS
187 -- p_offers_tbl: Input, table of qp_list_header_id of offers
188 -- p_party_id: Input, party id
189 -- x_offers_tbl: Output, table of qp_list_header_id of offers
190 -- NOTES
191 --
192 --------------------------------------------------------------------
193 PROCEDURE find_party_elig(
194 p_offers_tbl IN num_tbl_type,
195 p_party_id IN NUMBER,
196 p_cust_acct_id IN NUMBER := NULL,
197 p_cust_site_id IN NUMBER := NULL,
198
199 p_api_version IN NUMBER,
200 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
201 p_commit IN VARCHAR2 := FND_API.g_false,
202
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2,
206
207 x_offers_tbl OUT NOCOPY num_tbl_type
208 );
209
210 -------------------------------------------------------------------
211 -- PROCEDURE
212 -- find_products_elig
213 --
214 -- PURPOSE
215 -- Find eligible offer for given party and products.
216 --
217 -- PARAMETERS
218 -- p_products_tbl: Input, table of product_id of products
219 -- p_party_id: Input, party id
220 -- x_offers_tbl: Output, table of qp_list_header_id of offers
221 --
222 -- NOTES
223 --
224 --------------------------------------------------------------------
225 PROCEDURE find_product_elig(
226 p_products_tbl IN num_tbl_type,
227 p_party_id IN NUMBER,
228 p_cust_acct_id IN NUMBER := NULL,
229 p_cust_site_id IN NUMBER := NULL,
230
231 p_api_version IN NUMBER,
232 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
233 p_commit IN VARCHAR2 := FND_API.g_false,
234
235 x_return_status OUT NOCOPY VARCHAR2,
236 x_msg_count OUT NOCOPY NUMBER,
237 x_msg_data OUT NOCOPY VARCHAR2,
238
239 x_offers_tbl OUT NOCOPY num_tbl_type
240 );
241
242 --------------------------------------------------------------------
243 -- PROCEDURE
244 -- get_party_product_stmt
245 --
246 -- PURPOSE
247 -- Generates denorm statement for budget validation.
248 --
249 -- PARAMETERS
250 -- p_list_header_id: list_header_id of the offer
251 -- x_party_stmt: party statement for the offer
252 -- x_product_stmt: product statement for the offer
253 -- DESCRIPTION
254 -- This procedure calls get_sql, builds SQL statment for product and refresh ams_offer_products
255 ----------------------------------------------------------------------
256 PROCEDURE get_party_product_stmt(
257 p_api_version IN NUMBER,
258 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
259 p_commit IN VARCHAR2 := FND_API.g_false,
260
261 p_list_header_id IN NUMBER,
262
263 x_return_status OUT NOCOPY VARCHAR2,
264 x_msg_count OUT NOCOPY NUMBER,
265 x_msg_data OUT NOCOPY VARCHAR2,
266
267 x_party_stmt OUT NOCOPY VARCHAR2,
268 x_product_stmt OUT NOCOPY VARCHAR2
269 );
270
271
272 END OZF_OFFR_ELIG_PROD_DENORM_PVT;