[Home] [Help]
PACKAGE: APPS.OKL_SIF_PVT
Source
1 PACKAGE OKL_SIF_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKLSSIFS.pls 115.5 2002/12/18 01:20:37 smahapat noship $ */
3 ---------------------------------------------------------------------------
4 -- GLOBAL DATASTRUCTURES
5 ---------------------------------------------------------------------------
6 TYPE sif_rec_type IS RECORD (
7 id NUMBER := OKC_API.G_MISS_NUM,
8 object_version_number NUMBER := OKC_API.G_MISS_NUM,
9 fasb_acct_treatment_method OKL_STREAM_INTERFACES.FASB_ACCT_TREATMENT_METHOD%TYPE := OKC_API.G_MISS_CHAR,
10 date_payments_commencement OKL_STREAM_INTERFACES.DATE_PAYMENTS_COMMENCEMENT%TYPE := OKC_API.G_MISS_DATE,
11 country OKL_STREAM_INTERFACES.COUNTRY%TYPE := OKC_API.G_MISS_CHAR,
12 security_deposit_amount NUMBER := OKC_API.G_MISS_NUM,
13 date_delivery OKL_STREAM_INTERFACES.DATE_DELIVERY%TYPE := OKC_API.G_MISS_DATE,
14 irs_tax_treatment_method OKL_STREAM_INTERFACES.IRS_TAX_TREATMENT_METHOD%TYPE := OKC_API.G_MISS_CHAR,
15 sif_mode OKL_STREAM_INTERFACES.SIF_MODE%TYPE := OKC_API.G_MISS_CHAR,
16 pricing_template_name OKL_STREAM_INTERFACES.PRICING_TEMPLATE_NAME%TYPE := OKC_API.G_MISS_CHAR,
17 date_sec_deposit_collected OKL_STREAM_INTERFACES.DATE_SEC_DEPOSIT_COLLECTED%TYPE := OKC_API.G_MISS_DATE,
18 transaction_number NUMBER := OKC_API.G_MISS_NUM,
19 total_funding NUMBER := OKC_API.G_MISS_NUM,
20 sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE := OKC_API.G_MISS_CHAR,
21 khr_id NUMBER := OKC_API.G_MISS_NUM,
22 adjust OKL_STREAM_INTERFACES.ADJUST%TYPE := OKC_API.G_MISS_CHAR,
23 implicit_interest_rate NUMBER := OKC_API.G_MISS_NUM,
24 adjustment_method OKL_STREAM_INTERFACES.ADJUSTMENT_METHOD%TYPE := OKC_API.G_MISS_CHAR,
25 date_processed OKL_STREAM_INTERFACES.DATE_PROCESSED%TYPE := OKC_API.G_MISS_DATE,
26 orp_code OKL_STREAM_INTERFACES.ORP_CODE%TYPE := OKC_API.G_MISS_CHAR,
27 lending_rate NUMBER := OKC_API.G_MISS_NUM,
28 rvi_yn OKL_STREAM_INTERFACES.RVI_YN%TYPE := OKC_API.G_MISS_CHAR,
29 rvi_rate NUMBER := OKC_API.G_MISS_NUM,
30 stream_interface_attribute01 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE01%TYPE := OKC_API.G_MISS_CHAR,
31 stream_interface_attribute02 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE02%TYPE := OKC_API.G_MISS_CHAR,
32 stream_interface_attribute03 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE03%TYPE := OKC_API.G_MISS_CHAR,
33 stream_interface_attribute04 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE04%TYPE := OKC_API.G_MISS_CHAR,
34 stream_interface_attribute05 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE05%TYPE := OKC_API.G_MISS_CHAR,
35 stream_interface_attribute06 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE06%TYPE := OKC_API.G_MISS_CHAR,
36 stream_interface_attribute07 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE07%TYPE := OKC_API.G_MISS_CHAR,
37 stream_interface_attribute08 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE08%TYPE := OKC_API.G_MISS_CHAR,
38 stream_interface_attribute09 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE09%TYPE := OKC_API.G_MISS_CHAR,
39 stream_interface_attribute10 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE10%TYPE := OKC_API.G_MISS_CHAR,
40 stream_interface_attribute11 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE11%TYPE := OKC_API.G_MISS_CHAR,
41 stream_interface_attribute12 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE12%TYPE := OKC_API.G_MISS_CHAR,
42 stream_interface_attribute13 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE13%TYPE := OKC_API.G_MISS_CHAR,
43 stream_interface_attribute14 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE14%TYPE := OKC_API.G_MISS_CHAR,
44 stream_interface_attribute15 OKL_STREAM_INTERFACES.STREAM_INTERFACE_ATTRIBUTE15%TYPE := OKC_API.G_MISS_CHAR,
45 created_by NUMBER := OKC_API.G_MISS_NUM,
46 last_updated_by NUMBER := OKC_API.G_MISS_NUM,
47 creation_date OKL_STREAM_INTERFACES.CREATION_DATE%TYPE := OKC_API.G_MISS_DATE,
48 last_update_date OKL_STREAM_INTERFACES.LAST_UPDATE_DATE%TYPE := OKC_API.G_MISS_DATE,
49 last_update_login NUMBER := OKC_API.G_MISS_NUM,
50 -- mvasudev -- 02/21/2002
51 -- new columns added for concurrent program manager
52 REQUEST_ID NUMBER := OKC_API.G_MISS_NUM,
53 PROGRAM_APPLICATION_ID NUMBER := OKC_API.G_MISS_NUM,
54 PROGRAM_ID NUMBER := OKC_API.G_MISS_NUM,
55 PROGRAM_UPDATE_DATE OKL_STREAM_INTERFACES.PROGRAM_UPDATE_DATE%TYPE := OKC_API.G_MISS_DATE,
56 -- mvasudev -- 05/13/2002
57 JTOT_OBJECT1_CODE OKL_STREAM_INTERFACES.JTOT_OBJECT1_CODE%TYPE := OKC_API.G_MISS_CHAR,
58 OBJECT1_ID1 OKL_STREAM_INTERFACES.OBJECT1_ID1%TYPE := OKC_API.G_MISS_CHAR,
59 OBJECT1_ID2 OKL_STREAM_INTERFACES.OBJECT1_ID2%TYPE := OKC_API.G_MISS_CHAR,
60 TERM NUMBER := OKC_API.G_MISS_NUM,
61 STRUCTURE OKL_STREAM_INTERFACES.STRUCTURE%TYPE := OKC_API.G_MISS_CHAR,
62 DEAL_TYPE OKL_STREAM_INTERFACES.DEAL_TYPE%TYPE := OKC_API.G_MISS_CHAR,
63 LOG_FILE OKL_STREAM_INTERFACES.LOG_FILE%TYPE := OKC_API.G_MISS_CHAR,
64 FIRST_PAYMENT OKL_STREAM_INTERFACES.FIRST_PAYMENT%TYPE := OKC_API.G_MISS_CHAR,
65 LAST_PAYMENT OKL_STREAM_INTERFACES.LAST_PAYMENT%TYPE := OKC_API.G_MISS_CHAR,
66 -- mvasudev, Bug#2650599
67 sif_id NUMBER := OKC_API.G_MISS_NUM,
68 purpose_code OKL_STREAM_INTERFACES.PURPOSE_CODE%TYPE := OKC_API.G_MISS_CHAR
69 -- end, mvasudev, Bug#2650599
70 );
71 g_miss_sif_rec sif_rec_type;
72 TYPE sif_tbl_type IS TABLE OF sif_rec_type
73 INDEX BY BINARY_INTEGER;
74 TYPE sifv_rec_type IS RECORD (
75 id NUMBER := OKC_API.G_MISS_NUM,
76 object_version_number NUMBER := OKC_API.G_MISS_NUM,
77 fasb_acct_treatment_method OKL_STREAM_INTERFACES_V.FASB_ACCT_TREATMENT_METHOD%TYPE := OKC_API.G_MISS_CHAR,
78 date_payments_commencement OKL_STREAM_INTERFACES_V.DATE_PAYMENTS_COMMENCEMENT%TYPE := OKC_API.G_MISS_DATE,
79 country OKL_STREAM_INTERFACES_V.COUNTRY%TYPE := OKC_API.G_MISS_CHAR,
80 security_deposit_amount NUMBER := OKC_API.G_MISS_NUM,
81 date_delivery OKL_STREAM_INTERFACES_V.DATE_DELIVERY%TYPE := OKC_API.G_MISS_DATE,
82 irs_tax_treatment_method OKL_STREAM_INTERFACES_V.IRS_TAX_TREATMENT_METHOD%TYPE := OKC_API.G_MISS_CHAR,
83 sif_mode OKL_STREAM_INTERFACES_V.SIF_MODE%TYPE := OKC_API.G_MISS_CHAR,
84 pricing_template_name OKL_STREAM_INTERFACES_V.PRICING_TEMPLATE_NAME%TYPE := OKC_API.G_MISS_CHAR,
85 date_sec_deposit_collected OKL_STREAM_INTERFACES_V.DATE_SEC_DEPOSIT_COLLECTED%TYPE := OKC_API.G_MISS_DATE,
86 transaction_number NUMBER := OKC_API.G_MISS_NUM,
87 total_funding NUMBER := OKC_API.G_MISS_NUM,
88 sis_code OKL_STREAM_INTERFACES_V.SIS_CODE%TYPE := OKC_API.G_MISS_CHAR,
89 khr_id NUMBER := OKC_API.G_MISS_NUM,
90 adjust OKL_STREAM_INTERFACES_V.ADJUST%TYPE := OKC_API.G_MISS_CHAR,
91 implicit_interest_rate NUMBER := OKC_API.G_MISS_NUM,
92 adjustment_method OKL_STREAM_INTERFACES_V.ADJUSTMENT_METHOD%TYPE := OKC_API.G_MISS_CHAR,
93 date_processed OKL_STREAM_INTERFACES_V.DATE_PROCESSED%TYPE := OKC_API.G_MISS_DATE,
94 orp_code OKL_STREAM_INTERFACES_V.ORP_CODE%TYPE := OKC_API.G_MISS_CHAR,
95 lending_rate NUMBER := OKC_API.G_MISS_NUM,
96 rvi_yn OKL_STREAM_INTERFACES_V.RVI_YN%TYPE := OKC_API.G_MISS_CHAR,
97 rvi_rate NUMBER := OKC_API.G_MISS_NUM,
98 stream_interface_attribute01 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE01%TYPE := OKC_API.G_MISS_CHAR,
99 stream_interface_attribute02 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE02%TYPE := OKC_API.G_MISS_CHAR,
100 stream_interface_attribute03 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE03%TYPE := OKC_API.G_MISS_CHAR,
101 stream_interface_attribute04 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE04%TYPE := OKC_API.G_MISS_CHAR,
102 stream_interface_attribute05 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE05%TYPE := OKC_API.G_MISS_CHAR,
103 stream_interface_attribute06 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE06%TYPE := OKC_API.G_MISS_CHAR,
104 stream_interface_attribute07 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE07%TYPE := OKC_API.G_MISS_CHAR,
105 stream_interface_attribute08 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE08%TYPE := OKC_API.G_MISS_CHAR,
106 stream_interface_attribute09 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE09%TYPE := OKC_API.G_MISS_CHAR,
107 stream_interface_attribute10 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE10%TYPE := OKC_API.G_MISS_CHAR,
108 stream_interface_attribute11 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE11%TYPE := OKC_API.G_MISS_CHAR,
109 stream_interface_attribute12 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE12%TYPE := OKC_API.G_MISS_CHAR,
110 stream_interface_attribute13 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE13%TYPE := OKC_API.G_MISS_CHAR,
111 stream_interface_attribute14 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE14%TYPE := OKC_API.G_MISS_CHAR,
112 stream_interface_attribute15 OKL_STREAM_INTERFACES_V.STREAM_INTERFACE_ATTRIBUTE15%TYPE := OKC_API.G_MISS_CHAR,
113 created_by NUMBER := OKC_API.G_MISS_NUM,
114 last_updated_by NUMBER := OKC_API.G_MISS_NUM,
115 creation_date OKL_STREAM_INTERFACES_V.CREATION_DATE%TYPE := OKC_API.G_MISS_DATE,
116 last_update_date OKL_STREAM_INTERFACES_V.LAST_UPDATE_DATE%TYPE := OKC_API.G_MISS_DATE,
117 last_update_login NUMBER := OKC_API.G_MISS_NUM,
118 -- mvasudev -- 02/21/2002
119 -- new columns added for concurrent program manager
120 REQUEST_ID NUMBER := OKC_API.G_MISS_NUM,
121 PROGRAM_APPLICATION_ID NUMBER := OKC_API.G_MISS_NUM,
122 PROGRAM_ID NUMBER := OKC_API.G_MISS_NUM,
123 PROGRAM_UPDATE_DATE OKL_STREAM_INTERFACES_V.PROGRAM_UPDATE_DATE%TYPE := OKC_API.G_MISS_DATE,
124 -- mvasudev -- 05/13/2002
125 JTOT_OBJECT1_CODE OKL_STREAM_INTERFACES_V.JTOT_OBJECT1_CODE%TYPE := OKC_API.G_MISS_CHAR,
126 OBJECT1_ID1 OKL_STREAM_INTERFACES_V.OBJECT1_ID1%TYPE := OKC_API.G_MISS_CHAR,
127 OBJECT1_ID2 OKL_STREAM_INTERFACES_V.OBJECT1_ID2%TYPE := OKC_API.G_MISS_CHAR,
128 TERM NUMBER := OKC_API.G_MISS_NUM,
129 STRUCTURE OKL_STREAM_INTERFACES_V.STRUCTURE%TYPE := OKC_API.G_MISS_CHAR,
130 DEAL_TYPE OKL_STREAM_INTERFACES_V.DEAL_TYPE%TYPE := OKC_API.G_MISS_CHAR,
131 LOG_FILE OKL_STREAM_INTERFACES_V.LOG_FILE%TYPE := OKC_API.G_MISS_CHAR,
132 FIRST_PAYMENT OKL_STREAM_INTERFACES_V.FIRST_PAYMENT%TYPE := OKC_API.G_MISS_CHAR,
133 LAST_PAYMENT OKL_STREAM_INTERFACES_V.LAST_PAYMENT%TYPE := OKC_API.G_MISS_CHAR,
134 -- mvasudev, Bug#2650599
135 sif_id NUMBER := OKC_API.G_MISS_NUM,
136 purpose_code OKL_STREAM_INTERFACES_V.PURPOSE_CODE%TYPE := OKC_API.G_MISS_CHAR
137 -- end, mvasudev, Bug#2650599
138 );
139 g_miss_sifv_rec sifv_rec_type;
140 TYPE sifv_tbl_type IS TABLE OF sifv_rec_type
141 INDEX BY BINARY_INTEGER;
142 ---------------------------------------------------------------------------
143 -- GLOBAL MESSAGE CONSTANTS
144 ---------------------------------------------------------------------------
145 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
146 G_OKC_APP CONSTANT VARCHAR2(200) := OKC_API.G_APP_NAME;
147 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
148 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_DELETED;
149 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_CHANGED;
150 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
151 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
152 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
153 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
154 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
155 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
156
157 -- START CHANGE : akjain -- 08/15/2001
158 -- Adding MESSAGE CONSTANTs for 'Unique Key Validation','OKL_SQLCODE', 'OKL_SQLERRM','Unexpected Error'
159 G_OKL_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLERRM';
160 G_OKL_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'OKL_SQLCODE';
161 G_OKL_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXPECTED_ERROR';
162
163 -- Added Exception for Halt_validation
164 --------------------------------------------------------------------------------
165 -- ERRORS AND EXCEPTIONS
166 --------------------------------------------------------------------------------
167 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
168 -- END change : akjain
169
170 ---------------------------------------------------------------------------
171 -- GLOBAL VARIABLES
172 ---------------------------------------------------------------------------
173 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_SIF_PVT';
174 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
175 ---------------------------------------------------------------------------
176 -- Procedures and Functions
177 ---------------------------------------------------------------------------
178
179 PROCEDURE qc;
180 PROCEDURE change_version;
181 PROCEDURE api_copy;
182 PROCEDURE insert_row(
183 p_api_version IN NUMBER,
184 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
185 x_return_status OUT NOCOPY VARCHAR2,
186 x_msg_count OUT NOCOPY NUMBER,
187 x_msg_data OUT NOCOPY VARCHAR2,
188 p_sifv_rec IN sifv_rec_type,
189 x_sifv_rec OUT NOCOPY sifv_rec_type);
190
191 PROCEDURE insert_row(
192 p_api_version IN NUMBER,
193 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
194 x_return_status OUT NOCOPY VARCHAR2,
195 x_msg_count OUT NOCOPY NUMBER,
196 x_msg_data OUT NOCOPY VARCHAR2,
197 p_sifv_tbl IN sifv_tbl_type,
198 x_sifv_tbl OUT NOCOPY sifv_tbl_type);
199
200 PROCEDURE lock_row(
201 p_api_version IN NUMBER,
202 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2,
206 p_sifv_rec IN sifv_rec_type);
207
208 PROCEDURE lock_row(
209 p_api_version IN NUMBER,
210 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
211 x_return_status OUT NOCOPY VARCHAR2,
212 x_msg_count OUT NOCOPY NUMBER,
213 x_msg_data OUT NOCOPY VARCHAR2,
214 p_sifv_tbl IN sifv_tbl_type);
215
216 PROCEDURE update_row(
217 p_api_version IN NUMBER,
218 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2,
222 p_sifv_rec IN sifv_rec_type,
223 x_sifv_rec OUT NOCOPY sifv_rec_type);
224
225 PROCEDURE update_row(
226 p_api_version IN NUMBER,
227 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
228 x_return_status OUT NOCOPY VARCHAR2,
229 x_msg_count OUT NOCOPY NUMBER,
230 x_msg_data OUT NOCOPY VARCHAR2,
231 p_sifv_tbl IN sifv_tbl_type,
232 x_sifv_tbl OUT NOCOPY sifv_tbl_type);
233
234 PROCEDURE delete_row(
235 p_api_version IN NUMBER,
236 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
237 x_return_status OUT NOCOPY VARCHAR2,
238 x_msg_count OUT NOCOPY NUMBER,
239 x_msg_data OUT NOCOPY VARCHAR2,
240 p_sifv_rec IN sifv_rec_type);
241
242 PROCEDURE delete_row(
243 p_api_version IN NUMBER,
244 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2,
248 p_sifv_tbl IN sifv_tbl_type);
249
250 PROCEDURE validate_row(
251 p_api_version IN NUMBER,
252 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
253 x_return_status OUT NOCOPY VARCHAR2,
254 x_msg_count OUT NOCOPY NUMBER,
255 x_msg_data OUT NOCOPY VARCHAR2,
256 p_sifv_rec IN sifv_rec_type);
257
258 PROCEDURE validate_row(
259 p_api_version IN NUMBER,
260 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2,
264 p_sifv_tbl IN sifv_tbl_type);
265
266 END OKL_SIF_PVT;