[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_PO_INTERFACE_PKG
Source
1 PACKAGE BODY IGC_CC_PO_INTERFACE_PKG AS
2 /*$Header: IGCCCPIB.pls 120.15 2008/05/28 10:57:40 anusaxen ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_PO_INTERFACE_PKG';
5
6 -- The flag determines whether to print debug information or not.
7 g_debug_flag VARCHAR2(1);
8
9 PROCEDURE Initialize_Header_Row(p_mode IN VARCHAR2,
10 p_encumbrance_on IN VARCHAR2,
11 p_cc_headers_rec IN igc_cc_headers%ROWTYPE,
12 p_po_headers_rec IN OUT NOCOPY po_headers_all%ROWTYPE)
13 IS
14
15 l_po_headers_rec po_headers_all%ROWTYPE;
16 l_employee_id NUMBER;
17 E_EMPLOYEE_NOT_FOUND EXCEPTION;
18 BEGIN
19 -- Bug 3605536 GSCC warnings fixed
20 l_employee_id := 0;
21
22 /* Initialize CC related attributes */
23 /* Insert */
24
25 IF (p_mode = 'I') THEN
26 SELECT po_headers_s.NEXTVAL
27 INTO l_po_headers_rec.po_header_id
28 FROM dual;
29 ELSE
30 /* Update */
31 l_po_headers_rec := p_po_headers_rec;
32 END IF;
33
34 BEGIN
35 SELECT employee_id
36 INTO l_employee_id
37 FROM fnd_user
38 WHERE user_id = p_cc_headers_rec.cc_preparer_user_id;
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 fnd_message.set_name('IGC','IGC_CC_PREPARER_EMPLOYEE_ID');
42 fnd_message.set_token('PREPARER_ID',to_char(p_cc_headers_rec.cc_preparer_user_id),TRUE);
43 fnd_msg_pub.add;
44 RAISE E_EMPLOYEE_NOT_FOUND;
45 END;
46
47 -- bug 2973033 - ssmales 30-May-2003 added IF statement below for preparers without linked employee
48 IF l_employee_id IS NULL THEN
49 fnd_message.set_name('IGC','IGC_CC_PREPARER_EMPLOYEE_ID');
50 fnd_message.set_token('PREPARER_ID',to_char(p_cc_headers_rec.cc_preparer_user_id),TRUE);
51 fnd_msg_pub.add;
52 RAISE E_EMPLOYEE_NOT_FOUND;
53 END IF;
54
55 l_po_headers_rec.agent_id := l_employee_id;
56 l_po_headers_rec.segment1 := p_cc_headers_rec.cc_num;
57 l_po_headers_rec.vendor_id := p_cc_headers_rec.vendor_id;
58 l_po_headers_rec.vendor_site_id := p_cc_headers_rec.vendor_site_id;
59 l_po_headers_rec.vendor_contact_id := p_cc_headers_rec.vendor_contact_id;
60 l_po_headers_rec.ship_to_location_id := p_cc_headers_rec.location_id;
61 l_po_headers_rec.bill_to_location_id := p_cc_headers_rec.location_id;
62 l_po_headers_rec.terms_id := p_cc_headers_rec.term_id;
63 l_po_headers_rec.revision_num := p_cc_headers_rec.cc_version_num;
64 l_po_headers_rec.currency_code := p_cc_headers_rec.currency_code;
65 l_po_headers_rec.rate_type := p_cc_headers_rec.conversion_type;
66 l_po_headers_rec.rate_date := p_cc_headers_rec.conversion_date;
67 l_po_headers_rec.rate := p_cc_headers_rec.conversion_rate;
68 l_po_headers_rec.org_id := p_cc_headers_rec.org_id;
69 l_po_headers_rec.last_update_login := p_cc_headers_rec.last_update_login;
70 l_po_headers_rec.creation_date := p_cc_headers_rec.creation_date;
71 l_po_headers_rec.created_by := p_cc_headers_rec.created_by;
72 l_po_headers_rec.last_update_date := p_cc_headers_rec.last_update_date;
73 l_po_headers_rec.last_updated_by := p_cc_headers_rec.last_updated_by;
74
75 /* Standard default values PO Columns */
76
77 IF (p_mode = 'I') THEN
78 l_po_headers_rec.type_lookup_code := 'STANDARD';
79 l_po_headers_rec.summary_flag := 'N';
80 l_po_headers_rec.enabled_flag := 'Y';
81 l_po_headers_rec.fob_lookup_code := 'Destination';
82 l_po_headers_rec.freight_terms_lookup_code := 'TBD';
83 l_po_headers_rec.print_count := 0;
84 l_po_headers_rec.confirming_order_flag := 'N';
85 l_po_headers_rec.acceptance_required_flag := 'N';
86 l_po_headers_rec.cancel_flag := 'N';
87 l_po_headers_rec.firm_status_lookup_code := 'N';
88 l_po_headers_rec.frozen_flag := 'N';
89 l_po_headers_rec.supply_agreement_flag := 'N';
90 END IF;
91
92 /* Check Contract Commitment document control status and approval status and
93 encumbrance status to set approved_flag, approved_date and authorization status columns
94 of PO */
95
96 IF (p_cc_headers_rec.cc_apprvl_status = 'AP') AND
97 (p_cc_headers_rec.cc_ctrl_status = 'O') AND
98 ( ((p_encumbrance_on = FND_API.G_TRUE) AND (p_cc_headers_rec.cc_encmbrnc_status = 'C')) OR
99 ((p_encumbrance_on = FND_API.G_FALSE) AND (p_cc_headers_rec.cc_encmbrnc_status = 'N'))
100 ) THEN
101
102 l_po_headers_rec.authorization_status := 'APPROVED';
103 l_po_headers_rec.approved_flag := 'Y';
104 l_po_headers_rec.approved_date := sysdate;
105 ELSE
106 l_po_headers_rec.authorization_status := NULL;
107 l_po_headers_rec.approved_flag := 'N';
108 l_po_headers_rec.approved_date := NULL;
109 END IF;
110
111
112 IF (p_mode = 'I') THEN
113 l_po_headers_rec.segment2 := NULL;
114 l_po_headers_rec.segment3 := NULL;
115 l_po_headers_rec.segment4 := NULL;
116 l_po_headers_rec.segment5 := NULL;
117 l_po_headers_rec.start_date_active := NULL;
118 l_po_headers_rec.end_date_active := NULL;
119 l_po_headers_rec.ship_via_lookup_code := NULL;
120 l_po_headers_rec.status_lookup_code := NULL;
121 l_po_headers_rec.from_header_id := NULL;
122 l_po_headers_rec.from_type_lookup_code := NULL;
123 l_po_headers_rec.start_date := NULL;
124 l_po_headers_rec.end_date := NULL;
125 l_po_headers_rec.blanket_total_amount := NULL;
126 l_po_headers_rec.revised_date := NULL;
127 l_po_headers_rec.amount_limit := NULL;
128 l_po_headers_rec.min_release_amount := NULL;
129 l_po_headers_rec.note_to_authorizer := NULL;
130 l_po_headers_rec.note_to_vendor := NULL;
131 l_po_headers_rec.note_to_receiver := NULL;
132 l_po_headers_rec.printed_date := NULL;
133 l_po_headers_rec.vendor_order_num := NULL;
134 l_po_headers_rec.comments := NULL;
135 l_po_headers_rec.reply_date := NULL;
136 l_po_headers_rec.reply_method_lookup_code := NULL;
137 l_po_headers_rec.rfq_close_date := NULL;
138 l_po_headers_rec.quote_type_lookup_code := NULL;
139 l_po_headers_rec.quotation_class_code := NULL;
140 l_po_headers_rec.quote_warning_delay_unit := NULL;
141 l_po_headers_rec.quote_warning_delay := NULL;
142 l_po_headers_rec.quote_vendor_quote_number := NULL;
143 l_po_headers_rec.acceptance_due_date := NULL;
144 l_po_headers_rec.closed_date := NULL;
145 l_po_headers_rec.user_hold_flag := NULL;
146 l_po_headers_rec.approval_required_flag := NULL;
147 l_po_headers_rec.firm_date := NULL;
148 l_po_headers_rec.attribute_category := NULL;
149 l_po_headers_rec.attribute1 := NULL;
150 l_po_headers_rec.attribute2 := NULL;
151 l_po_headers_rec.attribute3 := NULL;
152 l_po_headers_rec.attribute4 := NULL;
153 l_po_headers_rec.attribute5 := NULL;
154 l_po_headers_rec.attribute6 := NULL;
155 l_po_headers_rec.attribute7 := NULL;
156 l_po_headers_rec.attribute8 := NULL;
157 l_po_headers_rec.attribute9 := NULL;
158 l_po_headers_rec.attribute10 := NULL;
159 l_po_headers_rec.attribute11 := NULL;
160 l_po_headers_rec.attribute12 := NULL;
161 l_po_headers_rec.attribute13 := NULL;
162 l_po_headers_rec.attribute14 := NULL;
163 l_po_headers_rec.attribute15 := NULL;
164 l_po_headers_rec.closed_code := NULL;
165 l_po_headers_rec.ussgl_transaction_code := NULL;
166 l_po_headers_rec.government_context := NULL;
167 l_po_headers_rec.request_id := NULL;
168 l_po_headers_rec.program_application_id := NULL;
169 l_po_headers_rec.program_id := NULL;
170 l_po_headers_rec.program_update_date := NULL;
171 l_po_headers_rec.edi_processed_flag := NULL;
172 l_po_headers_rec.edi_processed_status := NULL;
173 l_po_headers_rec.global_attribute_category := NULL;
174 l_po_headers_rec.global_attribute1 := NULL;
175 l_po_headers_rec.global_attribute2 := NULL;
176 l_po_headers_rec.global_attribute3 := NULL;
177 l_po_headers_rec.global_attribute4 := NULL;
178 l_po_headers_rec.global_attribute5 := NULL;
179 l_po_headers_rec.global_attribute6 := NULL;
180 l_po_headers_rec.global_attribute7 := NULL;
181 l_po_headers_rec.global_attribute8 := NULL;
182 l_po_headers_rec.global_attribute9 := NULL;
183 l_po_headers_rec.global_attribute10 := NULL;
184 l_po_headers_rec.global_attribute11 := NULL;
185 l_po_headers_rec.global_attribute12 := NULL;
186 l_po_headers_rec.global_attribute13 := NULL;
187 l_po_headers_rec.global_attribute14 := NULL;
188 l_po_headers_rec.global_attribute15 := NULL;
189 l_po_headers_rec.global_attribute16 := NULL;
190 l_po_headers_rec.global_attribute17 := NULL;
191 l_po_headers_rec.global_attribute18 := NULL;
192 l_po_headers_rec.global_attribute19 := NULL;
193 l_po_headers_rec.global_attribute20 := NULL;
194 l_po_headers_rec.interface_source_code := NULL;
195 l_po_headers_rec.reference_num := NULL;
196 l_po_headers_rec.wf_item_type := NULL;
197 l_po_headers_rec.wf_item_key := NULL;
198 l_po_headers_rec.mrc_rate_type := NULL;
199 l_po_headers_rec.mrc_rate_date := NULL;
200 l_po_headers_rec.mrc_rate := NULL;
201 l_po_headers_rec.pcard_id := NULL;
202 l_po_headers_rec.price_update_tolerance := NULL;
203 l_po_headers_rec.pay_on_code := NULL;
204 END IF;
205
206 p_po_headers_rec := l_po_headers_rec;
207
208 END Initialize_Header_Row;
209
210
211 PROCEDURE Initialize_Lines_Row(p_mode IN VARCHAR2,
212 p_po_header_id IN NUMBER,
213 p_org_id IN NUMBER,
214 p_cc_acct_lines_rec IN igc_cc_acct_lines%ROWTYPE,
215 p_po_lines_rec IN OUT NOCOPY po_lines_all%ROWTYPE,
216 p_yr_start_date IN DATE,
217 p_yr_end_date IN DATE)
218 IS
219 l_po_lines_rec po_lines_all%ROWTYPE;
220 l_line_type_id po_line_types_tl.line_type_id%TYPE;
221 E_CC_PO_LINE_TYPE EXCEPTION;
222
223 -- M van der geest, added 18-OKT-2001
224 l_icx_lang VARCHAR2(30);
225
226 -- bug 4097669, start 1
227 l_order_type_lookup_code po_line_types_b.order_type_lookup_code%TYPE;
228 l_purchase_basis po_line_types_b.purchase_basis%TYPE;
229 l_matching_basis po_line_types_b.matching_basis%TYPE;
230 -- bug 4097669, end 1
231
232 BEGIN
233 /* Insert */
234 IF (p_mode = 'I') THEN
235 SELECT po_lines_s.NEXTVAL
236 INTO l_po_lines_rec.po_line_id
237 FROM dual;
238 ELSE
239 /* Update */
240 l_po_lines_rec := p_po_lines_rec;
241 END IF;
242
243 l_po_lines_rec.last_update_date := p_cc_acct_lines_rec.last_update_date;
244 l_po_lines_rec.last_updated_by := p_cc_acct_lines_rec.last_updated_by;
245 l_po_lines_rec.po_header_id := p_po_header_id;
246
247 BEGIN
248 -- M van der Geest, added 18-Oct-2001
249 fnd_profile.get('ICX_LANGUAGE',l_icx_lang);
250
251 SELECT line_type_id
252 INTO l_line_type_id
253 FROM po_line_types_tl
254 WHERE line_type = 'IGC CONTRACT COMMITMENT'
255 AND LANGUAGE = DECODE(l_icx_lang,'AMERICAN','US',
256 'DUTCH','NL','US');
257
258 -- bug 4097669, start 2
259 SELECT order_type_lookup_code,
260 purchase_basis,
261 matching_basis
262 INTO l_order_type_lookup_code,
263 l_purchase_basis,
264 l_matching_basis
265 FROM po_line_types_b
266 WHERE line_type_id = l_line_type_id;
267 -- bug 4097669, end 2
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 fnd_message.set_name('IGC','IGC_CC_PO_LINE_TYPE');
271 fnd_message.set_token('PO_LINE_TYPE','IGC CONTRACT COMMITMENT',TRUE);
272 fnd_msg_pub.add;
273 RAISE E_CC_PO_LINE_TYPE;
274 END;
275
276 l_po_lines_rec.line_type_id := l_line_type_id;
277
278 -- bug 4097669, start 3
279 l_po_lines_rec.order_type_lookup_code := l_order_type_lookup_code;
280 l_po_lines_rec.purchase_basis := l_purchase_basis;
281 l_po_lines_rec.matching_basis := l_matching_basis;
282 -- bug 4097669, end 3
283
284 l_po_lines_rec.line_num := p_cc_acct_lines_rec.cc_acct_line_num;
285 l_po_lines_rec.last_update_login := p_cc_acct_lines_rec.last_update_login;
286 l_po_lines_rec.creation_date := p_cc_acct_lines_rec.creation_date;
287 l_po_lines_rec.created_by := p_cc_acct_lines_rec.created_by;
288
289 l_po_lines_rec.taxable_flag := p_cc_acct_lines_rec.cc_acct_taxable_flag ;
290
291 l_po_lines_rec.tax_name := p_cc_acct_lines_rec.tax_classif_code;
292
293 /* tax_classif_code of igc_cc_acct_lines is assigned to tax_name of po_lines_all
294 for r12 EBtax uptake for CC */
295
296 /* Commented for bug 6472296 - r12 EBtax uptake for CC
297 BEGIN
298 SELECT apt.name,ccal.tax_id
299 INTO l_po_lines_rec.tax_name, l_po_lines_rec.tax_code_id
300 FROM igc_cc_acct_lines ccal,
301 ap_tax_codes apt
302 WHERE ccal.cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id
303 AND apt.tax_id = ccal.tax_id;
304 EXCEPTION -- 3326801
305 WHEN NO_DATA_FOUND THEN
306 -- Continue processing.
307 NULL;
308 END ;
309 */
310
311 /* Current year payment forecast only */
312
313 SELECT SUM(cc_det_pf_entered_amt)
314 INTO l_po_lines_rec.quantity
315 FROM igc_cc_det_pf
316 WHERE cc_acct_line_id = p_cc_acct_lines_rec.cc_acct_line_id AND
317 /* Commented this part of code to fix bug 1576123 (cc_det_pf_date >= p_yr_start_date AND */
318 ( cc_det_pf_date <= p_yr_end_date) ;
319
320 l_po_lines_rec.org_id := p_org_id;
321 l_po_lines_rec.project_id := p_cc_acct_lines_rec.project_id;
322 l_po_lines_rec.task_id := p_cc_acct_lines_rec.task_id;
323
324 IF (p_mode = 'I') THEN
325 l_po_lines_rec.category_id := NULL; /* Bug 1390901 */
326 l_po_lines_rec.item_description := p_cc_acct_lines_rec.cc_acct_desc; /*Bug 1372370 */
327 l_po_lines_rec.unit_meas_lookup_code := 'Each'; /* Bug 3605536 */
328 l_po_lines_rec.allow_price_override_flag := 'N';
329 l_po_lines_rec.list_price_per_unit := 1;
330 l_po_lines_rec.unit_price := 1;
331 l_po_lines_rec.unordered_flag := 'N';
332 l_po_lines_rec.closed_flag := 'N';
333 l_po_lines_rec.cancel_flag := 'N';
334 l_po_lines_rec.price_type_lookup_code := 'FIXED';
335 l_po_lines_rec.capital_expense_flag := 'N';
336 l_po_lines_rec.negotiated_by_preparer_flag := 'N';
337
338 l_po_lines_rec.item_id := NULL;
339 l_po_lines_rec.item_revision := NULL;
340 l_po_lines_rec.quantity_committed := NULL;
341 l_po_lines_rec.committed_amount := NULL;
342 l_po_lines_rec.not_to_exceed_price := NULL;
343 l_po_lines_rec.un_number_id := NULL;
344 l_po_lines_rec.hazard_class_id := NULL;
345 l_po_lines_rec.note_to_vendor := NULL;
346 l_po_lines_rec.from_header_id := NULL;
347 l_po_lines_rec.from_line_id := NULL;
348 l_po_lines_rec.min_order_quantity := NULL;
349 l_po_lines_rec.max_order_quantity := NULL;
350 l_po_lines_rec.qty_rcv_tolerance := NULL;
351 l_po_lines_rec.over_tolerance_error_flag := NULL;
352 l_po_lines_rec.market_price := NULL;
353 l_po_lines_rec.user_hold_flag := NULL;
354 l_po_lines_rec.cancelled_by := NULL;
355 l_po_lines_rec.cancel_date := NULL;
356 l_po_lines_rec.cancel_reason := NULL;
357 l_po_lines_rec.firm_status_lookup_code := NULL;
358 l_po_lines_rec.firm_date := NULL;
359 l_po_lines_rec.vendor_product_num := NULL;
360 l_po_lines_rec.contract_num := NULL;
361 l_po_lines_rec.type_1099 := NULL;
362 l_po_lines_rec.attribute_category := NULL;
363 l_po_lines_rec.attribute1 := NULL;
364 l_po_lines_rec.attribute2 := NULL;
365 l_po_lines_rec.attribute3 := NULL;
366 l_po_lines_rec.attribute4 := NULL;
367 l_po_lines_rec.attribute5 := NULL;
368 l_po_lines_rec.attribute6 := NULL;
369 l_po_lines_rec.attribute7 := NULL;
370 l_po_lines_rec.attribute8 := NULL;
371 l_po_lines_rec.attribute9 := NULL;
372 l_po_lines_rec.attribute10 := NULL;
373 l_po_lines_rec.reference_num := NULL;
374 l_po_lines_rec.attribute11 := NULL;
375 l_po_lines_rec.attribute12 := NULL;
376 l_po_lines_rec.attribute13 := NULL;
377 l_po_lines_rec.attribute14 := NULL;
378 l_po_lines_rec.attribute15 := NULL;
379 l_po_lines_rec.min_release_amount := NULL;
380 l_po_lines_rec.closed_code := NULL;
381 l_po_lines_rec.price_break_lookup_code := NULL;
382 l_po_lines_rec.ussgl_transaction_code := NULL;
383 l_po_lines_rec.government_context := NULL;
384 l_po_lines_rec.request_id := NULL;
385 l_po_lines_rec.program_application_id := NULL;
386 l_po_lines_rec.program_id := NULL;
387 l_po_lines_rec.program_update_date := NULL;
388 l_po_lines_rec.closed_date := NULL;
389 l_po_lines_rec.closed_reason := NULL;
390 l_po_lines_rec.closed_by := NULL;
391 l_po_lines_rec.transaction_reason_code := NULL;
392 l_po_lines_rec.qc_grade := NULL;
393 l_po_lines_rec.base_uom := NULL;
394 l_po_lines_rec.base_qty := NULL;
395 l_po_lines_rec.secondary_uom := NULL;
396 l_po_lines_rec.secondary_qty := NULL;
397 l_po_lines_rec.global_attribute_category := NULL;
398 l_po_lines_rec.global_attribute1 := NULL;
399 l_po_lines_rec.global_attribute2 := NULL;
400 l_po_lines_rec.global_attribute3 := NULL;
401 l_po_lines_rec.global_attribute4 := NULL;
402 l_po_lines_rec.global_attribute5 := NULL;
403 l_po_lines_rec.global_attribute6 := NULL;
404 l_po_lines_rec.global_attribute7 := NULL;
405 l_po_lines_rec.global_attribute8 := NULL;
406 l_po_lines_rec.global_attribute9 := NULL;
407 l_po_lines_rec.global_attribute10 := NULL;
408 l_po_lines_rec.global_attribute11 := NULL;
409 l_po_lines_rec.global_attribute12 := NULL;
410 l_po_lines_rec.global_attribute13 := NULL;
411 l_po_lines_rec.global_attribute14 := NULL;
412 l_po_lines_rec.global_attribute15 := NULL;
413 l_po_lines_rec.global_attribute16 := NULL;
414 l_po_lines_rec.global_attribute17 := NULL;
415 l_po_lines_rec.global_attribute18 := NULL;
416 l_po_lines_rec.global_attribute19 := NULL;
417 l_po_lines_rec.global_attribute20 := NULL;
418 l_po_lines_rec.line_reference_num := NULL;
419 l_po_lines_rec.expiration_date := NULL;
420 l_po_lines_rec.base_unit_price := 1; /* Bug 6341012 */
421 END IF;
422
423 p_po_lines_rec := l_po_lines_rec;
424
425 END Initialize_Lines_Row;
426
427
428 PROCEDURE Initialize_Line_Locs_Row(p_mode IN VARCHAR2,
429 p_encumbrance_on IN VARCHAR2,
430 p_po_headers_rec IN po_headers_all%ROWTYPE,
431 p_po_lines_rec IN po_lines_all%ROWTYPE,
432 p_po_line_locs_rec IN OUT NOCOPY po_line_locations_all%ROWTYPE)
433 IS
434 l_po_line_locs_rec po_line_locations_all%ROWTYPE;
435
436 BEGIN
437 /* Insert */
438 IF (p_mode = 'I') THEN
439 SELECT po_line_locations_s.nextval
440 INTO l_po_line_locs_rec.line_location_id
441 FROM DUAL;
442 ELSE
443 l_po_line_locs_rec := p_po_line_locs_rec;
444 END IF;
445
446 l_po_line_locs_rec.po_header_id := p_po_lines_rec.po_header_id ;
447 l_po_line_locs_rec.po_line_id := p_po_lines_rec.po_line_id ;
448 l_po_line_locs_rec.shipment_num := p_po_lines_rec.line_num;
449 l_po_line_locs_rec.last_update_date := p_po_lines_rec.last_update_date;
450 l_po_line_locs_rec.last_updated_by := p_po_lines_rec.last_updated_by;
451 l_po_line_locs_rec.last_update_login := p_po_lines_rec.last_update_login;
452 l_po_line_locs_rec.creation_date := p_po_lines_rec.creation_date;
453 l_po_line_locs_rec.created_by := p_po_lines_rec.created_by;
454 l_po_line_locs_rec.quantity := p_po_lines_rec.quantity;
455 l_po_line_locs_rec.ship_to_location_id := p_po_headers_rec.ship_to_location_id;
456 l_po_line_locs_rec.tax_code_id := p_po_lines_rec.tax_code_id;
457 l_po_line_locs_rec.taxable_flag := p_po_lines_rec.taxable_flag;
458 l_po_line_locs_rec.tax_name := p_po_lines_rec.tax_name ;
459 l_po_line_locs_rec.terms_id := p_po_headers_rec.terms_id;
460 l_po_line_locs_rec.approved_flag := p_po_headers_rec.approved_flag;
461 l_po_line_locs_rec.approved_date := p_po_headers_rec.approved_date;
462 l_po_line_locs_rec.ship_to_organization_id := p_po_headers_rec.org_id;
463 l_po_line_locs_rec.org_id := p_po_headers_rec.org_id;
464
465
466 IF (p_mode = 'I') THEN
467 l_po_line_locs_rec.quantity_received := 0;
468 l_po_line_locs_rec.quantity_accepted := 0;
469 l_po_line_locs_rec.quantity_rejected := 0;
470 l_po_line_locs_rec.quantity_billed := 0;
471 l_po_line_locs_rec.quantity_cancelled := 0;
472 l_po_line_locs_rec.price_override := 1;
473 END IF;
474
475 IF (p_encumbrance_on = FND_API.G_TRUE) THEN
476 l_po_line_locs_rec.encumbered_flag := 'Y';
477 ELSE
478 l_po_line_locs_rec.encumbered_flag := 'N';
479 END IF;
480
481 IF (p_mode = 'I') THEN
482 l_po_line_locs_rec.cancel_flag := 'N';
483 l_po_line_locs_rec.firm_status_lookup_code := 'N';
484 l_po_line_locs_rec.inspection_required_flag := 'N';
485 l_po_line_locs_rec.receipt_required_flag := 'N';
486 l_po_line_locs_rec.qty_rcv_tolerance := 0;
487 l_po_line_locs_rec.qty_rcv_exception_code := 'WARNING';
488 l_po_line_locs_rec.enforce_ship_to_location_code := 'WARNING';
489 l_po_line_locs_rec.allow_substitute_receipts_flag := 'Y';
490 l_po_line_locs_rec.days_early_receipt_allowed := 0;
491 l_po_line_locs_rec.days_late_receipt_allowed := 99;
492 l_po_line_locs_rec.receipt_days_exception_code := 'WARNING';
493 l_po_line_locs_rec.invoice_close_tolerance := 0;
494 l_po_line_locs_rec.receive_close_tolerance := 0;
495 l_po_line_locs_rec.shipment_type := 'STANDARD';
496 l_po_line_locs_rec.closed_code := 'OPEN';
497 l_po_line_locs_rec.receiving_routing_id := 2;
498 l_po_line_locs_rec.accrue_on_receipt_flag := 'N';
499 l_po_line_locs_rec.tax_user_override_flag := 'N';
500 l_po_line_locs_rec.match_option := 'P';
501 l_po_line_locs_rec.calculate_tax_flag := 'N';
502
503 l_po_line_locs_rec.unit_meas_lookup_code := 'Each'; /* Bug 6341012 */
504 l_po_line_locs_rec.po_release_id := NULL;
505 l_po_line_locs_rec.ship_via_lookup_code := NULL;
506 l_po_line_locs_rec.need_by_date := NULL;
507 l_po_line_locs_rec.promised_date := NULL;
508 l_po_line_locs_rec.last_accept_date := NULL;
509
510 l_po_line_locs_rec.encumbered_date := NULL;
511 l_po_line_locs_rec.unencumbered_quantity := NULL;
512 l_po_line_locs_rec.fob_lookup_code := NULL;
513 l_po_line_locs_rec.freight_terms_lookup_code := NULL;
514 l_po_line_locs_rec.estimated_tax_amount := NULL;
515 l_po_line_locs_rec.from_header_id := NULL;
516 l_po_line_locs_rec.from_line_id := NULL;
517 l_po_line_locs_rec.from_line_location_id := NULL;
518 l_po_line_locs_rec.start_date := NULL;
519 l_po_line_locs_rec.end_date := NULL;
520 l_po_line_locs_rec.lead_time := NULL;
521 l_po_line_locs_rec.lead_time_unit := NULL;
522 l_po_line_locs_rec.price_discount := NULL;
523 l_po_line_locs_rec.closed_flag := NULL;
524 l_po_line_locs_rec.cancelled_by := NULL;
525 l_po_line_locs_rec.cancel_date := NULL;
526 l_po_line_locs_rec.cancel_reason := NULL;
527 l_po_line_locs_rec.firm_date := NULL;
528 l_po_line_locs_rec.attribute_category := NULL;
529 l_po_line_locs_rec.attribute1 := NULL;
530 l_po_line_locs_rec.attribute2 := NULL;
531 l_po_line_locs_rec.attribute3 := NULL;
532 l_po_line_locs_rec.attribute4 := NULL;
533 l_po_line_locs_rec.attribute5 := NULL;
534 l_po_line_locs_rec.attribute6 := NULL;
535 l_po_line_locs_rec.attribute7 := NULL;
536 l_po_line_locs_rec.attribute8 := NULL;
537 l_po_line_locs_rec.attribute9 := NULL;
538 l_po_line_locs_rec.attribute10 := NULL;
539 l_po_line_locs_rec.unit_of_measure_class := NULL;
540 l_po_line_locs_rec.encumber_now := NULL;
541 l_po_line_locs_rec.attribute11 := NULL;
542 l_po_line_locs_rec.attribute12 := NULL;
543 l_po_line_locs_rec.attribute13 := NULL;
544 l_po_line_locs_rec.attribute14 := NULL;
545 l_po_line_locs_rec.attribute15 := NULL;
546 l_po_line_locs_rec.source_shipment_id := NULL;
547 l_po_line_locs_rec.request_id := NULL;
548 l_po_line_locs_rec.program_application_id := NULL;
549 l_po_line_locs_rec.program_id := NULL;
550 l_po_line_locs_rec.program_update_date := NULL;
551 l_po_line_locs_rec.ussgl_transaction_code := NULL;
552 l_po_line_locs_rec.government_context := NULL;
553 l_po_line_locs_rec.closed_reason := NULL;
554 l_po_line_locs_rec.closed_date := NULL;
555 l_po_line_locs_rec.closed_by := NULL;
556 l_po_line_locs_rec.global_attribute1 := NULL;
557 l_po_line_locs_rec.global_attribute2 := NULL;
558 l_po_line_locs_rec.global_attribute3 := NULL;
559 l_po_line_locs_rec.global_attribute4 := NULL;
560 l_po_line_locs_rec.global_attribute5 := NULL;
561 l_po_line_locs_rec.global_attribute6 := NULL;
562 l_po_line_locs_rec.global_attribute7 := NULL;
563 l_po_line_locs_rec.global_attribute8 := NULL;
564 l_po_line_locs_rec.global_attribute9 := NULL;
565 l_po_line_locs_rec.global_attribute10 := NULL;
566 l_po_line_locs_rec.global_attribute11 := NULL;
567 l_po_line_locs_rec.global_attribute12 := NULL;
568 l_po_line_locs_rec.global_attribute13 := NULL;
569 l_po_line_locs_rec.global_attribute14 := NULL;
570 l_po_line_locs_rec.global_attribute15 := NULL;
571 l_po_line_locs_rec.global_attribute16 := NULL;
572 l_po_line_locs_rec.global_attribute17 := NULL;
573 l_po_line_locs_rec.global_attribute18 := NULL;
574 l_po_line_locs_rec.global_attribute19 := NULL;
575 l_po_line_locs_rec.global_attribute20 := NULL;
576 l_po_line_locs_rec.global_attribute_category := NULL;
577 l_po_line_locs_rec.quantity_shipped := NULL;
578 l_po_line_locs_rec.country_of_origin_code := NULL;
579 l_po_line_locs_rec.change_promised_date_reason := NULL;
580
581 l_po_line_locs_rec.matching_Basis := p_po_lines_rec.matching_basis; /* Bug 6341012 */
582
583 /* Bug 7110860 Outsourced Assembly value is 1 for Shikyu and 2 for Non- Shikyu, For IGC it should be 2*/
584 l_po_line_locs_rec.outsourced_assembly := 2;
585 END IF;
586
587 IF (p_mode = 'U') THEN
588 /* Bug 7110860 Outsourced Assembly value is 1 for Shikyu and 2 for Non- Shikyu, For IGC it should be 2*/
589 l_po_line_locs_rec.outsourced_assembly := 2;
590 END IF;
591
592 p_po_line_locs_rec := l_po_line_locs_rec;
593
594 END Initialize_Line_Locs_Row;
595
596
597 PROCEDURE Initialize_Distributions_Row(p_mode IN VARCHAR2,
598 p_encumbrance_on IN VARCHAR2,
599 p_cc_headers_rec IN igc_cc_headers%ROWTYPE,
600 p_cc_acct_lines_rec IN igc_cc_acct_lines%ROWTYPE,
601 p_cc_pmt_fcst_rec IN igc_cc_det_pf%ROWTYPE,
602 p_po_line_locs_rec IN po_line_locations_all%ROWTYPE,
603 p_po_dist_rec IN OUT NOCOPY po_distributions_all%ROWTYPE )
604 IS
605 l_po_dist_rec po_distributions_all%ROWTYPE;
606 E_CC_GL_PERIOD EXCEPTION;
607 BEGIN
608 IF (p_mode = 'I') THEN
609 SELECT po_distributions_s.nextval
610 INTO l_po_dist_rec.po_distribution_id
611 FROM DUAL;
612 ELSE
613 l_po_dist_rec := p_po_dist_rec ; /* CC */
614 END IF;
615
616 l_po_dist_rec.po_header_id := p_po_line_locs_rec.po_header_id;
617 l_po_dist_rec.po_line_id := p_po_line_locs_rec.po_line_id;
618 l_po_dist_rec.line_location_id := p_po_line_locs_rec.line_location_id;
619 l_po_dist_rec.distribution_num := p_cc_pmt_fcst_rec.cc_det_pf_line_num;
620 -- Added for PRC.FP.J, 3173178
621 -- PO Standalone patch 3205071 is a pre-req for this change
622 l_po_dist_rec.distribution_type := 'STANDARD';
623
624 IF (p_encumbrance_on = FND_API.G_TRUE) THEN
625 l_po_dist_rec.encumbered_flag := p_po_line_locs_rec.encumbered_flag;
626 l_po_dist_rec.gl_encumbered_date := p_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_date;
627 l_po_dist_rec.encumbered_amount := p_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_amt;
628 /* Removed the hard coded reference to period type of CC Month to fix bug 1427486*/
629 BEGIN
630 SELECT period_name
631 INTO l_po_dist_rec.gl_encumbered_period_name
632 FROM gl_periods gp, gl_sets_of_books gb
633 WHERE gb.set_of_books_id = p_cc_headers_rec.set_of_books_id AND
634 gp.period_set_name = gb.period_set_name AND
635 /* Begin Fix for bug 1569257 */
636 gp.adjustment_period_flag = 'N' AND
637 /* End Fix for bug 1569257 */
638 gb.accounted_period_type = gp.period_type AND
639 ( (gp.start_date <= TRUNC(l_po_dist_rec.gl_encumbered_date) ) AND
640 (gp.end_date >= TRUNC(l_po_dist_rec.gl_encumbered_date) ) );
641
642 EXCEPTION
643 WHEN NO_DATA_FOUND THEN
644 fnd_message.set_name('IGC','IGC_CC_GL_PERIOD_NOT_FOUND');
645 fnd_message.set_token('PF_LINE_NUM',p_cc_pmt_fcst_rec.cc_det_pf_line_num,TRUE);
646 fnd_msg_pub.add;
647 RAISE E_CC_GL_PERIOD;
648 END;
649 ELSE
650 l_po_dist_rec.encumbered_flag := 'N';
651 l_po_dist_rec.gl_encumbered_date := NULL;
652 l_po_dist_rec.encumbered_amount := NULL;
653 l_po_dist_rec.gl_encumbered_period_name := NULL;
654 END IF;
655
656 l_po_dist_rec.rate_date := p_cc_headers_rec.conversion_date;
657 l_po_dist_rec.rate := p_cc_headers_rec.conversion_rate;
658 l_po_dist_rec.org_id := p_cc_headers_rec.org_id;
659 l_po_dist_rec.set_of_books_id := p_cc_headers_rec.set_of_books_id;
660 l_po_dist_rec.quantity_ordered := p_cc_pmt_fcst_rec.cc_det_pf_entered_amt;
661 l_po_dist_rec.last_update_login := p_cc_pmt_fcst_rec.last_update_login;
662 l_po_dist_rec.last_update_date := p_cc_pmt_fcst_rec.last_update_date;
663 l_po_dist_rec.last_updated_by := p_cc_pmt_fcst_rec.last_updated_by;
664 l_po_dist_rec.creation_date := p_cc_pmt_fcst_rec.creation_date;
665 l_po_dist_rec.created_by := p_cc_pmt_fcst_rec.created_by;
666 l_po_dist_rec.variance_account_id := NULL;
667 l_po_dist_rec.accrual_account_id := NULL;
668 l_po_dist_rec.code_combination_id := p_cc_acct_lines_rec.cc_charge_code_combination_id;
669 l_po_dist_rec.budget_account_id := p_cc_acct_lines_rec.cc_budget_code_combination_id;
670
671 /* Begin Project accounting related columns */
672
673 l_po_dist_rec.project_id := p_cc_acct_lines_rec.project_id;
674 l_po_dist_rec.task_id := p_cc_acct_lines_rec.task_id;
675 l_po_dist_rec.expenditure_type := p_cc_acct_lines_rec.expenditure_type;
676 l_po_dist_rec.expenditure_organization_id := p_cc_acct_lines_rec.expenditure_org_id;
677 l_po_dist_rec.expenditure_item_date := p_cc_acct_lines_rec.expenditure_item_date;
678
679 IF (l_po_dist_rec.project_id IS NOT NULL) THEN
680 l_po_dist_rec.project_accounting_context := 'Y';
681 ELSE
682 l_po_dist_rec.project_accounting_context := 'N';
683 END IF;
684
685 /* End Project accounting related columns */
686
687 IF (p_mode = 'I') THEN
688 l_po_dist_rec.quantity_delivered := 0;
689 l_po_dist_rec.quantity_billed := 0;
690 l_po_dist_rec.quantity_cancelled := 0;
691 l_po_dist_rec.amount_billed := 0;
692 l_po_dist_rec.destination_type_code := 'EXPENSE';
693 l_po_dist_rec.prevent_encumbrance_flag := 'N';
694 l_po_dist_rec.destination_context := 'EXPENSE';
695 l_po_dist_rec.accrue_on_receipt_flag := 'N';
696 l_po_dist_rec.tax_recovery_override_flag := 'N';
697
698 l_po_dist_rec.po_release_id := NULL;
699 l_po_dist_rec.req_header_reference_num := p_cc_headers_rec.cc_header_id;
700 l_po_dist_rec.req_line_reference_num := p_cc_pmt_fcst_rec.cc_det_pf_line_id;
701 l_po_dist_rec.req_distribution_id := NULL;
702 l_po_dist_rec.deliver_to_location_id := NULL;
703 l_po_dist_rec.deliver_to_person_id := NULL;
704 l_po_dist_rec.accrued_flag := NULL;
705 l_po_dist_rec.unencumbered_quantity := NULL;
706 l_po_dist_rec.unencumbered_amount := NULL;
707 l_po_dist_rec.failed_funds_lookup_code := NULL;
708 l_po_dist_rec.gl_cancelled_date := NULL;
709 l_po_dist_rec.destination_organization_id := NULL;
710 l_po_dist_rec.destination_subinventory := NULL;
711 l_po_dist_rec.attribute_category := NULL;
712 l_po_dist_rec.attribute1 := NULL;
713 l_po_dist_rec.attribute2 := NULL;
714 l_po_dist_rec.attribute3 := NULL;
715 l_po_dist_rec.attribute4 := NULL;
716 l_po_dist_rec.attribute5 := NULL;
717 l_po_dist_rec.attribute6 := NULL;
718 l_po_dist_rec.attribute7 := NULL;
719 l_po_dist_rec.attribute8 := NULL;
720 l_po_dist_rec.attribute9 := NULL;
721 l_po_dist_rec.attribute10 := NULL;
722 l_po_dist_rec.attribute11 := NULL;
723 l_po_dist_rec.attribute12 := NULL;
724 l_po_dist_rec.attribute13 := NULL;
725 l_po_dist_rec.attribute14 := NULL;
726 l_po_dist_rec.attribute15 := NULL;
727 l_po_dist_rec.wip_entity_id := NULL;
728 l_po_dist_rec.wip_operation_seq_num := NULL;
729 l_po_dist_rec.wip_resource_seq_num := NULL;
730 l_po_dist_rec.wip_repetitive_schedule_id := NULL;
731 l_po_dist_rec.wip_line_id := NULL;
732 l_po_dist_rec.bom_resource_id := NULL;
733 l_po_dist_rec.ussgl_transaction_code := NULL;
734 l_po_dist_rec.government_context := NULL;
735 l_po_dist_rec.source_distribution_id := NULL;
736 l_po_dist_rec.request_id := NULL;
737 l_po_dist_rec.program_application_id := NULL;
738 l_po_dist_rec.program_id := NULL;
739 l_po_dist_rec.program_update_date := NULL;
740 l_po_dist_rec.gl_closed_date := NULL;
741 l_po_dist_rec.kanban_card_id := NULL;
742 l_po_dist_rec.award_id := NULL;
743 l_po_dist_rec.mrc_rate_date := NULL;
744 l_po_dist_rec.mrc_rate := NULL;
745 l_po_dist_rec.mrc_encumbered_amount := NULL;
746 l_po_dist_rec.mrc_unencumbered_amount := NULL;
747 l_po_dist_rec.end_item_unit_number := NULL;
748 l_po_dist_rec.recoverable_tax := NULL;
749 l_po_dist_rec.nonrecoverable_tax := NULL;
750 l_po_dist_rec.recovery_rate := NULL;
751 END IF;
752
753
754 p_po_dist_rec := l_po_dist_rec;
755
756 END Initialize_Distributions_Row;
757
758 /*-------------------------------------------------------------------------*/
759
760 /*=======================================================================+
761 | PROCEDURE Update_PO_Approved_Flag |
762 +=======================================================================*/
763
764 PROCEDURE Update_PO_Approved_Flag
765 (
766 p_api_version IN NUMBER,
767 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
768 p_commit IN VARCHAR2 := FND_API.G_FALSE,
769 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
770 x_return_status OUT NOCOPY VARCHAR2,
771 x_msg_count OUT NOCOPY NUMBER,
772 x_msg_data OUT NOCOPY VARCHAR2,
773 p_cc_header_id IN NUMBER
774 )
775 IS
776
777 l_api_name CONSTANT VARCHAR2(30) := 'Update_PO_Approved_Flag';
778 l_api_version CONSTANT NUMBER := 1.0;
779
780 l_cc_headers_rec igc_cc_headers%ROWTYPE;
781 l_cc_acct_lines_rec igc_cc_acct_lines%ROWTYPE;
782
783 l_po_headers_rec po_headers_all%ROWTYPE;
784 l_po_lines_rec po_lines_all%ROWTYPE;
785 l_po_line_locs_rec po_line_locations_all%ROWTYPE;
786
787 l_po_found BOOLEAN;
788 l_new_account_line BOOLEAN;
789 l_encumbrance_on VARCHAR2(1);
790
791 l_msg_data VARCHAR2(1000);
792 l_error_message VARCHAR2(1000);
793 l_msg_count NUMBER;
794 l_return_status VARCHAR2(1);
795
796 e_cc_not_found EXCEPTION;
797 e_po_not_found EXCEPTION;
798 e_cc_type EXCEPTION;
799 e_cc_state EXCEPTION;
800 e_line_locations EXCEPTION;
801 e_internal_error EXCEPTION;
802 e_unable_to_open_po EXCEPTION;
803
804 l_start_date gl_periods.start_date%TYPE;
805 l_end_date gl_periods.end_date%TYPE;
806 l_curr_year_pf_lines NUMBER;
807
808 /* Start Date and End Date of current fiscal year for set of books
809 indicated by p_sob_id */
810
811 CURSOR c_fiscal_year_dates(p_sob_id NUMBER)
812 IS
813 SELECT MIN(start_date) start_date, MAX(end_date) end_date
814 FROM GL_PERIODS GP,
815 GL_SETS_OF_BOOKS GB
816 WHERE
817 GP.period_set_name = GB.period_set_name AND
818 GP.period_type = GB.accounted_period_type AND
819 GB.set_of_books_id = p_sob_id AND
820 TO_CHAR(start_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
821 TO_CHAR(end_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
822 GP.adjustment_period_flag = 'N';
823
824 /* Contract Commitment account lines */
825
826 CURSOR c_account_lines(t_cc_header_id NUMBER) IS
827 SELECT *
828 FROM igc_cc_acct_lines
829 WHERE cc_header_id = t_cc_header_id;
830
831 BEGIN
832 -- Bug 3605536 GSCC Warnings fixed
833
834 l_po_found := FALSE;
835 l_new_account_line := FALSE;
836 l_encumbrance_on := FND_API.G_TRUE;
837 l_curr_year_pf_lines := 0;
838
839 SAVEPOINT Update_PO_Approved_Flag;
840
841 IF FND_API.to_Boolean(p_init_msg_list)
842 THEN
843 FND_MSG_PUB.initialize;
844 END IF;
845
846 IF NOT FND_API.Compatible_API_Call(l_api_version,
847 p_api_version,
848 l_api_name,
849 G_PKG_NAME)
850 THEN
851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
852 END IF;
853
854
855 x_return_status := FND_API.G_RET_STS_SUCCESS;
856 l_return_status := FND_API.G_RET_STS_SUCCESS;
857 l_msg_data := NULL;
858 l_msg_count := 0;
859
860 /* Check whether Contract Commitment exists or not */
861
862 BEGIN
863
864 SELECT * INTO l_cc_headers_rec
865 FROM igc_cc_headers
866 WHERE cc_header_id = p_cc_header_id;
867
868 EXCEPTION
869 WHEN no_data_found
870 THEN
871 fnd_message.set_name('IGC','IGC_CC_NOT_FOUND');
872 fnd_message.set_token('CC_NUM',to_char(p_cc_header_id),TRUE);
873 fnd_msg_pub.add;
874 RAISE E_CC_NOT_FOUND;
875 END;
876
877 IF ( (l_cc_headers_rec.cc_type IS NOT NULL) AND
878 (l_cc_headers_rec.cc_type = 'C') )
879 THEN
880 fnd_message.set_name('IGC','IGC_CC_INVALID_CC_TYPE');
881 fnd_message.set_token('CC_NUM',l_cc_headers_rec.cc_num,TRUE);
882 fnd_msg_pub.add;
883 RAISE e_cc_type;
884 END IF;
885
886 IF ((l_cc_headers_rec.cc_state IS NOT NULL) AND
887 ((l_cc_headers_rec.cc_state = 'CL') OR (l_cc_headers_rec.cc_state = 'PR')))
888 THEN
889 fnd_message.set_name('IGC','IGC_CC_INVALID_CC_STATE');
890 fnd_message.set_token('CC_NUM',l_cc_headers_rec.cc_num,TRUE);
891 fnd_msg_pub.add;
892 RAISE e_cc_state;
893 END IF;
894
895 /* Begin fix for bug 1715221 */
896 /* Get the start date and end date of current fiscal year */
897 OPEN c_fiscal_year_dates(l_cc_headers_rec.set_of_books_id);
898 FETCH c_fiscal_year_dates INTO l_start_date, l_end_date;
899 CLOSE c_fiscal_year_dates;
900
901 /* Check whether current fiscal year payment forecast lines exist in CC */
902
903 BEGIN
904 l_curr_year_pf_lines := 0;
905
906 SELECT count(cc_det_pf_line_id)
907 INTO l_curr_year_pf_lines
908 FROM igc_cc_det_pf a, igc_cc_acct_lines b, igc_cc_headers c
909 WHERE
910 NVL(a.cc_det_pf_date,l_end_date + 1) <= l_end_date AND
911 a.cc_acct_line_id = b.cc_acct_line_id AND
912 b.cc_header_id = c.cc_header_id AND
913 c.cc_header_id = p_cc_header_id;
914
915 EXCEPTION
916 WHEN NO_DATA_FOUND
917 THEN
918 l_curr_year_pf_lines := 0;
919 END;
920
921 IF (l_curr_year_pf_lines = 0)
922 THEN
923 fnd_message.set_name('IGC','IGC_CC_UNABLE_TO_OPEN_PO');
924 fnd_message.set_token('CC_NUM',l_cc_headers_rec.cc_num,TRUE);
925 fnd_msg_pub.add;
926 RAISE e_unable_to_open_po;
927 ELSIF (l_curr_year_pf_lines > 0)
928 THEN
929 /* Check whether Contract Commitment is already populated in PO Tables */
930 l_po_found := TRUE;
931
932 BEGIN
933
934 SELECT * INTO l_po_headers_rec
935 FROM PO_HEADERS_ALL
936 WHERE segment1 = l_cc_headers_rec.cc_num AND
937 type_lookup_code = 'STANDARD' AND
938 org_id = l_cc_headers_rec.org_id;
939 EXCEPTION
940 WHEN no_data_found
941 THEN
942 fnd_message.set_name('IGC','IGC_CC_PO_NOT_FOUND');
943 fnd_message.set_token('CC_NUM',l_cc_headers_rec.cc_num,TRUE);
944 fnd_msg_pub.add;
945 RAISE e_po_not_found;
946 END;
947 END IF;
948 /* End fix for bug 1715221 */
949
950 /* Check whether encumbrance is turned on */
951
952 IGC_CC_BUDGETARY_CTRL_PKG.Check_Budgetary_Ctrl_On
953 (
954 1.0,
955 FND_API.G_FALSE,
956 FND_API.G_VALID_LEVEL_FULL,
957 l_return_status,
958 l_msg_count,
959 l_msg_data ,
960 l_cc_headers_rec.org_id,
961 l_cc_headers_rec.set_of_books_id,
962 l_cc_headers_rec.cc_state,
963 l_encumbrance_on
964 );
965
966 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
967 THEN
968 RAISE e_internal_error;
969 END IF;
970
971 /* Corresponding PO exists */
972
973 IF (l_po_found)
974 THEN
975
976 /* Contract Commitment in Confirmed State and approved
977 and document control status has changed from ENTERED->OPENED, OPENED->ON HOLD,
978 OPENED->CLOSED, CLOSED->OPENED */
979
980 IF (l_cc_headers_rec.cc_apprvl_status = 'AP') AND
981 (l_cc_headers_rec.cc_state = 'CM') AND
982 (
983 ((l_encumbrance_on = FND_API.G_TRUE) AND (l_cc_headers_rec.cc_encmbrnc_status = 'C')) OR
984 ((l_encumbrance_on = FND_API.G_FALSE) AND (l_cc_headers_rec.cc_encmbrnc_status = 'N'))
985 )
986 THEN
987 IF (l_cc_headers_rec.cc_ctrl_status = 'O')
988 THEN
989 l_po_headers_rec.authorization_status := 'APPROVED';
990 l_po_headers_rec.approved_flag := 'Y';
991 l_po_headers_rec.approved_date := sysdate;
992 ELSE
993 l_po_headers_rec.authorization_status := NULL;
994 l_po_headers_rec.approved_flag := 'N';
995 l_po_headers_rec.approved_date := NULL;
996 END IF;
997 END IF;
998
999 /* Contract Commitment has been Changed in Confirmed state
1000 and approval status is Requires Reapproval*/
1001
1002 IF (l_cc_headers_rec.cc_apprvl_status <> 'AP') AND
1003 (l_cc_headers_rec.cc_state = 'CM')
1004 THEN
1005 l_po_headers_rec.authorization_status := NULL;
1006 l_po_headers_rec.approved_flag := 'N';
1007 l_po_headers_rec.approved_date := NULL;
1008 END IF;
1009
1010 /* Contract Commitment in Complete state*/
1011 IF (l_cc_headers_rec.cc_state = 'CT')
1012 THEN
1013 l_po_headers_rec.authorization_status := NULL;
1014 l_po_headers_rec.approved_flag := 'N';
1015 l_po_headers_rec.approved_date := NULL;
1016 END IF;
1017
1018 IGC_CC_PO_HEADERS_ALL_PVT.Update_Row(1.0,
1019 FND_API.G_FALSE,
1020 FND_API.G_FALSE,
1021 FND_API.G_VALID_LEVEL_NONE,
1022 l_return_status,
1023 l_msg_count,
1024 l_msg_data,
1025 l_po_headers_rec);
1026
1027 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1028 THEN
1029 RAISE e_internal_error;
1030 END IF;
1031
1032 OPEN c_account_lines(p_cc_header_id);
1033
1034 LOOP
1035
1036 FETCH c_account_lines INTO l_cc_acct_lines_rec;
1037
1038 EXIT WHEN c_account_lines%NOTFOUND;
1039
1040 /* Check whether it is a new account line */
1041
1042 l_new_account_line := FALSE;
1043
1044 BEGIN
1045
1046 SELECT * INTO l_po_lines_rec
1047 FROM po_lines_all pol
1048 WHERE
1049 pol.po_header_id = l_po_headers_rec.po_header_id AND
1050 pol.line_num = l_cc_acct_lines_rec.cc_acct_line_num;
1051 EXCEPTION
1052 WHEN NO_DATA_FOUND
1053 THEN
1054 l_new_account_line := TRUE;
1055 END;
1056
1057 IF (l_new_account_line = FALSE)
1058 THEN
1059 BEGIN
1060
1061 SELECT * INTO l_po_line_locs_rec
1062 FROM po_line_locations_all pll
1063 WHERE
1064 pll.po_header_id = l_po_headers_rec.po_header_id AND
1065 pll.po_line_id = l_po_lines_rec.po_line_id;
1066 EXCEPTION
1067 WHEN NO_DATA_FOUND
1068 THEN
1069 RAISE e_line_locations;
1070 END;
1071 END IF;
1072
1073 /* Existing account line */
1074 IF ( NOT l_new_account_line)
1075 THEN
1076 l_po_line_locs_rec.approved_flag := l_po_headers_rec.approved_flag;
1077 l_po_line_locs_rec.approved_date := l_po_headers_rec.approved_date;
1078 /* Bug 7110860 Outsourced Assembly value is 1 for Shikyu and 2 for Non- Shikyu, For IGC it should be 2*/
1079 IF (l_po_line_locs_rec.outsourced_assembly IS NULL) THEN
1080 l_po_line_locs_rec.outsourced_assembly := 2;
1081 END IF;
1082
1083 IGC_CC_PO_LINE_LOCS_ALL_PVT.Update_Row(1.0,
1084 FND_API.G_FALSE,
1085 FND_API.G_FALSE,
1086 FND_API.G_VALID_LEVEL_NONE,
1087 l_return_status,
1088 l_msg_count,
1089 l_msg_data,
1090 l_po_line_locs_rec);
1091
1092 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1093 THEN
1094 RAISE e_internal_error;
1095 END IF;
1096
1097 END IF;
1098 /* Existing account line */
1099
1100 END LOOP;
1101 /* Account_Lines_Cursor */
1102
1103 CLOSE c_account_lines;
1104
1105 END IF; /* PO Exisists */
1106
1107 IF FND_API.To_Boolean(p_commit)
1108 THEN
1109 COMMIT WORK;
1110 END IF;
1111
1112 EXCEPTION
1113
1114 WHEN E_INTERNAL_ERROR
1115 THEN
1116 ROLLBACK TO Update_PO_Approved_Flag;
1117 x_return_status := l_return_status;
1118 x_msg_count := l_msg_count;
1119 x_msg_data := l_msg_data;
1120
1121 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1122 THEN
1123 ROLLBACK TO Update_PO_Approved_Flag;
1124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1125
1126 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1127 p_data => x_msg_data );
1128
1129 WHEN E_CC_NOT_FOUND OR E_CC_STATE OR E_CC_TYPE OR E_PO_NOT_FOUND OR E_LINE_LOCATIONS OR E_UNABLE_TO_OPEN_PO
1130 THEN
1131 ROLLBACK TO Update_PO_Approved_Flag;
1132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1133
1134 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1135 p_data => x_msg_data );
1136
1137 WHEN OTHERS
1138 THEN
1139 ROLLBACK TO Update_PO_Approved_Flag;
1140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1141
1142 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1143 THEN
1144 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1145 l_api_name);
1146 END IF;
1147
1148 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1149 p_data => x_msg_data );
1150 END Update_PO_Approved_Flag;
1151
1152 /*=======================================================================+
1153 | PROCEDURE Convert_CC_To_PO |
1154 +=======================================================================*/
1155
1156 PROCEDURE Convert_CC_To_PO
1157 (
1158 p_api_version IN NUMBER,
1159 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1160 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1161 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1162 x_return_status OUT NOCOPY VARCHAR2,
1163 x_msg_count OUT NOCOPY NUMBER,
1164 x_msg_data OUT NOCOPY VARCHAR2,
1165 p_cc_header_id IN NUMBER
1166 )
1167 IS
1168
1169 l_api_name CONSTANT VARCHAR2(30) := 'Convert_CC_To_PO';
1170 l_api_version CONSTANT NUMBER := 1.0;
1171
1172 l_cc_headers_rec igc_cc_headers%ROWTYPE;
1173 l_cc_acct_lines_rec igc_cc_acct_lines%ROWTYPE;
1174 l_cc_pmt_fcst_rec igc_cc_det_pf%ROWTYPE;
1175
1176 l_po_headers_rec po_headers_all%ROWTYPE;
1177 l_po_lines_rec po_lines_all%ROWTYPE;
1178 l_po_line_locs_rec po_line_locations_all%ROWTYPE;
1179 l_po_dist_rec po_distributions_all%ROWTYPE;
1180
1181 l_encumbered_period_name gl_periods.period_name%TYPE;
1182
1183 l_new_account_line BOOLEAN;
1184
1185 l_new_payment_forecast_line BOOLEAN;
1186
1187 l_po_found BOOLEAN;
1188 l_encumbrance_on VARCHAR2(1);
1189
1190 l_msg_data VARCHAR2(1000);
1191 l_error_message VARCHAR2(1000);
1192 l_msg_count NUMBER;
1193 l_return_status VARCHAR2(1);
1194
1195
1196 e_cc_not_found EXCEPTION;
1197 e_cc_type EXCEPTION;
1198 e_cc_state EXCEPTION;
1199 e_cc_not_encumbered EXCEPTION;
1200 e_cc_not_approved EXCEPTION;
1201 e_line_locations EXCEPTION;
1202 e_internal_error EXCEPTION;
1203
1204
1205 l_start_date gl_periods.start_date%TYPE;
1206 l_end_date gl_periods.end_date%TYPE;
1207 l_curr_year_pf_lines NUMBER;
1208
1209 /* Start Date and End Date of current fiscal year for set of books
1210 indicated by p_sob_id */
1211
1212 CURSOR c_fiscal_year_dates(p_sob_id NUMBER)
1213 IS
1214 SELECT MIN(start_date) start_date, MAX(end_date) end_date
1215 FROM GL_PERIODS GP,
1216 GL_SETS_OF_BOOKS GB
1217 WHERE
1218 GP.period_set_name = GB.period_set_name AND
1219 GP.period_type = GB.accounted_period_type AND
1220 GB.set_of_books_id = p_sob_id AND
1221 TO_CHAR(start_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
1222 TO_CHAR(end_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
1223 GP.adjustment_period_flag = 'N';
1224
1225 /* Contract Commitment detail payment forecast */
1226 CURSOR c_payment_forecast(t_cc_acct_line_id NUMBER) IS
1227 SELECT *
1228 FROM igc_cc_det_pf
1229 WHERE cc_acct_line_id = t_cc_acct_line_id;
1230
1231 /* Contract Commitment account lines */
1232
1233 CURSOR c_account_lines(t_cc_header_id NUMBER) IS
1234 SELECT *
1235 FROM igc_cc_acct_lines ccac
1236 WHERE ccac.cc_header_id = t_cc_header_id;
1237
1238
1239 BEGIN
1240
1241 --Bug 3605536 GSCC Warnings fixed
1242 l_encumbered_period_name := 'MAY-01';
1243 l_curr_year_pf_lines := 0;
1244 l_encumbrance_on := FND_API.G_FALSE;
1245 l_new_account_line := FALSE;
1246 l_new_payment_forecast_line := FALSE;
1247 l_po_found := FALSE;
1248
1249 SAVEPOINT Convert_CC_To_PO;
1250
1251 IF FND_API.to_Boolean(p_init_msg_list)
1252 THEN
1253 FND_MSG_PUB.initialize;
1254 END IF;
1255
1256 IF NOT FND_API.Compatible_API_Call(l_api_version,
1257 p_api_version,
1258 l_api_name,
1259 G_PKG_NAME)
1260 THEN
1261 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1262 END IF;
1263
1264 x_return_status := FND_API.G_RET_STS_SUCCESS;
1265 l_return_status := FND_API.G_RET_STS_SUCCESS;
1266 l_msg_data := NULL;
1267 l_msg_count := 0;
1268
1269
1270 /* Check whether Contract Commitment exists or not */
1271
1272 BEGIN
1273
1274 SELECT * INTO l_cc_headers_rec
1275 FROM igc_cc_headers
1276 WHERE cc_header_id = p_cc_header_id;
1277
1278 EXCEPTION
1279 WHEN no_data_found
1280 THEN
1281 fnd_message.set_name('IGC','IGC_CC_NOT_FOUND');
1282 fnd_message.set_token('CC_NUM',to_char(p_cc_header_id),TRUE);
1283 fnd_msg_pub.add;
1284 RAISE E_CC_NOT_FOUND;
1285 END;
1286
1287 IF ( (l_cc_headers_rec.cc_type IS NOT NULL) AND
1288 (l_cc_headers_rec.cc_type = 'C') )
1289 THEN
1290 fnd_message.set_name('IGC','IGC_CC_INVALID_CC_TYPE');
1291 fnd_message.set_token('CC_NUM',l_cc_headers_rec.cc_num,TRUE);
1292 fnd_msg_pub.add;
1293 RAISE e_cc_type;
1294 END IF;
1295
1296 IF ((l_cc_headers_rec.cc_state IS NOT NULL) AND
1297 ((l_cc_headers_rec.cc_state = 'CL') OR (l_cc_headers_rec.cc_state = 'PR')))
1298 THEN
1299 fnd_message.set_name('IGC','IGC_CC_INVALID_CC_STATE');
1300 fnd_message.set_token('CC_NUM',l_cc_headers_rec.cc_num,TRUE);
1301 fnd_msg_pub.add;
1302 RAISE e_cc_state;
1303 END IF;
1304
1305 /* Check whether encumbrance is turned on */
1306
1307 IGC_CC_BUDGETARY_CTRL_PKG.Check_Budgetary_Ctrl_On
1308 (
1309 1.0,
1310 FND_API.G_FALSE,
1311 FND_API.G_VALID_LEVEL_FULL,
1312 l_return_status,
1313 l_msg_count,
1314 l_msg_data ,
1315 l_cc_headers_rec.org_id,
1316 l_cc_headers_rec.set_of_books_id,
1317 l_cc_headers_rec.cc_state,
1318 l_encumbrance_on
1319 );
1320
1321 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1322 THEN
1323 RAISE e_internal_error;
1324 END IF;
1325
1326 /* Check whether contract commitment is Approved */
1327
1328 IF (l_cc_headers_rec.cc_state = 'CM')
1329 THEN
1330 IF (l_cc_headers_rec.cc_apprvl_status = 'AP')
1331 THEN
1332 /* Check whether budetary control has been turned on */
1333 IF (l_encumbrance_on = FND_API.G_TRUE)
1334 THEN
1335 /* fix for bug 1567120 */
1336 IF (l_cc_headers_rec.cc_type = 'S') OR (l_cc_headers_rec.cc_type = 'R')
1337 THEN
1338 /* Check whether Contract Commitment is encumbered */
1339 IF (l_cc_headers_rec.cc_encmbrnc_status <> 'C')
1340 THEN
1341 fnd_message.set_name('IGC','IGC_CC_CC_NOT_ENCUMBERED');
1342 fnd_message.set_token('CC_NUM',l_cc_headers_rec.cc_num,TRUE);
1343 fnd_msg_pub.add;
1344 RAISE e_cc_not_encumbered;
1345 END IF;
1346 END IF;
1347 END IF;
1348 ELSE
1349 /* Contract Commitment is not approved */
1350 fnd_message.set_name('IGC','IGC_CC_CC_NOT_APPROVED');
1351 fnd_message.set_token('CC_NUM',l_cc_headers_rec.cc_num,TRUE);
1352 fnd_msg_pub.add;
1353 RAISE e_cc_not_approved;
1354 END IF;
1355 END IF;
1356
1357 /* Get the start date and end date of current fiscal year */
1358 OPEN c_fiscal_year_dates(l_cc_headers_rec.set_of_books_id);
1359 FETCH c_fiscal_year_dates INTO l_start_date, l_end_date;
1360 CLOSE c_fiscal_year_dates;
1361
1362 /* Check whether Contract Commitment is already populated in PO Tables */
1363
1364 l_po_found := TRUE;
1365
1366 BEGIN
1367
1368 SELECT * INTO l_po_headers_rec
1369 FROM PO_HEADERS_ALL
1370 WHERE segment1 = l_cc_headers_rec.cc_num AND
1371 type_lookup_code = 'STANDARD' AND
1372 org_id = l_cc_headers_rec.org_id;
1373
1374 EXCEPTION
1375 WHEN no_data_found
1376 THEN
1377 l_po_found := FALSE;
1378 END;
1379
1380 IF (l_po_found = FALSE)
1381 THEN
1382
1383 /* Check whether current fiscal year payment forecast lines exist in CC */
1384
1385 BEGIN
1386 l_curr_year_pf_lines := 0;
1387
1388 /* begin fix for bug 1578214 */
1389
1390 SELECT count(cc_det_pf_line_id)
1391 INTO l_curr_year_pf_lines
1392 FROM igc_cc_det_pf a, igc_cc_acct_lines b, igc_cc_headers c
1393 WHERE
1394 NVL(a.cc_det_pf_date,l_end_date + 1) <= l_end_date AND
1395 a.cc_acct_line_id = b.cc_acct_line_id AND
1396 b.cc_header_id = c.cc_header_id AND
1397 c.cc_header_id = p_cc_header_id;
1398
1399 /* end fix for bug 1578214 */
1400
1401 EXCEPTION
1402 WHEN NO_DATA_FOUND
1403 THEN
1404 l_curr_year_pf_lines := 0;
1405 END;
1406 END IF;
1407
1408
1409 IF (l_curr_year_pf_lines = 0) AND (l_po_found = FALSE)
1410 THEN
1411 RETURN;
1412 END IF;
1413
1414
1415 /* Corresponding PO exists */
1416
1417 IF (l_po_found)
1418 THEN
1419 Initialize_Header_Row('U',l_encumbrance_on, l_cc_headers_rec, l_po_headers_rec);
1420 IGC_CC_PO_HEADERS_ALL_PVT.Update_Row(1.0,
1421 FND_API.G_FALSE,
1422 FND_API.G_FALSE,
1423 FND_API.G_VALID_LEVEL_NONE,
1424 l_return_status,
1425 l_msg_count,
1426 l_msg_data,
1427 l_po_headers_rec);
1428
1429 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1430 THEN
1431 RAISE e_internal_error;
1432 END IF;
1433
1434 /* Find the changes made to Contract Commitment */
1435
1436 OPEN c_account_lines(p_cc_header_id);
1437
1438 LOOP
1439
1440 FETCH c_account_lines INTO l_cc_acct_lines_rec;
1441
1442 EXIT WHEN c_account_lines%NOTFOUND;
1443
1444 /* Check whether it is a new account line */
1445
1446 l_new_account_line := FALSE;
1447
1448 BEGIN
1449
1450 SELECT * INTO l_po_lines_rec
1451 FROM po_lines_all pol
1452 WHERE
1453 pol.po_header_id = l_po_headers_rec.po_header_id AND
1454 pol.line_num = l_cc_acct_lines_rec.cc_acct_line_num;
1455 EXCEPTION
1456 WHEN NO_DATA_FOUND
1457 THEN
1458 l_new_account_line := TRUE;
1459 END;
1460
1461 IF (l_new_account_line = FALSE)
1462 THEN
1463 BEGIN
1464
1465 SELECT *
1466 INTO l_po_line_locs_rec
1467 FROM po_line_locations_all pll
1468 WHERE pll.po_header_id = l_po_headers_rec.po_header_id
1469 AND pll.po_line_id = l_po_lines_rec.po_line_id;
1470 EXCEPTION
1471 WHEN NO_DATA_FOUND
1472 THEN
1473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1474 END;
1475 END IF;
1476
1477 BEGIN
1478 l_curr_year_pf_lines := 0;
1479
1480 SELECT count(*)
1481 INTO l_curr_year_pf_lines
1482 FROM igc_cc_det_pf b
1483 WHERE
1484 /* commented this part of code to fix bug 1576123 ( cc_det_pf_date >= l_start_date
1485 AND */
1486 (cc_det_pf_date <= l_end_date ) AND
1487 b.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
1488 EXCEPTION
1489 WHEN NO_DATA_FOUND
1490 THEN
1491 l_curr_year_pf_lines := 0;
1492 END;
1493
1494 /* New account line */
1495 IF (l_new_account_line) AND (l_curr_year_pf_lines > 0 )
1496 THEN
1497 Initialize_Lines_Row('I',
1498 l_po_headers_rec.po_header_id,
1499 l_cc_headers_rec.org_id,
1500 l_cc_acct_lines_rec,
1501 l_po_lines_rec,
1502 l_start_date,
1503 l_end_date);
1504
1505 IGC_CC_PO_LINES_ALL_PVT.Insert_Row(1.0,
1506 FND_API.G_FALSE,
1507 FND_API.G_FALSE,
1508 FND_API.G_VALID_LEVEL_NONE,
1509 l_return_status,
1510 l_msg_count,
1511 l_msg_data,
1512 l_po_lines_rec);
1513
1514 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1515 THEN
1516 RAISE e_internal_error;
1517 END IF;
1518
1519 Initialize_Line_Locs_Row('I',
1520 l_encumbrance_on,
1521 l_po_headers_rec,
1522 l_po_lines_rec,
1523 l_po_line_locs_rec);
1524
1525 IGC_CC_PO_LINE_LOCS_ALL_PVT.Insert_Row(1.0,
1526 FND_API.G_FALSE,
1527 FND_API.G_FALSE,
1528 FND_API.G_VALID_LEVEL_NONE,
1529 l_return_status,
1530 l_msg_count,
1531 l_msg_data,
1532 l_po_line_locs_rec);
1533
1534 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1535 THEN
1536 RAISE e_internal_error;
1537 END IF;
1538
1539 OPEN c_payment_forecast(l_cc_acct_lines_rec.cc_acct_line_id);
1540
1541 LOOP
1542 FETCH c_payment_forecast INTO l_cc_pmt_fcst_rec;
1543
1544 EXIT WHEN c_payment_forecast%NOTFOUND;
1545
1546 /* Current year payment forecast line */
1547 IF
1548 /* Commented this part of code to fix bug 1576123
1549 ( l_cc_pmt_fcst_rec.cc_det_pf_date >= l_start_date )
1550 AND */
1551 ( l_cc_pmt_fcst_rec.cc_det_pf_date <= l_end_date )
1552 THEN
1553
1554 Initialize_Distributions_Row('I',
1555 l_encumbrance_on,
1556 l_cc_headers_rec,
1557 l_cc_acct_lines_rec,
1558 l_cc_pmt_fcst_rec,
1559 l_po_line_locs_rec,
1560 l_po_dist_rec);
1561
1562 IGC_CC_PO_DIST_ALL_PVT.Insert_Row(1.0,
1563 FND_API.G_FALSE,
1564 FND_API.G_FALSE,
1565 FND_API.G_VALID_LEVEL_NONE,
1566 l_return_status,
1567 l_msg_count,
1568 l_msg_data,
1569 l_po_dist_rec);
1570
1571 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1572 THEN
1573 RAISE e_internal_error;
1574 END IF;
1575 END IF;
1576 END LOOP; /* payment forecast cursor */
1577
1578 CLOSE c_payment_forecast;
1579
1580 END IF; /* New account line */
1581
1582
1583 /* Existing account line */
1584
1585 IF ( NOT l_new_account_line)
1586 THEN
1587 Initialize_Lines_Row('U',
1588 l_po_headers_rec.po_header_id,
1589 l_cc_headers_rec.org_id,
1590 l_cc_acct_lines_rec,
1591 l_po_lines_rec,
1592 l_start_date,
1593 l_end_date);
1594
1595 IGC_CC_PO_LINES_ALL_PVT.Update_Row(1.0,
1596 FND_API.G_FALSE,
1597 FND_API.G_FALSE,
1598 FND_API.G_VALID_LEVEL_NONE,
1599 l_return_status,
1600 l_msg_count,
1601 l_msg_data,
1602 l_po_lines_rec);
1603 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1604 THEN
1605 RAISE e_internal_error;
1606 END IF;
1607
1608 Initialize_Line_Locs_Row('U',
1609 l_encumbrance_on,
1610 l_po_headers_rec,
1611 l_po_lines_rec,
1612 l_po_line_locs_rec);
1613
1614 IGC_CC_PO_LINE_LOCS_ALL_PVT.Update_Row(1.0,
1615 FND_API.G_FALSE,
1616 FND_API.G_FALSE,
1617 FND_API.G_VALID_LEVEL_NONE,
1618 l_return_status,
1619 l_msg_count,
1620 l_msg_data,
1621 l_po_line_locs_rec);
1622
1623 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1624 THEN
1625 RAISE e_internal_error;
1626 END IF;
1627
1628 l_new_payment_forecast_line := FALSE;
1629
1630 OPEN c_payment_forecast(l_cc_acct_lines_rec.cc_acct_line_id);
1631
1632 LOOP
1633 FETCH c_payment_forecast INTO l_cc_pmt_fcst_rec;
1634 EXIT WHEN c_payment_forecast%NOTFOUND;
1635 /* Check PO_DISTRIBUTIONS_ALL for the record */
1636 l_new_payment_forecast_line := FALSE;
1637
1638 BEGIN
1639
1640 SELECT *
1641 INTO l_po_dist_rec
1642 FROM
1643 po_distributions_all pod
1644 WHERE
1645 pod.po_header_id = l_po_headers_rec.po_header_id AND
1646 pod.po_line_id = l_po_lines_rec.po_line_id AND
1647 pod.line_location_id = l_po_line_locs_rec.line_location_id AND
1648 pod.distribution_num = l_cc_pmt_fcst_rec.cc_det_pf_line_num;
1649
1650 EXCEPTION
1651 WHEN no_data_found
1652 THEN
1653 l_new_payment_forecast_line := TRUE;
1654 END;
1655
1656
1657 /* New payment forecast record */
1658
1659 IF (l_new_payment_forecast_line)
1660 THEN
1661 /* Current year payment forecast line */
1662 IF
1663 /* Commented this part of code to fix bug 1576123
1664 ( l_cc_pmt_fcst_rec.cc_det_pf_date >= l_start_date ) AND */
1665 ( l_cc_pmt_fcst_rec.cc_det_pf_date <= l_end_date )
1666 THEN
1667 Initialize_Distributions_Row('I',
1668 l_encumbrance_on,
1669 l_cc_headers_rec,
1670 l_cc_acct_lines_rec,
1671 l_cc_pmt_fcst_rec,
1672 l_po_line_locs_rec,
1673 l_po_dist_rec);
1674
1675 IGC_CC_PO_DIST_ALL_PVT.Insert_Row(1.0,
1676 FND_API.G_FALSE,
1677 FND_API.G_FALSE,
1678 FND_API.G_VALID_LEVEL_NONE,
1679 l_return_status,
1680 l_msg_count,
1681 l_msg_data,
1682 l_po_dist_rec);
1683
1684
1685 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1686 THEN
1687 RAISE e_internal_error;
1688 END IF;
1689 END IF;
1690
1691 END IF; /* New payment forecast cursor */
1692
1693 /* Existing payment forecast record */
1694 IF ( NOT l_new_payment_forecast_line)
1695 THEN
1696 Initialize_Distributions_Row(
1697 'U',
1698 l_encumbrance_on,
1699 l_cc_headers_rec,
1700 l_cc_acct_lines_rec,
1701 l_cc_pmt_fcst_rec,
1702 l_po_line_locs_rec,
1703 l_po_dist_rec);
1704
1705 IGC_CC_PO_DIST_ALL_PVT.Update_Row(1.0,
1706 FND_API.G_FALSE,
1707 FND_API.G_FALSE,
1708 FND_API.G_VALID_LEVEL_NONE,
1709 l_return_status,
1710 l_msg_count,
1711 l_msg_data,
1712 l_po_dist_rec);
1713
1714 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1715 THEN
1716 RAISE e_internal_error;
1717 END IF;
1718
1719 END IF; /* Existing payment forecast record */
1720
1721 END LOOP; /* payment forecast cursor */
1722
1723 CLOSE c_payment_forecast;
1724 END IF; /* Existing account line */
1725 END LOOP; /* Account_Lines_Cursor */
1726 CLOSE c_account_lines;
1727
1728 END IF; /* PO Exisists */
1729
1730 /* PO does not exist */
1731
1732 IF ( not l_po_found ) AND (l_curr_year_pf_lines > 0)
1733 THEN
1734 /* Insert row into PO_HEADERS_ALL */
1735
1736 Initialize_Header_Row('I',l_encumbrance_on, l_cc_headers_rec, l_po_headers_rec);
1737
1738 IGC_CC_PO_HEADERS_ALL_PVT.Insert_Row(1.0,
1739 FND_API.G_FALSE,
1740 FND_API.G_FALSE,
1741 FND_API.G_VALID_LEVEL_NONE,
1742 l_return_status,
1743 l_msg_count,
1744 l_msg_data,
1745 l_po_headers_rec);
1746
1747 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1748 THEN
1749 RAISE e_internal_error;
1750 END IF;
1751
1752 /* Process Account Lines */
1753
1754 OPEN c_account_lines(p_cc_header_id);
1755
1756 LOOP
1757
1758 FETCH c_account_lines INTO l_cc_acct_lines_rec;
1759
1760 EXIT WHEN c_account_lines%NOTFOUND;
1761
1762 BEGIN
1763 l_curr_year_pf_lines := 0;
1764
1765 SELECT count(*)
1766 INTO l_curr_year_pf_lines
1767 FROM igc_cc_det_pf b
1768 WHERE
1769 /* commented this part of code to fix bug 1576123 ( cc_det_pf_date >= l_start_date AND */
1770 (cc_det_pf_date <= l_end_date ) AND
1771 b.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
1772 EXCEPTION
1773 WHEN NO_DATA_FOUND
1774 THEN
1775 l_curr_year_pf_lines := 0;
1776 END;
1777
1778 IF (l_curr_year_pf_lines > 0)
1779 THEN
1780 /* Insert row into PO_LINES_ALL */
1781
1782 Initialize_Lines_Row('I',
1783 l_po_headers_rec.po_header_id,
1784 l_cc_headers_rec.org_id,
1785 l_cc_acct_lines_rec,
1786 l_po_lines_rec,
1787 l_start_date,
1788 l_end_date);
1789 IGC_CC_PO_LINES_ALL_PVT.Insert_Row(1.0,
1790 FND_API.G_FALSE,
1791 FND_API.G_FALSE,
1792 FND_API.G_VALID_LEVEL_NONE,
1793 l_return_status,
1794 l_msg_count,
1795 l_msg_data,
1796 l_po_lines_rec);
1797
1798 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1799 THEN
1800 RAISE e_internal_error;
1801 END IF;
1802
1803 /* Insert row into PO_LINE_LOCATIONS_ALL */
1804
1805 Initialize_Line_Locs_Row('I',
1806 l_encumbrance_on,
1807 l_po_headers_rec,
1808 l_po_lines_rec,
1809 l_po_line_locs_rec);
1810
1811 IGC_CC_PO_LINE_LOCS_ALL_PVT.Insert_Row(1.0,
1812 FND_API.G_FALSE,
1813 FND_API.G_FALSE,
1814 FND_API.G_VALID_LEVEL_NONE,
1815 l_return_status,
1816 l_msg_count,
1817 l_msg_data,
1818 l_po_line_locs_rec);
1819
1820 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1821 THEN
1822 RAISE e_internal_error;
1823 END IF;
1824
1825 /* Process payment forecast lines */
1826
1827 OPEN c_payment_forecast(l_cc_acct_lines_rec.cc_acct_line_id);
1828
1829 LOOP
1830 FETCH c_payment_forecast INTO l_cc_pmt_fcst_rec;
1831
1832 EXIT WHEN c_payment_forecast%NOTFOUND;
1833
1834 /* Current year payment forecast line */
1835 IF
1836 /* Commented this part of the code to fix bug 1576123
1837 ( l_cc_pmt_fcst_rec.cc_det_pf_date >= l_start_date )
1838 AND */
1839
1840 ( l_cc_pmt_fcst_rec.cc_det_pf_date <= l_end_date )
1841 THEN
1842
1843 Initialize_Distributions_Row('I',
1844 l_encumbrance_on,
1845 l_cc_headers_rec,
1846 l_cc_acct_lines_rec,
1847 l_cc_pmt_fcst_rec,
1848 l_po_line_locs_rec,
1849 l_po_dist_rec);
1850
1851 IGC_CC_PO_DIST_ALL_PVT.Insert_Row(1.0,
1852 FND_API.G_FALSE,
1853 FND_API.G_FALSE,
1854 FND_API.G_VALID_LEVEL_NONE,
1855 l_return_status,
1856 l_msg_count,
1857 l_msg_data,
1858 l_po_dist_rec);
1859 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1860 THEN
1861 RAISE e_internal_error;
1862 END IF;
1863 END IF;
1864
1865 END LOOP; /* Payment forecast cursor */
1866
1867 CLOSE c_payment_forecast;
1868 END IF;
1869
1870 END LOOP; /*account lines cursor*/
1871
1872 CLOSE c_account_lines;
1873
1874 END IF; /* PO do not exist */
1875
1876
1877 IF FND_API.To_Boolean(p_commit)
1878 THEN
1879 COMMIT WORK;
1880 END IF;
1881
1882 EXCEPTION
1883
1884 WHEN E_INTERNAL_ERROR
1885 THEN
1886 ROLLBACK TO Convert_CC_To_PO;
1887 x_return_status := l_return_status;
1888 x_msg_count := l_msg_count;
1889 x_msg_data := l_msg_data;
1890
1891
1892 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
1893 THEN
1894 ROLLBACK TO Convert_CC_To_PO;
1895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1896
1897 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1898 p_data => x_msg_data );
1899
1900 WHEN E_CC_NOT_FOUND OR E_CC_STATE OR E_CC_TYPE OR E_CC_NOT_ENCUMBERED OR E_LINE_LOCATIONS
1901 OR E_CC_NOT_APPROVED
1902 THEN
1903 ROLLBACK TO Convert_CC_To_PO;
1904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1905 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1906 p_data => x_msg_data );
1907
1908 WHEN OTHERS
1909 THEN
1910 ROLLBACK TO Convert_CC_To_PO;
1911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1912
1913 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1914 THEN
1915 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
1916 l_api_name);
1917 END IF;
1918
1919 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1920 p_data => x_msg_data );
1921
1922
1923 END Convert_CC_To_PO;
1924
1925 /*==========================================================================+
1926 | PROCEDURE Lock_PO_Row |
1927 +==========================================================================*/
1928 PROCEDURE Lock_PO_Row
1929 (
1930 p_api_version IN NUMBER,
1931 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1932 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1933 x_return_status OUT NOCOPY VARCHAR2,
1934 x_msg_count OUT NOCOPY NUMBER,
1935 x_msg_data OUT NOCOPY VARCHAR2,
1936 p_cc_header_id IN NUMBER,
1937 x_row_locked OUT NOCOPY VARCHAR2
1938 )
1939 IS
1940
1941 l_api_name CONSTANT VARCHAR2(30) := 'Lock_PO_Row';
1942 l_api_version CONSTANT NUMBER := 1.0;
1943
1944 l_cc_headers_rec igc_cc_headers%ROWTYPE;
1945 e_cc_not_found EXCEPTION;
1946 e_po_not_found EXCEPTION;
1947
1948
1949 Counter NUMBER;
1950 CURSOR C(p_cc_num VARCHAR2, p_org_id NUMBER)
1951 IS
1952 --SELECT *
1953 -- Reducing the number of columns selected reduces the shared
1954 -- memory used. Performance tuning project
1955 SELECT po_header_id
1956 FROM po_headers_all
1957 WHERE segment1 = p_cc_num AND
1958 type_lookup_code = 'STANDARD' AND
1959 org_id = p_org_id
1960 FOR UPDATE NOWAIT;
1961
1962 Recinfo C%ROWTYPE;
1963 BEGIN
1964
1965 SAVEPOINT Lock_Row_Pvt ;
1966
1967 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1968 p_api_version,
1969 l_api_name,
1970 G_PKG_NAME )
1971 THEN
1972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1973 END IF;
1974
1975
1976 IF FND_API.to_Boolean ( p_init_msg_list )
1977 THEN
1978 FND_MSG_PUB.initialize ;
1979 END IF;
1980
1981 x_return_status := FND_API.G_RET_STS_SUCCESS ;
1982
1983 x_row_locked := FND_API.G_TRUE ;
1984
1985 /* Check whether Contract Commitment exists or not */
1986
1987 BEGIN
1988
1989 SELECT * INTO l_cc_headers_rec
1990 FROM igc_cc_headers
1991 WHERE cc_header_id = p_cc_header_id;
1992
1993 EXCEPTION
1994 WHEN no_data_found
1995 THEN
1996 fnd_message.set_name('IGC','IGC_CC_NOT_FOUND');
1997 fnd_message.set_token('CC_NUM',to_char(p_cc_header_id),TRUE);
1998 fnd_msg_pub.add;
1999 RAISE E_CC_NOT_FOUND;
2000 END;
2001
2002
2003 OPEN C(l_cc_headers_rec.cc_num, l_cc_headers_rec.org_id);
2004 FETCH C INTO Recinfo;
2005
2006 IF (C%NOTFOUND)
2007 THEN
2008 CLOSE C;
2009 fnd_message.set_name('IGC','IGC_CC_PO_NOT_FOUND');
2010 fnd_message.set_token('CC_NUM',l_cc_headers_rec.cc_num,TRUE);
2011 fnd_msg_pub.add;
2012 RAISE e_po_not_found;
2013
2014 END IF;
2015
2016 CLOSE C;
2017
2018 EXCEPTION
2019 WHEN E_CC_NOT_FOUND OR E_PO_NOT_FOUND
2020 THEN
2021 ROLLBACK TO Lock_Row_Pvt ;
2022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2023 x_row_locked := FND_API.G_FALSE;
2024
2025 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2026 p_data => x_msg_data );
2027
2028
2029 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION
2030 THEN
2031
2032 ROLLBACK TO Lock_Row_Pvt ;
2033 x_row_locked := FND_API.G_FALSE;
2034 x_return_status := FND_API.G_RET_STS_ERROR;
2035 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2036 p_data => x_msg_data );
2037
2038 WHEN FND_API.G_EXC_ERROR
2039 THEN
2040
2041 ROLLBACK TO Lock_Row_Pvt ;
2042 x_return_status := FND_API.G_RET_STS_ERROR;
2043 x_row_locked := FND_API.G_FALSE;
2044 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2045 p_data => x_msg_data );
2046
2047 WHEN FND_API.G_EXC_UNEXPECTED_ERROR
2048 THEN
2049 ROLLBACK TO Lock_Row_Pvt ;
2050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2051 x_row_locked := FND_API.G_FALSE;
2052 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2053 p_data => x_msg_data );
2054
2055 WHEN OTHERS
2056 THEN
2057
2058 ROLLBACK TO Lock_Row_Pvt ;
2059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2060 x_row_locked := FND_API.G_FALSE;
2061
2062 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2063 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
2064 l_api_name);
2065 END IF;
2066
2067 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2068 p_data => x_msg_data );
2069
2070 END Lock_PO_Row;
2071 BEGIN
2072 -- Bug 3605536 GSCC Warnings fixed
2073 g_debug_flag := 'N' ;
2074
2075 END IGC_CC_PO_INTERFACE_PKG;