1 PACKAGE CS_GET_CONTRACTS_PUB AUTHID CURRENT_USER AS
2 /* $Header: csctglcs.pls 115.5 99/08/13 16:30:46 porting ship $ */
3
4 /****************************************************************************
5 -- GLOBAL VARIABLES
6 ****************************************************************************/
7
8 G_PKG_NAME CONSTANT VARCHAR2(200) := 'CS_GET_CONTRACTS_PUB';
9 G_APP_NAME CONSTANT VARCHAR2(3) := 'CS';
10 G_API_VERSION CONSTANT NUMBER := 1;
11
12 /****************************************************************************
13 -- DATA STRUCTURES
14 *****************************************************************************/
15
16 TYPE Contracts_Rec_Type IS RECORD (
17 V_Contract_Id CS_CONTRACTS.CONTRACT_ID%TYPE,
18 V_Contract_Number CS_CONTRACTS.CONTRACT_NUMBER%TYPE,
19 V_Contract_Status CS_CONTRACT_STATUSES.NAME%TYPE,
20 V_Contract_Start_Date CS_CONTRACTS.START_DATE_ACTIVE%TYPE,
21 V_Contract_End_Date CS_CONTRACTS.END_DATE_ACTIVE%TYPE,
22 V_Contract_Duration CS_CONTRACTS.DURATION%TYPE,
23 V_Currency_Code CS_CONTRACTS.CURRENCY_CODE%TYPE,
24 V_Bill_On CS_CONTRACTS.BILL_ON%TYPE,
25 V_First_Bill_Date CS_CONTRACTS.FIRST_BILL_DATE%TYPE,
26 V_Next_Bill_Date CS_CONTRACTS.NEXT_BILL_DATE%TYPE,
27 V_Workflow_Process_Id CS_CONTRACTS.WORKFLOW_PROCESS_ID%TYPE,
28 V_Renewal_Rule CS_CONTRACTS.RENEWAL_RULE%TYPE,
29 V_Termination_Rule CS_CONTRACTS.TERMINATION_RULE%TYPE,
30 V_Contract_Amount CS_CONTRACTS.CONTRACT_AMOUNT%TYPE,
31 V_Service_Id CS_CP_SERVICES.CP_SERVICE_ID%TYPE,
32 V_Service
33 MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE,
34 V_Service_Start_Date CS_CP_SERVICES_ALL.START_DATE_ACTIVE%TYPE,
35 V_Service_End_Date CS_CP_SERVICES_ALL.END_DATE_ACTIVE%TYPE,
36 V_Coverage_Id CS_COVERAGES.COVERAGE_ID%TYPE,
37 V_Coverage CS_COVERAGES.NAME%TYPE);
38 G_Contract_rec_type contracts_rec_type;
39
40 TYPE Contracts_Tab_Type IS TABLE OF Contracts_Rec_Type INDEX BY BINARY_INTEGER;
41 G_Contracts_Tab_Type Contracts_Tab_Type;
42
43 /*****************************************************************************
44 -- Procedures and Functions
45 *****************************************************************************/
46
47 PROCEDURE Get_List_Of_Contracts (
48 p_api_version IN NUMBER,
49 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
50 p_commit IN VARCHAR2 := FND_API.G_TRUE,
51 p_Customer_Product_Id IN OUT NUMBER,
52 p_Business_Process_Id IN NUMBER,
53 p_charge_date_time IN DATE,
54 p_time_zone_id IN NUMBER,
55 p_exception_coverage_flag IN VARCHAR2,
56 x_contract_rec_type OUT CONTRACTS_REC_TYPE,
57 x_Contract_Id OUT NUMBER,
58 x_Contract_Number OUT NUMBER,
59 x_Contract_Status OUT VARCHAR2,
60 x_Contract_Type OUT VARCHAR2,
61 x_Contract_Group OUT VARCHAR2,
62 x_Contract_Duration OUT NUMBER,
63 x_Contract_Period OUT VARCHAR2,
64 x_Contract_Start_Date OUT DATE,
65 x_Contract_End_Date OUT DATE,
66 x_Contract_Agreement OUT VARCHAR2,
67 x_Contract_Price_List OUT VARCHAR2,
68 x_Currency_Code OUT VARCHAR2,
69 x_Invoicing_Rule OUT VARCHAR2,
70 x_Accounting_Rule OUT VARCHAR2,
71 x_Billing_Frequency_Period OUT VARCHAR2,
72 x_Bill_On OUT NUMBER,
73 x_First_Bill_Date OUT DATE,
74 x_Next_Bill_Date OUT DATE,
75 x_Workflow_Process_Id OUT NUMBER,
76 x_Renewal_Rule OUT VARCHAR2,
77 x_Termination_Rule OUT VARCHAR2,
78 x_Contract_Amount OUT NUMBER,
79 x_discount OUT VARCHAR2,
80 x_Service_Id OUT NUMBER,
81 x_Service OUT VARCHAR2,
82 x_Service_Start_Date OUT DATE,
83 x_Service_End_Date OUT DATE,
84 x_Coverage_Id OUT NUMBER,
85 x_coverage OUT VARCHAR2,
86 p_rec_count IN OUT NUMBER,
87 x_return_status OUT VARCHAR2,
88 x_msg_count OUT NUMBER,
89 x_msg_data OUT VARCHAR2 );
90
91 PROCEDURE Get_List_Of_Contracts (
92 p_api_version IN NUMBER,
93 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
94 p_commit IN VARCHAR2 := FND_API.G_TRUE,
95 p_Coverage_level_value IN NUMBER,
96 p_coverage_level_code IN VARCHAR2,
97 p_Business_Process_Id IN NUMBER,
98 p_charge_date_time IN DATE,
99 p_time_zone_id IN NUMBER,
100 p_exception_coverage_flag IN VARCHAR2,
101 x_contract_rec_type OUT CONTRACTS_REC_TYPE,
102 x_Contract_Id OUT NUMBER,
103 x_Contract_Number OUT NUMBER,
104 x_Contract_Status OUT VARCHAR2,
105 x_Contract_Type OUT VARCHAR2,
106 x_Contract_Group OUT VARCHAR2,
107 x_Contract_Duration OUT NUMBER,
108 x_Contract_Period OUT VARCHAR2,
109 x_Contract_Start_Date OUT DATE,
110 x_Contract_End_Date OUT DATE,
111 x_Contract_Agreement OUT VARCHAR2,
112 x_Contract_Price_List OUT VARCHAR2,
113 x_Currency_Code OUT VARCHAR2,
114 x_Invoicing_Rule OUT VARCHAR2,
115 x_Accounting_Rule OUT VARCHAR2,
116 x_Billing_Frequency_Period OUT VARCHAR2,
117 x_Bill_On OUT NUMBER,
118 x_First_Bill_Date OUT DATE,
119 x_Next_Bill_Date OUT DATE,
120 x_Workflow_Process_Id OUT NUMBER,
121 x_Renewal_Rule OUT VARCHAR2,
122 x_Termination_Rule OUT VARCHAR2,
123 x_Contract_Amount OUT NUMBER,
124 x_discount OUT VARCHAR2,
125 x_Service_Id OUT NUMBER,
126 x_Service OUT VARCHAR2,
127 x_Service_Start_Date OUT DATE,
128 x_Service_End_Date OUT DATE,
129 x_Coverage_Id OUT NUMBER,
130 x_coverage OUT VARCHAR2,
131 p_rec_count IN OUT NUMBER,
132 x_return_status OUT VARCHAR2,
133 x_msg_count OUT NUMBER,
134 x_msg_data OUT VARCHAR2 );
135
136 PROCEDURE Get_List_Of_Contracts (
137 p_api_version IN NUMBER,
138 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
139 p_commit IN VARCHAR2 := FND_API.G_TRUE,
140 p_System_Id IN NUMBER,
141 p_site_use_Id IN NUMBER,
142 p_Business_Process_Id IN NUMBER,
143 p_charge_date_time IN DATE,
144 p_time_zone_id IN NUMBER,
145 p_exception_coverage_flag IN VARCHAR2,
146 x_contract_rec_type OUT CONTRACTS_REC_TYPE,
147 x_Contract_Id OUT NUMBER,
148 x_Contract_Number OUT NUMBER,
149 x_Contract_Status OUT VARCHAR2,
150 x_Contract_Type OUT VARCHAR2,
151 x_Contract_Group OUT VARCHAR2,
152 x_Contract_Duration OUT NUMBER,
153 x_Contract_Period OUT VARCHAR2,
154 x_Contract_Start_Date OUT DATE,
155 x_Contract_End_Date OUT DATE,
156 x_Contract_Agreement OUT VARCHAR2,
157 x_Contract_Price_List OUT VARCHAR2,
158 x_Currency_Code OUT VARCHAR2,
159 x_Invoicing_Rule OUT VARCHAR2,
160 x_Accounting_Rule OUT VARCHAR2,
161 x_Billing_Frequency_Period OUT VARCHAR2,
162 x_Bill_On OUT NUMBER,
163 x_First_Bill_Date OUT DATE,
164 x_Next_Bill_Date OUT DATE,
165 x_Workflow_Process_Id OUT NUMBER,
166 x_Renewal_Rule OUT VARCHAR2,
167 x_Termination_Rule OUT VARCHAR2,
168 x_Contract_Amount OUT NUMBER,
169 x_discount OUT VARCHAR2,
170 x_Service_Id OUT NUMBER,
171 x_Service OUT VARCHAR2,
172 x_Service_Start_Date OUT DATE,
173 x_Service_End_Date OUT DATE,
174 x_Coverage_Id OUT NUMBER,
175 x_coverage OUT VARCHAR2,
176 p_rec_count IN OUT NUMBER,
177 x_return_status OUT VARCHAR2,
178 x_msg_count OUT NUMBER,
179 x_msg_data OUT VARCHAR2 );
180
181 PROCEDURE Get_List_Of_Contracts (
182 p_api_version IN NUMBER,
183 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
184 p_commit IN VARCHAR2 := FND_API.G_TRUE,
185 p_Customer_Product_Id IN OUT NUMBER,
186 p_Customer_Id IN OUT NUMBER,
187 p_site_use_Id IN OUT NUMBER,
188 p_system_Id IN OUT NUMBER,
189 p_inventory_Item_Id IN OUT NUMBER,
190 p_Business_Process_Id IN NUMBER,
191 p_charge_date_time IN DATE,
192 p_time_zone_id IN NUMBER,
193 p_exception_coverage_flag IN VARCHAR2,
194 x_Contract_Id OUT NUMBER,
195 x_Contract_Number OUT NUMBER,
196 x_Contract_Status OUT VARCHAR2,
197 x_Contract_Type OUT VARCHAR2,
198 x_Contract_Group OUT VARCHAR2,
199 x_Contract_Duration OUT NUMBER,
200 x_Contract_Period OUT VARCHAR2,
201 x_Contract_Start_Date OUT DATE,
202 x_Contract_End_Date OUT DATE,
203 x_Contract_Agreement OUT VARCHAR2,
204 x_Contract_Price_List OUT VARCHAR2,
205 x_Currency_Code OUT VARCHAR2,
206 x_Invoicing_Rule OUT VARCHAR2,
207 x_Accounting_Rule OUT VARCHAR2,
208 x_Billing_Frequency_Period OUT VARCHAR2,
209 x_Bill_On OUT NUMBER,
210 x_First_Bill_Date OUT DATE,
211 x_Next_Bill_Date OUT DATE,
212 x_Workflow_Process_Id OUT NUMBER,
213 x_Renewal_Rule OUT VARCHAR2,
214 x_Termination_Rule OUT VARCHAR2,
215 x_Contract_Amount OUT NUMBER,
216 x_discount OUT VARCHAR2,
217 x_Service_Id OUT NUMBER,
218 x_Service OUT VARCHAR2,
219 x_Service_Start_Date OUT DATE,
220 x_Service_End_Date OUT DATE,
221 x_Coverage_Id OUT NUMBER,
222 x_coverage OUT VARCHAR2,
223 p_rec_count IN OUT NUMBER,
224 x_return_status OUT VARCHAR2,
225 x_msg_count OUT NUMBER,
226 x_msg_data OUT VARCHAR2 );
227
228 Procedure migrate_to_table(
229 p_api_version IN NUMBER,
230 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
231 p_commit IN VARCHAR2 := FND_API.G_TRUE,
232 p_contract_rec_type IN CONTRACTS_REC_TYPE,
233 p_rec_index IN NUMBER,
234 x_return_status OUT VARCHAR2,
235 x_msg_count OUT NUMBER,
236 x_msg_data OUT VARCHAR2 );
237
238 Procedure count_and_get(
239 p_api_version IN NUMBER,
240 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
241 p_commit IN VARCHAR2 := FND_API.G_TRUE,
242 p_rec_index IN NUMBER,
243 x_Contract_Id OUT NUMBER,
244 x_Contract_Number OUT NUMBER,
245 x_Contract_Status OUT VARCHAR2,
246 x_Contract_Type OUT VARCHAR2,
247 x_Contract_Group OUT VARCHAR2,
248 x_Contract_Duration OUT NUMBER,
249 x_Contract_Period OUT VARCHAR2,
250 x_Contract_Start_Date OUT DATE,
251 x_Contract_End_Date OUT DATE,
252 x_Contract_Agreement OUT VARCHAR2,
253 x_Contract_Price_List OUT VARCHAR2,
254 x_Currency_Code OUT VARCHAR2,
255 x_Invoicing_Rule OUT VARCHAR2,
256 x_Accounting_Rule OUT VARCHAR2,
257 x_Billing_Frequency_Period OUT VARCHAR2,
258 x_Bill_On OUT NUMBER ,
259 x_First_Bill_Date OUT DATE,
260 x_Next_Bill_Date OUT DATE,
261 x_Workflow_Process_Id OUT NUMBER,
262 x_Renewal_Rule OUT VARCHAR2,
263 x_Termination_Rule OUT VARCHAR2,
264 x_Contract_Amount OUT NUMBER,
265 x_discount OUT VARCHAR2,
266 x_Service_Id OUT NUMBER,
267 x_Service OUT VARCHAR2,
268 x_Service_Start_Date OUT DATE,
269 x_Service_End_Date OUT DATE,
270 x_Coverage_Id OUT NUMBER,
271 x_coverage OUT VARCHAR2,
272 p_rec_count IN OUT NUMBER,
273 x_return_status OUT VARCHAR2,
274 x_msg_count OUT NUMBER,
275 x_msg_data OUT VARCHAR2 );
276
277
278 Procedure Migrate_to_Out_Variables(
279 p_api_version IN NUMBER,
280 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
281 p_commit IN VARCHAR2 := FND_API.G_TRUE,
282 x_contract_rec_type IN CONTRACTS_REC_TYPE,
283 x_Contract_Id OUT NUMBER,
284 x_Contract_Number OUT NUMBER,
285 x_Contract_Status OUT VARCHAR2,
286 x_Contract_Type OUT VARCHAR2,
287 x_Contract_Group OUT VARCHAR2,
288 x_Contract_Duration OUT NUMBER,
289 x_Contract_Period OUT VARCHAR2,
290 x_Contract_Start_Date OUT DATE,
291 x_Contract_End_Date OUT DATE,
292 x_Contract_Agreement OUT VARCHAR2,
293 x_Contract_Price_List OUT VARCHAR2,
294 x_Currency_Code OUT VARCHAR2,
295 x_Invoicing_Rule OUT VARCHAR2,
296 x_Accounting_Rule OUT VARCHAR2,
297 x_Billing_Frequency_Period OUT VARCHAR2,
298 x_Bill_On OUT NUMBER ,
299 x_First_Bill_Date OUT DATE,
300 x_Next_Bill_Date OUT DATE,
301 x_Workflow_Process_Id OUT NUMBER,
302 x_Renewal_Rule OUT VARCHAR2,
303 x_Termination_Rule OUT VARCHAR2,
304 x_Contract_Amount OUT NUMBER,
305 x_discount OUT VARCHAR2,
306 x_Service_Id OUT NUMBER,
307 x_Service OUT VARCHAR2,
308 x_Service_Start_Date OUT DATE,
309 x_Service_End_Date OUT DATE,
310 x_Coverage_Id OUT NUMBER,
311 x_coverage OUT VARCHAR2,
312 x_return_status OUT VARCHAR2,
313 x_msg_count OUT NUMBER,
314 x_msg_data OUT VARCHAR2 );
315
316 END CS_GET_CONTRACTS_PUB;
[Home] [Help]