1 PACKAGE BODY PSP_PSPENORG_XMLP_PKG AS
2 /* $Header: PSPENORGB.pls 120.3.12020000.4 2013/03/27 00:35:45 lkodaman ship $ */
3 FUNCTION AFTERPFORM RETURN BOOLEAN IS
4 CURSOR C1(P_LOOKUP_CODE IN VARCHAR2,P_TEMPLATE_ID IN NUMBER) IS
5 SELECT
6 COUNT(1)
7 FROM
8 PSP_REPORT_TEMPLATE_DETAILS
9 WHERE TEMPLATE_ID = P_TEMPLATE_ID
10 AND CRITERIA_LOOKUP_TYPE = 'PSP_SELECTION_CRITERIA'
11 AND CRITERIA_LOOKUP_CODE = P_LOOKUP_CODE;
12 L_NUM NUMBER;
13 L_NUM2 NUMBER;
14 BEGIN
15 IF P_ORG_TEMPLATE_ID IS NULL THEN
16 P_ORGANIZATION_ID := ' and 1 = 1 ';
17 ELSE
18 OPEN C1('ORG',P_ORG_TEMPLATE_ID);
19 FETCH C1
20 INTO L_NUM;
21 CLOSE C1;
22 IF L_NUM <> 0 THEN
23 P_ORGANIZATION_ID := ' and hrou.organization_id IN (select criteria_value1 from psp_report_template_details
24 where template_id = ' || P_ORG_TEMPLATE_ID || '
25 and criteria_lookup_type = ''PSP_SELECTION_CRITERIA''
26 and criteria_lookup_code = ''ORG'' ' || ' ) ';
27 ELSE
28 P_ORGANIZATION_ID := ' and 1 = 1 ';
29 END IF;
30 END IF;
31 IF P_PAY_TEMPLATE_ID IS NULL THEN
32 P_PAYROLL_ID := ' and 1 = 1 ';
33 ELSE
34 OPEN C1('PAY',P_PAY_TEMPLATE_ID);
35 FETCH C1
36 INTO L_NUM2;
37 CLOSE C1;
38 IF L_NUM2 <> 0 THEN
39 P_PAYROLL_ID := ' and pelh.payroll_id IN (select criteria_value1 from psp_report_template_details
40 where template_id = ' || P_PAY_TEMPLATE_ID || '
41 and criteria_lookup_type = ''PSP_SELECTION_CRITERIA''
42 and criteria_lookup_code = ''PAY'' ' || ' ) ';
43 ELSE
44 P_PAYROLL_ID := ' and 1 = 1 ';
45 END IF;
46 END IF;
47 RETURN (TRUE);
48 END AFTERPFORM;
49
50 FUNCTION CF_CHARGING_INSTFORMULA(GL_CODE_COMBINATION_ID IN NUMBER
51 ,PROJECT_ID1 IN NUMBER
52 ,TASK_ID1 IN NUMBER
53 ,AWARD_ID1 IN NUMBER
54 ,EXPENDITURE_ORGANIZATION_ID IN NUMBER
55 ,EXPENDITURE_TYPE IN VARCHAR2) RETURN CHAR IS
56 V_RETCODE NUMBER;
57 L_CHART_OF_ACCTS VARCHAR2(20);
58 GL_FLEX_VALUES VARCHAR2(2000);
59 L_PROJECT_NAME VARCHAR2(30);
60 L_AWARD_NUMBER VARCHAR2(15);
61 L_TASK_NUMBER VARCHAR2(300); -- Bug : 16391366 (27/03/2013)
62 L_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
63 BEGIN
64 IF GL_CODE_COMBINATION_ID IS NOT NULL THEN
65 V_RETCODE := PSP_GENERAL.FIND_CHART_OF_ACCTS(TO_NUMBER(P_SET_OF_BOOKS_ID)
66 ,L_CHART_OF_ACCTS);
67 GL_FLEX_VALUES := FND_FLEX_EXT.GET_SEGS(APPLICATION_SHORT_NAME => 'SQLGL'
68 ,KEY_FLEX_CODE => 'GL#'
69 ,STRUCTURE_NUMBER => TO_NUMBER(L_CHART_OF_ACCTS)
70 ,COMBINATION_ID => GL_CODE_COMBINATION_ID);
71 RETURN (GL_FLEX_VALUES);
72 ELSE
73 SELECT
74 NAME
75 INTO L_PROJECT_NAME
76 FROM
77 PA_PROJECTS_ALL
78 WHERE PROJECT_ID = PROJECT_ID1;
79 SELECT
80 TASK_NUMBER
81 INTO L_TASK_NUMBER
82 FROM
83 PA_TASKS_EXPEND_V -- Bug : 16391366 (20/03/2013)
84 WHERE TASK_ID = TASK_ID1;
85 SELECT
86 AWARD_NUMBER
87 INTO L_AWARD_NUMBER
88 FROM
89 GMS_AWARDS_ALL
90 WHERE AWARD_ID = AWARD_ID1;
91 SELECT
92 NAME
93 INTO L_ORG_NAME
94 FROM
95 HR_ALL_ORGANIZATION_UNITS
96 WHERE ORGANIZATION_ID = EXPENDITURE_ORGANIZATION_ID;
97 RETURN (L_PROJECT_NAME || ' ' || L_TASK_NUMBER || ' ' || L_AWARD_NUMBER || ' ' || L_ORG_NAME || ' ' || EXPENDITURE_TYPE);
98 END IF;
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 RETURN ('No Data Found');
102 WHEN OTHERS THEN
103 RETURN ('Other Error');
104 END CF_CHARGING_INSTFORMULA;
105
106 FUNCTION CF_AWD_END_DTFORMULA(AWARD_ID1 IN NUMBER) RETURN DATE IS
107 CURSOR C_AWD_END IS
108 SELECT
109 GA.END_DATE_ACTIVE
110 FROM
111 GMS_AWARDS GA
112 WHERE GA.AWARD_ID = AWARD_ID1;
113 V_AWD_END_DATE DATE;
114 BEGIN
115 IF (AWARD_ID1 IS NOT NULL) THEN
116 OPEN C_AWD_END;
117 FETCH C_AWD_END
118 INTO V_AWD_END_DATE;
119 CLOSE C_AWD_END;
120 END IF;
121 RETURN (V_AWD_END_DATE);
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 RETURN (NULL);
125 WHEN OTHERS THEN
126 RETURN (NULL);
127 END CF_AWD_END_DTFORMULA;
128
129 FUNCTION CF_DATE_CHECKFORMULA(CF_AWD_END_DT IN DATE
130 ,ENC_END_DATE IN DATE) RETURN CHAR IS
131 BEGIN
132 IF CF_AWD_END_DT > ENC_END_DATE THEN
133 RETURN ('*');
134 END IF;
135 RETURN (NULL);
136 END CF_DATE_CHECKFORMULA;
137
138 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
139 BEGIN
140 -- HR_STANDARD.EVENT('BEFORE REPORT');
141 CP_BEGIN_DATE := TO_CHAR(P_BEGIN_DATE,'DD-MON-YYYY');
142 CP_END_DATE := TO_CHAR(P_END_DATE,'DD-MON-YYYY');
143 RETURN (TRUE);
144 END BEFOREREPORT;
145
146 FUNCTION CF_ENC_DATE_RANGEFORMULA(ENC_BEGIN_DATE IN DATE
147 ,ENC_END_DATE IN DATE) RETURN CHAR IS
148 V_DATE_RANGE VARCHAR2(22);
149 BEGIN
150 V_DATE_RANGE := TO_CHAR(ENC_BEGIN_DATE
151 ,'DD-MON-YY') || ' To ' || TO_CHAR(ENC_END_DATE
152 ,'DD-MON-YY');
153 RETURN (V_DATE_RANGE);
154 END CF_ENC_DATE_RANGEFORMULA;
155
156 FUNCTION CF_ENC_AMOUNT_DSPFORMULA(ENC_AMOUNT IN NUMBER
157 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
158 BEGIN
159 /*SRW.REFERENCE(ENC_AMOUNT)*/NULL;
160 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
161 RETURN (TO_CHAR(ENC_AMOUNT
162 ,CF_CURRENCY_FORMAT));
163 END CF_ENC_AMOUNT_DSPFORMULA;
164
165 FUNCTION CF_ORG_TOTAL_DSPFORMULA(CS_ORG_TOTAL IN NUMBER
166 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
167 BEGIN
168 /*SRW.REFERENCE(CS_ORG_TOTAL)*/NULL;
169 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
170 RETURN (TO_CHAR(CS_ORG_TOTAL
171 ,CF_CURRENCY_FORMAT));
172 END CF_ORG_TOTAL_DSPFORMULA;
173
174 FUNCTION AFTERREPORT RETURN BOOLEAN IS
175 BEGIN
176 -- HR_STANDARD.EVENT('AFTER REPORT');
177 RETURN (TRUE);
178 END AFTERREPORT;
179
180 FUNCTION CF_PAY_TOTAL_DISPFORMULA(CS_PAY_TOTAL IN NUMBER
181 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
182 BEGIN
183 /*SRW.REFERENCE(CS_PAY_TOTAL)*/NULL;
184 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
185 RETURN (TO_CHAR(CS_PAY_TOTAL
186 ,CF_CURRENCY_FORMAT));
187 END CF_PAY_TOTAL_DISPFORMULA;
188
189 FUNCTION CF_CURRENCY_FORMATFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN CHAR IS
190 BEGIN
191 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
192 RETURN (FND_CURRENCY.GET_FORMAT_MASK(CURRENCY_CODE
193 ,30));
194 END CF_CURRENCY_FORMATFORMULA;
195
196 FUNCTION CF_CURRENCY_TOTAL_DSPFORMULA(CURRENCY_CODE IN VARCHAR2
197 ,CF_CURRENCY_FORMAT IN VARCHAR2
198 ,CS_CURRENCY_TOTAL IN NUMBER) RETURN CHAR IS
199 BEGIN
200 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
201 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
202 RETURN (TO_CHAR(CS_CURRENCY_TOTAL
203 ,CF_CURRENCY_FORMAT));
204 END CF_CURRENCY_TOTAL_DSPFORMULA;
205
206 END PSP_PSPENORG_XMLP_PKG;
[Home] [Help]