[Home] [Help]
PACKAGE: APPS.OKL_QA_SECURITIZATION
Source
1 PACKAGE OKL_QA_SECURITIZATION AUTHID CURRENT_USER AS
2 /* $Header: OKLRSZQS.pls 120.3 2005/09/30 16:55:48 avsingh noship $ */
3
4 ---------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 ---------------------------------------------------------------------------
7 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
8 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
9 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
10 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_DELETED;
11 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_CHANGED;
12 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
13 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
14 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
15 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
16 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
17 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
18 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
19 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
20 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
21 G_UPPERCASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKC_UPPERCASE_REQUIRED';
22 G_INVALID_END_DATE CONSTANT VARCHAR2(200) := 'OKC_INVALID_END_DATE';
23 --
24 G_QA_SUCCESS CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_QA_SUCCESS';
25 G_PARTY_COUNT CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_PARTY_COUNT';
26 G_REQUIRED_RULE CONSTANT VARCHAR2(200) := 'OKC_REQUIRED_RULE';
27 G_REQUIRED_RULE_GROUP CONSTANT VARCHAR2(200) := 'OKC_REQUIRED_RULE_GROUP';
28 G_REQUIRED_RULE_VALUES CONSTANT VARCHAR2(200) := 'OKC_REQUIRED_RULE_VALUES';
29 G_REQUIRED_RULE_PARTY_ROLE CONSTANT VARCHAR2(200) := 'OKC_REQUIRED_RULE_PARTY_ROLE';
30 G_RULE_DEPENDENT_VALUE CONSTANT VARCHAR2(200) := 'OKC_RULE_DEPENDENT_VALUE';
31 G_INVALID_LINE_DATES CONSTANT VARCHAR2(200) := 'OKC_INVALID_LINE_DATES';
32 G_REQUIRED_LINE_VALUE CONSTANT VARCHAR2(200) := 'OKC_REQUIRED_LINE_FIELD';
33 G_INVALID_LINE_CURRENCY CONSTANT VARCHAR2(200) := 'OKC_INVALID_LINE_CURRENCY';
34 G_RULE_ROLE_DELETED CONSTANT VARCHAR2(200) := 'OKC_RULE_ROLE_DELETED';
35 G_RULE_ROLE_CHANGED CONSTANT VARCHAR2(200) := 'OKC_RULE_ROLE_CHANGED';
36 G_NO_SUBLINE_PARTY CONSTANT VARCHAR2(200) := 'OKC_NO_PARTY_SUBLINE';
37 G_NO_HEADER_PARTY CONSTANT VARCHAR2(200) := 'OKC_NO_PARTY_HEADER';
38 G_NO_SUBLINE_RULE CONSTANT VARCHAR2(200) := 'OKC_NO_RULE_SUBLINE';
39 G_NOT_ALLOWED_RULE CONSTANT VARCHAR2(200) := 'OKC_NOT_ALLOWED_RULE';
40 ------------------------------------------------------------------------------------
41 -- GLOBAL EXCEPTION
42 ---------------------------------------------------------------------------
43 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
44
45 -- GLOBAL VARIABLES
46 ---------------------------------------------------------------------------
47 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_QA_SECURITIZATION';
48 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
49 ---------------------------------------------------------------------------
50 cursor l_lnerl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
51 rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
52 chrId NUMBER,
53 cleId NUMBER ) IS
54 select crl.id slh_id,
55 crl.object1_id1,
56 crl.RULE_INFORMATION1,
57 crl.RULE_INFORMATION2,
58 crl.RULE_INFORMATION3,
59 crl.RULE_INFORMATION5,
60 crl.RULE_INFORMATION6,
61 crl.RULE_INFORMATION10
62 from OKC_RULE_GROUPS_B crg,
63 OKC_RULES_B crl
64 where crl.rgp_id = crg.id
65 and crg.RGD_CODE = rgcode
66 and crl.RULE_INFORMATION_CATEGORY = rlcat
67 and crg.dnz_chr_id = chrId
68 and crg.cle_id = cleId
69 order by crl.RULE_INFORMATION1;
70
71 cursor l_hdrrl_csr( rgcode OKC_RULE_GROUPS_B.RGD_CODE%TYPE,
72 rlcat OKC_RULES_B.RULE_INFORMATION_CATEGORY%TYPE,
73 chrId NUMBER) IS
74 select crl.object1_id1,
75 crl.RULE_INFORMATION1,
76 crl.RULE_INFORMATION2,
77 crl.RULE_INFORMATION3,
78 crl.RULE_INFORMATION4,
79 crl.RULE_INFORMATION5,
80 crl.RULE_INFORMATION6,
81 crl.RULE_INFORMATION10,
82 crl.RULE_INFORMATION11
83 from OKC_RULE_GROUPS_B crg,
84 OKC_RULES_B crl
85 where crl.rgp_id = crg.id
86 and crg.RGD_CODE like rgcode
87 and crl.RULE_INFORMATION_CATEGORY = rlcat
88 and crg.dnz_chr_id = chrId;
89
90 CURSOR l_hdr_csr(chrid OKL_K_HEADERS.KHR_ID%TYPE) IS
91 SELECT chr.SCS_CODE,
92 chr.START_DATE,
93 chr.DATE_SIGNED,
94 chr.CURRENCY_CODE,
95 chr.TEMPLATE_YN,
96 chr.contract_number,
97 khr.accepted_date,
98 khr.DEAL_TYPE,
99 khr.term_duration term,
100 khr.after_tax_yield
101 FROM OKC_K_HEADERS_B chr, OKL_K_HEADERS khr
102 WHERE chr.id = chrid
103 AND chr.id = khr.id;
104
105 CURSOR l_lne_csr1(ltycode VARCHAR2, chrid OKL_K_HEADERS.KHR_ID%TYPE) IS
106 SELECT kle.name,
107 kle.CURRENCY_CODE,
108 kle.id,
109 kle.cle_id,
110 kle.RESIDUAL_VALUE,
111 kle.TRACKED_RESIDUAL,
112 kle.CAPITAL_REDUCTION,
113 kle.TRADEIN_AMOUNT,
114 kle.RVI_PREMIUM,
115 kle.OEC,
116 kle.residual_code,
117 kle.residual_grnty_amount
118 FROM OKL_K_LINES_FULL_V kle,
119 OKC_LINE_STYLES_B ls,
120 OKC_STATUSES_B sts
121 WHERE kle.lse_id = ls.id
122 AND ls.lty_code = ltycode
123 AND kle.dnz_chr_id = chrid
124 AND sts.code = kle.sts_code
125 AND sts.ste_code not in ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
126
127 CURSOR l_lne_csr(ltycode VARCHAR2, chrid OKL_K_HEADERS.KHR_ID%TYPE) IS
128 SELECT kle.name,
129 kle.CURRENCY_CODE,
130 kle.id,
131 kle.cle_id,
132 kle.RESIDUAL_VALUE,
133 kle.TRACKED_RESIDUAL,
134 kle.CAPITAL_REDUCTION,
135 kle.TRADEIN_AMOUNT,
136 kle.RVI_PREMIUM,
137 kle.OEC,
138 kle.residual_code,
139 kle.residual_grnty_amount
140 FROM OKL_K_LINES_FULL_V kle,
141 OKC_LINE_STYLES_B ls,
142 OKC_STATUSES_B sts
143 WHERE kle.lse_id = ls.id
144 AND ls.lty_code = ltycode
145 AND kle.dnz_chr_id = chrid
146 AND sts.code = kle.sts_code
147 AND sts.ste_code not in ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
148
149
150 cursor strm_name_csr ( styid NUMBER ) is
151 select tl.name name,
152 stm.stream_type_class stream_type_class,
153 tl.description ALLOC_BASIS,
154 stm.capitalize_yn capitalize_yn,
155 stm.periodic_yn periodic_yn
156 from okl_strm_type_b stm,
157 OKL_STRM_TYPE_TL tl
158 where tl.id = stm.id
159 and tl.language = 'US'
160 and stm.id = styid;
161
162
163 Cursor fnd_csr ( lkupcode VARCHAR2 ) IS
164 SELECT Meaning
165 FROM OKC_RULE_DEFS_V
166 WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'OKL Rule Developer DF'
167 AND RULE_CODE = lkupcode;
168
169 Cursor t_and_c_csr ( code VARCHAR2 ) IS
170 SELECT MEANING
171 FROM FND_LOOKUPS
172 WHERE LOOKUP_TYPE = 'OKL_LA_SEC_LINKS'
173 AND LOOKUP_CODE = code;
174
175
176 Cursor Invstr_csr ( chrId NUMBER, cleId NUMBER) IS
177 SELECT
178 PARB.NAME NAME
179 FROM
180 OKX_PARTIES_V PARB,
181 OKC_K_PARTY_ROLES_B CPLB
182 WHERE
183 PARB.ID1 = CPLB.OBJECT1_ID1 AND
184 PARB.ID2 = CPLB.OBJECT1_ID2 AND
185 CPLB.JTOT_OBJECT1_CODE = 'OKX_PARTY' AND
186 CPLB.RLE_CODE = 'INVESTOR' AND
187 CPLB.DNZ_CHR_ID = chrId AND
188 CPLB.CLE_ID = cleId;
189
190 PROCEDURE check_rule_constraints(
191 x_return_status OUT NOCOPY VARCHAR2,
192 p_chr_id IN NUMBER);
193
194 PROCEDURE check_functional_constraints(
195 x_return_status OUT NOCOPY VARCHAR2,
196 p_chr_id IN NUMBER);
197
198 PROCEDURE check_ia_type_for_strms(
199 x_return_status OUT NOCOPY VARCHAR2,
200 p_chr_id IN NUMBER);
201
202 END OKL_QA_SECURITIZATION;