eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.AP_APXHRUPD_XMLP_PKG

Source


1 PACKAGE BODY AP_APXHRUPD_XMLP_PKG AS
2 /* $Header: APXHRUPDB.pls 120.0 2007/12/27 07:42:04 vjaganat noship $ */
3   FUNCTION GET_BASE_CURR_DATA RETURN BOOLEAN IS
4     BASE_CURR AP_SYSTEM_PARAMETERS.BASE_CURRENCY_CODE%TYPE;
5     PREC FND_CURRENCIES.PRECISION%TYPE;
6     MIN_AU FND_CURRENCIES.MINIMUM_ACCOUNTABLE_UNIT%TYPE;
7     DESCR FND_CURRENCIES.DESCRIPTION%TYPE;
8   BEGIN
9     BASE_CURR := '';
10     PREC := 0;
11     MIN_AU := 0;
12     DESCR := '';
13     SELECT
14       P.BASE_CURRENCY_CODE,
15       C.PRECISION,
16       C.MINIMUM_ACCOUNTABLE_UNIT,
17       C.DESCRIPTION
18     INTO BASE_CURR,PREC,MIN_AU,DESCR
19     FROM
20       AP_SYSTEM_PARAMETERS P,
21       FND_CURRENCIES C
22     WHERE P.BASE_CURRENCY_CODE = C.CURRENCY_CODE;
23     C_BASE_CURRENCY_CODE := BASE_CURR;
24     C_BASE_PRECISION := PREC;
25     C_BASE_MIN_ACCT_UNIT := MIN_AU;
26     C_BASE_DESCRIPTION := DESCR;
27     RETURN (TRUE);
28     RETURN NULL;
29   EXCEPTION
30     WHEN OTHERS THEN
31       RETURN (FALSE);
32   END GET_BASE_CURR_DATA;
33 
34   FUNCTION CUSTOM_INIT RETURN BOOLEAN IS
35   BEGIN
36     RETURN (TRUE);
37     RETURN NULL;
38   EXCEPTION
39     WHEN OTHERS THEN
40       RETURN (FALSE);
41   END CUSTOM_INIT;
42 
43   FUNCTION GET_COVER_PAGE_VALUES RETURN BOOLEAN IS
44   BEGIN
45     RETURN (TRUE);
46     RETURN NULL;
47   EXCEPTION
48     WHEN OTHERS THEN
49       RETURN (FALSE);
50   END GET_COVER_PAGE_VALUES;
51 
52   FUNCTION GET_NLS_STRINGS RETURN BOOLEAN IS
53     NLS_VOID AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
54     NLS_NA AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
55     NLS_ALL AP_LOOKUP_CODES.DISPLAYED_FIELD%TYPE;
56     NLS_YES FND_LOOKUPS.MEANING%TYPE;
57     NLS_NO FND_LOOKUPS.MEANING%TYPE;
58   BEGIN
59     SELECT
60       LY.MEANING,
61       LN.MEANING,
62       L1.DISPLAYED_FIELD,
63       L2.DISPLAYED_FIELD,
64       L3.DISPLAYED_FIELD
65     INTO NLS_YES,NLS_NO,NLS_ALL,NLS_VOID,NLS_NA
66     FROM
67       FND_LOOKUPS LY,
68       FND_LOOKUPS LN,
69       AP_LOOKUP_CODES L1,
70       AP_LOOKUP_CODES L2,
71       AP_LOOKUP_CODES L3
72     WHERE LY.LOOKUP_TYPE = 'YES_NO'
73       AND LY.LOOKUP_CODE = 'Y'
74       AND LN.LOOKUP_TYPE = 'YES_NO'
75       AND LN.LOOKUP_CODE = 'N'
76       AND L1.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
77       AND L1.LOOKUP_CODE = 'ALL'
78       AND L2.LOOKUP_TYPE = 'NLS TRANSLATION'
79       AND L2.LOOKUP_CODE = 'VOID'
80       AND L3.LOOKUP_TYPE = 'NLS REPORT PARAMETER'
81       AND L3.LOOKUP_CODE = 'NA';
82     C_NLS_YES := NLS_YES;
83     C_NLS_NO := NLS_NO;
84     C_NLS_ALL := NLS_ALL;
85     C_NLS_VOID := NLS_VOID;
86     C_NLS_NA := NLS_NA;
87     FND_MESSAGE.SET_NAME('SQLAP'
88                         ,'AP_APPRVL_NO_DATA');
89     C_NLS_NO_DATA_EXISTS := FND_MESSAGE.GET;
90     FND_MESSAGE.SET_NAME('SQLAP'
91                         ,'AP_ALL_END_OF_REPORT');
92     C_NLS_END_OF_REPORT := FND_MESSAGE.GET;
93    -- C_NLS_NO_DATA_EXISTS := '*** ' || C_NLS_NO_DATA_EXISTS || ' ***';
94    -- C_NLS_END_OF_REPORT := '*** ' || C_NLS_END_OF_REPORT || ' ***';
95     RETURN (TRUE);
96     RETURN NULL;
97   EXCEPTION
98     WHEN OTHERS THEN
99       RETURN (FALSE);
100   END GET_NLS_STRINGS;
101 
102   FUNCTION BEFOREREPORT RETURN BOOLEAN IS
103   BEGIN
104     DECLARE
105       INIT_FAILURE EXCEPTION;
106     BEGIN
107       C_REPORT_START_DATE := SYSDATE;
108       P_CONC_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
109       /*SRW.USER_EXIT('FND SRWINIT')*/NULL;
110       IF (P_DEBUG_SWITCH in ('y','Y')) THEN
111         /*SRW.MESSAGE('1'
112                    ,'After SRWINIT')*/NULL;
113       END IF;
114       IF (GET_LAST_RUN_DATE <> TRUE) THEN
115         RAISE INIT_FAILURE;
116       END IF;
117       IF (P_DEBUG_SWITCH in ('y','Y')) THEN
118         /*SRW.MESSAGE('3'
119                    ,'After Get_Last_Run_Date')*/NULL;
120       END IF;
121       IF (SET_SUPPLIER_WHERE <> TRUE) THEN
122         RAISE INIT_FAILURE;
123       END IF;
124       IF (P_DEBUG_SWITCH in ('y','Y')) THEN
125         /*SRW.MESSAGE('9'
126                    ,'After Set_Supplier_Where')*/NULL;
127       END IF;
128       IF (GET_COMPANY_NAME <> TRUE) THEN
129         RAISE INIT_FAILURE;
130       END IF;
131       IF (P_DEBUG_SWITCH in ('y','Y')) THEN
132         /*SRW.MESSAGE('11'
133                    ,'After Get_Company_Name')*/NULL;
134       END IF;
135       IF (GET_BASE_CURR_DATA <> TRUE) THEN
136         RAISE INIT_FAILURE;
137       END IF;
138       IF (P_DEBUG_SWITCH in ('y','Y')) THEN
139         /*SRW.MESSAGE('13'
140                    ,'After Get_Base_Curr_Data')*/NULL;
141       END IF;
142       IF (P_DEBUG_SWITCH in ('y','Y')) THEN
143         /*SRW.BREAK*/NULL;
144       END IF;
145       RETURN (TRUE);
146     EXCEPTION
147       WHEN OTHERS THEN
148         /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
149     END;
150     RETURN (TRUE);
151   END BEFOREREPORT;
152 
153   FUNCTION AFTERREPORT RETURN BOOLEAN IS
154   BEGIN
155     DECLARE
156       INIT_FAILURE EXCEPTION;
157     BEGIN
158       IF (AP_EMPLOYEE_UPDATE_PKG.UPDATE_EMPLOYEE(C_UPDATE_DATE
159                                             ,P_FROM_SUPPLIER
160                                             ,P_TO_SUPPLIER
161                                             ,P_DEBUG_SWITCH
162                                             ,'After Report Trigger') <> TRUE) THEN
163         RAISE INIT_FAILURE;
164       END IF;
165       IF (P_DEBUG_SWITCH = 'Y') THEN
166         /*SRW.MESSAGE('21'
167                    ,'AP_EMPLOYEE_UPDATE_PKG.Update_Employee')*/NULL;
168       END IF;
169       /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
170       IF (P_DEBUG_SWITCH = 'Y') THEN
171         /*SRW.MESSAGE('22'
172                    ,'After SRWEXIT')*/NULL;
173       END IF;
174     EXCEPTION
175       WHEN OTHERS THEN
176         /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
177     END;
178     RETURN (TRUE);
179   END AFTERREPORT;
180 
181   FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
182     L_CHART_OF_ACCOUNTS_ID GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
183     L_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
184     L_SOB_ID NUMBER;
185     L_REPORT_START_DATE DATE;
186   BEGIN
187     L_REPORT_START_DATE := SYSDATE;
188     L_SOB_ID := P_SET_OF_BOOKS_ID;
189     /*SRW.MESSAGE('10'
190                ,'l_sob_id: ' || L_SOB_ID)*/NULL;
191     SELECT
192       NAME,
193       CHART_OF_ACCOUNTS_ID
194     INTO L_NAME,L_CHART_OF_ACCOUNTS_ID
195     FROM
196       GL_SETS_OF_BOOKS
197     WHERE SET_OF_BOOKS_ID = L_SOB_ID;
198     C_COMPANY_NAME_HEADER := L_NAME;
199     C_CHART_OF_ACCOUNTS_ID := L_CHART_OF_ACCOUNTS_ID;
200     C_REPORT_START_DATE := L_REPORT_START_DATE;
201     RETURN (TRUE);
202     RETURN NULL;
203   EXCEPTION
204     WHEN OTHERS THEN
205       RETURN (FALSE);
206   END GET_COMPANY_NAME;
207 
208   FUNCTION GET_LAST_RUN_DATE RETURN BOOLEAN IS
209     L_REQUESTED_START_DATE FND_CONCURRENT_REQUESTS.REQUESTED_START_DATE%TYPE;
210   BEGIN
211     SELECT
212       MAX(REQUESTED_START_DATE)
213     INTO L_REQUESTED_START_DATE
214     FROM
215       FND_CONCURRENT_PROGRAMS FCONC,
216       FND_CONCURRENT_REQUESTS FREQ
217     WHERE FCONC.APPLICATION_ID = FREQ.PROGRAM_APPLICATION_ID
218       AND FCONC.CONCURRENT_PROGRAM_ID = FREQ.CONCURRENT_PROGRAM_ID
219       AND FCONC.CONCURRENT_PROGRAM_NAME = 'APXHRUPD'
220       AND FREQ.STATUS_CODE = 'C';
221     IF (P_FROM_SUPPLIER IS NOT NULL AND P_TO_SUPPLIER IS NOT NULL) THEN
222       C_UPDATE_DATE := NULL;
223     ELSE
224       C_UPDATE_DATE := NVL(L_REQUESTED_START_DATE
225                           ,TO_DATE('01/01/1951'
226                                  ,'DD/MM/YYYY'));
227     END IF;
228     IF (P_DEBUG_SWITCH = 'Y') THEN
229       /*SRW.MESSAGE('2'
230                  ,'C_Update Date: ' || C_UPDATE_DATE)*/NULL;
231     END IF;
232     RETURN (TRUE);
233     RETURN NULL;
234   EXCEPTION
235     WHEN OTHERS THEN
236       RETURN (FALSE);
237   END GET_LAST_RUN_DATE;
238 
239   FUNCTION CALCULATE_RUN_TIME RETURN BOOLEAN IS
240     END_DATE DATE;
241     START_DATE DATE;
242   BEGIN
243     END_DATE := SYSDATE;
244     START_DATE := C_REPORT_START_DATE;
245     C_REPORT_RUN_TIME := TO_CHAR(TO_DATE('01/01/0001'
246                                         ,'DD/MM/YYYY') + ((END_DATE - START_DATE))
247                                 ,'HH24:MI:SS');
248     RETURN (TRUE);
249     RETURN NULL;
250   EXCEPTION
251     WHEN OTHERS THEN
252       /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
253   END CALCULATE_RUN_TIME;
254 
255   FUNCTION C_DUPLICATE_YES_NOFORMULA(UPDATED_VALUE IN VARCHAR2
256                                     ,UPDATED_TO IN VARCHAR2) RETURN VARCHAR2 IS
257     L_DUPLICATES VARCHAR2(1);
258     L_COUNT NUMBER;
259   BEGIN
260     /*SRW.REFERENCE(UPDATED_VALUE)*/NULL;
261     IF UPDATED_VALUE = 'Name' THEN
262       BEGIN
263         SELECT
264           'Y'
265         INTO L_DUPLICATES
266         FROM
267           DUAL
268         WHERE EXISTS (
269           SELECT
270             'Duplicates exist in Po_Vendors'
271           FROM
272             AP_SUPPLIERS
273           WHERE VENDOR_NAME = UPDATED_TO );
274         RETURN ('Y');
275       EXCEPTION
276         WHEN NO_DATA_FOUND THEN
277           SELECT
278             COUNT(PERSON_ID)
279           INTO L_COUNT
280           FROM
281             PER_ALL_PEOPLE_F PPF
282           WHERE TRUNC(SYSDATE) between PPF.EFFECTIVE_START_DATE
283             AND PPF.EFFECTIVE_END_DATE
284             AND DECODE(PPF.MIDDLE_NAMES
285                 ,NULL
286                 ,PPF.LAST_NAME || ', ' || PPF.FIRST_NAME
287                 ,PPF.LAST_NAME || ', ' || PPF.FIRST_NAME || ' ' || PPF.MIDDLE_NAMES) = UPDATED_TO;
288           IF L_COUNT > 1 THEN
289             RETURN ('Y');
290           ELSE
291             RETURN ('N');
292           END IF;
293       END;
294     ELSE
295       RETURN ('N');
296     END IF;
297   END C_DUPLICATE_YES_NOFORMULA;
298 
299   FUNCTION SET_SUPPLIER_WHERE RETURN BOOLEAN IS
300   BEGIN
301     IF (P_FROM_SUPPLIER IS NOT NULL AND P_TO_SUPPLIER IS NOT NULL) THEN
302       C_SUPPLIER_PREDICATE := ' and pv1.vendor_name between ' || '''' || P_FROM_SUPPLIER || ''' and ''' || P_TO_SUPPLIER || '''';
303       C_NAME_UPDATE_DATE := ' ';
304       C_INACTIVE_UPDATE_DATE := ' ';
305       C_HOME_UPDATE_DATE := ' ';
306       C_OFFICE_UPDATE_DATE := ' ';
307     ELSE
308       C_SUPPLIER_PREDICATE := ' ';
309       C_NAME_UPDATE_DATE := ' and ppf.last_update_date  > ' || '''' || C_UPDATE_DATE || '''';
310       C_INACTIVE_UPDATE_DATE := ' and ppos.last_update_date > ' || '''' || C_UPDATE_DATE || '''';
311       C_HOME_UPDATE_DATE := ' and pa1.last_update_date > ' || '''' || C_UPDATE_DATE || '''';
312       C_OFFICE_UPDATE_DATE := ' and (trunc(paf.last_update_date) >= ' || '''' || C_UPDATE_DATE || '''' || ' OR ' || 'trunc(locs.last_update_date) >= ' || '''' || C_UPDATE_DATE || '''' || ')';
313     END IF;
314     /*SRW.MESSAGE('4'
315                ,'C_supplier_predicate: ' || C_SUPPLIER_PREDICATE)*/NULL;
316     /*SRW.MESSAGE('5'
317                ,'C_name_update_date: ' || C_NAME_UPDATE_DATE)*/NULL;
318     /*SRW.MESSAGE('6'
319                ,'C_inactive_update_date: ' || C_INACTIVE_UPDATE_DATE)*/NULL;
320     /*SRW.MESSAGE('7'
321                ,'C_home_update_date: ' || C_HOME_UPDATE_DATE)*/NULL;
322     /*SRW.MESSAGE('8'
323                ,'C_office_update_date: ' || C_OFFICE_UPDATE_DATE)*/NULL;
324     RETURN (TRUE);
325     RETURN NULL;
326   EXCEPTION
327     WHEN OTHERS THEN
328       RETURN (FALSE);
329   END SET_SUPPLIER_WHERE;
330 
331   FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
332   BEGIN
333     RETURN C_NLS_YES;
334   END C_NLS_YES_P;
335 
336   FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
337   BEGIN
338     RETURN C_NLS_NO;
339   END C_NLS_NO_P;
340 
341   FUNCTION C_NLS_ALL_P RETURN VARCHAR2 IS
342   BEGIN
343     RETURN C_NLS_ALL;
344   END C_NLS_ALL_P;
345 
346   FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
347   BEGIN
348     RETURN C_NLS_NO_DATA_EXISTS;
349   END C_NLS_NO_DATA_EXISTS_P;
350 
351   FUNCTION C_NLS_VOID_P RETURN VARCHAR2 IS
352   BEGIN
353     RETURN C_NLS_VOID;
354   END C_NLS_VOID_P;
355 
356   FUNCTION C_NLS_NA_P RETURN VARCHAR2 IS
357   BEGIN
358     RETURN C_NLS_NA;
359   END C_NLS_NA_P;
360 
361   FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
362   BEGIN
363     RETURN C_NLS_END_OF_REPORT;
364   END C_NLS_END_OF_REPORT_P;
365 
366   FUNCTION C_REPORT_START_DATE_P RETURN DATE IS
367   BEGIN
368     RETURN C_REPORT_START_DATE;
369   END C_REPORT_START_DATE_P;
370 
371   FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
372   BEGIN
373     RETURN C_COMPANY_NAME_HEADER;
374   END C_COMPANY_NAME_HEADER_P;
375 
376   FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
377   BEGIN
378     RETURN C_BASE_CURRENCY_CODE;
379   END C_BASE_CURRENCY_CODE_P;
380 
381   FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
382   BEGIN
383     RETURN C_BASE_PRECISION;
384   END C_BASE_PRECISION_P;
385 
386   FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
387   BEGIN
388     RETURN C_BASE_MIN_ACCT_UNIT;
389   END C_BASE_MIN_ACCT_UNIT_P;
390 
391   FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
392   BEGIN
393     RETURN C_BASE_DESCRIPTION;
394   END C_BASE_DESCRIPTION_P;
395 
396   FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
397   BEGIN
398     RETURN C_CHART_OF_ACCOUNTS_ID;
399   END C_CHART_OF_ACCOUNTS_ID_P;
400 
401   FUNCTION C_REPORT_RUN_TIME_P RETURN VARCHAR2 IS
402   BEGIN
403     RETURN C_REPORT_RUN_TIME;
404   END C_REPORT_RUN_TIME_P;
405 
406   FUNCTION C_SUPPLIER_PREDICATE_P RETURN VARCHAR2 IS
407   BEGIN
408     RETURN C_SUPPLIER_PREDICATE;
409   END C_SUPPLIER_PREDICATE_P;
410 
411   FUNCTION C_NAME_UPDATE_DATE_P RETURN VARCHAR2 IS
412   BEGIN
413     RETURN C_NAME_UPDATE_DATE;
414   END C_NAME_UPDATE_DATE_P;
415 
416   FUNCTION C_INACTIVE_UPDATE_DATE_P RETURN VARCHAR2 IS
417   BEGIN
418     RETURN C_INACTIVE_UPDATE_DATE;
419   END C_INACTIVE_UPDATE_DATE_P;
420 
421   FUNCTION C_HOME_UPDATE_DATE_P RETURN VARCHAR2 IS
422   BEGIN
423     RETURN C_HOME_UPDATE_DATE;
424   END C_HOME_UPDATE_DATE_P;
425 
426   FUNCTION C_OFFICE_UPDATE_DATE_P RETURN VARCHAR2 IS
427   BEGIN
428     RETURN C_OFFICE_UPDATE_DATE;
429   END C_OFFICE_UPDATE_DATE_P;
430 
431 END AP_APXHRUPD_XMLP_PKG;
432 
433