1 PACKAGE BODY PSP_PSPENAWD_XMLP_PKG AS
2 /* $Header: PSPENAWDB.pls 120.7.12020000.4 2013/03/27 00:34:10 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_AWD_TEMPLATE_ID IS NULL THEN
16 P_AWARD_ID := ' and 1 = 1 ';
17 ELSE
18 OPEN C1('AWD',P_AWD_TEMPLATE_ID);
19 FETCH C1
20 INTO L_NUM;
21 CLOSE C1;
22 IF L_NUM <> 0 THEN
23 P_AWARD_ID := ' and gaa.award_id IN (select criteria_value1 from psp_report_template_details
24 where template_id = ' || P_AWD_TEMPLATE_ID || '
25 and criteria_lookup_type = ''PSP_SELECTION_CRITERIA''
26 and criteria_lookup_code = ''AWD'' ' || ' ) ';
27 ELSE
28 P_AWARD_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(PROJECT_ID IN NUMBER
51 ,TASK_ID IN NUMBER
52 ,AWARD_ID IN NUMBER
53 ,EXPENDITURE_ORGANIZATION_ID IN NUMBER
54 ,EXPENDITURE_TYPE IN VARCHAR2) RETURN CHAR IS
55 L_PROJECT_NAME VARCHAR2(30);
56 L_AWARD_NUMBER VARCHAR2(15);
57 L_TASK_NUMBER VARCHAR2(300); -- Bug : 16391366 (27/03/2013)
58 L_ORG_NAME HR_ALL_ORGANIZATION_UNITS_TL.NAME%TYPE;
59 BEGIN
60 SELECT
61 NAME
62 INTO L_PROJECT_NAME
63 FROM
64 PA_PROJECTS_ALL
65 WHERE PROJECT_ID = CF_CHARGING_INSTFORMULA.PROJECT_ID;
66 SELECT
67 TASK_NUMBER
68 INTO L_TASK_NUMBER
69 FROM
70 PA_TASKS_EXPEND_V -- Bug : 16391366 (20/03/2013)
71 WHERE TASK_ID = CF_CHARGING_INSTFORMULA.TASK_ID;
72 SELECT
73 AWARD_NUMBER
74 INTO L_AWARD_NUMBER
75 FROM
76 GMS_AWARDS_ALL
77 WHERE AWARD_ID = CF_CHARGING_INSTFORMULA.AWARD_ID;
78 SELECT
79 NAME
80 INTO L_ORG_NAME
81 FROM
82 HR_ALL_ORGANIZATION_UNITS
83 WHERE ORGANIZATION_ID = EXPENDITURE_ORGANIZATION_ID;
84 RETURN (L_PROJECT_NAME || ' ' || L_TASK_NUMBER || ' ' || L_AWARD_NUMBER || ' ' || L_ORG_NAME || ' ' || EXPENDITURE_TYPE);
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 RETURN ('No Data Found');
88 WHEN OTHERS THEN
89 RETURN ('Other Error');
90 END CF_CHARGING_INSTFORMULA;
91
92 FUNCTION CF_AWD_END_DTFORMULA(AWARD_ID IN NUMBER) RETURN DATE IS
93 CURSOR C_AWD_END IS
94 SELECT
95 GA.END_DATE_ACTIVE
96 FROM
97 GMS_AWARDS GA
98 WHERE GA.AWARD_ID = CF_AWD_END_DTFORMULA.AWARD_ID;
99 V_AWD_END_DATE DATE;
100 BEGIN
101 IF (AWARD_ID IS NOT NULL) THEN
102 OPEN C_AWD_END;
103 FETCH C_AWD_END
104 INTO V_AWD_END_DATE;
105 CLOSE C_AWD_END;
106 END IF;
107 RETURN (V_AWD_END_DATE);
108 EXCEPTION
109 WHEN NO_DATA_FOUND THEN
110 RETURN (NULL);
111 WHEN OTHERS THEN
112 RETURN (NULL);
113 END CF_AWD_END_DTFORMULA;
114
115 FUNCTION CF_DATE_CHECKFORMULA(CF_AWD_END_DT IN DATE
116 ,ENC_END_DATE IN DATE) RETURN CHAR IS
117 BEGIN
118 IF CF_AWD_END_DT > ENC_END_DATE THEN
119 RETURN ('*');
120 END IF;
121 RETURN (NULL);
122 END CF_DATE_CHECKFORMULA;
123
124 FUNCTION BEFOREREPORT RETURN BOOLEAN IS
125 BEGIN
126 --HR_STANDARD.EVENT('BEFORE REPORT');
127 LP_BEGIN_DATE:=to_char(P_BEGIN_DATE,'DD-MON-YYYY');
128 LP_END_DATE:=to_char(P_END_DATE,'DD-MON-YYYY');
129 RETURN (TRUE);
130 END BEFOREREPORT;
131
132 FUNCTION CF_END_DATE_RANGEFORMULA(ENC_BEGIN_DATE IN DATE
133 ,ENC_END_DATE IN DATE) RETURN CHAR IS
134 V_DATE_RANGE VARCHAR2(22);
135 BEGIN
136 V_DATE_RANGE := TO_CHAR(ENC_BEGIN_DATE
137 ,'DD-MON-YY') || ' To ' || TO_CHAR(ENC_END_DATE
138 ,'DD-MON-YY');
139 RETURN (V_DATE_RANGE);
140 EXCEPTION
141 WHEN OTHERS THEN
142 RETURN ('Error');
143 END CF_END_DATE_RANGEFORMULA;
144
145 FUNCTION CF_AWARD_TOTAL_DSPFORMULA(CS_AWARD_TOTAL IN NUMBER
146 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
147 BEGIN
148 /*SRW.REFERENCE(CS_AWARD_TOTAL)*/NULL;
149 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
150 RETURN (TO_CHAR(CS_AWARD_TOTAL
151 ,CF_CURRENCY_FORMAT));
152 END CF_AWARD_TOTAL_DSPFORMULA;
153
154 FUNCTION CF_PELH_ENC_AMOUNT_DSPFORMULA(SUM_PELH_ENCUMBRANCE_AMOUNT IN NUMBER
155 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
156 BEGIN
157 /*SRW.REFERENCE(SUM_PELH_ENCUMBRANCE_AMOUNT)*/NULL;
158 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
159 RETURN (TO_CHAR(SUM_PELH_ENCUMBRANCE_AMOUNT
160 ,CF_CURRENCY_FORMAT));
161 END CF_PELH_ENC_AMOUNT_DSPFORMULA;
162
163 FUNCTION AFTERREPORT RETURN BOOLEAN IS
164 BEGIN
165 --HR_STANDARD.EVENT('AFTER REPORT');
166 RETURN (TRUE);
167 END AFTERREPORT;
168
169 FUNCTION CF_PAY_TOTAL_DSPFORMULA(CS_PAY_TOTAL IN NUMBER
170 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN CHAR IS
171 BEGIN
172 /*SRW.REFERENCE(CS_PAY_TOTAL)*/NULL;
173 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
174 RETURN (TO_CHAR(CS_PAY_TOTAL
175 ,CF_CURRENCY_FORMAT));
176 END CF_PAY_TOTAL_DSPFORMULA;
177
178 FUNCTION CF_CURRENCY_FORMATFORMULA(CURRENCY_CODE IN VARCHAR2) RETURN VARCHAR2 IS
179 BEGIN
180 /*SRW.REFERENCE(CURRENCY_CODE)*/NULL;
181 RETURN (FND_CURRENCY.GET_FORMAT_MASK(CURRENCY_CODE
182 ,30));
183 END CF_CURRENCY_FORMATFORMULA;
184
185 FUNCTION CF_CURRENCY_TOTAL_DSPFORMULA(CS_CURRENCY_TOTAL IN NUMBER
186 ,CF_CURRENCY_FORMAT IN VARCHAR2) RETURN VARCHAR2 IS
187 BEGIN
188 /*SRW.REFERENCE(CS_CURRENCY_TOTAL)*/NULL;
189 /*SRW.REFERENCE(CF_CURRENCY_FORMAT)*/NULL;
190 RETURN (TO_CHAR(CS_CURRENCY_TOTAL
191 ,CF_CURRENCY_FORMAT));
192 END CF_CURRENCY_TOTAL_DSPFORMULA;
193
194 END PSP_PSPENAWD_XMLP_PKG;
[Home] [Help]