1 PACKAGE BODY JA_JAINJVR_XMLP_PKG AS
2 /* $Header: JAINJVRB.pls 120.1 2007/12/25 16:22:07 dwkrishn noship $ */
3 FUNCTION CF_ACCOUNTFORMULA(CODE_COMBINATION_ID IN NUMBER) RETURN VARCHAR2 IS
4 V_ACCOUNT VARCHAR(1000);
5 BEGIN
6 JAI_CMN_GL_PKG.GET_ACCOUNT_NUMBER(P_CHART_OF_ACCTS_ID
7 ,CODE_COMBINATION_ID
8 ,V_ACCOUNT);
9 RETURN (V_ACCOUNT);
10 END CF_ACCOUNTFORMULA;
11
12 FUNCTION CF_SOB_NAMEFORMULA RETURN VARCHAR2 IS
13 CURSOR FOR_SOB_NAME(SOB_ID IN NUMBER) IS
14 SELECT
15 NAME
16 FROM
17 GL_SETS_OF_BOOKS
18 WHERE SET_OF_BOOKS_ID = SOB_ID;
19 V_NAME VARCHAR2(100);
20 BEGIN
21 OPEN FOR_SOB_NAME(P_SOB_ID);
22 FETCH FOR_SOB_NAME
23 INTO V_NAME;
24 CLOSE FOR_SOB_NAME;
25 RETURN (V_NAME);
26 END CF_SOB_NAMEFORMULA;
27
28 FUNCTION CF_ACCTS_DESCFORMULA (CODE_COMBINATION_ID IN NUMBER) RETURN VARCHAR2 IS
29 CURSOR GET_APP_COLUMN_NAME IS
30 SELECT
31 DISTINCT
32 APPLICATION_COLUMN_NAME
33 FROM
34 FND_SEGMENT_ATTRIBUTE_VALUES
35 WHERE APPLICATION_ID = 101
36 AND ID_FLEX_CODE = 'GL#'
37 AND ID_FLEX_NUM = P_CHART_OF_ACCTS_ID
38 AND SEGMENT_ATTRIBUTE_TYPE = 'GL_ACCOUNT'
39 AND ATTRIBUTE_VALUE = 'Y';
40 CURSOR FLEX_VAL_SET_ID(V_COLUMN_NAME IN VARCHAR2) IS
41 SELECT
42 A.FLEX_VALUE_SET_ID
43 FROM
44 FND_ID_FLEX_SEGMENTS A
45 WHERE A.APPLICATION_COLUMN_NAME = V_COLUMN_NAME
46 AND A.APPLICATION_ID = 101
47 AND A.ID_FLEX_CODE = 'GL#'
48 AND A.ID_FLEX_NUM = P_CHART_OF_ACCTS_ID;
49 V_COLUMN_NAME VARCHAR2(30);
50 V_COLUMN_VALUE VARCHAR2(30);
51 V_FLEX_ID NUMBER;
52 V_DESCRIPTION VARCHAR2(100);
53 CURSOR GET_DESCRIPTION IS
54 SELECT
55 SUBSTR(DESCRIPTION
56 ,1
57 ,50)
58 FROM
59 FND_FLEX_VALUES_VL
60 WHERE FLEX_VALUE_SET_ID = V_FLEX_ID
61 AND FLEX_VALUE = V_COLUMN_VALUE;
62 BEGIN
63 OPEN GET_APP_COLUMN_NAME;
64 FETCH GET_APP_COLUMN_NAME
65 INTO V_COLUMN_NAME;
66 CLOSE GET_APP_COLUMN_NAME;
67 IF V_COLUMN_NAME IS NULL THEN
68 V_COLUMN_NAME := 'SEGMENT3';
69 END IF;
70 OPEN FLEX_VAL_SET_ID(V_COLUMN_NAME);
71 FETCH FLEX_VAL_SET_ID
72 INTO V_FLEX_ID;
73 CLOSE FLEX_VAL_SET_ID;
74 EXECUTE IMMEDIATE
75 'select ' || V_COLUMN_NAME || ' from gl_code_combinations
76 where chart_of_accounts_id = :P_CHART_OF_ACCTS_ID AND code_combination_id = :code_combination_id'
77 INTO p_column_value
78 USING P_CHART_OF_ACCTS_ID,CODE_COMBINATION_ID ;
79 V_COLUMN_VALUE := P_COLUMN_VALUE;
80 OPEN GET_DESCRIPTION;
81 FETCH GET_DESCRIPTION
82 INTO V_DESCRIPTION;
83 CLOSE GET_DESCRIPTION;
84 RETURN (V_DESCRIPTION);
85 END CF_ACCTS_DESCFORMULA;
86
87 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
88 CURSOR C_PROGRAM_ID(P_REQUEST_ID IN NUMBER) IS
89 SELECT
90 CONCURRENT_PROGRAM_ID,
91 NVL(ENABLE_TRACE
92 ,'N')
93 FROM
94 FND_CONCURRENT_REQUESTS
95 WHERE REQUEST_ID = P_REQUEST_ID;
96 V_ENABLE_TRACE FND_CONCURRENT_PROGRAMS.ENABLE_TRACE%TYPE;
97 V_PROGRAM_ID FND_CONCURRENT_PROGRAMS.CONCURRENT_PROGRAM_ID%TYPE;
98 BEGIN
99 P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
100 /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
101 /*SRW.MESSAGE(1275
102 ,'Report Version is 120.3 Last modified date is 30/07/2005')*/NULL;
103 BEGIN
104 OPEN C_PROGRAM_ID(P_CONC_REQUEST_ID);
105 FETCH C_PROGRAM_ID
106 INTO V_PROGRAM_ID,V_ENABLE_TRACE;
107 CLOSE C_PROGRAM_ID;
108 /*SRW.MESSAGE(1275
109 ,'v_program_id -> ' || V_PROGRAM_ID || ', v_enable_trace -> ' || V_ENABLE_TRACE || ', request_id -> ' || P_CONC_REQUEST_ID)*/NULL;
110 IF V_ENABLE_TRACE = 'Y' THEN
111 EXECUTE IMMEDIATE
112 'ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''';
113 END IF;
114 LP_COLUMN_VALUE:=P_COLUMN_VALUE;
115 LP_FROM_DATE:=to_char(P_FROM_DATE,'DD-MON-YYYY');
116 LP_TO_DATE:=to_char(P_TO_DATE,'DD-MON-YYYY');
117 RETURN (TRUE);
118 EXCEPTION
119 WHEN OTHERS THEN
120 /*SRW.MESSAGE(1275
121 ,'Error during enabling the trace. ErrCode -> ' || SQLCODE || ', ErrMesg -> ' || SQLERRM)*/NULL;
122 END;
123 END BEFOREREPORT;
124
125 FUNCTION AFTERPFORM RETURN BOOLEAN IS
126 NL CONSTANT VARCHAR2(1) DEFAULT ' ';
127 BEGIN
128 IF P_JV_NO IS NOT NULL THEN
129 P_SELECTED_JV_NO := 'AND gjh.doc_sequence_value = :p_jv_no ' || ' ' || NL;
130 END IF;
131 RETURN (TRUE);
132 END AFTERPFORM;
133
134 FUNCTION AFTERREPORT RETURN BOOLEAN IS
135 BEGIN
136 /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
137 RETURN (TRUE);
138 END AFTERREPORT;
139
140 FUNCTION M_2_GRPFRFORMATTRIGGER RETURN Number IS
141 v_count NUMBER;
142 begin
143 /*
144 || code modified by aiyer for the bug 4523064
145 || Column set_of_books_id does not exist in table gl_je_headers leading to compilation error.
146 || This has been changed to ledger_id
147 */
148 SELECT count(*) INTO v_count
149 FROM gl_je_headers gjh,
150 gl_je_lines gjl,
151 gl_code_combinations gcc,
152 gl_sets_of_books gsob
153 WHERE gjh.je_header_id = gjl.je_header_id
154 AND gcc.code_combination_id = gjl.code_combination_id
155 AND gsob.set_of_books_id = gjh.ledger_id
156 AND gjh.je_source = NVL(p_source, gjh.je_source)
157 AND (gjh.doc_sequence_value = NVL(p_jv_no, gjh.doc_sequence_value) OR
158 gjh.doc_sequence_value IS NULL)
159 AND gjh.ledger_id = p_sob_id
160 AND TRUNC(gjh.default_effective_date) BETWEEN
161 NVL(TRUNC(p_from_date),TRUNC(gjh.default_effective_date))
162 AND NVL(TRUNC(p_to_date),TRUNC(gjh.default_effective_date));
163 return (v_count);
164
165 END;
166 END JA_JAINJVR_XMLP_PKG;
167
168
169
[Home] [Help]