1 PACKAGE CS_CONTRACTS_PUB AUTHID CURRENT_USER AS
2 /* $Header: csctpaps.pls 115.3 99/07/16 08:53:14 porting ship $ */
3 --------------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 --------------------------------------------------------------------------------
6 SUBTYPE contract_rec_type IS CS_CONTRACTS_ALL%ROWTYPE;
7 TYPE contract_tbl_type IS TABLE OF contract_rec_type
8 INDEX BY BINARY_INTEGER;
9 SUBTYPE contracts_billing_rec_type IS CS_CONTRACTS_BILLING%ROWTYPE;
10 TYPE contracts_billing_tbl_type IS TABLE OF contracts_billing_rec_type
11 INDEX BY BINARY_INTEGER;
12 SUBTYPE services_all_rec_type IS CS_CP_SERVICES_ALL%ROWTYPE;
13 TYPE services_all_tbl_type IS TABLE OF services_all_rec_type
14 INDEX BY BINARY_INTEGER;
15 SUBTYPE contract_cov_lvls_rec_type IS CS_CONTRACT_COV_LEVELS%ROWTYPE;
16 TYPE contract_cov_lvls_tbl_type IS TABLE OF contract_cov_lvls_rec_type
17 INDEX BY BINARY_INTEGER;
18 SUBTYPE covered_Products_rec_type IS CS_COVERED_PRODUCTS%ROWTYPE;
19 TYPE covered_products_tbl_type IS TABLE OF covered_products_rec_type
20 INDEX BY BINARY_INTEGER;
21 SUBTYPE service_txns_rec_type IS CS_CP_SERVICE_TRANSACTIONS%ROWTYPE;
22 TYPE service_txns_tbl_type IS TABLE OF service_txns_rec_type
23 INDEX BY BINARY_INTEGER;
24 SUBTYPE contract_Line_Templts_rec_type IS CS_CONTRACT_LINE_TPLTS%ROWTYPE;
25 TYPE contract_line_templts_tbl_type IS TABLE OF contract_line_templts_rec_type
26 INDEX BY BINARY_INTEGER;
27 SUBTYPE coverages_rec_type IS CS_COVERAGES%ROWTYPE;
28 TYPE coverages_tbl_type IS TABLE OF coverages_rec_type
29 INDEX BY BINARY_INTEGER;
30 -- COMMENTED OUT 30-SEP-98 JSU
31 -- SUBTYPE coverages_Used_rec_type IS CS_COVERAGES_USED%ROWTYPE;
32 -- TYPE coverages_used_tbl_type IS TABLE OF coverages_used_rec_type
33 -- INDEX BY BINARY_INTEGER;
34 SUBTYPE coverage_txn_groups_rec_type IS CS_COVERAGE_TXN_GROUPS%ROWTYPE;
35 TYPE coverage_txn_groups_tbl_type IS TABLE OF coverage_txn_groups_rec_type
36 INDEX BY BINARY_INTEGER;
37 SUBTYPE cov_reactn_times_rec_type IS CS_COV_REACTION_TIMES%ROWTYPE;
38 TYPE cov_reactn_times_tbl_type IS TABLE OF cov_reactn_times_rec_type
39 INDEX BY BINARY_INTEGER;
40 SUBTYPE cov_bill_types_rec_type IS CS_COV_BILLING_TYPES%ROWTYPE;
41 TYPE cov_bill_types_tbl_type IS TABLE OF cov_bill_types_rec_type
42 INDEX BY BINARY_INTEGER;
43 SUBTYPE cov_bill_rates_rec_type IS CS_COV_BILL_RATES%ROWTYPE;
44 TYPE cov_bill_rates_tbl_type IS TABLE OF cov_bill_rates_rec_type
45 INDEX BY BINARY_INTEGER;
46 -- COMMENTED OUT 14-OCT-98 SKARUPPA
47 ---SUBTYPE coverage_events_rec_type IS CS_COVERAGE_EVENTS%ROWTYPE;
48 ---TYPE coverage_events_tbl_type IS TABLE OF coverage_events_rec_type
49 ---INDEX BY BINARY_INTEGER;
50 -- COMMENTED OUT 30-SEP-98 JSU
51 -- SUBTYPE cov_txn_grp_count_rec_type IS CS_COVERAGE_TXN_GRP_CTRS%ROWTYPE;
52 -- TYPE cov_txn_grp_count_tbl_type IS TABLE OF cov_txn_grp_count_rec_type
53 -- INDEX BY BINARY_INTEGER;
54 --------------------------------------------------------------------------------
55 -- GLOBAL MESSAGE CONSTANTS
56 --------------------------------------------------------------------------------
57 G_FND_APP CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_FND_APP;
58 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_FORM_UNABLE_TO_RESERVE_REC;
59 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_FORM_RECORD_DELETED;
60 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_FORM_RECORD_CHANGED;
61 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_RECORD_LOGICALLY_DELETED;
62 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_REQUIRED_VALUE;
63 G_INVALID_VALUE CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_INVALID_VALUE;
64 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_COL_NAME_TOKEN;
65 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_PARENT_TABLE_TOKEN;
66 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := TAPI_DEV_KIT.G_CHILD_TABLE_TOKEN;
67 --------------------------------------------------------------------------------
68 -- GLOBAL VARIABLES
69 --------------------------------------------------------------------------------
70 G_PKG_NAME CONSTANT VARCHAR2(200) := 'CS_CONTRACTS_PUB';
71 G_APP_NAME CONSTANT VARCHAR2(3) := TAPI_DEV_KIT.G_APP_NAME;
72 --------------------------------------------------------------------------------
73 -- Procedures and Functions
74 --------------------------------------------------------------------------------
75 PROCEDURE validate_contract
76 (
77 p_api_version IN NUMBER,
78 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
79 p_validation_level IN NUMBER,
80 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
81 x_return_status OUT VARCHAR2,
82 x_msg_count OUT NUMBER,
83 x_msg_data OUT VARCHAR2,
84 p_contract_rec IN contract_rec_type
85 );
86 PROCEDURE validate_contract
87 (
88 p_api_version IN NUMBER,
89 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
90 p_validation_level IN NUMBER,
91 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
92 x_return_status OUT VARCHAR2,
93 x_msg_count OUT NUMBER,
94 x_msg_data OUT VARCHAR2,
95 p_contract_id IN CS_CONTRACTS.CONTRACT_ID%TYPE
96 /*, -- COMMENTED OUT 17-SEP-98 DEVELOPER/2000 FORMS uses PL/SQL 1.6 which cannot
97 -- handle selective parameter passing
98 p_contract_number IN CS_CONTRACTS.CONTRACT_NUMBER%TYPE := NULL,
99 p_workflow IN CS_CONTRACTS.WORKFLOW%TYPE := NULL,
100 p_agreement_id IN CS_CONTRACTS.AGREEMENT_ID%TYPE := NULL,
101 p_price_list_id IN CS_CONTRACTS.PRICE_LIST_ID%TYPE := NULL,
102 p_currency_code IN CS_CONTRACTS.CURRENCY_CODE%TYPE := NULL,
103 p_conversion_type_code IN CS_CONTRACTS.CONVERSION_TYPE_CODE%TYPE := NULL,
104 p_conversion_rate IN CS_CONTRACTS.CONVERSION_RATE%TYPE := NULL,
105 p_conversion_date IN CS_CONTRACTS.CONVERSION_DATE%TYPE := NULL,
106 p_invoicing_rule_id IN CS_CONTRACTS.INVOICING_RULE_ID%TYPE := NULL,
107 p_accounting_rule_id IN CS_CONTRACTS.ACCOUNTING_RULE_ID%TYPE := NULL,
108 p_billing_frequency_period IN CS_CONTRACTS.BILLING_FREQUENCY_PERIOD%TYPE := NULL,
109 p_first_bill_date IN CS_CONTRACTS.FIRST_BILL_DATE%TYPE := NULL,
110 p_next_bill_date IN CS_CONTRACTS.NEXT_BILL_DATE%TYPE := NULL,
111 p_create_sales_order IN CS_CONTRACTS.CREATE_SALES_ORDER%TYPE := NULL,
112 p_renewal_rule IN CS_CONTRACTS.RENEWAL_RULE%TYPE := NULL,
113 p_termination_rule IN CS_CONTRACTS.TERMINATION_RULE%TYPE := NULL,
114 p_bill_to_site_use_id IN CS_CONTRACTS.BILL_TO_SITE_USE_ID%TYPE := NULL,
115 p_contract_status_id IN CS_CONTRACTS.CONTRACT_STATUS_ID%TYPE := NULL,
116 p_contract_type_id IN CS_CONTRACTS.CONTRACT_TYPE_ID%TYPE := NULL,
117 p_contract_template_id IN CS_CONTRACTS.CONTRACT_TEMPLATE_ID%TYPE := NULL,
118 p_contract_group_id IN CS_CONTRACTS.CONTRACT_GROUP_ID%TYPE := NULL,
119 p_customer_id IN CS_CONTRACTS.CUSTOMER_ID%TYPE := NULL,
120 p_duration IN CS_CONTRACTS.DURATION%TYPE := NULL,
121 p_period_code IN CS_CONTRACTS.PERIOD_CODE%TYPE := NULL,
122 p_ship_to_site_use_id IN CS_CONTRACTS.SHIP_TO_SITE_USE_ID%TYPE := NULL,
123 p_salesperson_id IN CS_CONTRACTS.SALESPERSON_ID%TYPE := NULL,
124 p_ordered_by_contact_id IN CS_CONTRACTS.ORDERED_BY_CONTACT_ID%TYPE := NULL,
125 p_source_code IN CS_CONTRACTS.SOURCE_CODE%TYPE := NULL,
126 p_source_reference IN CS_CONTRACTS.SOURCE_REFERENCE%TYPE := NULL,
127 p_terms_id IN CS_CONTRACTS.TERMS_ID%TYPE := NULL,
128 p_po_number IN CS_CONTRACTS.PO_NUMBER%TYPE := NULL,
129 p_bill_on IN CS_CONTRACTS.BILL_ON%TYPE := NULL,
130 p_tax_handling IN CS_CONTRACTS.TAX_HANDLING%TYPE := NULL,
131 p_tax_exempt_num IN CS_CONTRACTS.TAX_EXEMPT_NUM%TYPE := NULL,
132 p_tax_exempt_reason_code IN CS_CONTRACTS.TAX_EXEMPT_REASON_CODE%TYPE := NULL,
133 p_contract_amount IN CS_CONTRACTS.CONTRACT_AMOUNT%TYPE := NULL,
134 p_auto_renewal_flag IN CS_CONTRACTS.AUTO_RENEWAL_FLAG%TYPE := NULL,
135 p_original_end_date IN CS_CONTRACTS.ORIGINAL_END_DATE%TYPE := NULL,
136 p_terminate_reason_code IN CS_CONTRACTS.TERMINATE_REASON_CODE%TYPE := NULL,
137 p_discount_id IN CS_CONTRACTS.DISCOUNT_ID%TYPE := NULL,
138 p_po_required_to_service IN CS_CONTRACTS.PO_REQUIRED_TO_SERVICE%TYPE := NULL,
139 p_pre_payment_required IN CS_CONTRACTS.PRE_PAYMENT_REQUIRED%TYPE := NULL,
140 p_last_update_date IN CS_CONTRACTS.LAST_UPDATE_DATE%TYPE := NULL,
141 p_last_updated_by IN CS_CONTRACTS.LAST_UPDATED_BY%TYPE := NULL,
142 p_creation_date IN CS_CONTRACTS.CREATION_DATE%TYPE := NULL,
143 p_created_by IN CS_CONTRACTS.CREATED_BY%TYPE := NULL,
144 p_last_update_login IN CS_CONTRACTS.LAST_UPDATE_LOGIN%TYPE := NULL,
145 p_start_date_active IN CS_CONTRACTS.START_DATE_ACTIVE%TYPE := NULL,
146 p_end_date_active IN CS_CONTRACTS.END_DATE_ACTIVE%TYPE := NULL,
147 p_attribute1 IN CS_CONTRACTS.ATTRIBUTE1%TYPE := NULL,
148 p_attribute2 IN CS_CONTRACTS.ATTRIBUTE2%TYPE := NULL,
149 p_attribute3 IN CS_CONTRACTS.ATTRIBUTE3%TYPE := NULL,
150 p_attribute4 IN CS_CONTRACTS.ATTRIBUTE4%TYPE := NULL,
151 p_attribute5 IN CS_CONTRACTS.ATTRIBUTE5%TYPE := NULL,
152 p_attribute6 IN CS_CONTRACTS.ATTRIBUTE6%TYPE := NULL,
153 p_attribute7 IN CS_CONTRACTS.ATTRIBUTE7%TYPE := NULL,
154 p_attribute8 IN CS_CONTRACTS.ATTRIBUTE8%TYPE := NULL,
155 p_attribute9 IN CS_CONTRACTS.ATTRIBUTE9%TYPE := NULL,
156 p_attribute10 IN CS_CONTRACTS.ATTRIBUTE10%TYPE := NULL,
157 p_attribute11 IN CS_CONTRACTS.ATTRIBUTE11%TYPE := NULL,
158 p_attribute12 IN CS_CONTRACTS.ATTRIBUTE12%TYPE := NULL,
159 p_attribute13 IN CS_CONTRACTS.ATTRIBUTE13%TYPE := NULL,
160 p_attribute14 IN CS_CONTRACTS.ATTRIBUTE14%TYPE := NULL,
161 p_attribute15 IN CS_CONTRACTS.ATTRIBUTE15%TYPE := NULL,
162 p_context IN CS_CONTRACTS.CONTEXT%TYPE := NULL,
163 p_object_version_number IN CS_CONTRACTS.OBJECT_VERSION_NUMBER%TYPE := NULL
164 */
165 );
166
167 Procedure update_contract
168 (
169 p_api_version IN NUMBER,
170 p_init_msg_list IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
171 p_validation_level IN NUMBER,
172 p_commit IN VARCHAR2 := TAPI_DEV_KIT.G_FALSE,
173 x_return_status OUT VARCHAR2,
174 x_msg_count OUT NUMBER,
175 x_msg_data OUT VARCHAR2,
176 p_contract_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
177 p_contract_number IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
178 p_workflow IN CS_CONTRACTS.WORKFLOW%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
179 p_workflow_process_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
180 p_agreement_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
181 p_price_list_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
182 p_currency_code IN CS_CONTRACTS.CURRENCY_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
183 p_conversion_type_code IN CS_CONTRACTS.CONVERSION_TYPE_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
184 p_conversion_rate IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
185 p_conversion_date IN CS_CONTRACTS.CONVERSION_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
186 p_invoicing_rule_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
187 p_accounting_rule_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
188 p_billing_frequency_period IN CS_CONTRACTS.BILLING_FREQUENCY_PERIOD%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
189 p_first_bill_date IN CS_CONTRACTS.FIRST_BILL_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
190 p_next_bill_date IN CS_CONTRACTS.NEXT_BILL_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
191 p_create_sales_order IN CS_CONTRACTS.CREATE_SALES_ORDER%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
192 p_renewal_rule IN CS_CONTRACTS.RENEWAL_RULE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
193 p_termination_rule IN CS_CONTRACTS.TERMINATION_RULE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
194 p_bill_to_site_use_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
195 p_contract_status_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
196 p_contract_type_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
197 p_contract_template_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
198 p_contract_group_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
199 p_customer_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
200 p_duration IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
201 p_period_code IN CS_CONTRACTS.PERIOD_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
202 p_ship_to_site_use_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
203 p_salesperson_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
204 p_ordered_by_contact_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
205 p_source_code IN CS_CONTRACTS.SOURCE_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
206 p_source_reference IN CS_CONTRACTS.SOURCE_REFERENCE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
207 p_terms_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
208 p_po_number IN CS_CONTRACTS.PO_NUMBER%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
209 p_bill_on IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
210 p_tax_handling IN CS_CONTRACTS.TAX_HANDLING%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
211 p_tax_exempt_num IN CS_CONTRACTS.TAX_EXEMPT_NUM%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
212 p_tax_exempt_reason_code IN CS_CONTRACTS.TAX_EXEMPT_REASON_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
213 p_contract_amount IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
214 p_auto_renewal_flag IN CS_CONTRACTS.AUTO_RENEWAL_FLAG%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
215 p_original_end_date IN CS_CONTRACTS.ORIGINAL_END_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
216 p_terminate_reason_code IN CS_CONTRACTS.TERMINATE_REASON_CODE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
217 p_discount_id IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
218 p_po_required_to_service IN CS_CONTRACTS.PO_REQUIRED_TO_SERVICE%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
219 p_pre_payment_required IN CS_CONTRACTS.PRE_PAYMENT_REQUIRED%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
220 p_last_update_date IN CS_CONTRACTS.LAST_UPDATE_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
221 p_last_updated_by IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
222 p_creation_date IN CS_CONTRACTS.CREATION_DATE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
223 p_created_by IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
224 p_last_update_login IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
225 p_start_date_active IN CS_CONTRACTS.START_DATE_ACTIVE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
226 p_end_date_active IN CS_CONTRACTS.END_DATE_ACTIVE%TYPE := TAPI_DEV_KIT.G_MISS_DATE,
227 p_attribute1 IN CS_CONTRACTS.ATTRIBUTE1%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
228 p_attribute2 IN CS_CONTRACTS.ATTRIBUTE2%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
229 p_attribute3 IN CS_CONTRACTS.ATTRIBUTE3%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
230 p_attribute4 IN CS_CONTRACTS.ATTRIBUTE4%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
231 p_attribute5 IN CS_CONTRACTS.ATTRIBUTE5%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
232 p_attribute6 IN CS_CONTRACTS.ATTRIBUTE6%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
233 p_attribute7 IN CS_CONTRACTS.ATTRIBUTE7%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
234 p_attribute8 IN CS_CONTRACTS.ATTRIBUTE8%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
235 p_attribute9 IN CS_CONTRACTS.ATTRIBUTE9%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
236 p_attribute10 IN CS_CONTRACTS.ATTRIBUTE10%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
237 p_attribute11 IN CS_CONTRACTS.ATTRIBUTE11%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
238 p_attribute12 IN CS_CONTRACTS.ATTRIBUTE12%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
239 p_attribute13 IN CS_CONTRACTS.ATTRIBUTE13%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
240 p_attribute14 IN CS_CONTRACTS.ATTRIBUTE14%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
241 p_attribute15 IN CS_CONTRACTS.ATTRIBUTE15%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
242 p_context IN CS_CONTRACTS.CONTEXT%TYPE := TAPI_DEV_KIT.G_MISS_CHAR,
243 p_object_version_number IN NUMBER := TAPI_DEV_KIT.G_MISS_NUM,
244 x_object_version_number OUT NUMBER);
245
246
247 END CS_CONTRACTS_PUB;