1 PACKAGE BODY GMF_FND_GET_SEGMENT_DTL AS
2 /* $Header: gmfsegdb.pls 115.2 2002/11/11 00:42:07 rseshadr Exp $ */
3 CURSOR cur_get_segment_dtl ( sobname varchar2,
4 segmentname varchar2,
5 segmentnum number,
6 segmentattr_type varchar2,
7 attributevalue varchar2 ) IS
8 SELECT FND.segment_name,
9 FND.segment_num,
10 ATT.segment_attribute_type,
11 ATT.attribute_value
12 FROM fnd_id_flex_segments FND,
13 gl_sets_of_books GLS,
14 fnd_segment_attribute_values ATT
15 WHERE GLS.name = NVL(sobname,GLS.name)
16 AND GLS.chart_of_accounts_id = FND.id_flex_num
17 AND LOWER(FND.segment_name) = LOWER(NVL(segmentname,FND.segment_name)) AND FND.segment_num = NVL(segmentnum, FND.segment_num)
18 AND FND.enabled_flag = 'Y'
19 AND ATT.APPLICATION_ID = FND.APPLICATION_ID
20 AND ATT.ID_FLEX_CODE = FND.ID_FLEX_CODE
21 AND ATT.ID_FLEX_NUM = FND.ID_FLEX_NUM
22 AND ATT.APPLICATION_COLUMN_NAME = FND.APPLICATION_COLUMN_NAME
23 AND ATT.SEGMENT_ATTRIBUTE_TYPE = NVL(segmentattr_type,ATT.SEGMENT_ATTRIBUTE_TYPE )
24 AND ATT.ATTRIBUTE_VALUE = NVL(attributevalue,ATT.ATTRIBUTE_VALUE);
25
26 PROCEDURE proc_get_segment_dtl(sobname IN varchar2,
27 segmentname IN OUT NOCOPY varchar2,
28 segmentnum IN OUT NOCOPY number,
29 segmentattr_type IN OUT NOCOPY varchar2,
30 attributevalue IN OUT NOCOPY varchar2,
31 row_to_fetch IN number,
32 statuscode OUT NOCOPY number) AS
33 BEGIN
34 IF NOT cur_get_segment_dtl%ISOPEN THEN
35 OPEN cur_get_segment_dtl( sobname,
36 segmentname,
37 segmentnum,
38 segmentattr_type,
39 attributevalue);
40 END IF;
41
42 FETCH cur_get_segment_dtl
43 INTO segmentname,
44 segmentnum,
45 segmentattr_type,
46 attributevalue;
47
48 IF cur_get_segment_dtl%NOTFOUND THEN
49 statuscode := 100;
50 CLOSE cur_get_segment_dtl;
51 END IF;
52
53 IF row_to_fetch = 1 AND cur_get_segment_dtl%ISOPEN THEN
54 CLOSE cur_get_segment_dtl;
55 END IF;
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 statuscode := SQLCODE;
60 END;
61 END GMF_FND_GET_SEGMENT_DTL;
[Home] [Help]