[Home] [Help]
PACKAGE BODY: APPS.PO_EDA_DATATEMPLATE_PKG
Source
1 PACKAGE BODY po_eda_datatemplate_pkg AS
2 /* $Header: POEDADTB.pls 120.19.12020000.3 2013/02/11 00:56:27 vegajula ship $ */
3
4 FUNCTION DocFilter(p_draft_id NUMBER) RETURN BOOLEAN AS
5 BEGIN
6 IF p_draft_id = -1 THEN
7 RETURN (true);
8 ELSE
9 RETURN (false);
10 END IF;
11 END DocFilter;
12
13 FUNCTION ModFilter(p_draft_id NUMBER) RETURN BOOLEAN AS
14 BEGIN
15 IF p_draft_id = -1 THEN
16 RETURN (false);
17 ELSE
18 RETURN (true);
19 END IF;
20 END ModFilter;
21
22 function get_uda_attr_desc_sql(p_doc_type varchar2, p_pk1_value number,
23 p_pk2_value number,
24 p_data_type varchar2,
25 p_appl_col_name varchar2,
26 p_end_user_col_name varchar2,
27 p_attr_group varchar2, p_attr_id number)
28 return varchar2 is
29
30 p_application_id number := 201;
31 p_attr_group_type varchar2(200);
32 p_object_name varchar2(200);
33 p_pk1_column_name varchar2(200);
34 p_pk2_column_name varchar2(200) := 'DRAFT_ID';
35 l_sql varchar2(1000);
36 begin
37 if p_doc_type = 'SHIPMENT' then
38 p_attr_group_type := 'PO_SHIPMENTS_EXT_ATTRS';
39 p_object_name := 'PO_LINE_LOCATIONS_ALL_EXT_B';
40 p_pk1_column_name := 'LINE_LOCATION_ID';
41 elsif p_doc_type = 'LINES' then
42 p_attr_group_type := 'PO_LINE_EXT_ATTRS';
43 p_object_name := 'PO_LINES_ALL_EXT_B';
44 p_pk1_column_name := 'PO_LINE_ID';
45 elsif p_doc_type = 'HEADER' then
46 p_attr_group_type := 'PO_HEADER_EXT_ATTRS';
47 p_object_name := 'PO_HEADERS_ALL_EXT_B';
48 p_pk1_column_name := 'PO_HEADER_ID';
49 end if;
50
51 if (p_data_type = 'C' or p_data_type = 'A' ) then
52 l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
53 p_application_id || ',' || ' null, ' ||
54 p_appl_col_name|| ', null , ''' ||
55 p_end_user_col_name|| ''',''' ||
56 p_attr_group_type || ''',''' ||
57 P_ATTR_GROUP|| ''',' ||
58 p_attr_id || ',''' ||
59 p_object_name || ''',''' ||
60 p_pk1_column_name || ''',' ||
61 p_pk1_value || ',''' ||
62 p_pk2_column_name || ''',' ||
63 p_pk2_value || ') as ' || p_end_user_col_name || '_DESC' ;
64 elsif p_data_type = 'N' then
65 l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
66 p_application_id || ',' || ' null, null, ' ||
67 p_appl_col_name|| ', ''' ||
68 p_end_user_col_name|| ''',''' ||
69 p_attr_group_type || ''',''' ||
70 p_attr_group || ''',' ||
71 p_attr_id || ',''' ||
72 p_object_name || ''',''' ||
73 p_pk1_column_name || ''',' ||
74 p_pk1_value || ',''' ||
75 p_pk2_column_name || ''',' ||
76 p_pk2_value || ') as ' || p_end_user_col_name || '_DESC' ;
77 elsif (p_data_type = 'X' or p_data_type = 'Y') then
78 l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
79 p_application_id || ',' || p_appl_col_name ||
80 ' , null, null, ''' ||
81 p_end_user_col_name|| ''',''' ||
82 p_attr_group_type || ''',''' ||
83 p_attr_group || ''',' ||
84 p_attr_id || ',''' ||
85 p_object_name || ''',''' ||
86 p_pk1_column_name || ''',' ||
87 p_pk1_value || ',''' ||
88 p_pk2_column_name || ''',' ||
89 p_pk2_value || ') as ' || p_end_user_col_name || '_DESC ' ;
90 end if;
91 return(l_sql);
92 end get_uda_attr_desc_sql;
93
94
95 function get_ProcurementInstVehicle3(p_auctHeaderId varchar2) return varchar2 is
96
97 CURSOR csr_auction_details is
98 SELECT paha.solicitation_type
99 from pon_auction_headers_all paha, pon_bid_headers pbh
100 WHERE pbh.po_header_id = DOCUMENTID
101 and pbh.auction_header_id = paha.auction_header_id;
102
103 v_instr_type varchar2(10);
104 begin
105
106 l_temp_attr := NULL;
107
108 OPEN csr_auction_details;
109 FETCH csr_auction_details INTO l_temp_attr;
110 CLOSE csr_auction_details;
111
112 OPEN csr_eda_mapping('ProcurementInstrumentVehicle',l_temp_attr);
113 FETCH csr_eda_mapping INTO l_temp_attr;
114 CLOSE csr_eda_mapping;
115
116 v_instr_type := PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PON_AUC_HDRS_EXT_ATTRS',pk1_value => p_auctHeaderId, pk2_value => -1,
117 p_attr_grp_int_name => 'DOD_SOL', p_attr_int_name => 'INSTR_TYPE');
118
119 IF v_instr_type = 'Q' THEN
120 l_temp_attr := l_temp_attr || '- Manual';
121 ELSIF v_instr_type = 'U' OR v_instr_type = 'T' THEN
122 l_temp_attr := l_temp_attr || '- Automated';
123 END IF;
124
125 RETURN l_temp_attr;
126 end;
127
128
129 function get_PricingArrangementBase(p_clause_type VARCHAR2) return varchar2 IS
130 l_clause_exists varchar2(1);
131 CURSOR csr_clause_exists(clause_t VARCHAR2) IS
132 SELECT 'Y' FROM dual
133 WHERE EXISTS (SELECT 1 FROM okc_k_articles_b kartb, okc_articles_all art
134 WHERE kartb.sav_sae_id = art.article_id
135 AND article_number = clause_t);
136 begin
137 l_temp_attr := p_clause_type;
138 OPEN csr_eda_mapping('PricingArrangementBase',l_temp_attr);
139 FETCH csr_eda_mapping INTO l_temp_attr;
140 CLOSE csr_eda_mapping;
141
142 if l_temp_attr = 'Fixed Price Incentive' then
143 OPEN csr_clause_exists('52.216-17');
144 FETCH csr_clause_exists into l_clause_exists;
145 CLOSE csr_clause_exists;
146 if l_clause_exists = 'Y' then
147 l_temp_attr := l_temp_attr|| ' (Successive Targets)';
148 else
149 l_temp_attr := l_temp_attr|| ' (Cost Based)';
150 end if;
151 end if;
152
153 if l_temp_attr = 'Fixed Price with Economic Price Adjustment' then
154 OPEN csr_clause_exists('52.216-3');
155 FETCH csr_clause_exists into l_clause_exists;
156 CLOSE csr_clause_exists;
157 if l_clause_exists = 'Y' then
158 l_temp_attr := l_temp_attr|| ' - Cost Indexes';
159 else
160 OPEN csr_clause_exists('52.216-2');
161 FETCH csr_clause_exists into l_clause_exists;
162 CLOSE csr_clause_exists;
163 if l_clause_exists = 'Y' then
164 l_temp_attr := l_temp_attr|| ' - Established Prices';
165 else
166 l_temp_attr := l_temp_attr|| ' - Actual Costs)';
167 end if;
168 end if;
169 end if;
170
171 RETURN l_temp_attr;
172 end get_PricingArrangementBase;
173
174 function get_AwardFee(ismod varchar2) return VARCHAR2 IS
175 CURSOR c IS
176 SELECT 'true'
177 FROM po_lines_merge_v pol
178 WHERE
179 ( ( (isMod = 'N') AND (pol.po_header_id = DOCUMENTID AND pol.draft_id = -1 AND pol.change_status is NULL)) OR
180 ( (isMod = 'Y') AND (pol.draft_id = DRAFTID AND pol.change_status IS NOT NULL)) )
181 AND CONTRACT_TYPE IN ('COST_PLUS_AWARD_FEE','FP_AWARD_FEE') ;
182
183 l_flag VARCHAR2(10);
184 BEGIN
185 OPEN C;
186 FETCH C INTO l_flag;
187 CLOSE c;
188 RETURN l_flag;
189 END get_AwardFee;
190
191
192 function get_PerformanceIncentive(ismod varchar2) return VARCHAR2 IS
193 CURSOR c IS
194 SELECT 'true'
195 FROM po_lines_merge_v pol
196 WHERE
197 ( ( (isMod = 'N') AND (pol.po_header_id = DOCUMENTID AND pol.draft_id = -1 AND pol.change_status is NULL)) OR
198 ( (isMod = 'Y') AND (pol.draft_id = DOCUMENTID AND pol.change_status IS NOT NULL)) )
199 AND CONTRACT_TYPE IN ('COST_PLUS_INCENTIVE_FEE','FP_INCENTIVE_SUCCESSIVE_TARGET','FP_INCENTIVE_FIRM_TARGET') ;
200
201 l_flag VARCHAR2(10);
202 BEGIN
203 OPEN C;
204 FETCH C INTO l_flag;
205 CLOSE c;
206 RETURN l_flag;
207 END get_PerformanceIncentive;
208
209
210 function get_AddendaAttached return varchar2 is
211
212 l_clause_exists varchar2(10);
213 cursor csr_check_clauses is
214 SELECT 'true' FROM (
215 SELECT Count(DISTINCT art.article_title) AS articles FROM okc_k_articles_b kartb, okc_article_versions ver, okc_articles_all art
216 WHERE kartb.article_version_id = ver.article_version_id
217 AND kartb.sav_Sae_id = art.article_id
218 AND ver.article_id = art.article_id
219 AND kartb.document_id in
220 (SELECT DISTINCT AUCTION_HEADER_ID FROM pon_bid_headers WHERE bid_number in
221 (SELECT bid_number FROM po_lines_all WHERE po_header_id = DOCUMENTID))
222 AND kartb.document_type = 'SOLICITATION'
223 AND ( (art.article_title LIKE '52.212-1%' AND ver.INSERT_BY_REFERENCE = 'Y')
224 OR (art.article_title LIKE '52.212-4%' AND ver.INSERT_BY_REFERENCE = 'Y')
225 OR (art.article_title LIKE '52.212-3%' AND ver.INSERT_BY_REFERENCE = 'N')
226 OR (art.article_title LIKE '52.212-5%' AND ver.INSERT_BY_REFERENCE = 'N') )) WHERE articles = 4;
227
228 begin
229 OPEN csr_check_clauses;
230 FETCH csr_check_clauses into l_clause_exists;
231 CLOSE csr_check_clauses;
232
233 return l_clause_exists;
234
235 end;
236
237 FUNCTION AfterPForm RETURN Boolean AS
238
239 l_return Boolean;
240 --l_header_id NUMBER;
241 BEGIN
242 l_return := set_uda_attributes_header;
243
244 l_return := set_uda_attributes_lines;
245
246 l_return := set_uda_attrs_line_locations;
247
248 l_return := populateAttrDiff;
249
250 RETURN (TRUE);
251
252 END AfterPForm;
253
254 function get_addressxml_pvt(p_header_id number, p_draft_id number) return CLOB as
255 l_where_sql varchar2(1000);
256 l_addr_sql varchar2(1000);
257 l_sql varchar2(4000);
258 l_col_name varchar2(50);
259 l_attr_grp_id number;
260 clob_addr CLOB;
261 c_addr SYS_REFCURSOR;
262 l_address varchar2(4000);
263 begin
264 l_where_sql := ' from po_headers_all_ext_vl where po_header_id = :1 and draft_id = :2';
265
266 begin
267 SELECT ag.attr_group_id,
268 efdfce.application_column_name
269 into l_attr_grp_id, l_col_name
270 FROM ego_fnd_dsc_flx_ctx_ext ag,
271 ego_fnd_df_col_usgs_ext efdfce,
272 fnd_descr_flex_column_usages fcu
273 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
274 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
275 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
276 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
277 and fcu.application_column_name = efdfce.application_column_name
278 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
279 and upper(ag.descriptive_flex_context_code) = upper('addresses')
280 and upper(fcu.end_user_column_name) = upper('addressdtlsxml');
281
282 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
283
284 l_addr_sql := 'select ' || l_col_name || ' as address ' ;
285
286 exception
287 when no_data_found then
288 l_addr_sql := '';
289 end;
290
291 if l_addr_sql is not null then
292 l_sql := l_addr_sql || l_where_sql;
293
294 dbms_lob.createtemporary(clob_addr, true);
295 dbms_lob.open(clob_addr, dbms_lob.lob_readwrite);
296 open c_addr for l_sql using p_header_id, p_draft_id, l_attr_grp_id;
297 loop
298 fetch c_addr into l_address;
299 exit when c_addr%notfound;
300 if l_address is not null then
301 dbms_lob.writeappend(clob_addr, length(l_address), l_address);
302 end if;
303 end loop;
304 dbms_lob.close(clob_addr);
305 end if;
306 return(clob_addr);
307 end get_addressxml_pvt;
308
309
310 function get_contactsxml_pvt(p_header_id number , p_draft_id number) return CLOB as
311 l_cnt_sql varchar2(1000);
312 l_where_sql varchar2(1000);
313 l_sql varchar2(4000);
314 l_cnt varchar2(4000);
315 l_col_name varchar2(50);
316 l_attr_grp_id number;
317 c_cnt SYS_REFCURSOR;
318 clob_cnt CLOB;
319
320 begin
321 l_where_sql := ' from po_headers_all_ext_vl where po_header_id = :1 and draft_id = :2';
322
323 begin
324 SELECT ag.attr_group_id,
325 efdfce.application_column_name
326 into l_attr_grp_id, l_col_name
327 FROM ego_fnd_dsc_flx_ctx_ext ag,
328 ego_fnd_df_col_usgs_ext efdfce,
329 fnd_descr_flex_column_usages fcu
330 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
331 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
332 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
333 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
334 and fcu.application_column_name = efdfce.application_column_name
335 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
336 and upper(ag.descriptive_flex_context_code) = upper('addresses')
337 and upper(fcu.end_user_column_name) = upper('contactdtlsxml');
338
339 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
340
341 l_cnt_sql := 'select ' || l_col_name || ' as contacts ' ;
342
343 exception
344 when no_data_found then
345 l_cnt_sql := '';
346 end;
347
348 if l_cnt_sql is not null then
349 l_sql := l_cnt_sql || l_where_sql;
350
351 dbms_lob.createtemporary(clob_cnt, true);
352 dbms_lob.open(clob_cnt , dbms_lob.lob_readwrite);
353 open c_cnt for l_sql using p_header_id, p_draft_id, l_attr_grp_id;
354 loop
355 fetch c_cnt into l_cnt;
356 exit when c_cnt%notfound;
357 if l_cnt is not null then
358 dbms_lob.writeappend(clob_cnt, length(l_cnt), l_cnt);
359 end if;
360 end loop;
361 dbms_lob.close(clob_cnt);
362 end if;
363 return(clob_cnt);
364 end get_contactsxml_pvt;
365
366 FUNCTION get_addressxml RETURN CLOB AS
367 BEGIN
368 return(get_addressxml_pvt(DOCUMENTID, DRAFTID));
369 END get_addressxml;
370
371 FUNCTION get_contactsxml RETURN CLOB AS
372 begin
373 return(get_contactsxml_pvt(DOCUMENTID, DRAFTID));
374 END get_contactsxml;
375
376 FUNCTION get_drafts_addressxml(p_headerId NUMBER) RETURN CLOB AS
377 begin
378 return(get_addressxml_pvt(DOCUMENTID, DRAFTID));
379 END get_drafts_addressxml;
380
381 FUNCTION get_drafts_contactsxml(p_headerId NUMBER) RETURN CLOB AS
382 begin
383 return(get_contactsxml_pvt(DOCUMENTID, DRAFTID));
384 END get_drafts_contactsxml;
385
386
387 FUNCTION set_uda_attributes_header RETURN BOOLEAN IS
388
389 CURSOR C_ATTR_GRP IS
390 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
391 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
392 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
393 AG.MULTI_ROW MULTI_ROW
394 FROM EGO_FND_DSC_FLX_CTX_EXT AG,
395 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
396 WHERE POT.TEMPLATE_ID = POTU.TEMPLATE_ID
397 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
398 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
399 AND potu.attribute_group_id in (select distinct attr_group_id
400 from po_headers_all_ext_b
401 where po_header_id = DOCUMENTID
402 and draft_id = DRAFTID and uda_template_id = potu.template_id
403 and attr_group_id = potu.attribute_group_id)
404 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
405
406 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
407 SELECT EFDFCE.ATTR_ID ,
408 EFDFCE.APPLICATION_COLUMN_NAME ,
409 FCU.END_USER_COLUMN_NAME,
410 fcu.flex_value_set_id,
411 EFDFCE.data_type
412 FROM
413 EGO_FND_DF_COL_USGS_EXT EFDFCE,
414 FND_DESCR_FLEX_COLUMN_USAGES FCU
415 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
416 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
417 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
418 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
419 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME;
420
421
422 L_SQL VARCHAR2(32767);
423 L_OP VARCHAR2(32767);
424 L_ATTR_GRP NUMBER;
425 L_CTR NUMBER;
426 L_O_CTR NUMBER;
427 l_uda_xml XMLTYPE;
428 L_SQL2 VARCHAR2(32767);
429 l_value VARCHAR2(4000);
430
431 BEGIN
432 DELETE po_eda_uda_attr_values WHERE pk1_value = DOCUMENTID AND lvl = 'AWARDHEADER' AND pk2_value = DRAFTID;
433 FOR REC IN C_ATTR_GRP LOOP
434 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
435 l_sql2 := 'select '||R.APPLICATION_COLUMN_NAME || ' from po_headers_all_ext_vl where po_header_id = ' || DOCUMENTID ||
436 ' AND draft_id = ' || DRAFTID || ' AND pk1_value IS NULL and attr_group_id = ' || REC.ATTR_GROUP_ID;
437 BEGIN
438 EXECUTE IMMEDIATE l_sql2 INTO l_value;
439 EXCEPTION WHEN OTHERS THEN
440 CONTINUE;
441 END;
442 INSERT INTO po_eda_uda_attr_values(pk1_value,pk2_value,lvl,attr, attrval) VALUES (DOCUMENTID, DRAFTID,'AWARDHEADER',REC.ATTR_GROUP||'_'||R.END_USER_COLUMN_NAME,l_value);
443 END LOOP;
444 END LOOP;
445
446 RETURN true;
447 END set_uda_attributes_header;
448
449
450 FUNCTION set_uda_attributes_lines RETURN BOOLEAN IS
451 CURSOR C_ATTR_GRP(p_po_line_id NUMBER, p_draft_Id number) IS
452 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
453 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
454 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
455 AG.MULTI_ROW MULTI_ROW
456 FROM EGO_FND_DSC_FLX_CTX_EXT AG,
457 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
458 WHERE POTU.TEMPLATE_ID IN (SELECT DISTINCT UDA_TEMPLATE_ID FROM PO_LINES_MERGE_V WHERE PO_HEADER_ID = DOCUMENTID AND
459 DRAFT_ID=DRAFTID)
460 AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
461 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
462 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
463 AND potu.attribute_group_id in (select distinct attr_group_id
464 from po_lines_all_ext_b
465 where po_line_id = p_po_line_id
466 and draft_id = DRAFTID
467 and attr_group_id = potu.attribute_group_id
468 and pk1_value IS NULL)
469 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
470
471 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
472 SELECT EFDFCE.ATTR_ID ,
473 EFDFCE.APPLICATION_COLUMN_NAME ,
474 FCU.END_USER_COLUMN_NAME,
475 fcu.flex_value_set_id,
476 EFDFCE.data_type
477 FROM
478 EGO_FND_DF_COL_USGS_EXT EFDFCE ,
479 FND_DESCR_FLEX_COLUMN_USAGES FCU
480 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
481 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
482 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
483 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
484 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME;
485
486
487 CURSOR c_line IS
488 SELECT po_line_id
489 FROM po_lines_merge_v
490 WHERE draft_id = DRAFTID
491 and po_header_id = DOCUMENTID;
492
493 p_draft_id NUMBER := DRAFTID;
494 L_SQL VARCHAR2(32767);
495 L_SQL2 VARCHAR2(32767);
496 l_value VARCHAR2(4000);
497 L_OP VARCHAR2(32767);
498 L_ATTR_GRP NUMBER;
499 L_CTR NUMBER;
500 L_O_CTR NUMBER;
501 l_uda_xml XMLTYPE;
502 BEGIN
503
504 FOR c IN c_line LOOP
505 DELETE FROM po_eda_uda_attr_values WHERE pk1_value = c.po_line_id AND lvl='AWARDLINE' AND pk2_value = DRAFTID;
506 FOR REC IN C_ATTR_GRP(c.po_line_id,p_draft_id) LOOP
507 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
508
509 l_sql2 := 'select '||R.APPLICATION_COLUMN_NAME || ' from po_lines_all_ext_vl where po_line_id = ' || c.po_line_id ||
510 ' AND draft_id = ' || DRAFTID || ' AND pk1_value IS NULL and attr_group_id = ' || REC.ATTR_GROUP_ID;
511 EXECUTE IMMEDIATE l_sql2 INTO l_value;
512
513
514 INSERT INTO po_eda_uda_attr_values(pk1_value,pk2_value,lvl,attr, attrval) VALUES (c.po_line_id,
515 DRAFTID,'AWARDLINE',REC.ATTR_GROUP||'_'||R.END_USER_COLUMN_NAME,l_value);
516
517 END LOOP;
518 END LOOP;
519 END LOOP;
520 RETURN true;
521 END set_uda_attributes_lines;
522
523
524 function set_uda_attrs_line_locations return BOOLEAN is
525 CURSOR C_ATTR_GRP(p_line_location_id NUMBER ,p_draft_Id NUMBER) IS
526 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
527 AG.ATTR_GROUP_ID ATTR_GROUP_ID,
528 AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
529 AG.MULTI_ROW MULTI_ROW
530 FROM EGO_FND_DSC_FLX_CTX_EXT AG,
531 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
532 WHERE POTU.TEMPLATE_ID IN
533 (SELECT DISTINCT UDA_TEMPLATE_ID FROM PO_LINE_LOCATIONS_MERGE_V WHERE PO_HEADER_ID =DOCUMENTID AND DRAFT_ID = DRAFTID)
534 AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
535 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
536 AND AG.ATTR_GROUP_ID = POTU.ATTRIBUTE_GROUP_ID
537 AND potu.attribute_group_id in (select distinct attr_group_id
538 from po_line_locations_all_ext_b
539 where line_location_id = p_line_location_id
540 and draft_id = DRAFTID
541 and attr_group_id = potu.attribute_group_id)
542 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
543
544 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2) IS
545 SELECT EFDFCE.ATTR_ID ,
546 EFDFCE.APPLICATION_COLUMN_NAME ,
547 FCU.END_USER_COLUMN_NAME,
548 fcu.flex_value_set_id,
549 EFDFCE.data_type
550 FROM
551 EGO_FND_DF_COL_USGS_EXT EFDFCE
552 , FND_DESCR_FLEX_COLUMN_USAGES FCU
553 WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
554 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE = P_ATTR_GROUP
555 AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
556 AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
557 AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME;
558
559
560 CURSOR C_LINE_LOC IS
561 SELECT poll.LINE_LOCATION_ID
562 FROM po_lines_merge_v pol , po_line_locations_merge_v poll
563 WHERE pol.po_header_id = DOCUMENTID
564 AND poll.po_header_id = DOCUMENTID
565 AND pol.DRAFT_ID = DRAFTID
566 AND poll.DRAFT_ID = pol.DRAFT_ID
567 AND poll.po_line_id = pol.po_line_id;
568
569 p_draft_id NUMBER := DRAFTID;
570 L_SQL VARCHAR2(32767);
571 L_OP VARCHAR2(32767);
572 L_ATTR_GRP NUMBER;
573 L_CTR NUMBER;
574 L_O_CTR NUMBER;
575 l_uda_xml XMLTYPE;
576 L_SQL2 VARCHAR2(32767);
577 l_value VARCHAR2(4000);
578
579
580
581 BEGIN
582
583 FOR c IN c_line_loc LOOP
584 DELETE po_eda_uda_attr_values WHERE pk1_value = c.LINE_LOCATION_ID AND lvl = 'AWARDLINELOC' AND pk2_value = DRAFTID;
585 FOR REC IN C_ATTR_GRP(c.LINE_LOCATION_ID,p_draft_id) LOOP
586 FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
587
588 l_sql2 := 'select '||R.APPLICATION_COLUMN_NAME || ' from po_line_locations_all_ext_vl where line_location_id = ' ||
589 c.LINE_LOCATION_ID || ' AND draft_id = ' || DRAFTID ||
590 ' AND pk1_value IS NULL and attr_group_id = ' || REC.ATTR_GROUP_ID;
591 EXECUTE IMMEDIATE l_sql2 INTO l_value;
592 INSERT INTO po_eda_uda_attr_values(pk1_value,pk2_value,lvl,attr, attrval) VALUES (c.LINE_LOCATION_ID,DRAFTID
593 ,'AWARDLINELOC',REC.ATTR_GROUP||'_'||R.END_USER_COLUMN_NAME,l_value);
594
595 END LOOP;
596 END LOOP;
597 END LOOP;
598
599 RETURN true;
600
601
602
603 end set_uda_attrs_line_locations;
604
605
606
607
608 FUNCTION get_LineItemType(p_line_num_disp VARCHAR2) return VARCHAR2 IS
609 BEGIN
610 IF Length(p_line_num_disp) = 4 THEN
611 IF LENGTH(TRIM(TRANSLATE(SubStr(p_line_num_disp,1,1), ' +-.0123456789',' '))) IS NULL THEN
612 RETURN 'CLIN';
613 END IF;
614 ELSIF Length(p_line_num_disp) > 4 THEN
615 IF (LENGTH(TRIM(TRANSLATE(SubStr(p_line_num_disp,5,1), ' +-.0123456789',' '))) > 0 AND
616 LENGTH(TRIM(TRANSLATE(SubStr(p_line_num_disp,6,1), ' +-.0123456789',' '))) > 0) THEN
617 RETURN 'SLIN (Separately Identified)';
618 ELSE
619 RETURN 'INFOSLIN';
620 END IF;
621 END IF;
622 RETURN NULL;
623 END;
624
625
626 FUNCTION get_shipaddressxml_pvt(p_line_location_id number, p_draft_id number) return CLOB as
627
628 l_where_sql varchar2(1000);
629 l_addr_sql varchar2(1000);
630 l_sql varchar2(4000);
631 l_col_name varchar2(50);
632 l_attr_grp_id number;
633 clob_addr CLOB;
634 c_addr SYS_REFCURSOR;
635 l_address varchar2(4000);
636
637 begin
638
639 l_where_sql := ' from PO_LINE_LOCATIONS_ALL_EXT_VL where line_location_id = :1 and draft_id = :2';
640
641 begin
642 SELECT ag.attr_group_id,
643 efdfce.application_column_name
644 into l_attr_grp_id, l_col_name
645
646 FROM ego_fnd_dsc_flx_ctx_ext ag,
647 ego_fnd_df_col_usgs_ext efdfce,
648 fnd_descr_flex_column_usages fcu
649
650 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
651 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
652 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
653 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
654 and fcu.application_column_name = efdfce.application_column_name
655 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_SHIPMENTS_EXT_ATTRS'
656 and upper(ag.descriptive_flex_context_code) = upper('SHIP_INFO')
657 and upper(fcu.end_user_column_name) = upper('hiddShipAddXml');
658
659 l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
660
661 l_addr_sql := 'select ' || l_col_name || ' as ship_address ' ;
662
663 exception
664 when no_data_found then
665 l_addr_sql := '';
666 end;
667
668 if l_addr_sql is not null then
669 l_sql := l_addr_sql || l_where_sql;
670 begin
671 execute immediate l_sql into clob_addr using p_line_location_id, p_draft_id,l_attr_grp_id ;
672 exception
673 when others then
674 select '' into clob_addr
675 from dual;
676 end;
677 end if;
678 return(clob_addr);
679 end get_shipaddressxml_pvt;
680
681 FUNCTION get_shipaddressxml(p_line_location_id number) return CLOB as
682 begin
683 return(get_shipaddressxml_pvt(p_line_location_id,DRAFTID));
684 end get_shipaddressxml;
685
686 FUNCTION get_shipaddressxml_base(p_line_location_id number) return CLOB as
687 begin
688 return(get_shipaddressxml_pvt(p_line_location_id,-1));
689 end get_shipaddressxml_base;
690
691 FUNCTION is_contingencyContract(p_doc_id NUMBER,p_doc_type varchar2) RETURN varchar2 AS
692
693 CURSOR c IS
694 SELECT 'true'
695 FROM okc_k_articles_b kartb, okc_articles_all art
696 WHERE kartb.sav_sae_id = art.article_id
697 AND kartb.document_id = p_doc_id
698 AND kartb.document_type = p_doc_type
699 AND art.article_number = '252.232-7011';
700
701 l_temp VARCHAR2(10) := 'false';
702
703 BEGIN
704
705 OPEN c;
706 FETCH c INTO l_temp;
707 CLOSE c;
708
709 RETURN l_temp;
710 END;
711
712
713 FUNCTION is_EmergencyRequestContract(p_doc_id NUMBER,p_doc_type varchar2) RETURN varchar2 AS
714
715 CURSOR c IS
716 SELECT 'true'
717 FROM okc_k_articles_b kartb, okc_articles_all art
718 WHERE kartb.sav_sae_id = art.article_id
719 AND kartb.document_id = p_doc_id
720 AND kartb.document_type = p_doc_type
721 AND art.article_number IN ('252.225-7997 (DEV)','252.232-7011','252.237-7023');
722
723 l_temp VARCHAR2(10) := 'false';
724
725 BEGIN
726
727 OPEN c;
728 FETCH c INTO l_temp;
729 CLOSE c;
730
731 RETURN l_temp;
732 END;
733
734 FUNCTION get_miscclausetext(p_scn_id NUMBER) RETURN CLOB AS
735
736 CURSOR c2 IS
737 SELECT article_text
738 FROM okc_k_articles_b kartb, okc_articles_all art, okc_article_versions ver
739 WHERE kartb.document_type = DOCUMENTTYPE
740 AND kartb.document_id = decode(ISMOD,'N',DOCUMENTID,DRAFTID)
741 AND kartb.sav_sae_id = art.article_id
742 AND kartb.article_version_id = ver.article_version_id
743 AND art.article_id = ver.article_id
744 AND art.standard_yn = 'N'
745 AND kartb.ref_article_id IS NULL
746 AND KARTB.scn_id = p_scn_id;
747
748
749 l_temp CLOB;
750
751 BEGIN
752
753 FOR c IN c2 LOOP
754 l_temp:=l_temp||' '||c.article_text;
755 END LOOP;
756 RETURN l_temp;
757 end;
758
759 FUNCTION get_requirementsdesc(isMod VARCHAR2) RETURN CLOB AS
760
761 CURSOR c2 IS
762
763 SELECT ARTICLE_TEXT
764 FROM okc_k_articles_b kartb,okc_articles_all art, okc_article_versions ver
765 WHERE kartb.sav_sae_id = art.article_id
766 AND art.article_id = ver.article_id
767 AND document_id = Decode(isMod,'N',DOCUMENTID,'Y',DRAFTID,DOCUMENTID)
768 AND kartb.document_type = DOCUMENTTYPE
769 AND article_type IN ('SOW','PWS');
770
771 l_temp CLOB;
772
773 BEGIN
774
775 FOR c IN c2 LOOP
776 l_temp:=l_temp||' '||c.article_text;
777 END LOOP;
778 RETURN l_temp;
779 end;
780
781 FUNCTION getGovtSharePerc(isMod VARCHAR2) RETURN number AS
782
783 CURSOR c IS
784
785 SELECT Sum(PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_LINE_EXT_ATTRS',pk1_value => po_line_id, pk2_value => DRAFTID,
786 p_attr_grp_int_name => 'AMT_CS_IIQ', p_attr_int_name => 'GOVT_SHARE_PER'))
787 FROM po_lines_merge_v pol
788 WHERE
789 ( ( (isMod = 'N') AND (pol.po_header_id = DOCUMENTID AND pol.draft_id = -1 AND pol.change_status is NULL)) OR
790 ( (isMod = 'Y') AND (pol.draft_id = DRAFTID AND pol.change_status IS NOT NULL)) );
791
792 l_temp NUMBER:=null;
793
794 BEGIN
795
796 OPEN c;
797 FETCH c INTO l_temp;
798 CLOSE c;
799
800 RETURN l_temp;
801
802 end;
803
804 FUNCTION getMonetaryAmount(isMod VARCHAR2) RETURN number AS
805
806 CURSOR c IS
807
808 SELECT Sum(PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_LINE_EXT_ATTRS',pk1_value => po_line_id, pk2_value => DRAFTID,
809 p_attr_grp_int_name => 'AMT_CS_IIQ', p_attr_int_name => 'GOVT_SH_AMOUNT'))
810 FROM po_lines_merge_v pol
811 WHERE
812 ( ( (isMod = 'N') AND (pol.po_header_id = DOCUMENTID AND pol.draft_id = -1 AND pol.change_status is NULL)) OR
813 ( (isMod = 'Y') AND (pol.draft_id = DRAFTID AND pol.change_status IS NOT NULL)) );
814
815
816 l_temp number;
817
818 BEGIN
819 l_temp:= getGovtSharePerc(isMod);
820 IF l_temp IS NULL THEN
821 OPEN c;
822 FETCH c INTO l_temp;
823 CLOSE c;
824 RETURN l_temp;
825 ELSE RETURN NULL;
826 END IF;
827 end;
828
829 FUNCTION getCtMonetaryAmount(isMod VARCHAR2) RETURN number AS
830
831 CURSOR c IS
832
833 SELECT Sum(PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_LINE_EXT_ATTRS',pk1_value => po_line_id, pk2_value => DRAFTID,
834 p_attr_grp_int_name => 'AMT_CS_IIQ', p_attr_int_name => 'CONT_SH_AMOUNT'))
835 FROM po_lines_merge_v pol
836 WHERE
837 ( ( (isMod = 'N') AND (pol.po_header_id = DOCUMENTID AND pol.draft_id = -1 AND pol.change_status is NULL)) OR
838 ( (isMod = 'Y') AND (pol.draft_id = DRAFTID AND pol.change_status IS NOT NULL)) );
839
840
841 l_temp number;
842
843 BEGIN
844 l_temp:= getGovtSharePerc(isMod);
845 IF l_temp IS NULL THEN
846 OPEN c;
847 FETCH c INTO l_temp;
848 CLOSE c;
849 RETURN l_temp;
850 ELSE RETURN NULL;
851 END IF;
852 end;
853
854
855 FUNCTION populateAttrDiff RETURN BOOLEAN IS
856
857
858 BEGIN
859
860 po_gen_diff_pkg.delete_differences('AWARD','ALL','PO_ENTITY_DIFFERENCES',NULL,NULL,NULL,NULL,NULL,DOCUMENTID,DRAFTID,NULL,NULL,NULL);
861 po_gen_diff_pkg.find_differences('AWARD','ALL','PO_ENTITY_DIFFERENCES',DOCUMENTID,-1,NULL,NULL,NULL,DOCUMENTID,DRAFTID,NULL,NULL,NULL);
862
863 RETURN (true);
864 END;
865
866 FUNCTION getDiffAttribute(p_element VARCHAR2,p_column_name VARCHAR2,p_line_id NUMBER:=NULL ,p_line_location_id NUMBER:= NULL
867 ,p_distribution_id NUMBER := NULL ) RETURN CLOB IS
868 CURSOR C_GETDIFF IS
869 SELECT Nvl(orig_value_desc,orig_value) Orig_value,Nvl(mod_value_desc,mod_value) Mod_value,
870 Decode(orig_value,NULL,Decode(mod_value,NULL,'No Change','Added'),
871 Mod_value,'No Change',
872 Decode(mod_value,NULL,'Deleted','Modified')) ChangeFlag,
873 Decode(orig_value,NULL,Decode(mod_value,NULL,'No Change','New value '''||Nvl(mod_value_desc,mod_value)||''' Added'),
874 Mod_value,'No Change',
875 Decode(mod_value,NULL,'value '''||Nvl(orig_value_desc,orig_value)||''' Deleted','Value modified from '''||Nvl(orig_value_desc,orig_value)||''' to '''||Nvl(mod_value_desc,mod_value)||'''')) ChangeText
876 FROM po_entity_differences
877 WHERE MOD_DOC_PK1_VAL = DOCUMENTID
878 AND MOD_DOC_PK2_VAL = DRAFTID
879 AND Nvl(MOD_DOC_PK3_VAL,-1) = Nvl(p_line_id,-1)
880 AND Nvl(MOD_DOC_PK4_VAL,-1) = Nvl(p_line_location_id,-1)
881 AND Nvl(MOD_DOC_PK5_VAL,-1) = Nvl(p_distribution_id,-1)
882 AND column_name = p_column_name;
883
884 CURSOR c_ele_exists is
885 SELECT 'Y'
886 FROM po_entity_differences
887 WHERE MOD_DOC_PK1_VAL = DOCUMENTID
888 AND MOD_DOC_PK2_VAL = DRAFTID
889 AND Nvl(MOD_DOC_PK3_VAL,-1) = Nvl(p_line_id,-1)
890 AND Nvl(MOD_DOC_PK4_VAL,-1) = Nvl(p_line_location_id,-1)
891 AND Nvl(MOD_DOC_PK5_VAL,-1) = Nvl(p_distribution_id,-1)
892 AND column_name = p_column_name;
893
894
895 l_previous_value VARCHAR2(4000);
896 l_mod_value VARCHAR2(4000);
897 l_change_flag VARCHAR2(50);
898 l_change_text VARCHAR2(4000);
899 l_ele_exists VARCHAR2(1):='N';
900 l_clob CLOB;
901 l_xml XMLType;
902
903 BEGIN
904
905 OPEN c_ele_exists;
906 FETCH c_ele_exists INTO l_ele_exists;
907 CLOSE c_ele_exists;
908
909 IF l_ele_exists = 'Y' THEN
910
911 OPEN C_GETDIFF;
912 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
913 CLOSE C_GETDIFF;
914
915 l_clob := '<'||p_element||' changeFlag="'||l_change_flag||'" changeText="'||l_change_text||'" previousValue="'||l_previous_value||'">'||l_mod_value||' </'||p_element||'>';
916 RETURN l_clob;
917 END IF;
918
919 RETURN NULL;
920
921 END;
922
923 FUNCTION getDiffelement(p_element VARCHAR2,p_old_value varchar2, p_new_value varchar2) RETURN CLOB IS
924
925 CURSOR C_GETDIFF IS
926 SELECT p_old_value Orig_value,p_new_value Mod_value,
927 Decode(p_old_value,NULL,Decode(p_new_value,NULL,'No Change','Added'),
928 p_new_value,'No Change',
929 Decode(p_new_value,NULL,'Deleted','Modified')) ChangeFlag,
930 Decode(p_old_value,NULL,Decode(p_new_value,NULL,'No Change','New value '''||p_new_value||''' Added'),
931 p_new_value,'No Change',
932 Decode(p_new_value,NULL,'value '''||p_old_value||''' Deleted','Value modified from '''||p_old_value||
933 ''' to '''||p_new_value||'''')) ChangeText
934 FROM dual;
935
936 l_previous_value VARCHAR2(4000);
937 l_mod_value VARCHAR2(4000);
938 l_change_flag VARCHAR2(50);
939 l_change_text VARCHAR2(4000);
940 l_clob CLOB;
941
942 BEGIN
943
944 OPEN C_GETDIFF;
945 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
946 CLOSE C_GETDIFF;
947 IF l_change_flag <> 'No Change' THEN
948 l_clob := '<'||p_element||' changeFlag="'||l_change_flag||'" changeText="'||l_change_text||
949 '" previousValue="'||l_previous_value||'">'||l_mod_value||' </'||p_element||'>';
950 ELSE
951 l_clob := '<'||p_element||'>'||l_mod_value||' </'||p_element||'>';
952 END IF;
953 RETURN l_clob;
954
955
956 END getDiffelement;
957
958 FUNCTION getElement(p_element VARCHAR2,p_line_id NUMBER:=NULL ,p_line_location_id NUMBER := NULL ,p_distribution_id NUMBER := NULL)
959 RETURN CLOB IS
960 CURSOR C_GETDIFF(p_column_name VARCHAR2) IS
961 SELECT Nvl(orig_value_desc,orig_value) Orig_value,Nvl(mod_value_desc,mod_value) Mod_value,
962 Decode(orig_value,NULL,Decode(mod_value,NULL,'No Change','Added'),
963 Mod_value,'No Change',
964 Decode(mod_value,NULL,'Deleted','Modified')) ChangeFlag,
965 Decode(orig_value,NULL,Decode(mod_value,NULL,'No Change','New value '''||Nvl(mod_value_desc,mod_value)||''' Added'),
966 Mod_value,'No Change',
967 Decode(mod_value,NULL,'value '''||Nvl(orig_value_desc,orig_value)||''' Deleted','Value modified from '''||
968 Nvl(orig_value_desc,orig_value)||''' to '''||Nvl(mod_value_desc,mod_value)||'''')) ChangeText
969 FROM po_entity_differences
970 WHERE MOD_DOC_PK1_VAL = DOCUMENTID
971 AND MOD_DOC_PK2_VAL = DRAFTID
972 AND Nvl(MOD_DOC_PK3_VAL,-1) = Nvl(p_line_id,-1)
973 AND Nvl(MOD_DOC_PK4_VAL,-1) = Nvl(p_line_location_id,-1)
974 AND Nvl(MOD_DOC_PK5_VAL,-1) = Nvl(p_distribution_id,-1)
975 AND column_name = p_column_name;
976
977
978 CURSOR cur_deliverydetails(isMod varchar2) IS
979 SELECT CASE WHEN poh.start_date IS NULL AND poh.end_date IS NULL AND Min(CLM_PERIOD_PERF_START_DATE) IS NULL AND
980 Max(CLM_PERIOD_PERF_END_DATE) IS NULL THEN 'Delivery Requested By' ELSE NULL END DeliveryDescription,
981 to_char(CASE WHEN poh.start_date IS NULL AND poh.end_date IS NULL AND Min(CLM_PERIOD_PERF_START_DATE) IS NULL AND
982 Max(CLM_PERIOD_PERF_END_DATE) IS NULL THEN ploc.NEED_BY_DATE ELSE NULL END,'CCYY-MM-DD') DateElement
983 FROM po_headers_merge_v poh, po_lines_merge_v pol , po_line_locations_merge_v ploc
984 WHERE
985 ( ( (isMod = 'N') AND (poh.po_header_id = DOCUMENTID AND poh.draft_id = -1 AND poh.change_status is NULL)) OR
986 ( (isMod = 'Y') AND (poh.draft_id = DRAFTID AND poh.change_status IS NOT NULL)) )
987 AND
988 ( ( (isMod = 'N') AND (pol.po_header_id = DOCUMENTID AND pol.draft_id = -1 AND pol.change_status is NULL)) OR
989 ( (isMod = 'Y') AND (pol.draft_id = DRAFTID AND pol.change_status IS NOT NULL)) )
990 AND
991 ( ( (isMod = 'N') AND (ploc.po_header_id = DOCUMENTID AND ploc.draft_id = -1 AND ploc.change_status is NULL)) OR
992 ( (isMod = 'Y') AND (ploc.draft_id = DRAFTID AND ploc.change_status IS NOT NULL)) )
993 AND poh.po_header_id = pol.po_header_id
994 AND pol.po_line_id = ploc.po_line_id
995 GROUP BY poh.start_date, poh.end_date, ploc.need_by_date ;
996
997
998 CURSOR c_getpaymentdisc(isMod varchar2) IS
999 WITH q_termid AS(SELECT poh.terms_id v_term
1000 FROM po_headers_merge_v poh
1001 WHERE
1002 ( ( (isMod = 'N') AND (poh.po_header_id = DOCUMENTID AND poh.draft_id = -1 AND poh.change_status is NULL)) OR
1003 ( (isMod = 'Y') AND (poh.draft_id = DRAFTID AND poh.change_status IS NOT NULL)) )
1004 ),
1005 q_netdays AS (SELECT Sum(DUE_DAYS) NetDays FROM ap_terms_lines a, q_termid t WHERE a.term_id = t.v_term),
1006 q_discount1 AS (SELECT DISCOUNT_PERCENT Percent,DISCOUNT_DAYS DaysDue FROM ap_terms_lines a, q_termid t WHERE a.term_id = t.v_term),
1007 q_discount2 AS (SELECT DISCOUNT_PERCENT_2 Percent,DISCOUNT_DAYS_2 DaysDue FROM ap_terms_lines a, q_termid t WHERE a.term_id = t.v_term),
1008 q_discount3 AS (SELECT DISCOUNT_PERCENT_3 Percent,DISCOUNT_DAYS_3 DaysDue FROM ap_terms_lines a, q_termid t WHERE a.term_id = t.v_term),
1009 q_discount AS (
1010 (SELECT Percent,DaysDue FROM q_discount1) UNION (SELECT Percent,DaysDue FROM q_discount2) UNION
1011 (SELECT Percent,DaysDue FROM q_discount3)
1012 ) SELECT Percent,DaysDue,NetDays FROM q_discount,q_netdays;
1013
1014 /*SELECT t.DUE_PERCENT Percent, t.DUE_DAYS DaysDue, (SYSDATE - t.DUE_DAYS) NetDays
1015 FROM po_headers_merge_v poh, ap_terms_lines t
1016 WHERE poh.terms_id = t.term_id
1017 and ( ( (isMod = 'N') AND (poh.po_header_id = DOCUMENTID AND poh.draft_id = -1 AND poh.change_status is NULL)) OR
1018 ( (isMod = 'Y') AND (poh.draft_id = DRAFTID AND poh.change_status IS NOT NULL)) );*/
1019
1020
1021 CURSOR c_distributions IS
1022 SELECT po_distribution_id
1023 FROM po_distributions_all
1024 WHERE po_header_id = DOCUMENTID;
1025
1026 CURSOR c_addedObligatedAmounts IS
1027 SELECT po_distribution_id po_dist_id,'Added' change_status FROM po_distributions_merge_v a WHERE po_header_id = DOCUMENTID
1028 AND draft_id = DRAFTID
1029 AND NOT EXISTS (SELECT 1 FROM po_distributions_merge_v WHERE po_distribution_id = a.po_distribution_id AND
1030 draft_id = -1);
1031
1032 --this is not needed as delete operation is not allowed as of now
1033 CURSOR c_deletedObligatedAmounts IS
1034 SELECT po_distribution_id po_dist_id,'Deleted' change_status FROM po_distributions_merge_v a WHERE po_header_id = DOCUMENTID
1035 AND draft_id = -1
1036 AND NOT EXISTS (SELECT 1 FROM po_distributions_merge_v WHERE po_distribution_id = a.po_distribution_id AND
1037 draft_id = DRAFTID);
1038
1039 CURSOR c_changedObligatedAmounts IS
1040 SELECT modi.po_distribution_id po_dist_id,'Modified' change_status FROM po_distributions_merge_v base,po_distributions_merge_v modi
1041 WHERE base.po_header_id = DOCUMENTID AND modi.po_header_id = base.po_header_id
1042 AND modi.draft_id = DRAFTID AND base.draft_id = -1
1043 AND modi.CHANGE_STATUS = 'UPDATE'
1044 AND (modi.CODE_COMBINATION_ID <> base.CODE_COMBINATION_ID
1045 OR modi.CLM_MISC_LOA <> base.CLM_MISC_LOA
1046 OR modi.CLM_AGENCY_ACCT_IDENTIFIER <> base.CLM_AGENCY_ACCT_IDENTIFIER
1047 OR modi.ACRN <> base.ACRN
1048 OR modi.AMOUNT_FUNDED <> base.AMOUNT_FUNDED);
1049
1050 CURSOR c_obligatedAmountcomponents(p_dist_id number,p_draft_id NUMBER) IS
1051 SELECT NVL((select eda from po_eda_mapping where GROUPING = 'LOA' and attribute = 'ComponentTitle' and
1052 ebs = flv.SEGMENT_NAME),flv.SEGMENT_NAME) ComponentTitle,
1053 PO_EDA_DATATEMPLATE_PKG.get_charge_acc_comp_val(flv.APPLICATION_COLUMN_NAME,pda.CODE_COMBINATION_ID) ComponentValue
1054 FROM fnd_id_flex_segments_vl flv, gl_code_combinations glc, po_distributions_merge_v pda
1055 WHERE po_distribution_id = p_dist_id and draft_id = p_draft_id
1056 AND glc.CODE_COMBINATION_ID = pda.CODE_COMBINATION_ID
1057 AND flv.ID_FLEX_CODE = 'GL#' AND flv.ID_FLEX_NUM = glc.CHART_OF_ACCOUNTS_ID ;
1058
1059
1060 CURSOR c_otheramounts IS
1061 select 'Firm Price','Target Price','Funded Amount','Award Fee','Base Price','Celing Price','Estimated Cost','Fixed Fee','Target Cost' AmountType from dual;
1062
1063 l_previous_value VARCHAR2(4000);
1064 l_mod_value VARCHAR2(4000);
1065 l_change_flag VARCHAR2(50);
1066 l_change_text VARCHAR2(4000);
1067 l_clob CLOB;
1068 i NUMBER := 0;
1069
1070 l_prev1 VARCHAR2(4000);
1071 l_prev2 VARCHAR2(4000);
1072 l_prev3 VARCHAR2(4000);
1073 l_prev4 VARCHAR2(4000);
1074 l_prev5 VARCHAR2(4000);
1075 l_prev6 VARCHAR2(4000);
1076 l_prev7 VARCHAR2(4000);
1077 l_prev8 VARCHAR2(4000);
1078 l_prev9 VARCHAR2(4000);
1079 l_prev10 VARCHAR2(4000);
1080 l_mod1 VARCHAR2(4000);
1081 l_mod2 VARCHAR2(4000);
1082 l_mod3 VARCHAR2(4000);
1083 l_mod4 VARCHAR2(4000);
1084 l_mod5 VARCHAR2(4000);
1085 l_mod6 VARCHAR2(4000);
1086 l_mod7 VARCHAR2(4000);
1087 l_mod8 VARCHAR2(4000);
1088 l_mod9 VARCHAR2(4000);
1089 l_mod10 VARCHAR2(4000);
1090 l_chg1 VARCHAR2(40);
1091 l_chg2 VARCHAR2(40);
1092 l_chg3 VARCHAR2(40);
1093 l_chg4 VARCHAR2(40);
1094 l_chg5 VARCHAR2(40);
1095 l_chg6 VARCHAR2(40);
1096 l_chg7 VARCHAR2(40);
1097 l_chg8 VARCHAR2(40);
1098 l_chg9 VARCHAR2(40);
1099 l_chg10 VARCHAR2(40);
1100 l_chgtxt1 VARCHAR2(4000);
1101 l_chgtxt2 VARCHAR2(4000);
1102 l_chgtxt3 VARCHAR2(4000);
1103 l_chgtxt4 VARCHAR2(4000);
1104 l_chgtxt5 VARCHAR2(4000);
1105 l_chgtxt6 VARCHAR2(4000);
1106 l_chgtxt7 VARCHAR2(4000);
1107 l_chgtxt8 VARCHAR2(4000);
1108 l_chgtxt9 VARCHAR2(4000);
1109 l_chgtxt10 VARCHAR2(4000);
1110 l_clob2 CLOB;
1111 l_clob3 CLOB;
1112
1113
1114 CURSOR c_misctext_base IS
1115 SELECT (SELECT heading FROM okc_sections_b WHERE id = kartb.scn_id) Section,get_miscclausetxt(scn_id,'N') MiscellaneousText
1116 FROM okc_k_articles_b kartb, okc_articles_all art, okc_article_versions ver
1117 WHERE kartb.document_id = DOCUMENTID
1118 AND kartb.sav_sae_id = art.article_id
1119 AND kartb.article_version_id = ver.article_version_id
1120 AND art.article_id = ver.article_id
1121 AND art.standard_yn = 'N'
1122 AND kartb.ref_article_id IS NULL
1123 GROUP BY scn_id
1124 ORDER BY scn_id;
1125
1126 CURSOR c_misctext_mod IS
1127 SELECT (SELECT heading FROM okc_sections_b WHERE id = kartb.scn_id) Section,get_miscclausetxt(scn_id,'Y') MiscellaneousText
1128 FROM okc_k_articles_b kartb, okc_articles_all art, okc_article_versions ver
1129 WHERE kartb.document_id = DRAFTID
1130 AND kartb.sav_sae_id = art.article_id
1131 AND kartb.article_version_id = ver.article_version_id
1132 AND art.article_id = ver.article_id
1133 AND art.standard_yn = 'N'
1134 AND kartb.ref_article_id IS NULL
1135 GROUP BY scn_id
1136 ORDER BY scn_id;
1137
1138
1139
1140
1141 BEGIN
1142
1143 IF p_element = 'ORDERINGINFORMATION_LIMITS' THEN
1144
1145 OPEN C_GETDIFF('CLM_MIN_ORDER_AMOUNT');
1146 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1147 CLOSE C_GETDIFF;
1148 l_prev1:=l_previous_value;
1149 l_mod1:= l_mod_value;
1150 l_chg1:= l_change_flag;
1151 l_chgtxt1 := l_change_text;
1152
1153 OPEN C_GETDIFF('CLM_MAX_ORDER_AMOUNT');
1154 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1155 CLOSE C_GETDIFF;
1156 l_prev2:=l_previous_value;
1157 l_mod2:= l_mod_value;
1158 l_chg2:= l_change_flag;
1159 l_chgtxt2 := l_change_text;
1160
1161 OPEN C_GETDIFF('AMOUNT_LIMIT');
1162 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1163 CLOSE C_GETDIFF;
1164 l_prev3:=l_previous_value;
1165 l_mod3:= l_mod_value;
1166 l_chg3:= l_change_flag;
1167 l_chgtxt3 := l_change_text;
1168
1169 OPEN C_GETDIFF('CLM_MIN_GUARANTEE_AWARD_AMT');
1170 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1171 CLOSE C_GETDIFF;
1172 l_prev4:=l_previous_value;
1173 l_mod4:= l_mod_value;
1174 l_chg4:= l_change_flag;
1175 l_chgtxt4 := l_change_text;
1176
1177 IF l_prev1 IS NOT NULL or l_prev2 IS NOT null THEN
1178 l_prev5:= 'Allowed Per Order';
1179 END IF;
1180
1181 IF l_mod1 IS NOT NULL or l_mod2 IS NOT null THEN
1182 l_mod5:= 'Allowed Per Order';
1183 END IF;
1184
1185 IF l_prev3 IS NOT NULL or l_prev4 IS NOT null THEN
1186 l_prev6:= 'Allowed Per Order';
1187 END IF;
1188
1189 IF l_mod3 IS NOT NULL or l_mod4 IS NOT null THEN
1190 l_mod6:= 'Allowed Per Order';
1191 END IF;
1192
1193 IF l_chg1 = l_chg2 THEN
1194 l_chg5 := l_chg1;
1195 ELSIF l_chg1 IS NOT NULL THEN
1196 l_chg5 := 'Modified';
1197 END IF;
1198
1199 IF l_chg3 = l_chg4 THEN
1200 l_chg6 := l_chg3;
1201 ELSIF l_chg3 IS NOT NULL THEN
1202 l_chg6 := 'Modified';
1203 END IF;
1204
1205 l_chgtxt5:= l_chgtxt1 || l_chgtxt2;
1206 l_chgtxt6:= l_chgtxt3 || l_chgtxt4;
1207
1208 IF l_chg5 IS NULL AND l_chg6 IS NULL THEN
1209 RETURN NULL;
1210 END IF;
1211
1212 l_clob := '<OrderingInformation>';
1213
1214 IF l_chg5 IS NOT NULL THEN
1215 l_clob:= l_clob || '<Limits changeFlag="'||l_chg5||'" changeText="'||l_chgtxt5||'" previousLimitDescription="'||l_prev5||
1216 '" previousMinimum="'||l_prev1||'" previousMaximum="'||l_prev2||'">';
1217 l_clob:= l_clob || '<LimitDescription>' || l_mod5 || '</LimitDescription>';
1218 l_clob:= l_clob || '<Minimum>' || l_mod1 || '</Minimum>';
1219 l_clob:= l_clob || '<Maximum>' || l_mod2 || '</Maximum></Limits>';
1220 END IF;
1221
1222 IF l_chg6 IS NOT NULL THEN
1223 l_clob:= l_clob || '<Limits changeFlag="'||l_chg6||'" changeText="'||l_chgtxt6||'" previousLimitDescription="'||l_prev6||
1224 '" previousMinimum="'||l_prev3||'" previousMaximum="'||l_prev4||'">';
1225 l_clob:= l_clob || '<LimitDescription>' || l_mod6 || '</LimitDescription>';
1226 l_clob:= l_clob || '<Minimum>' || l_mod3 || '</Minimum>';
1227 l_clob:= l_clob || '<Maximum>' || l_mod4 || '</Maximum></Limits>';
1228 END IF;
1229
1230 l_clob := l_clob || '</OrderingInformation>';
1231
1232 RETURN l_clob;
1233
1234 ELSIF p_element = 'PRICINGARRANGEMENT' THEN -- ORDERINGINFORMATION_LIMITS
1235
1236 l_prev1 := po_eda_datatemplate_pkg.get_AwardFee('N');
1237 l_prev2 := po_eda_datatemplate_pkg.get_PerformanceIncentive('N');
1238 select PO_EDA_DATATEMPLATE_PKG.get_PricingArrangementBase(contract_type) PricingArrangementBase
1239 INTO l_prev3
1240 from (
1241 SELECT CASE
1242 WHEN cnt = 1 THEN
1243 (
1244 SELECT distinct contract_type FROM po_lines_merge_v
1245 WHERE po_header_id = DOCUMENTID AND draft_id = -1 AND change_status is NULL and contract_type is not null
1246 )
1247 ELSE 'FFP'
1248 END AS contract_type FROM (
1249 SELECT Count(distinct contract_type) AS cnt FROM po_lines_merge_v
1250 WHERE po_header_id = DOCUMENTID AND draft_id = -1 AND change_status is NULL AND contract_type is not null )
1251 );
1252
1253 l_mod1 := po_eda_datatemplate_pkg.get_AwardFee('Y');
1254 l_mod2 := po_eda_datatemplate_pkg.get_PerformanceIncentive('Y');
1255 select PO_EDA_DATATEMPLATE_PKG.get_PricingArrangementBase(contract_type) PricingArrangementBase
1256 INTO l_mod3
1257 from (
1258 SELECT CASE
1259 WHEN cnt = 1 THEN
1260 (
1261 SELECT distinct contract_type FROM po_lines_merge_v
1262 WHERE po_header_id = DOCUMENTID AND draft_id = DRAFTID AND change_status is NOT NULL and contract_type is not null
1263 )
1264 ELSE 'FFP'
1265 END AS contract_type FROM (
1266 SELECT Count(distinct contract_type) AS cnt FROM po_lines_merge_v
1267 WHERE po_header_id = DOCUMENTID AND draft_id = DRAFTID AND change_status is NOT NULL AND contract_type is not null )
1268 );
1269
1270 IF l_prev1 IS NULL AND l_mod1 IS NOT NULL THEN l_chg1:= 'Added'; l_chgtxt1:= 'Award Fee Value '||l_mod1||' added';
1271 ELSIF l_prev1 IS NOT NULL AND l_mod1 IS NULL THEN l_chg1:= 'Deleted'; l_chgtxt1:= 'Award Fee Value '||l_prev1||' deleted';
1272 ELSIF l_prev1 <> l_mod1 THEN l_chg1 := 'Modified' ; l_chgtxt1:= 'Award Fee Value modified from '||l_prev1||' to '||l_mod1;
1273 END IF;
1274
1275 IF l_prev2 IS NULL AND l_mod2 IS NOT NULL THEN l_chg2:= 'Added';l_chgtxt2:= 'Performance Incentive Value '||l_mod2||' added';
1276 ELSIF l_prev2 IS NOT NULL AND l_mod2 IS NULL THEN l_chg2:= 'Deleted'; l_chgtxt2:= 'Performance Incentive Value '||l_prev2||' deleted';
1277 ELSIF l_prev2 <> l_mod2 THEN l_chg2 := 'Modified' ; l_chgtxt2:= 'Performance Incentive Value modified from '||l_prev2||' to '||l_mod2;
1278 END IF;
1279
1280 IF l_prev3 IS NULL AND l_mod3 IS NOT NULL THEN l_chg3:= 'Added';l_chgtxt3:= 'Price Arrangement Base Value '||l_mod3||' added';
1281 ELSIF l_prev3 IS NOT NULL AND l_mod3 IS NULL THEN l_chg3:= 'Deleted'; l_chgtxt3:= 'Price Arrangement Base Value '||l_prev3||' deleted';
1282 ELSIF l_prev3 <> l_mod3 THEN l_chg3 := 'Modified' ; l_chgtxt3:= 'Price Arrangement Base Value modified from '||l_prev3||' to '||l_mod3;
1283 END IF;
1284
1285 l_chgtxt4:=l_chgtxt1|| ', ' || l_chgtxt2 || ', ' || l_chgtxt3;
1286
1287
1288 IF l_chg1 IS NULL AND l_chg2 IS NULL THEN RETURN NULL;
1289 ELSIF l_chg1 = l_chg2 THEN
1290 l_chg3 := l_chg1;
1291 ELSIF l_chg1 IS NOT NULL THEN
1292 l_chg3 := 'Modified';
1293 END IF;
1294
1295 l_clob:= '<PricingArrangement changeFlag="'||l_chg3||'" changeText="'||l_chgtxt4||'" previousAwardFee="'||l_prev1||'" previousPerformanceIncentive="'||l_prev2||'">';
1296 l_clob := l_clob|| '<PricingArrangementBase>'||l_mod3||'</PricingArrangementBase>';
1297 l_clob := l_clob|| '<AwardFee>'||l_mod1||'</AwardFee>';
1298 l_clob := l_clob|| '<PerformanceIncentive>'||l_mod1||'</PerformanceIncentive></PricingArrangement>';
1299
1300 RETURN l_clob;
1301
1302 ELSIF p_element = 'SHARERATIO' THEN
1303
1304 l_prev1 := PO_EDA_DATATEMPLATE_PKG.getGovtSharePerc('N');
1305 l_prev2 := PO_EDA_DATATEMPLATE_PKG.getMonetaryAmount('N');
1306 l_prev3 := PO_EDA_DATATEMPLATE_PKG.getCtMonetaryAmount('N');
1307 l_mod1 := PO_EDA_DATATEMPLATE_PKG.getGovtSharePerc('Y');
1308 l_mod2 := PO_EDA_DATATEMPLATE_PKG.getMonetaryAmount('Y');
1309 l_mod3 := PO_EDA_DATATEMPLATE_PKG.getCtMonetaryAmount('Y');
1310
1311 IF l_prev1 IS NULL AND l_mod1 IS NOT NULL THEN l_chg1:= 'Added'; l_chgtxt1:= 'Value '||l_mod1||' added';
1312 ELSIF l_prev1 IS NOT NULL AND l_mod1 IS NULL THEN l_chg1:= 'Deleted'; l_chgtxt1:= 'Value '||l_prev1||' deleted';
1313 ELSIF l_prev1 <> l_mod1 THEN l_chg1 := 'Modified' ; l_chgtxt1:= 'Value modified from '||l_prev1||' to '||l_mod1;
1314 END IF;
1315
1316 IF l_prev2 IS NULL AND l_mod2 IS NOT NULL THEN l_chg2:= 'Added';l_chgtxt2:= 'Value '||l_mod2||' added';
1317 ELSIF l_prev2 IS NOT NULL AND l_mod2 IS NULL THEN l_chg2:= 'Deleted'; l_chgtxt2:= 'Value '||l_prev2||' deleted';
1318 ELSIF l_prev2 <> l_mod2 THEN l_chg2 := 'Modified' ; l_chgtxt2:= 'Value modified from '||l_prev2||' to '||l_mod2;
1319 END IF;
1320
1321 IF l_prev3 IS NULL AND l_mod3 IS NOT NULL THEN l_chg3:= 'Added';l_chgtxt3:= 'Value '||l_mod3||' added';
1322 ELSIF l_prev3 IS NOT NULL AND l_mod3 IS NULL THEN l_chg3:= 'Deleted'; l_chgtxt3:= 'Value '||l_prev3||' deleted';
1323 ELSIF l_prev3 <> l_mod3 THEN l_chg3 := 'Modified' ; l_chgtxt3:= 'Value modified from '||l_prev3||' to '||l_mod3;
1324 END IF;
1325
1326 IF l_chg1 IS NULL AND l_chg2 IS NULL AND l_chg3 IS NULL THEN
1327 RETURN NULL;
1328 END IF;
1329
1330 l_clob := '<ShareRatio>';
1331
1332 IF l_chg1 = 'Added' OR l_chg2 = 'Added' OR l_chg3 = 'Added' THEN
1333 l_clob := l_clob || '<AddedShareRatio>';
1334 IF l_chg1 = 'Added' OR l_chg2 = 'Added' THEN
1335 l_clob := l_clob || '<GovernmentShare>';
1336
1337 IF l_chg1 = 'Added' THEN
1338 l_clob := l_clob || '<SharePercentage>' || l_mod1 || '</SharePercentage>';
1339 END IF;
1340 IF l_chg2 = 'Added' THEN
1341 l_clob := l_clob || '<MonetaryAmount>' || l_mod2 || '</MonetaryAmount>';
1342 END IF;
1343 l_clob := l_clob || '</GovernmentShare>';
1344 END IF;
1345 IF l_chg1 = 'Added' OR l_chg3 = 'Added' THEN
1346 l_clob := l_clob || '<ContractorShare>';
1347
1348 IF l_chg1 = 'Added' THEN
1349 l_clob := l_clob || '<SharePercentage>' || l_mod1 || '</SharePercentage>';
1350 END IF;
1351 IF l_chg3 = 'Added' THEN
1352 l_clob := l_clob || '<MonetaryAmount>' || l_mod3 || '</MonetaryAmount>';
1353 END IF;
1354 l_clob := l_clob || '</ContractorShare>';
1355 END IF;
1356 l_clob := l_clob || '</AddedShareRatio>';
1357 END IF;
1358
1359 IF l_chg1 = 'Deleted' OR l_chg2 = 'Deleted' OR l_chg3 = 'Deleted' THEN
1360 l_clob := l_clob || '<DeletedShareRatio>';
1361 IF l_chg1 = 'Deleted' OR l_chg2 = 'Deleted' THEN
1362 l_clob := l_clob || '<GovernmentShare>';
1363
1364 IF l_chg1 = 'Deleted' THEN
1365 l_clob := l_clob || '<SharePercentage>' || l_prev1 || '</SharePercentage>';
1366 END IF;
1367 IF l_chg2 = 'Deleted' THEN
1368 l_clob := l_clob || '<MonetaryAmount>' || l_prev2 || '</MonetaryAmount>';
1369 END IF;
1370 l_clob := l_clob || '</GovernmentShare>';
1371 END IF;
1372 IF l_chg1 = 'Deleted' OR l_chg3 = 'Deleted' THEN
1373 l_clob := l_clob || '<ContractorShare>';
1374
1375 IF l_chg1 = 'Deleted' THEN
1376 l_clob := l_clob || '<SharePercentage>' || l_prev1 || '</SharePercentage>';
1377 END IF;
1378 IF l_chg3 = 'Deleted' THEN
1379 l_clob := l_clob || '<MonetaryAmount>' || l_prev3 || '</MonetaryAmount>';
1380 END IF;
1381 l_clob := l_clob || '</ContractorShare>';
1382 END IF;
1383 l_clob := l_clob || '</DeletedShareRatio>';
1384 END IF;
1385
1386 IF l_chg1 = 'Modified' OR l_chg2 = 'Modified' OR l_chg3 = 'Modified' THEN
1387 l_clob := l_clob || '<ChangedShareRatio>';
1388 l_clob := l_clob || '<ShareRatioChangeText>' || l_chgtxt1 || l_chgtxt2 || l_chgtxt3 || '</ShareRatioChangeText>';
1389 l_clob := l_clob || '<CurrentShareRatio>';
1390 IF l_chg1 = 'Modified' OR l_chg2 = 'Modified' THEN
1391 l_clob := l_clob || '<GovernmentShare>';
1392
1393 IF l_chg1 = 'Modified' THEN
1394 l_clob := l_clob || '<SharePercentage>' || l_mod1 || '</SharePercentage>';
1395 END IF;
1396 IF l_chg2 = 'Modified' THEN
1397 l_clob := l_clob || '<MonetaryAmount>' || l_mod2 || '</MonetaryAmount>';
1398 END IF;
1399 l_clob := l_clob || '</GovernmentShare>';
1400 END IF;
1401 IF l_chg1 = 'Modified' OR l_chg3 = 'Modified' THEN
1402 l_clob := l_clob || '<ContractorShare>';
1403
1404 IF l_chg1 = 'Modified' THEN
1405 l_clob := l_clob || '<SharePercentage>' || l_mod1 || '</SharePercentage>';
1406 END IF;
1407 IF l_chg3 = 'Modified' THEN
1408 l_clob := l_clob || '<MonetaryAmount>' || l_mod3 || '</MonetaryAmount>';
1409 END IF;
1410 l_clob := l_clob || '</ContractorShare>';
1411 END IF;
1412 l_clob := l_clob || '</CurrentShareRatio>';
1413
1414 l_clob := l_clob || '<PreviousShareRatio>';
1415 IF l_chg1 = 'Modified' OR l_chg2 = 'Modified' THEN
1416 l_clob := l_clob || '<GovernmentShare>';
1417
1418 IF l_chg1 = 'Modified' THEN
1419 l_clob := l_clob || '<SharePercentage>' || l_prev1 || '</SharePercentage>';
1420 END IF;
1421 IF l_chg2 = 'Modified' THEN
1422 l_clob := l_clob || '<MonetaryAmount>' || l_prev2 || '</MonetaryAmount>';
1423 END IF;
1424 l_clob := l_clob || '</GovernmentShare>';
1425 END IF;
1426 IF l_chg1 = 'Modified' OR l_chg3 = 'Modified' THEN
1427 l_clob := l_clob || '<ContractorShare>';
1428
1429 IF l_chg1 = 'Modified' THEN
1430 l_clob := l_clob || '<SharePercentage>' || l_prev1 || '</SharePercentage>';
1431 END IF;
1432 IF l_chg3 = 'Modified' THEN
1433 l_clob := l_clob || '<MonetaryAmount>' || l_prev3 || '</MonetaryAmount>';
1434 END IF;
1435 l_clob := l_clob || '</ContractorShare>';
1436 END IF;
1437 l_clob := l_clob || '</PreviousShareRatio>';
1438 l_clob := l_clob || '</ChangedShareRatio>';
1439 END IF;
1440
1441 l_clob := l_clob || '</ShareRatio>' ;
1442 RETURN l_clob;
1443
1444 ELSIF p_element = 'PROCUREMENTINSTRUMENTPERIODS' THEN
1445
1446 OPEN C_GETDIFF('START_DATE');
1447 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1448 CLOSE C_GETDIFF;
1449 l_prev1:=l_previous_value;
1450 l_mod1:= l_mod_value;
1451 l_chg1:= l_change_flag;
1452 l_chgtxt1 := l_change_text;
1453
1454 OPEN C_GETDIFF('END_DATE');
1455 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1456 CLOSE C_GETDIFF;
1457 l_prev2:=l_previous_value;
1458 l_mod2:= l_mod_value;
1459 l_chg2:= l_change_flag;
1460 l_chgtxt2 := l_change_text;
1461
1462 OPEN C_GETDIFF('CLM_PERIOD_PERF_START_DATE');
1463 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1464 CLOSE C_GETDIFF;
1465 l_prev3:=l_previous_value;
1466 l_mod3:= l_mod_value;
1467 l_chg3:= l_change_flag;
1468 l_chgtxt3 := l_change_text;
1469
1470 OPEN C_GETDIFF('CLM_PERIOD_PERF_END_DATE');
1471 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1472 CLOSE C_GETDIFF;
1473 l_prev4:=l_previous_value;
1474 l_mod4:= l_mod_value;
1475 l_chg4:= l_change_flag;
1476 l_chgtxt4 := l_change_text;
1477
1478 OPEN C_GETDIFF('CLM_ORDER_START_DATE');
1479 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1480 CLOSE C_GETDIFF;
1481 l_prev5:=l_previous_value;
1482 l_mod5:= l_mod_value;
1483 l_chg5:= l_change_flag;
1484 l_chgtxt5 := l_change_text;
1485
1486 OPEN C_GETDIFF('CLM_ORDER_END_DATE');
1487 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1488 CLOSE C_GETDIFF;
1489 l_prev6:=l_previous_value;
1490 l_mod6 := l_mod_value;
1491 l_chg6:= l_change_flag;
1492 l_chgtxt6 := l_change_text;
1493
1494 IF l_prev1 IS NOT NULL or l_prev2 IS NOT null THEN
1495 l_prev7:= 'Ordering Period';
1496 END IF;
1497
1498 IF l_mod1 IS NOT NULL or l_mod2 IS NOT null THEN
1499 l_mod7 := 'Ordering Period';
1500 END IF;
1501
1502 IF l_prev3 IS NOT NULL or l_prev4 IS NOT null THEN
1503 l_prev8:= 'Period of Performance';
1504 END IF;
1505
1506 IF l_mod3 IS NOT NULL or l_mod4 IS NOT null THEN
1507 l_mod8:= 'Period of Performance';
1508 END IF;
1509
1510 IF l_prev5 IS NOT NULL or l_prev6 IS NOT null THEN
1511 l_prev9:= 'Delivery Requested By';
1512 END IF;
1513
1514 IF l_mod5 IS NOT NULL or l_mod6 IS NOT null THEN
1515 l_mod9:= 'Delivery Requested By';
1516 END IF;
1517
1518
1519 IF l_chg1 IS NULL AND l_chg2 IS NULL AND l_chg3 IS NULL AND l_chg4 IS NULL AND l_chg5 IS NULL AND l_chg6 IS NULL THEN
1520 RETURN NULL;
1521 END IF;
1522
1523 IF l_chg1 = l_chg2 THEN
1524 l_chg7 := l_chg1;
1525 ELSIF l_chg1 IS NOT NULL THEN
1526 l_chg7 := 'Modified';
1527 END IF;
1528
1529 IF l_chg3 = l_chg4 THEN
1530 l_chg8 := l_chg3;
1531 ELSIF l_chg3 IS NOT NULL THEN
1532 l_chg8 := 'Modified';
1533 END IF;
1534
1535 IF l_chg3 = l_chg4 THEN
1536 l_chg9 := l_chg3;
1537 ELSIF l_chg3 IS NOT NULL THEN
1538 l_chg9 := 'Modified';
1539 END IF;
1540
1541 l_chgtxt7:= l_chgtxt1 || l_chgtxt2;
1542 l_chgtxt8:= l_chgtxt3 || l_chgtxt4;
1543 l_chgtxt9:= l_chgtxt5 || l_chgtxt6;
1544
1545
1546
1547 IF l_chg7 IS NOT NULL THEN
1548 l_clob:= l_clob || '<ProcurementInstrumentPeriods changeFlag="'||l_chg7||'" changeText="'||l_chgtxt7||
1549 '" previousPeriodDescription="'||l_prev7||'" previousStartDate="'||l_prev1||'" previousEndDate="'||l_prev2||'">';
1550 l_clob:= l_clob || '<PeriodDescription>' || l_mod7 || '</PeriodDescription>';
1551 l_clob:= l_clob || '<StartDate>' || l_mod1 || '</StartDate>';
1552 l_clob:= l_clob || '<EndDate>' || l_mod2 || '</EndDate></ProcurementInstrumentPeriods>';
1553 END IF;
1554
1555 IF l_chg8 IS NOT NULL THEN
1556 l_clob:= l_clob || '<ProcurementInstrumentPeriods changeFlag="'||l_chg8||'" changeText="'||l_chgtxt8||
1557 '" previousPeriodDescription="'||l_prev8||'" previousStartDate="'||l_prev3||'" previousEndDate="'||l_prev4||'">';
1558 l_clob:= l_clob || '<PeriodDescription>' || l_mod8 || '</PeriodDescription>';
1559 l_clob:= l_clob || '<StartDate>' || l_mod3 || '</StartDate>';
1560 l_clob:= l_clob || '<EndDate>' || l_mod4 || '</EndDate></ProcurementInstrumentPeriods>';
1561 END IF;
1562
1563 IF l_chg9 IS NOT NULL THEN
1564 l_clob:= l_clob || '<ProcurementInstrumentPeriods changeFlag="'||l_chg9||'" changeText="'||l_chgtxt9||
1565 '" previousPeriodDescription="'||l_prev9||'" previousStartDate="'||l_prev5||'" previousEndDate="'||l_prev6||'">';
1566 l_clob:= l_clob || '<PeriodDescription>' || l_mod9 || '</PeriodDescription>';
1567 l_clob:= l_clob || '<StartDate>' || l_mod5 || '</StartDate>';
1568 l_clob:= l_clob || '<EndDate>' || l_mod6 || '</EndDate></ProcurementInstrumentPeriods>';
1569 END IF;
1570
1571 RETURN l_clob;
1572
1573 ELSIF p_element = 'HEADERCURRENCY' THEN
1574
1575 OPEN C_GETDIFF('CURRENCY_CODE');
1576 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1577 CLOSE C_GETDIFF;
1578 l_prev1:=l_previous_value;
1579 l_mod1:= l_mod_value;
1580 l_chg1:= l_change_flag;
1581 l_chgtxt1 := l_change_text;
1582
1583 OPEN C_GETDIFF('RATE');
1584 FETCH C_GETDIFF INTO l_previous_value,l_mod_value,l_change_flag,l_change_text;
1585 CLOSE C_GETDIFF;
1586 l_prev2:=l_previous_value;
1587 l_mod2:= l_mod_value;
1588 l_chg2:= l_change_flag;
1589 l_chgtxt2 := l_change_text;
1590
1591 IF l_chg1 IS NULL AND l_chg2 IS NULL THEN
1592 RETURN NULL;
1593 END IF;
1594
1595 IF l_chg1 = l_chg2 THEN
1596 l_chg3 := l_chg1;
1597 ELSIF l_chg1 IS NOT NULL THEN
1598 l_chg3 := 'Modified';
1599 END IF;
1600
1601 l_chgtxt3 := l_chgtxt1 || l_chgtxt2;
1602
1603 IF l_chg3 IS NOT NULL THEN
1604 l_clob:= l_clob || '<HeaderCurrency changeFlag="'||l_chg3||'" changeText="'||l_chgtxt3||'" previousBuyingCurrency="'||l_prev1||
1605 '" previousBuyingCurrencyExchangeRate="'||l_prev2||'">';
1606 l_clob:= l_clob || '<BuyingCurrency exchangeRate="'||l_mod2||'">' || l_mod1 || '</BuyingCurrency></HeaderCurrency>';
1607 END IF;
1608
1609 RETURN l_clob;
1610
1611 ELSIF p_element = 'DELIVERYDETAILS' THEN
1612
1613 OPEN cur_deliverydetails('N');
1614 FETCH cur_deliverydetails INTO l_prev1,l_prev2;
1615 CLOSE cur_deliverydetails;
1616 OPEN cur_deliverydetails('Y');
1617 FETCH cur_deliverydetails INTO l_mod1,l_mod2;
1618 CLOSE cur_deliverydetails;
1619
1620 IF l_prev1 IS NULL AND l_mod1 IS NOT NULL THEN l_chg1:= 'Added'; l_chgtxt1:= 'Value '||l_mod1||' added';
1621 ELSIF l_prev1 IS NOT NULL AND l_mod1 IS NULL THEN l_chg1:= 'Deleted'; l_chgtxt1:= 'Value '||l_prev1||' deleted';
1622 ELSIF l_prev1 <> l_mod1 THEN l_chg1 := 'Modified' ; l_chgtxt1:= 'Value modified from '||l_prev1||' to '||l_mod1;
1623 END IF;
1624
1625 IF l_prev2 IS NULL AND l_mod2 IS NOT NULL THEN l_chg2:= 'Added';l_chgtxt2:= 'Value '||l_mod2||' added';
1626 ELSIF l_prev2 IS NOT NULL AND l_mod2 IS NULL THEN l_chg2:= 'Deleted'; l_chgtxt2:= 'Value '||l_prev2||' deleted';
1627 ELSIF l_prev2 <> l_mod2 THEN l_chg2 := 'Modified' ; l_chgtxt2:= 'Value modified from '||l_prev2||' to '||l_mod2;
1628 END IF;
1629
1630 l_chgtxt3:=l_chgtxt1||l_chgtxt2;
1631
1632
1633 IF l_chg1 IS NULL AND l_chg2 IS NULL THEN RETURN NULL;
1634 ELSIF l_chg1 = l_chg2 THEN
1635 l_chg3 := l_chg1;
1636 ELSIF l_chg1 IS NOT NULL THEN
1637 l_chg3 := 'Modified';
1638 END IF;
1639
1640 l_clob:= '<DeliveryDates>';
1641 IF l_chg1 = 'Added' OR l_chg2 = 'Added' THEN
1642 l_clob := l_clob || '<AddedDeliveryDates>';
1643 IF l_chg1 = 'Added' THEN
1644 l_clob := l_clob || '<DeliveryDescription>'||l_mod1||'</DeliveryDescription>';
1645 END IF;
1646 IF l_chg2 = 'Added' THEN
1647 l_clob := l_clob || '<DeliveryDate><DateElement>'||l_mod2||'</DateElement></DeliveryDate>';
1648 END IF;
1649 l_clob := l_clob || '</AddedDeliveryDates>';
1650 END IF;
1651 IF l_chg1 = 'Deleted' OR l_chg2 = 'Deleted' THEN
1652 l_clob := l_clob || '<DeletedDeliveryDates>';
1653 IF l_chg1 = 'Deleted' THEN
1654 l_clob := l_clob || '<DeliveryDescription>'||l_prev1||'</DeliveryDescription>';
1655 END IF;
1656 IF l_chg2 = 'Deleted' THEN
1657 l_clob := l_clob || '<DeliveryDate><DateElement>'||l_prev2||'</DateElement></DeliveryDate>';
1658 END IF;
1659 l_clob := l_clob || '</DeletedDeliveryDates>';
1660 END IF;
1661
1662 IF l_chg3 = 'Modified' THEN
1663 l_clob := l_clob || '<ChangedDeliveryDates><DeliveryDateChangeText>'||l_chgtxt3||'</DeliveryDateChangeText>';
1664 l_clob := l_clob || '<CurrentDeliveryDate>';
1665 IF l_chg1 = 'Modified' THEN
1666 l_clob := l_clob || '<DeliveryDescription>'||l_mod1||'</DeliveryDescription>';
1667 END IF;
1668 IF l_chg2 = 'Modified' THEN
1669 l_clob := l_clob || '<DeliveryDate><DateElement>'||l_mod2||'</DateElement></DeliveryDate>';
1670 END IF;
1671 l_clob := l_clob || '</CurrentDeliveryDate>';
1672 l_clob := l_clob || '<PreviousDeliveryDate>';
1673 IF l_chg1 = 'Modified' THEN
1674 l_clob := l_clob || '<DeliveryDescription>'||l_prev1||'</DeliveryDescription>';
1675 END IF;
1676 IF l_chg2 = 'Modified' THEN
1677 l_clob := l_clob || '<DeliveryDate><DateElement>'||l_prev2||'</DateElement></DeliveryDate>';
1678 END IF;
1679 l_clob := l_clob || '</PreviousDeliveryDate>';
1680 l_clob := l_clob || '</ChangedDeliveryDates>';
1681 END IF;
1682
1683 l_clob:= l_clob||'</DeliveryDates>';
1684
1685 RETURN l_clob;
1686
1687 ELSIF p_element = 'PAYMENTDISCOUNT' THEN
1688
1689 OPEN c_getpaymentdisc('N');
1690 FETCH c_getpaymentdisc INTO l_prev1,l_prev2,l_prev3;
1691 CLOSE c_getpaymentdisc;
1692 OPEN c_getpaymentdisc('Y');
1693 FETCH c_getpaymentdisc INTO l_mod1,l_mod2,l_mod3;
1694 CLOSE c_getpaymentdisc;
1695
1696 IF l_prev1 IS NULL AND l_mod1 IS NOT NULL THEN l_chg1:= 'Added'; l_chgtxt1:= 'Value '||l_mod1||' added';
1697 ELSIF l_prev1 IS NOT NULL AND l_mod1 IS NULL THEN l_chg1:= 'Deleted'; l_chgtxt1:= 'Value '||l_prev1||' deleted';
1698 ELSIF l_prev1 <> l_mod1 THEN l_chg1 := 'Modified' ; l_chgtxt1:= 'Value modified from '||l_prev1||' to '||l_mod1;
1699 END IF;
1700
1701 IF l_prev2 IS NULL AND l_mod2 IS NOT NULL THEN l_chg2:= 'Added';l_chgtxt2:= 'Value '||l_mod2||' added';
1702 ELSIF l_prev2 IS NOT NULL AND l_mod2 IS NULL THEN l_chg2:= 'Deleted'; l_chgtxt2:= 'Value '||l_prev2||' deleted';
1703 ELSIF l_prev2 <> l_mod2 THEN l_chg2 := 'Modified' ; l_chgtxt2:= 'Value modified from '||l_prev2||' to '||l_mod2;
1704 END IF;
1705
1706 IF l_prev3 IS NULL AND l_mod3 IS NOT NULL THEN l_chg3:= 'Added';l_chgtxt3:= 'Value '||l_mod3||' added';
1707 ELSIF l_prev3 IS NOT NULL AND l_mod3 IS NULL THEN l_chg3:= 'Deleted'; l_chgtxt3:= 'Value '||l_prev3||' deleted';
1708 ELSIF l_prev3 <> l_mod3 THEN l_chg3 := 'Modified' ; l_chgtxt3:= 'Value modified from '||l_prev3||' to '||l_mod3;
1709 END IF;
1710
1711 IF l_chg1 IS NULL AND l_chg2 IS NULL AND l_chg3 IS NULL THEN
1712 RETURN NULL;
1713 END IF;
1714
1715 l_clob := '<PaymentDiscount>';
1716
1717 IF l_chg1 = 'Added' OR l_chg2 = 'Added' OR l_chg3 = 'Added' THEN
1718 l_clob := l_clob || '<AddedPaymentDiscounts>';
1719 IF l_chg1 = 'Added' OR l_chg2 = 'Added' THEN
1720 l_clob := l_clob || '<Terms>';
1721
1722 IF l_chg1 = 'Added' THEN
1723 l_clob := l_clob || '<Percent>' || l_mod1 || '</Percent>';
1724 END IF;
1725 IF l_chg2 = 'Added' THEN
1726 l_clob := l_clob || '<DaysDue>' || l_mod2 || '</DaysDue>';
1727 END IF;
1728 l_clob := l_clob || '</Terms>';
1729 END IF;
1730 IF l_chg3 = 'Added' THEN
1731 l_clob := l_clob || '<NetDays>' || l_mod3 || '</NetDays>';
1732 END IF;
1733 l_clob := l_clob || '</AddedPaymentDiscounts>';
1734 END IF;
1735
1736 IF l_chg1 = 'Deleted' OR l_chg2 = 'Deleted' OR l_chg3 = 'Deleted' THEN
1737 l_clob := l_clob || '<DeletedPaymentDiscounts>';
1738 IF l_chg1 = 'Deleted' OR l_chg2 = 'Deleted' THEN
1739 l_clob := l_clob || '<Terms>';
1740
1741 IF l_chg1 = 'Deleted' THEN
1742 l_clob := l_clob || '<Percent>' || l_prev1 || '</Percent>';
1743 END IF;
1744 IF l_chg2 = 'Deleted' THEN
1745 l_clob := l_clob || '<DaysDue>' || l_prev2 || '</DaysDue>';
1746 END IF;
1747 l_clob := l_clob || '</Terms>';
1748 END IF;
1749 IF l_chg3 = 'Deleted' THEN
1750 l_clob := l_clob || '<NetDays>' || l_prev3 || '</NetDays>';
1751 END IF;
1752 l_clob := l_clob || '</DeletedPaymentDiscounts>';
1753 END IF;
1754
1755 IF l_chg1 = 'Modified' OR l_chg2 = 'Modified' OR l_chg3 = 'Modified' THEN
1756 l_clob := l_clob || '<ChangePaymentDiscounts>';
1757 l_clob := l_clob || '<PaymentDiscountChangeText>' || l_chgtxt1 || l_chgtxt2 || l_chgtxt3 || '</PaymentDiscountChangeText>';
1758 l_clob := l_clob || '<CurrentPaymentDiscount>';
1759 IF l_chg1 = 'Modified' OR l_chg2 = 'Modified' THEN
1760 l_clob := l_clob || '<Terms>';
1761 IF l_chg1 = 'Modified' THEN
1762 l_clob := l_clob || '<Percent>' || l_mod1 || '</Percent>';
1763 END IF;
1764 IF l_chg2 = 'Modified' THEN
1765 l_clob := l_clob || '<DaysDue>' || l_mod2 || '</DaysDue>';
1766 END IF;
1767 l_clob := l_clob || '</Terms>';
1768 END IF;
1769 IF l_chg3 = 'Modified' THEN
1770 l_clob := l_clob || '<NetDays>' || l_mod1 || '</NetDays>';
1771 END IF;
1772 l_clob := l_clob || '</CurrentPaymentDiscount>';
1773 l_clob := l_clob || '<PreviousPaymentDiscount>';
1774 IF l_chg1 = 'Modified' OR l_chg2 = 'Modified' THEN
1775 l_clob := l_clob || '<Terms>';
1776 IF l_chg1 = 'Modified' THEN
1777 l_clob := l_clob || '<Percent>' || l_prev1 || '</Percent>';
1778 END IF;
1779 IF l_chg2 = 'Modified' THEN
1780 l_clob := l_clob || '<DaysDue>' || l_prev2 || '</DaysDue>';
1781 END IF;
1782 l_clob := l_clob || '</Terms>';
1783 END IF;
1784 IF l_chg3 = 'Modified' THEN
1785 l_clob := l_clob || '<NetDays>' || l_prev3 || '</NetDays>';
1786 END IF;
1787 l_clob := l_clob || '</PreviousPaymentDiscount>';
1788 l_clob := l_clob || '</ChangePaymentDiscounts>';
1789 END IF;
1790 l_clob := l_clob || '</PaymentDiscount>';
1791 RETURN l_clob;
1792
1793 ELSIF p_element = 'MISCELLANEOUSTEXTDETAILS' THEN
1794 OPEN c_misctext_base;
1795 FETCH c_misctext_base INTO l_prev1,l_clob2;
1796 CLOSE c_misctext_base;
1797
1798 OPEN c_misctext_mod;
1799 FETCH c_misctext_mod INTO l_mod1,l_clob3;
1800 CLOSE c_misctext_mod;
1801
1802 IF l_prev1 IS NULL AND l_mod1 IS NOT NULL THEN l_chg1:= 'Added'; l_chgtxt1:= 'Value '||l_mod1||' added';
1803 ELSIF l_prev1 IS NOT NULL AND l_mod1 IS NULL THEN l_chg1:= 'Deleted'; l_chgtxt1:= 'Value '||l_prev1||' deleted';
1804 ELSIF l_prev1 <> l_mod1 THEN l_chg1 := 'Modified' ; l_chgtxt1:= 'Value modified from '||l_prev1||' to '||l_mod1;
1805 END IF;
1806
1807 IF l_clob2 IS NULL AND l_clob3 IS NOT NULL THEN l_chg2:= 'Added';l_chgtxt2:= 'Value '||l_mod2||' added';
1808 ELSIF l_clob2 IS NOT NULL AND l_clob3 IS NULL THEN l_chg2:= 'Deleted'; l_chgtxt2:= 'Value '||l_prev2||' deleted';
1809 ELSIF l_clob2 <> l_clob3 THEN l_chg2 := 'Modified' ; l_chgtxt2:= 'Value modified from '||l_prev2||' to '||l_mod2;
1810 END IF;
1811
1812 IF l_chg1 IS NULL AND l_chg2 IS NULL THEN
1813 RETURN NULL;
1814 END IF;
1815
1816
1817 l_chgtxt3:= l_chgtxt1||l_chgtxt2;
1818
1819 IF l_chg1 = l_chg2 THEN
1820 l_chg3 := l_chg1;
1821 ELSIF l_chg1 IS NOT NULL AND l_chg2 IS NOT NULL THEN
1822 l_chg3 := 'Modified';
1823 END IF;
1824
1825 dbms_lob.createtemporary(l_clob, true);
1826 dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
1827 dbms_lob.append(l_clob,'<MiscellaneousTextDetails changeFlag="'||l_chg3||'" changeText="'||l_chgtxt3||
1828 '" previousMiscellaneousText="'||l_clob2||'" previousSection="'||l_prev1||'">' || '<MiscellaneousText>' ||l_clob3
1829 || '</MiscellaneousText>' || '<Section>' ||l_mod1|| '</Section></MiscellaneousTextDetails>');
1830 dbms_lob.close(l_clob);
1831 RETURN l_clob;
1832
1833 ELSIF p_element = 'OBLIGATEDAMOUNT' THEN
1834
1835 --addedobligatedAmounts xml formation
1836 FOR c IN c_addedObligatedAmounts LOOP
1837 SELECT CLM_MISC_LOA LoaDetails,CLM_AGENCY_ACCT_IDENTIFIER AccountingIdentifier,
1838 ACRN FinancialCodeValue,round(AMOUNT_FUNDED,5) ObligatedAmount,CODE_COMBINATION_ID code_comb_id
1839 INTO l_mod1,l_mod2,l_mod3,l_mod4,l_mod5
1840 FROM po_distributions_all WHERE po_distribution_id = c.po_dist_id and draft_id = DRAFTID;
1841
1842 l_clob := l_clob || '<AddedObligatedAmounts><ObligatedAmount>'||l_mod4||'</ObligatedAmount><FinancialReference>' ||
1843 '<FinancialCodeDescription>Accounting Classification Reference Number (ACRN)</FinancialCodeDescription><FinancialCodeValue>'||
1844 l_mod3||'</FinancialCodeValue></FinancialReference>';
1845 IF l_mod2 IS NOT NULL THEN
1846 l_clob := l_clob ||
1847 '<AccountingIdentifier><AgencyAccountingIdentifier>'||l_mod2||'</AgencyAccountingIdentifier></AccountingIdentifier>';
1848 ELSIF l_mod1 IS NOT NULL THEN
1849 l_clob := l_clob || '<LoaDetails><Blob>'||l_mod1||'</Blob>';
1850 FOR i IN c_obligatedAmountcomponents(c.po_dist_id,DRAFTID) LOOP
1851 l_clob := l_clob || '<LOA><ComponentTitle>'||
1852 i.ComponentTitle||'</ComponentTitle><ComponentValue>'||
1853 i.ComponentValue||'</ComponentValue></LOA>';
1854 END LOOP;
1855 l_clob := l_clob || '</LoaDetails>';
1856 END IF;
1857 l_clob := l_clob || '</AddedObligatedAmounts>';
1858 END LOOP;
1859 --end
1860 --deletedobligatedAmounts xml formation
1861 FOR c IN c_deletedObligatedAmounts LOOP
1862 SELECT CLM_MISC_LOA LoaDetails,CLM_AGENCY_ACCT_IDENTIFIER AccountingIdentifier,
1863 ACRN FinancialCodeValue,round(AMOUNT_FUNDED,5) ObligatedAmount,CODE_COMBINATION_ID code_comb_id
1864 INTO l_mod1,l_mod2,l_mod3,l_mod4,l_mod5
1865 FROM po_distributions_all WHERE po_distribution_id = c.po_dist_id and draft_id = -1;
1866
1867 l_clob := l_clob || '<DeletedObligatedAmounts><ObligatedAmount>'||l_mod4||'</ObligatedAmount><FinancialReference>' ||
1868 '<FinancialCodeDescription>Accounting Classification Reference Number (ACRN)</FinancialCodeDescription><FinancialCodeValue>'||
1869 l_mod3||'</FinancialCodeValue></FinancialReference>';
1870 IF l_mod2 IS NOT NULL THEN
1871 l_clob := l_clob ||
1872 '<AccountingIdentifier><AgencyAccountingIdentifier>'||l_mod2||'</AgencyAccountingIdentifier></AccountingIdentifier>';
1873 ELSIF l_mod1 IS NOT NULL THEN
1874 l_clob := l_clob || '<LoaDetails><Blob>'||l_mod1||'</Blob>';
1875 FOR i IN c_obligatedAmountcomponents(c.po_dist_id,-1) LOOP
1876 l_clob := l_clob || '<LOA><ComponentTitle>'||
1877 i.ComponentTitle||'</ComponentTitle><ComponentValue>'||
1878 i.ComponentValue||'</ComponentValue></LOA>';
1879 END LOOP;
1880 l_clob := l_clob || '</LoaDetails>';
1881 END IF;
1882 l_clob := l_clob || '</DeletedObligatedAmounts>';
1883 END LOOP;
1884 --end
1885 --changedligatedAmounts xml formation
1886 FOR c IN c_changedObligatedAmounts LOOP
1887 SELECT CLM_MISC_LOA LoaDetails,CLM_AGENCY_ACCT_IDENTIFIER AccountingIdentifier,
1888 ACRN FinancialCodeValue,round(AMOUNT_FUNDED,5) ObligatedAmount,CODE_COMBINATION_ID code_comb_id
1889 INTO l_mod1,l_mod2,l_mod3,l_mod4,l_mod5
1890 FROM po_distributions_all WHERE po_distribution_id = c.po_dist_id and draft_id = DRAFTID;
1891
1892 SELECT CLM_MISC_LOA LoaDetails,CLM_AGENCY_ACCT_IDENTIFIER AccountingIdentifier,
1893 ACRN FinancialCodeValue,round(AMOUNT_FUNDED,5) ObligatedAmount,CODE_COMBINATION_ID code_comb_id
1894 INTO l_prev1,l_prev2,l_prev3,l_prev4,l_prev5
1895 FROM po_distributions_all WHERE po_distribution_id = c.po_dist_id and draft_id = -1;
1896
1897 IF nvl(l_mod1,-99) <> nvl(l_prev1,-98) THEN l_chg1:= 'Modified'; l_chgtxt1:= 'Blob Value modified from '|| l_prev1 || ' to '
1898 ||l_mod1; END IF;
1899 IF nvl(l_mod2,-99) <> nvl(l_prev2,-98) THEN l_chg2:= 'Modified'; l_chgtxt2:= 'AgencyAccountingIdentifier Value modified from '||
1900 l_prev2 || ' to ' ||l_mod2; END IF;
1901 IF nvl(l_mod3,-99) <> nvl(l_prev3,-98) THEN l_chg3:= 'Modified'; l_chgtxt3:= 'FinancialCodeValue modified from '|| l_prev3 ||
1902 ' to ' ||l_mod3; END IF;
1903 IF nvl(l_mod4,-99) <> nvl(l_prev4,-98) THEN l_chg4:= 'Modified'; l_chgtxt4:= 'ObligatedAmount Value modified from '|| l_prev4 ||
1904 ' to ' ||l_mod4; END IF;
1905 IF nvl(l_mod5,-99) <> nvl(l_prev5,-98) THEN l_chg5:= 'Modified'; l_chgtxt5:= 'LOA Value modified from '|| l_prev5 ||
1906 ' to ' ||l_mod5; END IF;
1907
1908 l_chgtxt6:=l_chgtxt1||l_chgtxt2||l_chgtxt3||l_chgtxt4||l_chgtxt5;
1909
1910 l_clob := l_clob || '<ChangedObligatedAmounts><ObligatedAmountsChangeText>'|| l_chgtxt6 ||'</ObligatedAmountsChangeText>'
1911 || '<ObligatedAmountDelta>'||(NVL(l_mod4,0)-NVL(l_prev4,0))||'</ObligatedAmountDelta>';
1912
1913 l_clob := l_clob || '<CurrentObligatedAmount><ObligatedAmount>'||l_mod4||'</ObligatedAmount><FinancialReference>' ||
1914 '<FinancialCodeDescription>Accounting Classification Reference Number (ACRN)</FinancialCodeDescription><FinancialCodeValue>'||
1915 l_mod3||'</FinancialCodeValue></FinancialReference>';
1916 IF l_mod2 IS NOT NULL THEN
1917 l_clob := l_clob ||
1918 '<AccountingIdentifier><AgencyAccountingIdentifier>'||l_mod2||'</AgencyAccountingIdentifier></AccountingIdentifier>';
1919 ELSIF l_mod1 IS NOT NULL THEN
1920 l_clob := l_clob || '<LoaDetails><Blob>'||l_mod1||'</Blob>';
1921 FOR i IN c_obligatedAmountcomponents(c.po_dist_id,DRAFTID) LOOP
1922 l_clob := l_clob || '<LOA><ComponentTitle>'||
1923 i.ComponentTitle||'</ComponentTitle><ComponentValue>'||
1924 i.ComponentValue||'</ComponentValue></LOA>';
1925 END LOOP;
1926 l_clob := l_clob || '</LoaDetails>';
1927 END IF;
1928 l_clob := l_clob || '</CurrentObligatedAmount>';
1929
1930 l_clob := l_clob || '<PreviousObligatedAmount><ObligatedAmount>'||l_prev4||'</ObligatedAmount><FinancialReference>' ||
1931 '<FinancialCodeDescription>Accounting Classification Reference Number (ACRN)</FinancialCodeDescription><FinancialCodeValue>'||
1932 l_prev3||'</FinancialCodeValue></FinancialReference>';
1933 IF l_prev2 IS NOT NULL THEN
1934 l_clob := l_clob ||
1935 '<AccountingIdentifier><AgencyAccountingIdentifier>'||l_prev2||'</AgencyAccountingIdentifier></AccountingIdentifier>';
1936 ELSIF l_prev1 IS NOT NULL THEN
1937 l_clob := l_clob || '<LoaDetails><Blob>'||l_prev1||'</Blob>';
1938 FOR i IN c_obligatedAmountcomponents(c.po_dist_id,-1) LOOP
1939 l_clob := l_clob || '<LOA><ComponentTitle>'||
1940 i.ComponentTitle||'</ComponentTitle><ComponentValue>'||
1941 i.ComponentValue||'</ComponentValue></LOA>';
1942 END LOOP;
1943 l_clob := l_clob || '</LoaDetails>';
1944 END IF;
1945 l_clob := l_clob || '</PreviousObligatedAmount>';
1946
1947 l_clob := l_clob || '</ChangedObligatedAmounts>';
1948 END LOOP;
1949 --end
1950
1951 IF L_CLOB IS NOT NULL THEN
1952 l_clob := '<ObligatedAmountsModificationDetails>' || l_clob || '</ObligatedAmountsModificationDetails>';
1953 END IF;
1954
1955 RETURN l_clob;
1956
1957 ELSIF p_element = 'OTHERAMOUNTS' THEN
1958
1959 FOR c IN c_otheramounts LOOP
1960
1961 l_prev1 := getOtherAmounts('N',c.AmountType);
1962 l_mod1 := getOtherAmounts('Y',c.AmountType);
1963
1964 IF l_prev1 IS NULL AND l_mod1 IS NOT NULL THEN l_chg1:= 'Added'; l_chgtxt1:= 'Value '||l_mod1||' added';
1965 ELSIF l_prev1 IS NOT NULL AND l_mod1 IS NULL THEN l_chg1:= 'Deleted'; l_chgtxt1:= 'Value '||l_prev1||' deleted';
1966 ELSIF l_prev1 <> l_mod1 THEN l_chg1 := 'Modified' ; l_chgtxt1:= 'Value modified from '||l_prev1||' to '||l_mod1;
1967 END IF;
1968
1969 IF l_chg1 IS NOT NULL THEN
1970 l_clob:= l_clob || '<OtherAmounts changeFlag="'||l_chg1||'" changeText="'||l_chgtxt1||
1971 '" previousAmountDescription="'||c.AmountType||'" previousAmount="'||l_prev1||'" deltaAmount="'||(l_mod1-l_prev1)||'">';
1972 l_clob:= l_clob || '<AmountDescription>' || c.AmountType || '</AmountDescription>';
1973 l_clob:= l_clob || '<Amount>' || l_mod1 || '</Amount></OtherAmounts>';
1974 END IF;
1975
1976 END LOOP;
1977
1978 RETURN l_clob;
1979
1980 END IF;
1981
1982 RETURN NULL;
1983 END getElement;
1984
1985 FUNCTION getTimezone(p_date DATE) RETURN VARCHAR2 IS
1986 l_date varchar2(100);
1987 l_res varchar2(100);
1988 l_sql VARCHAR(1000);
1989 c_zone SYS_REFCURSOR;
1990 BEGIN
1991
1992 IF p_date IS NULL THEN
1993 RETURN NULL;
1994 END IF;
1995
1996 BEGIN
1997 l_date := To_Char(CAST(p_date AS TIMESTAMP WITH TIME ZONE),'TZH:TZM');
1998 EXCEPTION WHEN OTHERS THEN
1999 NULL;
2000 END;
2001
2002 l_sql := 'SELECT EDA FROM PO_EDA_MAPPING WHERE Grouping = ''Common'' AND ATTRIBUTE=''TimeZone'' AND EBS = '''|| l_date ||'''';
2003 OPEN c_zone FOR l_sql;
2004 FETCH c_zone INTO l_res;
2005 IF l_res IS NULL THEN
2006 l_res := 'Non-US Local Time';
2007 END IF;
2008
2009 RETURN l_res;
2010 END getTimezone;
2011
2012
2013 FUNCTION GetVariableValue(p_cat_id number,p_variable_code varchar2,p_variable_value_id varchar2,p_attribute_value_set_id number)
2014 return VARCHAR2 is
2015 p_api_version NUMBER;
2016 p_init_msg_list VARCHAR2(4000) := FND_API.G_FALSE;
2017 p_doc_type VARCHAR2(50):=DOCUMENTTYPE;
2018 p_doc_id NUMBER :=DOCUMENTID;
2019 x_return_status VARCHAR2(10);
2020 x_msg_data VARCHAR2(4000);
2021 x_msg_count NUMBER;
2022
2023 x_sys_var_value_tbl OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type;
2024
2025 l_variable_value VARCHAR2(4000);
2026 l_validation_type fnd_flex_value_sets.validation_type%TYPE;
2027 l_table_name fnd_flex_validation_tables.application_table_name%TYPE;
2028 l_name_col fnd_flex_validation_tables.value_column_name%TYPE;
2029 l_id_col fnd_flex_validation_tables.id_column_name%TYPE;
2030 l_additional_where_clause fnd_flex_validation_tables.additional_where_clause%TYPE;
2031 l_sql_stmt LONG;
2032 TYPE cur_typ IS REF CURSOR;
2033 c_cursor cur_typ;
2034
2035
2036
2037 CURSOR csr_get_validation_type(p_value_set_id IN NUMBER) IS
2038 SELECT validation_type
2039 FROM FND_FLEX_VALUE_SETS
2040 WHERE flex_value_set_id = p_value_set_id;
2041
2042 CURSOR csr_value_set_table(p_value_set_id IN NUMBER) IS
2043 SELECT application_table_name,
2044 value_column_name,
2045 id_column_name,
2046 additional_where_clause
2047 FROM fnd_flex_validation_tables
2048 WHERE flex_value_set_id = p_value_set_id;
2049
2050
2051 PROCEDURE Get_System_Variables (
2052 p_api_version IN NUMBER,
2053 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2054
2055 x_return_status OUT NOCOPY VARCHAR2,
2056 x_msg_data OUT NOCOPY VARCHAR2,
2057 x_msg_count OUT NOCOPY NUMBER,
2058
2059 p_doc_type IN VARCHAR2,
2060 p_doc_id IN NUMBER,
2061 p_only_doc_variables IN VARCHAR2 := FND_API.G_TRUE,
2062
2063 x_sys_var_value_tbl OUT NOCOPY OKC_TERMS_UTIL_GRP.sys_var_value_tbl_type
2064 ) IS
2065 l_api_version CONSTANT NUMBER := 1;
2066 l_api_name CONSTANT VARCHAR2(30) := 'Get_System_Variables';
2067 i NUMBER := 1;
2068 l_doc_class VARCHAR2(30) := '?';
2069 CURSOR var_doc_lst_crs IS
2070 SELECT distinct var.variable_code
2071 FROM okc_k_articles_b kart, okc_k_art_variables var
2072 WHERE kart.document_type=p_doc_type AND kart.document_id=p_doc_id
2073 and var.cat_id=kart.id AND variable_type='S';
2074
2075 CURSOR var_def_lst_crs IS
2076 SELECT busvar.variable_code
2077 FROM OKC_BUS_DOC_TYPES_B vo, OKC_BUS_VARIABLES_B busvar
2078 WHERE vo.document_type=p_doc_type AND busvar.contract_expert_yn='Y'
2079 AND busvar.variable_intent=vo.intent;
2080 CURSOR doc_cls_lst_crs IS
2081 SELECT document_type_class
2082 FROM okc_bus_doc_types_v
2083 WHERE document_type=p_doc_type;
2084 BEGIN
2085
2086 x_return_status := 'S';
2087
2088 IF p_only_doc_variables = FND_API.G_TRUE THEN
2089 FOR cr IN var_doc_lst_crs LOOP
2090 x_sys_var_value_tbl(i).Variable_code := cr.variable_code;
2091 i := i+1;
2092 END LOOP;
2093 ELSE
2094 FOR cr IN var_def_lst_crs LOOP
2095 x_sys_var_value_tbl(i).Variable_code := cr.variable_code;
2096 i := i+1;
2097 END LOOP;
2098 END IF;
2099
2100 OPEN doc_cls_lst_crs;
2101 FETCH doc_cls_lst_crs INTO l_doc_class;
2102 CLOSE doc_cls_lst_crs;
2103
2104 IF l_doc_class in ('BSA','SO') THEN
2105 --IF l_doc_class = 'OM' THEN
2106 OKC_OM_INT_GRP.get_article_variable_values(
2107 p_api_version => p_api_version ,
2108 p_init_msg_list => p_init_msg_list,
2109 x_msg_data => x_msg_data ,
2110 x_msg_count => x_msg_count ,
2111 x_return_status => x_return_status,
2112
2113 p_doc_type => p_doc_type,
2114 p_doc_id => p_doc_id,
2115 p_sys_var_value_tbl => x_sys_var_value_tbl);
2116
2117 --------------------------------------------
2118
2119 --------------------------------------------
2120 ELSIF l_doc_class = 'PO' THEN
2121 OKC_PO_INT_GRP.get_article_variable_values(
2122 p_api_version => p_api_version ,
2123 p_init_msg_list => p_init_msg_list,
2124 x_msg_data => x_msg_data ,
2125 x_msg_count => x_msg_count ,
2126 x_return_status => x_return_status,
2127
2128 p_doc_type => p_doc_type,
2129 p_doc_id => p_doc_id,
2130 p_sys_var_value_tbl => x_sys_var_value_tbl);
2131 --------------------------------------------
2132 --------------------------------------------
2133 ELSIF l_doc_class = 'SOURCING' THEN
2134
2135 OKC_PON_INT_GRP.get_article_variable_values(
2136 p_api_version => p_api_version ,
2137 p_init_msg_list => p_init_msg_list,
2138 x_msg_data => x_msg_data ,
2139 x_msg_count => x_msg_count ,
2140 x_return_status => x_return_status,
2141
2142 p_doc_type => p_doc_type,
2143 p_doc_id => p_doc_id,
2144 p_sys_var_value_tbl => x_sys_var_value_tbl);
2145 --------------------------------------------
2146 --------------------------------------------
2147 ELSIF l_doc_class = 'QUOTE' THEN
2148
2149 OKC_ASO_INT_GRP.get_article_variable_values(
2150 p_api_version => p_api_version ,
2151 p_init_msg_list => p_init_msg_list,
2152 x_msg_data => x_msg_data ,
2153 x_msg_count => x_msg_count ,
2154 x_return_status => x_return_status,
2155 -- p_doc_type => p_doc_type,
2156 p_doc_id => p_doc_id,
2157 p_sys_var_value_tbl => x_sys_var_value_tbl);
2158
2159 --------------------------------------------
2160 --------------------------------------------
2161 --kkolukul:bug 6924032 Modified for Repository Enhancement
2162 ELSIF l_doc_class = 'REPOSITORY' THEN
2163
2164 OKC_XPRT_REP_INT_PVT.get_clause_variable_values(
2165 p_api_version => p_api_version,
2166 p_init_msg_list => p_init_msg_list,
2167 p_doc_type => p_doc_type,
2168 p_doc_id => p_doc_id,
2169 p_sys_var_value_tbl => x_sys_var_value_tbl,
2170 x_return_status => x_return_status,
2171 x_msg_data => x_msg_data,
2172 x_msg_count => x_msg_count );
2173
2174
2175
2176 ELSE
2177 NULL;
2178 END IF;
2179
2180 -- Standard call to get message count and if count is 1, get message info.
2181 EXCEPTION
2182 WHEN OTHERS THEN
2183 --ROLLBACK TO g_Get_System_Variables;
2184
2185 x_return_status := 'U' ;
2186 END Get_System_Variables ;
2187
2188
2189
2190 BEGIN
2191
2192 IF p_variable_value_id IS NULL THEN
2193 Get_System_Variables (
2194 p_api_version => 1,
2195 p_init_msg_list => p_init_msg_list,
2196 p_doc_type => p_doc_type,
2197 p_doc_id => p_doc_id,
2198 x_return_status => x_return_status,
2199 x_msg_data => x_msg_data,
2200 x_msg_count => x_msg_count,
2201 x_sys_var_value_tbl => x_sys_var_value_tbl
2202 );
2203
2204 IF x_sys_var_value_tbl.Count > 0 THEN
2205 FOR i IN x_sys_var_value_tbl.first..x_sys_var_value_tbl.last LOOP
2206 IF x_sys_var_value_tbl(i).variable_code = p_variable_code THEN
2207 l_variable_value := x_sys_var_value_tbl(i).variable_value_id;
2208 RETURN l_variable_value;
2209 END IF;
2210 END LOOP;
2211 END IF;
2212 ELSIF p_variable_value_id IS NOT NULL THEN
2213
2214 BEGIN
2215
2216 l_variable_value := NULL;
2217
2218 OPEN csr_get_validation_type(p_value_set_id => p_attribute_value_set_id);
2219 FETCH csr_get_validation_type INTO l_validation_type;
2220 CLOSE csr_get_validation_type;
2221
2222 /* Valueset is Table type, execute the dynamic sql to get the variable value */
2223 IF l_validation_type = 'F' THEN
2224
2225 OPEN csr_value_set_table(p_value_set_id => p_attribute_value_set_id);
2226 FETCH csr_value_set_table INTO l_table_name, l_name_col, l_id_col, l_additional_where_clause;
2227 CLOSE csr_value_set_table;
2228
2229 l_sql_stmt := 'SELECT '||l_name_col||
2230 ' FROM ('||
2231 ' SELECT '||l_name_col||' , '||l_id_col||
2232 ' FROM '||l_table_name||' ';
2233
2234 IF TRIM(l_additional_where_clause) IS NOT NULL THEN
2235 IF INSTR(UPPER(l_additional_where_clause),'WHERE') > 0 THEN
2236 l_sql_stmt := l_sql_stmt || l_additional_where_clause;
2237 ELSE
2238 l_sql_stmt := l_sql_stmt || 'WHERE '||l_additional_where_clause;
2239 END IF;
2240 END IF;
2241
2242 l_sql_stmt := l_sql_stmt || ' ) WHERE to_char('||l_id_col|| ') = '''|| p_variable_value_id || '''';
2243
2244 OPEN c_cursor FOR l_sql_stmt;
2245 FETCH c_cursor INTO l_variable_value;
2246 CLOSE c_cursor;
2247
2248 /* Valueset is Independent type */
2249 ELSIF l_validation_type = 'I' THEN
2250
2251 l_variable_value := OKC_TERMS_UTIL_PVT.GET_VALUE_SET_VARIABLE_VALUE (
2252 p_CONTEXT => l_validation_type,
2253 p_VALUE_SET_ID => p_attribute_value_set_id,
2254 p_FLEX_VALUE_ID => p_variable_value_id);
2255
2256 /* Valueset is None type */
2257 ELSE
2258
2259 l_variable_value := p_variable_value_id;
2260 END IF;
2261
2262 EXCEPTION
2263 WHEN OTHERS THEN
2264
2265 l_variable_value := NULL;
2266
2267 END;
2268 RETURN l_variable_value;
2269 END IF;
2270
2271 RETURN NULL;
2272
2273 END GetVariableValue;
2274
2275 FUNCTION get_miscclausetxt(p_scn_id NUMBER,isMod VARCHAR2) RETURN CLOB AS
2276
2277 CURSOR c2 IS
2278 SELECT article_text
2279 FROM okc_article_versions ver, okc_k_articles_b kartb
2280 WHERE kartb.article_version_id = ver.article_version_id
2281 AND kartb.sav_sae_id = ver.article_id
2282 AND scn_id = p_scn_id
2283 AND kartb.document_id = Decode(isMod,'N',DOCUMENTID,DRAFTID);
2284
2285 l_temp CLOB;
2286
2287 BEGIN
2288
2289 FOR c IN c2 LOOP
2290 l_temp:=l_temp||' '||c.article_text;
2291 END LOOP;
2292 RETURN l_temp;
2293 END get_miscclausetxt;
2294
2295
2296 FUNCTION getOtherAmounts(isMod varchar2, p_amt_type varchar2) RETURN number is
2297 cursor c_amount is
2298 select sum(LIST_PRICE_PER_UNIT * QUANTITY )
2299 FROM
2300 po_lines_merge_v pol
2301 WHERE ( ( (isMod = 'N') AND (pol.po_header_id = DOCUMENTID AND pol.draft_id = -1 AND pol.change_status is NULL)) OR
2302 ( (isMod = 'Y') AND (pol.draft_id = DRAFTID AND pol.change_status IS NOT NULL)) )
2303 and nvl(CLM_EXERCISED_FLAG,'N') = 'N'
2304 and CONTRACT_TYPE = 'FFP';
2305
2306 cursor c_amount2 is
2307 select sum(LIST_PRICE_PER_UNIT)
2308 FROM
2309 po_lines_merge_v pol
2310 WHERE ( ( (isMod = 'N') AND (pol.po_header_id = DOCUMENTID AND pol.draft_id = -1 AND pol.change_status is NULL)) OR
2311 ( (isMod = 'Y') AND (pol.draft_id = DRAFTID AND pol.change_status IS NOT NULL)) )
2312 and nvl(CLM_EXERCISED_FLAG,'N') = 'N';
2313
2314 cursor c_amount4 is
2315 select sum(FUNDED_VALUE)
2316 FROM PO_DISTRIBUTIONS_merge_v ploc
2317 WHERE
2318 ( ( (isMod = 'N') AND (ploc.po_header_id = DOCUMENTID AND ploc.draft_id = -1 AND ploc.change_status is NULL)) OR
2319 ( (isMod = 'Y') AND (ploc.draft_id = DRAFTID AND ploc.change_status is not null) ) ) ;
2320
2321 CURSOR c_award_fee IS
2322 SELECT Sum(PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_LINE_EXT_ATTRS',pk1_value => documentId, pk2_value => -1,
2323 p_attr_grp_int_name =>
2324 'AMT_'||Decode(pline.contract_type,'AWARD_FEE','AF','COST_PLUS_AWARD_FEE','CPAF')||'_'
2325 ||Decode(phdr.clm_award_type,'IQ','IIQ','REQ','IR','NEG_AGMNT','IN','DQ','IDQ'),
2326 p_attr_int_name => 'AWARD_FEE'))
2327 FROM po_lines_merge_v pline, po_headers_merge_v phdr
2328 WHERE
2329 ( ( (isMod = 'N') AND (phdr.po_header_id = DOCUMENTID AND phdr.draft_id = -1 AND phdr.change_status is NULL)) OR
2330 ( (isMod = 'Y') AND (phdr.draft_id = DRAFTID AND phdr.change_status is not null) ) )
2331 and
2332 ( ( (isMod = 'N') AND (pline.po_header_id = DOCUMENTID AND pline.draft_id = -1 AND pline.change_status is NULL)) OR
2333 ( (isMod = 'Y') AND (pline.draft_id = DRAFTID AND pline.change_status is not null) ) )
2334 AND phdr.po_header_id = pline.po_header_id
2335 AND phdr.po_header_id = documentId
2336 AND Nvl(CLM_EXERCISED_FLAG,'N') = 'N';
2337
2338 CURSOR c_base_fee IS
2339 SELECT Sum(PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_LINE_EXT_ATTRS',pk1_value => documentId, pk2_value => -1,
2340 p_attr_grp_int_name =>
2341 'AMT_CPAF_'||Decode(phdr.clm_award_type,'IQ','IIQ','REQ','IR','NEG_AGMNT','IN','DQ','IDQ'),
2342 p_attr_int_name => 'BASE_FEE'))
2343 FROM po_lines_merge_v pline, po_headers_merge_v phdr
2344 WHERE
2345 ( ( (isMod = 'N') AND (phdr.po_header_id = DOCUMENTID AND phdr.draft_id = -1 AND phdr.change_status is NULL)) OR
2346 ( (isMod = 'Y') AND (phdr.draft_id = DRAFTID AND phdr.change_status is not null) ) )
2347 and
2348 ( ( (isMod = 'N') AND (pline.po_header_id = DOCUMENTID AND pline.draft_id = -1 AND pline.change_status is NULL)) OR
2349 ( (isMod = 'Y') AND (pline.draft_id = DRAFTID AND pline.change_status is not null) ) )
2350 AND phdr.po_header_id = pline.po_header_id
2351 AND phdr.po_header_id = documentId
2352 AND Nvl(CLM_EXERCISED_FLAG,'N') = 'N';
2353
2354 CURSOR c_ceiling_ex IS
2355 SELECT Sum(PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_LINE_EXT_ATTRS',pk1_value => documentId,
2356 pk2_value => -1,p_attr_grp_int_name => 'AMT_'||
2357 Decode(pline.contract_type,'FP_INCENTIVE_SUCCESSIVE_TARGET','FIST','FP_INCENTIVE_FIRM_TARGET','FIFT')
2358 ||'_'||Decode(phdr.clm_award_type,'IQ','IIQ','REQ','IR','NEG_AGMNT','IN','DQ','IDQ'),
2359 p_attr_int_name => 'CEILING_PRICE'))
2360 FROM po_lines_merge_v pline, po_headers_merge_v phdr
2361 WHERE
2362 ( ( (isMod = 'N') AND (phdr.po_header_id = DOCUMENTID AND phdr.draft_id = -1 AND phdr.change_status is NULL)) OR
2363 ( (isMod = 'Y') AND (phdr.draft_id = DRAFTID AND phdr.change_status is not null) ) )
2364 and
2365 ( ( (isMod = 'N') AND (pline.po_header_id = DOCUMENTID AND pline.draft_id = -1 AND pline.change_status is NULL)) OR
2366 ( (isMod = 'Y') AND (pline.draft_id = DRAFTID AND pline.change_status is not null) ) )
2367 AND phdr.po_header_id = pline.po_header_id
2368 AND phdr.po_header_id = documentId
2369 AND Nvl(CLM_EXERCISED_FLAG,'N') = 'N';
2370
2371 CURSOR c_estimated_cost IS
2372 SELECT Sum(PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_LINE_EXT_ATTRS',pk1_value => documentId, pk2_value => -1,
2373 p_attr_grp_int_name => 'AMT_'|| Decode(pline.contract_type,'COST_PLUS_FIXED_FEE','CPFF','COST_PLUS_AWARD_FEE','CPAF')||'_'||
2374 Decode(phdr.clm_award_type,'IQ','IIQ','REQ','IR','NEG_AGMNT','IN','DQ','IDQ'),
2375 p_attr_int_name => 'ESTIMATED_COST'))
2376 FROM po_lines_merge_v pline, po_headers_merge_v phdr
2377 WHERE
2378 ( ( (isMod = 'N') AND (phdr.po_header_id = DOCUMENTID AND phdr.draft_id = -1 AND phdr.change_status is NULL)) OR
2379 ( (isMod = 'Y') AND (phdr.draft_id = DRAFTID AND phdr.change_status is not null) ))
2380 and
2381 ( ( (isMod = 'N') AND (pline.po_header_id = DOCUMENTID AND pline.draft_id = -1 AND pline.change_status is NULL)) OR
2382 ( (isMod = 'Y') AND (pline.draft_id = DRAFTID AND pline.change_status is not null) ))
2383 AND phdr.po_header_id = pline.po_header_id
2384 AND phdr.po_header_id = documentId
2385 AND Nvl(CLM_EXERCISED_FLAG,'N') = 'N';
2386
2387 CURSOR c_fixed_fee IS
2388 SELECT Sum(PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_LINE_EXT_ATTRS',pk1_value => documentId, pk2_value => -1,
2389 p_attr_grp_int_name => 'AMT_CPFF_'||
2390 Decode(phdr.clm_award_type,'IQ','IIQ','REQ','IR','NEG_AGMNT','IN','DQ','IDQ'),
2391 p_attr_int_name => 'FIXED_FEE'))
2392 FROM po_lines_merge_v pline, po_headers_merge_v phdr
2393 WHERE
2394 ( ( (isMod = 'N') AND (phdr.po_header_id = DOCUMENTID AND phdr.draft_id = -1 AND phdr.change_status is NULL)) OR
2395 ( (isMod = 'Y') AND (phdr.draft_id = DRAFTID AND phdr.change_status is not null) ) )
2396 and
2397 ( ( (isMod = 'N') AND (pline.po_header_id = DOCUMENTID AND pline.draft_id = -1 AND pline.change_status is NULL)) OR
2398 ( (isMod = 'Y') AND (pline.draft_id = DRAFTID AND pline.change_status is not null) ) )
2399 AND phdr.po_header_id = pline.po_header_id
2400 AND phdr.po_header_id = documentId
2401 AND Nvl(CLM_EXERCISED_FLAG,'N') = 'N';
2402
2403 CURSOR c_target_cost IS
2404 SELECT Sum(PO_UDA_PUB.get_single_attr_value(p_entity_code => 'PO_LINE_EXT_ATTRS',pk1_value => documentId, pk2_value => -1,
2405 p_attr_grp_int_name => 'AMT_'||Decode(pline.contract_type,'COST_PLUS_INCENTIVE_FEE','CPIF',
2406 'FP_INCENTIVE_FIRM_TARGET','FIFT','FP_INCENTIVE_SUCCESSIVE_TARGET','FIST')
2407 ||'_'||Decode(phdr.clm_award_type,'IQ','IIQ','REQ','IR','NEG_AGMNT','IN','DQ','IDQ'),
2408 p_attr_int_name => 'TARGET_COST'))
2409 FROM po_lines_merge_v pline, po_headers_merge_v phdr
2410 WHERE
2411 ( ( (isMod = 'N') AND (phdr.po_header_id = DOCUMENTID AND phdr.draft_id = -1 AND phdr.change_status is NULL)) OR
2412 ( (isMod = 'Y') AND (phdr.draft_id = DRAFTID AND phdr.change_status is not null) ) )
2413 and
2414 ( ( (isMod = 'N') AND (pline.po_header_id = DOCUMENTID AND pline.draft_id = -1 AND pline.change_status is NULL)) OR
2415 ( (isMod = 'Y') AND (pline.draft_id = DRAFTID AND pline.change_status is not null) ) )
2416 AND phdr.po_header_id = pline.po_header_id
2417 AND phdr.po_header_id = documentId
2418 AND Nvl(CLM_EXERCISED_FLAG,'N') = 'N';
2419
2420 p_Amount number;
2421 BEGIN
2422
2423 if p_amt_type = 'Firm Price' then
2424 open c_amount;
2425 fetch c_amount into p_Amount;
2426 close c_amount;
2427 end if;
2428
2429 if p_amt_type = 'Target Price' then
2430 open c_amount2;
2431 fetch c_amount2 into p_Amount;
2432 close c_amount2;
2433 end if;
2434
2435 if p_amt_type = 'Funded Amount' then
2436 open c_amount4;
2437 fetch c_amount4 into p_Amount;
2438 close c_amount4;
2439 end if;
2440
2441 if p_amt_type = 'Award Fee' then
2442 open c_award_fee;
2443 fetch c_award_fee into p_Amount;
2444 close c_award_fee;
2445 end if;
2446
2447 if p_amt_type = 'Base Price' then
2448 open c_base_fee;
2449 fetch c_base_fee into p_Amount;
2450 close c_base_fee;
2451 end if;
2452
2453 if p_amt_type = 'Celing Price' then
2454 open c_ceiling_ex;
2455 fetch c_ceiling_ex into p_Amount;
2456 close c_ceiling_ex;
2457 end if;
2458
2459 if p_amt_type = 'Estimated Cost' then
2460 open c_estimated_cost;
2461 fetch c_estimated_cost into p_Amount;
2462 close c_estimated_cost;
2463 end if;
2464
2465 if p_amt_type = 'Fixed Fee' then
2466 open c_fixed_fee;
2467 fetch c_fixed_fee into p_Amount;
2468 close c_fixed_fee;
2469 end if;
2470
2471 if p_amt_type = 'Target Cost' then
2472 open c_target_cost;
2473 fetch c_target_cost into p_Amount;
2474 close c_target_cost;
2475 end if;
2476
2477 RETURN p_Amount;
2478
2479 end getOtherAmounts;
2480
2481 function get_mod_addressxml_pvt(p_header_id number, p_draft_id number, p_diff_type varchar2) return CLOB as
2482 l_sql varchar2(4000);
2483 l_addr_val_col_name varchar2(50);
2484 l_addr_typ_col_name varchar2(50);
2485 l_contacts_col_name varchar2(50);
2486 l_attr_grp_id number;
2487 clob_addr CLOB;
2488 c_addr SYS_REFCURSOR;
2489 l_address varchar2(4000);
2490 l_address_type varchar2(4000);
2491 l_address_type_name varchar2(4000);
2492 l_contacts varchar2(4000);
2493 begin
2494
2495 WITH temp AS (SELECT ag.attr_group_id gid,
2496 decode(upper(fcu.end_user_column_name),upper('addressdtlsxml'),efdfce.application_column_name,null) addressxml,
2497 decode(upper(fcu.end_user_column_name),upper('addresstype'),efdfce.application_column_name,null) addresstype,
2498 decode(upper(fcu.end_user_column_name),upper('contactdtlsxml'),efdfce.application_column_name,null) contacts
2499 FROM ego_fnd_dsc_flx_ctx_ext ag,
2500 ego_fnd_df_col_usgs_ext efdfce,
2501 fnd_descr_flex_column_usages fcu
2502 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
2503 and efdfce.descriptive_flex_context_code = ag.descriptive_flex_context_code
2504 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
2505 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
2506 and fcu.application_column_name = efdfce.application_column_name
2507 and ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_HEADER_EXT_ATTRS'
2508 and upper(ag.descriptive_flex_context_code) = upper('addresses')
2509 and upper(fcu.end_user_column_name) in (upper('addressdtlsxml'),upper('contactdtlsxml'),upper('addresstype')))
2510 SELECT (SELECT DISTINCT gid FROM temp WHERE gid IS NOT NULL),(SELECT DISTINCT addressxml FROM temp WHERE addressxml IS NOT null),
2511 (SELECT DISTINCT addresstype FROM temp WHERE addresstype IS NOT NULL),
2512 (SELECT DISTINCT contacts FROM temp WHERE contacts is NOT NULL)
2513 into l_attr_grp_id,
2514 l_addr_val_col_name,
2515 l_addr_typ_col_name,
2516 l_contacts_col_name
2517 FROM dual;
2518
2519 if l_addr_val_col_name is not null AND l_addr_typ_col_name IS NOT NULL then
2520
2521 CASE p_diff_type
2522 WHEN 'ADD' THEN
2523 l_sql := 'SELECT ' || l_addr_typ_col_name || ',' || l_addr_val_col_name || ',' || l_contacts_col_name ||
2524 ' FROM po_headers_all_ext_vl WHERE po_header_id = '
2525 || p_header_id || ' and draft_id = ' || p_draft_id ||
2526 ' AND attr_group_id = ' || l_attr_grp_id || 'and ' || l_addr_val_col_name || ' NOT LIKE ''%MOD_ADMIN_OFFICE_ADDR%'' AND '
2527 || l_addr_val_col_name || ' NOT LIKE ''%MOD_ISSUING_OFFICE_ADDR%'' AND '
2528 || l_addr_typ_col_name || ' NOT IN (SELECT ' || l_addr_typ_col_name || ' FROM po_headers_all_ext_vl WHERE
2529 po_header_id = ' || p_header_id || ' and draft_id = -1 AND attr_group_id = ' || l_attr_grp_id ||')' ;
2530 WHEN 'DELETE' THEN
2531 l_sql := 'SELECT ' || l_addr_typ_col_name || ',' || l_addr_val_col_name || ',' || l_contacts_col_name ||
2532 ' FROM po_headers_all_ext_vl WHERE po_header_id = '
2533 || p_header_id || ' and draft_id = -1 AND attr_group_id = '
2534 || l_attr_grp_id || ' AND ' || l_addr_typ_col_name || ' NOT IN (SELECT ' || l_addr_typ_col_name ||
2535 ' FROM po_headers_all_ext_vl WHERE po_header_id = ' || p_header_id ||
2536 ' and draft_id = ' || p_draft_id || ' AND attr_group_id = ' || l_attr_grp_id || '
2537 and ' || l_addr_val_col_name || ' NOT LIKE ''%MOD_ADMIN_OFFICE_ADDR%'' AND ' || l_addr_val_col_name ||
2538 ' NOT LIKE ''%MOD_ISSUING_OFFICE_ADDR%'')' ;
2539 WHEN 'CHANGED' THEN
2540 l_sql := 'SELECT modi.' || l_addr_typ_col_name ||
2541 ',REPLACE(Concat(base.' || l_addr_val_col_name || ',base.' || l_contacts_col_name || ') ,modi.' || l_addr_typ_col_name ||
2542 ',''CUR'')' ||
2543 ',REPLACE(Concat(modi.' || l_addr_val_col_name || ',modi.' || l_contacts_col_name || ') ,modi.' || l_addr_typ_col_name ||
2544 ',''MOD'')' ||
2545 ' FROM po_headers_all_ext_vl base, po_headers_all_ext_vl modi WHERE base.po_header_id = ' || p_header_id ||
2546 ' AND modi.po_header_id = base.po_header_id AND base.draft_id = -1 AND modi.draft_id = ' || p_draft_id ||
2547 ' AND base.attr_group_id = ' || l_attr_grp_id || '
2548 AND base.attr_group_id = modi.attr_group_id AND modi.' || l_addr_val_col_name || ' NOT LIKE ''%MOD_ADMIN_OFFICE_ADDR%'' AND
2549 modi.' || l_addr_val_col_name || ' NOT LIKE ''%MOD_ISSUING_OFFICE_ADDR%'' AND base.' || l_addr_typ_col_name || ' = modi.' ||
2550 l_addr_typ_col_name || '
2551 AND (base.' || l_addr_val_col_name || ' <> modi.' || l_addr_val_col_name || ' OR base.' || l_contacts_col_name || ' <> modi.' ||
2552 l_contacts_col_name || ') ' ;
2553 ELSE l_sql := '' ;
2554 END case;
2555 dbms_lob.createtemporary(clob_addr, true);
2556 dbms_lob.open(clob_addr, dbms_lob.lob_readwrite);
2557 open c_addr for l_sql;
2558 loop
2559 fetch c_addr into l_address_type,l_address,l_contacts;
2560 exit when c_addr%notfound;
2561 if l_address is not null then
2562 CASE UPPER(l_address_type)
2563 WHEN UPPER('INV_OFFICE') THEN l_address_type_name := 'Party to receive Invoice';
2564 WHEN UPPER('ISSUING_OFFICE') THEN l_address_type_name := 'Contract Issuing Office';
2565 WHEN UPPER('PAY_OFFICE') THEN l_address_type_name := 'Paying Office';
2566 WHEN UPPER('ADMIN_OFFICE') THEN l_address_type_name := 'Contract Administrative Office';
2567 WHEN UPPER('SBA_OFFICE') THEN l_address_type_name := 'SBA Contracting Office';
2568 WHEN UPPER('COTR_OFFICE') THEN l_address_type_name := 'Contracting Officer Representative';
2569 WHEN UPPER('PRO_ADMIN_OFFICE') THEN l_address_type_name := 'Government Property Office';
2570 WHEN UPPER('REQ_OFFICE') THEN l_address_type_name := 'Requiring Activity';
2571 ELSE l_address_type_name := '';
2572 END CASE;
2573 l_address:= REPLACE(l_address,l_address_type||'_','');
2574 l_contacts:= REPLACE(l_contacts,l_address_type||'_','');
2575 dbms_lob.writeappend(clob_addr, length('<Address><Description>'|| l_address_type_name
2576 ||'</Description>'||l_address||l_contacts||'</Address>'),
2577 '<Address><Description>'|| l_address_type_name ||'</Description>'||l_address||l_contacts||'</Address>');
2578 end if;
2579 end loop;
2580 dbms_lob.close(clob_addr);
2581 end if;
2582 return(clob_addr);
2583 end get_mod_addressxml_pvt;
2584
2585 FUNCTION get_mod_addressxml(p_operation varchar2) RETURN CLOB AS
2586 BEGIN
2587 return(get_mod_addressxml_pvt(DOCUMENTID, DRAFTID,p_operation));
2588 END get_mod_addressxml;
2589
2590 FUNCTION get_currency_xml RETURN CLOB AS
2591 l_currency varchar2(100);
2592 l_exchange varchar2(100);
2593 l_return CLOB;
2594 BEGIN
2595 select CURRENCY_CODE,RATE
2596 INTO l_currency , l_exchange
2597 FROM po_headers_merge_v where po_header_id = DOCUMENTID AND CURRENCY_CODE IS NOT NULL;
2598
2599 if l_exchange is not null then
2600 l_return := to_clob('<BuyingCurrency exchangeRate = "'||l_exchange||'">'||l_currency||
2601 '</BuyingCurrency><PaymentCurrency exchangeRate ="'||l_exchange||'">'||l_currency||'</PaymentCurrency>');
2602 else
2603 l_return := to_clob('<BuyingCurrency>'||l_currency||
2604 '</BuyingCurrency><PaymentCurrency>'||l_currency||'</PaymentCurrency>');
2605 end if;
2606
2607 RETURN l_return;
2608
2609 EXCEPTION
2610 WHEN OTHERS THEN
2611 RETURN NULL;
2612 END get_currency_xml;
2613
2614
2615 FUNCTION get_contract_desc RETURN CLOB AS
2616 l_col_name varchar2(100);
2617 l_sql_query varchar2(1000);
2618 l_return_val clob;
2619 c_addr SYS_REFCURSOR;
2620 BEGIN
2621 SELECT MAPPING_FIELD_VALUE_COLUMN
2622 INTO l_col_name
2623 FROM pon_form_section_compiled
2624
2625 WHERE form_id =
2626 (SELECT FORM_ID FROM PON_FORMS_SECTIONS WHERE FORM_CODE = 'FED_PRESOL' AND STATUS = 'ACTIVE')
2627 AND field_code = 'FED_DESC';
2628
2629 l_sql_query :='SELECT '|| l_col_name ||
2630 ' FROM pon_form_field_values WHERE OWNING_ENTITY_CODE = ''PON_AUCTION_HEADERS_ALL'' AND ENTITY_PK1 IN (SELECT DISTINCT AUCTION_HEADER_ID FROM po_lines_merge_v WHERE po_header_id = '
2631 || DOCUMENTID ||'AND draft_id = '||
2632 DRAFTID ||') AND FORM_ID = (SELECT FORM_ID FROM PON_FORMS_SECTIONS WHERE FORM_CODE = ''FED_PRESOL'' AND STATUS = ''ACTIVE'') ';
2633
2634 open c_addr for l_sql_query;
2635 loop
2636 fetch c_addr into l_return_val;
2637 exit when c_addr%notfound;
2638 end loop;
2639 return l_return_val;
2640 EXCEPTION
2641 WHEN OTHERS THEN
2642 RETURN null;
2643
2644 END get_contract_desc;
2645
2646 FUNCTION getattachmentbintext(p_media_id number) RETURN CLOB IS
2647
2648 CURSOR c_get_short_text is
2649 SELECT PO_FBO_PKG.encode_blob(PO_COMM_FPDSNG_PVT.clob_to_blob(to_clob(short_text)))
2650 FROM fnd_documents_short_text
2651 WHERE media_id = p_media_id;
2652
2653 CURSOR c_get_file_text is
2654 SELECT PO_FBO_PKG.encode_blob(file_data)
2655 FROM fnd_lobs
2656 WHERE file_id = p_media_id ;
2657
2658 l_bintext CLOB;
2659
2660 BEGIN
2661 OPEN c_get_short_text;
2662 FETCH c_get_short_text INTO l_bintext;
2663 CLOSE c_get_short_text;
2664
2665 OPEN c_get_file_text;
2666 FETCH c_get_file_text INTO l_bintext;
2667 CLOSE c_get_file_text;
2668
2669 RETURN l_bintext;
2670
2671 END getattachmentbintext;
2672
2673 FUNCTION get_charge_acc_comp_val(p_col_name VARCHAR2, p_code_comb_id NUMBER) RETURN VARCHAR2 AS
2674 l_output VARCHAR2(1000);
2675 l_sql VARCHAR2(1000);
2676 BEGIN
2677 l_sql := 'SELECT '|| p_col_name ||' FROM gl_code_combinations glc WHERE glc.CODE_COMBINATION_ID = ' || p_code_comb_id;
2678 execute immediate l_sql into l_output;
2679 RETURN(l_output);
2680 END get_charge_acc_comp_val;
2681
2682 FUNCTION get_change_line_text_desc_B(p_line_id varchar2) RETURN VARCHAR2 AS
2683 CURSOR section_names is
2684 SELECT DISTINCT
2685 (CASE SUB_ENTITY_NAME
2686 WHEN 'AMT_PRICING' THEN 'Amount Pricing'
2687 WHEN 'FILTER_PREDICATE' THEN 'Filter Predicate'
2688 WHEN 'ADD_ITEM_INFO' THEN 'Additional Item Information'
2689 WHEN 'INSPECT_INFO' THEN 'Inspection Information'
2690 WHEN 'CONTRACTOR' THEN 'Contractor'
2691 WHEN 'FED_CUST_DESG' THEN 'Federal Customer Designation'
2692 WHEN 'LINE_INFO' THEN 'Line Information'
2693 WHEN 'CONTRACT' THEN 'Contract'
2694 WHEN 'BILLING' THEN 'Billing'
2695 WHEN 'ADD_SHIP_INFO' THEN 'Additional Shipping Inforamtion'
2696 WHEN 'ORDERING_CONSTRAINTS' THEN 'Ordering constraints'
2697 WHEN 'OPTION_INFO' THEN 'Optional Information'
2698 WHEN 'QTY_PRICING' THEN 'Quantity Pricing'
2699 WHEN 'SOURCE_DOC' THEN 'Sourcing document'
2700 ELSE SUB_ENTITY_NAME
2701 END ) AS entityName
2702 FROM po_entity_differences a,po_diff_config b
2703 WHERE a.ENTITY_NAME IN ('LINE','LINELOCATION','DISTRIBUTION')
2704 AND a.DOCUMENT_TYPE=b.DOCUMENT_TYPE AND a.ENTITY_NAME=b.ENTITY_NAME AND a.MOD_DOC_SOURCE_NAME =b.MOD_DOC_SOURCE_NAME
2705 AND a.COLUMN_NAME=b.COLUMN_NAME
2706 AND MOD_DOC_PK1_VAL = DOCUMENTID
2707 AND MOD_DOC_PK2_VAL = DRAFTID
2708 AND MOD_DOC_PK3_VAL = p_line_id
2709 AND b.SUB_ENTITY_NAME NOT IN ('DELIVERY','SHIP_INFO') ;
2710
2711 l_output VARCHAR2(4000);
2712 l_temp VARCHAR2(1000);
2713 BEGIN
2714 OPEN section_names;
2715 LOOP
2716 FETCH section_names INTO l_temp;
2717 EXIT WHEN section_names%NOTFOUND;
2718 IF l_temp IS NOT NULL THEN
2719 l_output := l_output || l_temp || ',';
2720 END IF;
2721 END LOOP;
2722 CLOSE section_names;
2723 l_output := SubStr(l_output,0, Length(l_output)-1);
2724 l_output := l_output || ' have changed';
2725 RETURN l_output;
2726 END get_change_line_text_desc_B;
2727
2728 FUNCTION getArticleText(p_article_version_id NUMBER, p_article_id NUMBER) RETURN CLOB AS
2729 l_sql VARCHAR2(4000);
2730 l_ret CLOB;
2731 BEGIN
2732 l_sql := 'SELECT article_text FROM okc_article_versions WHERE INSERT_BY_REFERENCE <> ''Y'' AND article_version_id=' ||
2733 p_article_version_id || ' AND article_id = ' || p_article_id ;
2734 EXECUTE IMMEDIATE l_sql INTO l_ret;
2735 RETURN l_ret;
2736
2737 EXCEPTION
2738 WHEN OTHERS THEN
2739 l_ret := NULL;
2740 RETURN l_ret;
2741 END getArticleText;
2742
2743 FUNCTION get_ModificationReason RETURN VARCHAR2 AS
2744 v_closeout_indicator VARCHAR2(50);
2745 v_mod_authority VARCHAR2(100);
2746 v_cancel_indicator VARCHAR2(50);
2747 v_cancel_type VARCHAR2(100);
2748 v_excersiced_flag VARCHAR2(10);
2749 v_duns VARCHAR2(100);
2750 v_vendor_change_flag VARCHAR2(10);
2751 v_output VARCHAR2(100);
2752 BEGIN
2753
2754 SELECT CLM_CLOSEOUT_STATUS
2755 INTO v_closeout_indicator
2756 FROM po_headers_merge_v WHERE
2757 PO_HEADER_ID = DOCUMENTID AND
2758 DRAFT_ID = DRAFTID;
2759
2760 SELECT A.ATTRVAL
2761 INTO v_mod_authority
2762 FROM po_eda_uda_attr_values a WHERE
2763 a.PK1_VALUE = DOCUMENTID AND
2764 a.PK2_VALUE = DRAFTID AND
2765 a.LVL = 'AWARDHEADER' AND
2766 a.attr = 'mod_Just_mod_auth';
2767
2768 IF v_closeout_indicator = 'CLOSED_OUT' THEN
2769 V_OUTPUT := 'Close Out';
2770 ELSIF v_mod_authority = 'ADMIN_CHANGE_PURSUANT_TO' THEN
2771 v_output := 'Other Administrative Action';
2772 ELSIF v_mod_authority = 'CHANGE_PURSUNT_TO' THEN
2773 v_output := 'Change Order';
2774 ELSIF v_mod_authority = 'SUPPLEMENTAL_AGREE_PURSUANT' THEN
2775
2776 SELECT USER_DOCUMENT_STATUS
2777 INTO v_cancel_indicator
2778 FROM po_headers_merge_v WHERE
2779 po_header_id = DOCUMENTID AND draft_id = DRAFTID;
2780
2781 IF v_cancel_indicator IS NULL THEN
2782
2783 SELECT USER_DOCUMENT_STATUS
2784 INTO v_cancel_indicator
2785 FROM (
2786 SELECT USER_DOCUMENT_STATUS, Count(USER_DOCUMENT_STATUS)
2787 FROM po_lines_merge_v
2788 WHERE po_header_id = DOCUMENTID AND draft_id = DRAFTID
2789 AND USER_DOCUMENT_STATUS IS NOT NULL
2790 GROUP BY USER_DOCUMENT_STATUS
2791 ORDER BY 2) WHERE ROWNUM = 1;
2792
2793 END IF;
2794
2795 BEGIN
2796 SELECT DISTINCT CLM_EXERCISED_FLAG
2797 INTO v_excersiced_flag
2798 FROM po_lines_merge_v polMod
2799 WHERE po_header_id = DOCUMENTID AND draft_id = DRAFTID
2800 AND CLM_EXERCISED_FLAG = 'Y'
2801 AND CLM_EXERCISED_FLAG <> (SELECT CLM_EXERCISED_FLAG
2802 FROM po_lines_merge_v polMod
2803 WHERE po_header_id = polMod.po_header_id
2804 AND draft_id = -1 AND po_line_id = polMod.po_line_id);
2805 EXCEPTION
2806 WHEN OTHERS THEN
2807 v_excersiced_flag := NULL;
2808 END;
2809
2810 BEGIN
2811 SELECT attrval
2812 INTO v_duns
2813 FROM po_eda_uda_attr_values
2814 WHERE attr = 'SUPPLIER_DTLS_DUNS_NUM'
2815 AND PK1_VALUE = DOCUMENTID AND PK2_VALUE = DRAFTID
2816 AND attrval <> (SELECT attrval
2817 FROM po_eda_uda_attr_values
2818 WHERE attr = 'SUPPLIER_DTLS_DUNS_NUM' AND
2819 PK1_VALUE = DOCUMENTID AND PK2_VALUE = -1);
2820 EXCEPTION
2821 WHEN OTHERS THEN
2822 v_duns := NULL;
2823 END;
2824
2825 BEGIN
2826 SELECT 'Y'
2827 INTO v_vendor_change_flag
2828 FROM dual
2829 WHERE EXISTS (SELECT 1 FROM PO_ENTITY_DIFFERENCES
2830 WHERE MOD_DOC_PK1_VAL = DOCUMENTID AND MOD_DOC_PK2_VAL = DRAFTID
2831 AND DIFF_ID IN (SELECT DISTINCT DIFF_CONFIG_ID
2832 FROM po_diff_config
2833 WHERE SUB_ENTITY_NAME = 'SUPPLIER_DTLS'));
2834 EXCEPTION
2835 WHEN OTHERS THEN
2836 v_vendor_change_flag := NULL;
2837 END;
2838
2839 IF v_cancel_indicator = 'TERMINATED FOR DEFAULT' THEN
2840 v_output := 'Terminate For Default (Complete Or Partial)';
2841 ELSIF v_cancel_indicator = 'TERMINATED FOR CONVENIENCE' THEN
2842 v_output := 'Terminate For Convenience (Complete Or Partial)';
2843 ELSIF v_excersiced_flag = 'Y' THEN
2844 v_output := 'Exercise An Option';
2845 ELSIF v_duns IS NOT NULL THEN
2846 v_output := 'Vendor DUNS CHANGE';
2847 ELSIF v_vendor_change_flag = 'Y' THEN
2848 v_output := 'Vendor Address Change';
2849 END IF;
2850 ELSIF v_mod_authority = 'OTHER_AUTHORITY' THEN
2851 v_output := 'Change Order';
2852 ELSE
2853 v_output := 'Supplemental Agreement For Work Within Scope';
2854 END IF;
2855
2856 --Dbms_Output.put_line(v_output);
2857 RETURN v_output ;
2858 END get_ModificationReason;
2859
2860
2861
2862 FUNCTION getregulationURLMod(p_docid number, p_draftid number, p_doctype varchar2) RETURN CLOB AS
2863 cursor csr_addedURLs is
2864 (select distinct RegulationURL from
2865 (SELECT
2866 Decode(art.standard_yn,'Y',decode(article_type,'FAR','http://www.arnet.gov',
2867 'DFARS','http://www.acq.osd.mil/dpap/dars/index.html',
2868 ' '),
2869 nvl(ver.REFERENCE_SOURCE,' ')) RegulationURL
2870 FROM okc_k_articles_b kartb,okc_articles_all art, okc_article_versions ver, po_headers_merge_v poh, okc_sections_b scn
2871 WHERE
2872 poh.draft_id = p_draftid AND poh.change_status IS NOT NULL
2873 AND kartb.sav_sae_id = art.article_id
2874 AND kartb.article_version_id = ver.article_version_id
2875 AND art.article_id = ver.article_id
2876 AND kartb.document_id = p_draftid
2877 AND kartb.document_type = p_doctype
2878 AND kartb.document_id = poh.po_header_id
2879 and kartb.scn_id = scn.id))
2880 minus
2881 (select distinct RegulationURL from
2882 (SELECT
2883 Decode(art.standard_yn,'Y',decode(article_type,'FAR','http://www.arnet.gov',
2884 'DFARS','http://www.acq.osd.mil/dpap/dars/index.html',
2885 ' '),
2886 nvl(ver.REFERENCE_SOURCE,' ')) RegulationURL
2887 FROM okc_k_articles_b kartb,okc_articles_all art, okc_article_versions ver, po_headers_merge_v poh, okc_sections_b scn
2888 WHERE
2889 poh.po_header_id = p_docid AND poh.draft_id = -1 AND poh.change_status is NULL
2890 AND kartb.sav_sae_id = art.article_id
2891 AND kartb.article_version_id = ver.article_version_id
2892 AND art.article_id = ver.article_id
2893 AND kartb.document_id = p_docid
2894 AND kartb.document_type = SubStr(p_doctype,1,Length(p_doctype)-4)
2895 AND kartb.document_id = poh.po_header_id
2896 and kartb.scn_id = scn.id));
2897
2898 cursor csr_deletedURLs is
2899 (select distinct RegulationURL from
2900 (SELECT
2901 Decode(art.standard_yn,'Y',decode(article_type,'FAR','http://www.arnet.gov',
2902 'DFARS','http://www.acq.osd.mil/dpap/dars/index.html',
2903 ' '),
2904 nvl(ver.REFERENCE_SOURCE,' ')) RegulationURL
2905 FROM okc_k_articles_b kartb,okc_articles_all art, okc_article_versions ver, po_headers_merge_v poh, okc_sections_b scn
2906 WHERE
2907 poh.po_header_id = p_docid AND poh.draft_id = -1 AND poh.change_status is NULL
2908 AND kartb.sav_sae_id = art.article_id
2909 AND kartb.article_version_id = ver.article_version_id
2910 AND art.article_id = ver.article_id
2911 AND kartb.document_id = p_docid
2912 AND kartb.document_type = SubStr(p_doctype,1,Length(p_doctype)-4)
2913 AND kartb.document_id = poh.po_header_id
2914 and kartb.scn_id = scn.id))
2915 minus
2916 (select distinct RegulationURL from
2917 (SELECT
2918 Decode(art.standard_yn,'Y',decode(article_type,'FAR','http://www.arnet.gov',
2919 'DFARS','http://www.acq.osd.mil/dpap/dars/index.html',
2920 ' '),
2921 nvl(ver.REFERENCE_SOURCE,' ')) RegulationURL
2922 FROM okc_k_articles_b kartb,okc_articles_all art, okc_article_versions ver, po_headers_merge_v poh, okc_sections_b scn
2923 WHERE
2924 poh.draft_id = p_draftid AND poh.change_status IS NOT NULL
2925 AND kartb.sav_sae_id = art.article_id
2926 AND kartb.article_version_id = ver.article_version_id
2927 AND art.article_id = ver.article_id
2928 AND kartb.document_id = p_draftid
2929 AND kartb.document_type = p_doctype
2930 AND kartb.document_id = poh.po_header_id
2931 and kartb.scn_id = scn.id));
2932
2933 l_clob clob;
2934 BEGIN
2935
2936 l_clob := '';
2937 for c in csr_addedURLs loop
2938 l_clob := l_clob || '<RegulationURL changeFlag="Added">' || c.RegulationURL || '</RegulationURL>';
2939 end loop;
2940 for c in csr_deletedURLs loop
2941 l_clob := l_clob || '<RegulationURL changeFlag="Deleted">' || c.RegulationURL || '</RegulationURL>';
2942 end loop;
2943
2944 return l_clob;
2945
2946 END getregulationURLMod;
2947
2948 FUNCTION getClauseChangeText(p_kartid NUMBER) RETURN CLOB AS
2949
2950 cursor c_getdiff is
2951 select decode(artmod.ARTICLE_TYPE, artorig.ARTICLE_TYPE, NULL, ' Regulation Or Supplement and Issuing Activity Changed.') CHANGE1,
2952 decode(artmod.ARTICLE_NUMBER, artorig.ARTICLE_NUMBER, NULL, ' Clause Number Changed.') CHANGE2,
2953 decode(artmod.ARTICLE_TITLE, artorig.ARTICLE_TITLE, NULL, ' Clause Title Changed.') CHANGE3,
2954 decode(vermod.START_DATE, verorig.START_DATE, NULL, ' Clause Effective Date Changed.') CHANGE4,
2955 decode(dbms_lob.compare(vermod.article_text,verorig.article_text),0,NULL,' Clause Text Changed.') CHANGE5
2956 from okc_k_articles_b kartborig, okc_k_articles_b kartbmod, okc_articles_all artorig, okc_articles_all artmod, okc_article_versions verorig,
2957 okc_article_versions vermod
2958 where kartbmod.id = p_kartid
2959 and kartbmod.orig_system_reference_id1 = kartborig.id
2960 and artmod.article_id = vermod.article_id
2961 and artorig.article_id = verorig.article_id
2962 and kartbmod.article_version_id = vermod.article_version_id
2963 and kartborig.article_version_id = verorig.article_version_id;
2964 l_clob CLOB;
2965 BEGIN
2966 l_clob := '';
2967 for c in c_getdiff loop
2968 l_clob := ' ';
2969 if c.CHANGE1 is not null then
2970 l_clob := l_clob || c.CHANGE1;
2971 end if;
2972 if c.CHANGE2 is not null then
2973 l_clob := l_clob || c.CHANGE2;
2974 end if;
2975 if c.CHANGE3 is not null then
2976 l_clob := l_clob || c.CHANGE3;
2977 end if;
2978 if c.CHANGE4 is not null then
2979 l_clob := l_clob || c.CHANGE4;
2980 end if;
2981 if c.CHANGE5 is not null then
2982 l_clob := l_clob || c.CHANGE5;
2983 end if;
2984 end loop;
2985
2986 RETURN l_clob;
2987 END getClauseChangeText;
2988
2989 FUNCTION get_clause_variable_pairs(p_kart_id NUMBER) RETURN CLOB AS
2990 l_clause_text CLOB;
2991 l_output_xml CLOB;
2992
2993 l_article_id NUMBER;
2994 l_article_ver_id NUMBER;
2995 l_cur_var VARCHAR2(100);
2996 l_var_type VARCHAR2(100);
2997 l_var_code VARCHAR2(100);
2998 l_var_value VARCHAR2(100);
2999 l_var_value_id VARCHAR2(100);
3000
3001 l_var_seq_num NUMBER :=1;
3002
3003 CURSOR c_varvalue(p_var_code VARCHAR2) is
3004 SELECT VARIABLE_VALUE,VARIABLE_VALUE_ID FROM okc_k_art_variables WHERE CAT_ID = p_kart_id AND VARIABLE_CODE = p_var_code;
3005
3006 BEGIN
3007
3008 SELECT sav_sae_id,article_version_id INTO l_article_id,l_article_ver_id FROM okc_k_articles_b WHERE id = p_kart_id;
3009 SELECT article_text INTO l_clause_text FROM okc_article_versions WHERE article_id = l_article_id AND ARTICLE_VERSION_ID=l_article_ver_id;
3010
3011 l_var_seq_num :=1;
3012 dbms_lob.createtemporary(l_output_xml, true);
3013 dbms_lob.open(l_output_xml, dbms_lob.lob_readwrite);
3014 dbms_lob.writeappend(l_output_xml,Length('<xml>'),'<xml>');
3015
3016
3017 LOOP
3018 EXIT WHEN regexp_instr(l_clause_text,'<var[^></]+/>',1,l_var_seq_num) = 0;
3019
3020 l_cur_var := regexp_substr(l_clause_text,'<var[^></]+/>',1,l_var_seq_num);
3021
3022 l_var_code := regexp_substr(l_cur_var,'"[^"]+"',1,1);
3023 l_var_code := SubStr(l_var_code,2,Length(l_var_code)-2);
3024
3025 l_var_type := regexp_substr(l_cur_var,'"[^"]+"',1,2);
3026 l_var_type := SubStr(l_var_type,2,Length(l_var_type)-2);
3027 l_var_value := NULL;
3028 l_var_value_id:= NULL;
3029 --deriving variable value
3030 IF l_var_type = 'U' THEN
3031 OPEN c_varvalue(l_var_code);
3032 FETCH c_varvalue INTO l_var_value,l_var_value_id;
3033 CLOSE c_varvalue;
3034 IF l_var_value IS NULL AND l_var_value_id IS NOT NULL THEN
3035 SELECT FLEX_VALUE INTO l_var_value FROM FND_FLEX_VALUES WHERE FLEX_VALUE_ID = l_var_value_id;
3036 END IF;
3037 ELSE
3038 l_var_value := PO_EDA_DATATEMPLATE_PKG.GetVariableValue(p_kart_id,l_var_code,NULL,null);
3039 END IF;
3040
3041 l_var_value := Nvl(l_var_value,'______');
3042
3043 dbms_lob.writeappend(l_output_xml,Length('<pair>'),'<pair>');
3044 dbms_lob.writeappend(l_output_xml,Length('<seq>'|| l_var_seq_num ||'</seq><val>'|| l_var_value ||'</val>'),
3045 '<seq>'|| l_var_seq_num ||'</seq><val>'|| l_var_value ||'</val>');
3046 dbms_lob.writeappend(l_output_xml,Length('</pair>'),'</pair>');
3047
3048
3049 l_var_seq_num := l_var_seq_num+1;
3050
3051 END LOOP;
3052
3053 dbms_lob.writeappend(l_output_xml,Length('</xml>'),'</xml>');
3054 dbms_lob.close(l_output_xml);
3055
3056 return(l_output_xml);
3057
3058 END get_clause_variable_pairs;
3059
3060 FUNCTION get_order_mod_identifier(p_header_id NUMBER, p_draft_id NUMBER) RETURN VARCHAR2 AS
3061 l_mod_type VARCHAR2(10);
3062 l_mod_serial_num NUMBER;
3063 l_lookup_constant VARCHAR2(100);
3064 l_ord_mod_identifier VARCHAR2(10);
3065 l_doc_number VARCHAR2(100);
3066
3067 BEGIN
3068
3069 SELECT attrval
3070 INTO l_mod_serial_num
3071 FROM po_eda_uda_attr_values WHERE
3072 PK1_VALUE = p_header_id
3073 AND PK2_value = p_draft_id
3074 AND LVL = 'AWARDHEADER'
3075 AND attr LIKE 'DOD_ORD_MOD_SERIAL_NUMBER';
3076
3077 SELECT attrval
3078 INTO l_mod_type
3079 FROM po_eda_uda_attr_values WHERE
3080 PK1_VALUE = p_header_id
3081 AND PK2_value = p_draft_id
3082 AND LVL = 'AWARDHEADER'
3083 AND attr LIKE 'DOD_ORD_MOD_MODIF_TYPE';
3084
3085 IF l_mod_type = 'ACO' THEN
3086 l_lookup_constant := 'PO_CLM_ACO_NUM_CONSTANTS';
3087 ELSIF l_mod_type = 'PCO' THEN
3088 l_lookup_constant := 'PO_CLM_PCO_NUM_CONSTANTS';
3089 END IF;
3090
3091 SELECT MODIFICATION_NUMBER INTO l_doc_number
3092 FROM PO_DRAFTS WHERE DRAFT_ID = p_draft_id;
3093
3094 l_ord_mod_identifier := SubStr(l_doc_number,InStr(l_doc_number,'-',1,5)+1);
3095
3096 IF Length(l_ord_mod_identifier) = 2 THEN
3097 RETURN l_ord_mod_identifier;
3098 ELSE
3099 SELECT DISPLAYED_FIELD
3100 INTO l_ord_mod_identifier
3101 FROM po_lookup_codes
3102 WHERE lookup_type = l_lookup_constant AND
3103 LOOKUP_CODE = l_mod_serial_num;
3104 END IF;
3105
3106
3107 RETURN l_ord_mod_identifier;
3108 END get_order_mod_identifier;
3109
3110 FUNCTION get_award_mod_identifier(p_header_id NUMBER, p_draft_id NUMBER) RETURN VARCHAR2 AS
3111 l_mod_type VARCHAR2(10);
3112 l_mod_serial_num NUMBER;
3113 l_mod_type_char VARCHAR2(100);
3114 l_awd_mod_identifier VARCHAR2(10);
3115 l_doc_number VARCHAR2(100);
3116
3117 BEGIN
3118
3119 SELECT attrval
3120 INTO l_mod_serial_num
3121 FROM po_eda_uda_attr_values WHERE
3122 PK1_VALUE = p_header_id
3123 AND PK2_value = p_draft_id
3124 AND LVL = 'AWARDHEADER'
3125 AND attr LIKE 'DOD_AWD_MOD_SERIAL_NUMBER';
3126
3127 SELECT attrval
3128 INTO l_mod_type
3129 FROM po_eda_uda_attr_values WHERE
3130 PK1_VALUE = p_header_id
3131 AND PK2_value = p_draft_id
3132 AND LVL = 'AWARDHEADER'
3133 AND attr LIKE 'DOD_AWD_MOD_MODIF_TYPE';
3134
3135 IF l_mod_type = 'ACO' THEN
3136 l_mod_type_char := 'A';
3137 ELSIF l_mod_type = 'PCO' THEN
3138 l_mod_type_char := 'P';
3139 END IF;
3140
3141 SELECT MODIFICATION_NUMBER INTO l_doc_number
3142 FROM PO_DRAFTS WHERE DRAFT_ID = p_draft_id;
3143
3144 l_awd_mod_identifier := SubStr(l_doc_number,InStr(l_doc_number,'-',1,4)+1);
3145
3146 IF SubStr(l_awd_mod_identifier,1,1) IN ('A','P') THEN
3147 RETURN l_awd_mod_identifier;
3148 ELSE
3149 l_mod_serial_num := l_mod_serial_num + 1;
3150 l_mod_serial_num := lpad(l_mod_serial_num,5,0);
3151 l_awd_mod_identifier := l_mod_type_char || l_mod_serial_num;
3152 END IF;
3153
3154
3155 RETURN l_awd_mod_identifier;
3156
3157 END get_award_mod_identifier;
3158
3159 FUNCTION containsNumber(p_string varchar2) return varchar2 is
3160 l_num NUMBER;
3161 l_flag varchar2(1):='N';
3162 begin
3163 for i in 1..length(p_string) loop
3164 begin
3165 l_num:=to_number(SubStr(p_string,i,1));
3166 l_flag:='Y';
3167 exception
3168 when others then
3169 NULL;
3170 END;
3171 end loop;
3172 return l_flag;
3173 end containsNumber;
3174
3175 END PO_EDA_DATATEMPLATE_PKG;