PACKAGE BODY: APPS.PAY_PAYIECHL_XMLP_PKG

Source

1 PACKAGE BODY PAY_PAYIECHL_XMLP_PKG AS
2 /* $Header: PAYIECHLB.pls 120.1 2008/01/07 15:56:53 srikrish noship $ */
3   FUNCTION REPORT_DATEFORMULA RETURN DATE IS
4   BEGIN
5     RETURN SYSDATE;
6   END REPORT_DATEFORMULA;
7 
8   FUNCTION EMPLOYEE_NAMEFORMULA(LAST_NAME IN VARCHAR2
9                                ,FIRST_NAME IN VARCHAR2
10                                ,MIDDLE_NAMES IN VARCHAR2) RETURN VARCHAR2 IS
11   BEGIN
12     RETURN LAST_NAME || ', ' || FIRST_NAME || ' ' || INITIALS(MIDDLE_NAMES);
13   END EMPLOYEE_NAMEFORMULA;
14 
15   FUNCTION INITIALS(NAME IN VARCHAR2) RETURN VARCHAR2 IS
16     L_INITIALS VARCHAR2(255);
17     L_POS NUMBER;
18     L_NAME VARCHAR2(255);
19   BEGIN
20     L_NAME := RTRIM(LTRIM(NAME));
21     IF NVL(LENGTH(L_NAME)
22        ,0) > 0 THEN
23       L_INITIALS := SUBSTR(L_NAME
24                           ,1
25                           ,1) || '.';
26     END IF;
27     L_POS := INSTR(L_NAME
28                   ,' '
29                   ,1
30                   ,1);
31     WHILE L_POS <> 0 LOOP
32 
33       L_INITIALS := L_INITIALS || SUBSTR(L_NAME
34                           ,L_POS + 1
35                           ,1) || '.';
36       L_POS := INSTR(L_NAME
37                     ,' '
38                     ,L_POS + 1
39                     ,1);
40     END LOOP;
41     RETURN L_INITIALS;
42   END INITIALS;
43 
44   FUNCTION PAYEEFORMULA(PERSONAL_PAYMENT_METHOD_ID IN NUMBER
45                        ,DATE_EARNED IN DATE
46                        ,LAST_NAME IN VARCHAR2
47                        ,FIRST_NAME IN VARCHAR2
48                        ,MIDDLE_NAMES IN VARCHAR2) RETURN VARCHAR2 IS
49     CURSOR PAYMENT_METHOD IS
50       SELECT
51         PAYEE_ID,
52         PAYEE_TYPE
53       FROM
54         PAY_PERSONAL_PAYMENT_METHODS_F
55       WHERE PERSONAL_PAYMENT_METHOD_ID = PERSONAL_PAYMENT_METHOD_ID
56         AND DATE_EARNED between EFFECTIVE_START_DATE
57         AND EFFECTIVE_END_DATE;
58     CURSOR PERSON(P_PERSON_ID IN NUMBER) IS
59       SELECT
60         FIRST_NAME,
61         MIDDLE_NAMES,
62         LAST_NAME
63       FROM
64         PER_ALL_PEOPLE_F
65       WHERE PERSON_ID = P_PERSON_ID
66         AND DATE_EARNED between EFFECTIVE_START_DATE
67         AND EFFECTIVE_END_DATE;
68     CURSOR ORGANIZATION(P_ORGANIZATION_ID IN NUMBER) IS
69       SELECT
70         NAME
71       FROM
72         HR_ALL_ORGANIZATION_UNITS
73       WHERE ORGANIZATION_ID = P_ORGANIZATION_ID;
74     L_PAYEE_ID NUMBER;
75     L_PAYEE_TYPE VARCHAR2(30);
76     L_FIRST_NAME VARCHAR2(20);
77     L_MIDDLE_NAMES VARCHAR2(60);
78     L_LAST_NAME VARCHAR2(40);
79     L_NAME VARCHAR2(60);
80   BEGIN
81     IF PERSONAL_PAYMENT_METHOD_ID IS NULL THEN
82       RETURN LAST_NAME || ', ' || FIRST_NAME || ' ' || INITIALS(MIDDLE_NAMES);
83     ELSE
84       OPEN PAYMENT_METHOD;
85       FETCH PAYMENT_METHOD
86        INTO L_PAYEE_ID,L_PAYEE_TYPE;
87       CLOSE PAYMENT_METHOD;
88       IF L_PAYEE_ID IS NULL THEN
89         RETURN LAST_NAME || ', ' || FIRST_NAME || ' ' || INITIALS(MIDDLE_NAMES);
90       ELSE
91         IF L_PAYEE_TYPE = 'P' THEN
92           OPEN PERSON(L_PAYEE_ID);
93           FETCH PERSON
94            INTO L_FIRST_NAME,L_MIDDLE_NAMES,L_LAST_NAME;
95           CLOSE PERSON;
96           RETURN L_LAST_NAME || ', ' || L_FIRST_NAME || ' ' || INITIALS(L_MIDDLE_NAMES);
97         END IF;
98         IF L_PAYEE_TYPE = 'O' THEN
99           OPEN ORGANIZATION(L_PAYEE_ID);
100           FETCH ORGANIZATION
101            INTO L_NAME;
102           CLOSE ORGANIZATION;
103           RETURN L_NAME;
104         END IF;
105       END IF;
106     END IF;
107     RETURN NULL;
108   EXCEPTION
109     WHEN NO_DATA_FOUND THEN
110       RETURN LAST_NAME || ', ' || FIRST_NAME || ' ' || INITIALS(MIDDLE_NAMES);
111   END PAYEEFORMULA;
112 
113   FUNCTION BEFOREPFORM RETURN BOOLEAN IS
114   BEGIN
115     IF P_SORT_ORDER_T IS NULL THEN
116       P_SORT_ORDER_T := '1';
117     END IF;
118     P_SORT_ORDER_T := 'order by ' || P_SORT_ORDER_T;
119     IF P_SORT_ORDER_T = 'order by 1' THEN
120       P_SORT_ORDER_T := 'order by upper(ppf.payroll_name), 6, 1';
121     END IF;
122     IF P_SORT_ORDER_T = 'order by 2' THEN
123       P_SORT_ORDER_T := 'order by upper(ppf.payroll_name), 6, upper(per.last_name), upper(per.first_name), upper(per.middle_names), lpad(upper(per.employee_number), 30, ''0''), lpad(upper(paf.assignment_number), 30, ''0'')';
124     END IF;
125     IF P_SORT_ORDER_T = 'order by 3' THEN
126       P_SORT_ORDER_T := 'order by upper(ppf.payroll_name), 6, lpad(upper(per.employee_number), 30, ''0''), lpad(upper(paf.assignment_number), 30, ''0'')';
127     END IF;
128     RETURN (TRUE);
129   END BEFOREPFORM;
130 
131   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
132   temp boolean;
133   BEGIN
134   P_SORT_ORDER_T := P_SORT_ORDER;
135   temp := BEFOREPFORM();
136     --HR_STANDARD.EVENT('BEFORE REPORT');
137     RETURN (TRUE);
138   END BEFOREREPORT;
139 
140   FUNCTION AFTERREPORT RETURN BOOLEAN IS
141   BEGIN
142     --HR_STANDARD.EVENT('AFTER REPORT');
143     RETURN (TRUE);
144   END AFTERREPORT;
145 
146 END PAY_PAYIECHL_XMLP_PKG;