1 PACKAGE BODY ICX_CAT_POPULATE_STATUS_PVT AS
2 /* $Header: ICXVPPSB.pls 120.6 2006/10/06 02:44:24 rwidjaja noship $*/
3
4 FUNCTION getCategoryStatus
5 ( p_end_date_active IN DATE ,
6 p_disable_date IN DATE ,
7 p_system_date IN DATE
8 )
9 RETURN NUMBER
10 IS
11 l_ret_status PLS_INTEGER;
12 l_err_loc PLS_INTEGER;
13 BEGIN
14 l_err_loc := 100;
15 l_ret_status := INVALID_FOR_POPULATE;
16
17 l_err_loc := 200;
18
19 IF (p_system_date < p_end_date_active AND
20 p_system_date < p_disable_date)
21 THEN
22 l_err_loc := 300;
23 l_ret_status := VALID_FOR_POPULATE;
24 END IF;
25
26 l_err_loc := 400;
27
28 RETURN l_ret_status;
29 EXCEPTION
30 WHEN OTHERS THEN
31 l_err_loc := 500;
32 RETURN l_ret_status;
33 END getCategoryStatus;
34
35 FUNCTION getBPALineStatus
36 ( p_BPA_line_status_rec IN g_BPA_line_status_rec_type
37 )
38 RETURN NUMBER
39 IS
40 l_ret_status PLS_INTEGER;
41 l_err_loc PLS_INTEGER;
42 BEGIN
43 l_err_loc := 100;
44 l_ret_status := INVALID_FOR_POPULATE;
45
46 l_err_loc := 200;
47
48 IF (p_BPA_line_status_rec.approved_date IS NOT NULL AND
49 p_BPA_line_status_rec.authorization_status NOT IN ('REJECTED', 'INCOMPLETE') AND
50 p_BPA_line_status_rec.frozen_flag = 'N' AND
51 p_BPA_line_status_rec.hdr_cancel_flag = 'N' AND
52 p_BPA_line_status_rec.line_cancel_flag = 'N' AND
53 p_BPA_line_status_rec.hdr_closed_code NOT IN ('CLOSED', 'FINALLY CLOSED') AND
54 p_BPA_line_status_rec.line_closed_code NOT IN ('CLOSED', 'FINALLY CLOSED') AND
55 p_BPA_line_status_rec.end_date >= p_BPA_line_status_rec.system_date AND
56 p_BPA_line_status_rec.expiration_date >= p_BPA_line_status_rec.system_date )
57 THEN
58 l_err_loc := 300;
59 l_ret_status := VALID_FOR_POPULATE;
60 END IF;
61
62 l_err_loc := 400;
63
64 RETURN l_ret_status;
65 EXCEPTION
66 WHEN OTHERS THEN
67 l_err_loc := 500;
68 RETURN l_ret_status;
69 END getBPALineStatus;
70
71 FUNCTION getQuoteLineStatus
72 ( p_po_line_id IN NUMBER
73 )
74 RETURN NUMBER
75 IS
76 l_ret_status PLS_INTEGER;
77 l_err_loc PLS_INTEGER;
78 BEGIN
79 l_err_loc := 100;
80 l_ret_status := INVALID_FOR_POPULATE;
81
82 l_err_loc := 200;
83
84 /* Comments on the Quotation Line status
85 1. status_lookup_code = 'A'
86 -- Checks if the quotation status is 'Active'.
87 -- Quotations can be created in the following status from the form:
88 -- In Process => I
89 -- Closed => C
90 -- Active => A
91 2. ph.quotation_class_code = 'CATALOG'
92 -- Check if the quotation type is 'Catalog Quotation' / 'Standard Quotation'.
93 -- Following types of Quotations can be created from the form:
94 -- Bid Quotation => BID
95 -- Catalog Quotation => CATALOG
96 -- Standard Quotation => CATALOG
97 3. Just check for the end date in po_line_locations_all. Start_date will be checked by the search code
98 4. pqa.approval_type IN ('ALL ORDERS','REQUISITIONS') changed to pqa.approval_type IS NOT NULL to accomodate the SIC rules
99 5. Just check for the end date in po_quotation_approvals_all. Start_date will be checked by the search code
100 6. Just check for the end date in po_headers_all. Start_date will be checked by the search code
101 */
102
103 SELECT VALID_FOR_POPULATE
104 INTO l_ret_status
105 FROM po_headers_all ph,
106 po_lines_all pl
107 WHERE pl.po_line_id = p_po_line_id
108 AND ph.po_header_id = pl.po_header_id
109 AND ph.type_lookup_code = 'QUOTATION'
110 AND ph.status_lookup_code = 'A'
111 AND ph.quotation_class_code = 'CATALOG'
112 AND (NVL(ph.approval_required_flag,'N') = 'N' OR
113 (ph.approval_required_flag ='Y' AND
114 EXISTS (SELECT 'current approved effective price break'
115 FROM po_line_locations_all pll,
116 po_quotation_approvals_all pqa
117 WHERE pl.po_line_id = pll.po_line_id
118 AND SYSDATE <= NVL(pll.end_date, SYSDATE+1)
119 AND pqa.line_location_id = pll.line_location_id
120 AND pqa.approval_type IS NOT NULL
121 AND SYSDATE <= NVL(pqa.end_date_active, SYSDATE+1) )))
122 AND TRUNC(SYSDATE) <= NVL(TRUNC(ph.end_date), TRUNC(SYSDATE + 1));
123
124 l_err_loc := 300;
125
126 RETURN l_ret_status;
127 EXCEPTION
128 WHEN NO_DATA_FOUND THEN
129 l_err_loc := 400;
130 RETURN l_ret_status;
131 END getQuoteLineStatus;
132
133 FUNCTION getGlobalAgreementStatus
134 ( p_enabled_flag IN VARCHAR2
135 )
136 RETURN NUMBER
137 IS
138 l_ret_status PLS_INTEGER;
139 l_err_loc PLS_INTEGER;
140 BEGIN
141 l_err_loc := 100;
142 l_ret_status := VALID_FOR_POPULATE;
143
144 l_err_loc := 200;
145
146 IF NVL(p_enabled_flag, 'N') = 'N' THEN
147 l_err_loc := 300;
148 l_ret_status := GLOBAL_BLANKET_DISABLED;
149 END IF;
150
151 l_err_loc := 400;
152
153 -- Following validations for master item
154 -- in purchasing org, enabled org and owning org on a GBPA
155 -- will be done by PO
156 -- 1. purchasing_enabled_flag
157 -- 2. outside_operation_flag
158 -- 3. UOM code/UOM Class
159
160 RETURN l_ret_status;
161 END getGlobalAgreementStatus;
162
163 FUNCTION getTemplateLineStatus
164 ( p_inactive_date IN DATE ,
165 p_contract_line_id IN NUMBER ,
166 p_BPA_line_status_rec IN g_BPA_line_status_rec_type
167 )
168 RETURN NUMBER
169 IS
170 l_ret_status PLS_INTEGER;
171 l_BPA_line_status_rec g_BPA_line_status_rec_type;
172 l_err_loc PLS_INTEGER;
173 BEGIN
174 l_err_loc := 100;
175 l_ret_status := VALID_FOR_POPULATE;
176
177 l_err_loc := 200;
178
179 IF NVL(p_inactive_date, SYSDATE+1) <= SYSDATE THEN
180 l_err_loc := 300;
181 l_ret_status := INACTIVE_TEMPLATE;
182 ELSE
183 l_err_loc := 400;
184 IF (p_contract_line_id IS NOT NULL AND
185 p_contract_line_id <> NULL_NUMBER)
186 THEN
187 l_err_loc := 500;
188 l_ret_status := getBPALineStatus(p_BPA_line_status_rec);
189 IF l_ret_status = VALID_FOR_POPULATE THEN
190 l_err_loc := 600;
191 l_ret_status := VALID_FOR_POPULATE;
192 ELSE
193 l_err_loc := 700;
194 l_ret_status := TEMPLATE_INVALID_BLANKET_LINE;
195 END IF;
196 END IF;
197 END IF;
198
199 l_err_loc := 800;
200
201 RETURN l_ret_status;
202 END getTemplateLineStatus;
203
204 PROCEDURE getMasterItemStatusAndType
205 ( p_internal_order_enabled_flag IN VARCHAR2 ,
206 p_outside_operation_flag IN VARCHAR2 ,
207 p_item_price IN NUMBER ,
208 p_item_status OUT NOCOPY NUMBER ,
209 p_item_type OUT NOCOPY VARCHAR2
210 )
211 IS
212 l_err_loc PLS_INTEGER;
213 l_is_purchasable BOOLEAN;
214 l_is_internal BOOLEAN;
215 BEGIN
216 l_err_loc := 100;
217 p_item_status := VALID_FOR_POPULATE;
218
219 -- item price is null if purchasing_enabled_flag is not 'Y'. We check this in the cursor,
220 -- so we do not need to check for the purchasing_enabled_flag here.
221 IF (p_item_price IS NOT NULL AND
222 NVL(p_outside_operation_flag, 'N') <> 'Y')
223 THEN
224 l_err_loc := 200;
225 l_is_purchasable := TRUE;
226 ELSE
227 l_err_loc := 300;
228 l_is_purchasable := FALSE;
229 END IF;
230
231 IF (NVL(p_internal_order_enabled_flag, 'N') = 'Y') THEN
232 l_is_internal := TRUE;
233 ELSE
234 l_is_internal := FALSE;
235 END IF;
236
237 l_err_loc := 500;
238 IF (l_is_purchasable AND l_is_internal) THEN
239 p_item_type := ICX_CAT_UTIL_PVT.g_both_item_type;
240 ELSE
241 IF (l_is_purchasable) THEN
242 p_item_type := ICX_CAT_UTIL_PVT.g_purchase_item_type;
243 ELSIF (l_is_internal) THEN
244 p_item_type := ICX_CAT_UTIL_PVT.g_internal_item_type;
245 ELSE
246 p_item_type := null;
247 p_item_status := INVALID_FOR_POPULATE;
248 END IF;
249 END IF;
250
251 END getMasterItemStatusAndType;
252
253
254 END ICX_CAT_POPULATE_STATUS_PVT;
[Home] [Help]