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.12020000.2 2012/07/20 12:38:27 lkarna ship $ */
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_BUSINESS_GROUP_ID   --bug 14353622
162                                             ,P_DEBUG_SWITCH
163                                             ,'After Report Trigger') <> TRUE) THEN
164         RAISE INIT_FAILURE;
165       END IF;
166       IF (P_DEBUG_SWITCH = 'Y') THEN
167         /*SRW.MESSAGE('21'
168                    ,'AP_EMPLOYEE_UPDATE_PKG.Update_Employee')*/NULL;
169       END IF;
170       /*SRW.USER_EXIT('FND SRWEXIT')*/NULL;
171       IF (P_DEBUG_SWITCH = 'Y') THEN
172         /*SRW.MESSAGE('22'
173                    ,'After SRWEXIT')*/NULL;
174       END IF;
175     EXCEPTION
176       WHEN OTHERS THEN
177         /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
178     END;
179     RETURN (TRUE);
180   END AFTERREPORT;
181 
182   FUNCTION GET_COMPANY_NAME RETURN BOOLEAN IS
183     L_CHART_OF_ACCOUNTS_ID GL_SETS_OF_BOOKS.CHART_OF_ACCOUNTS_ID%TYPE;
184     L_NAME GL_SETS_OF_BOOKS.NAME%TYPE;
185     L_SOB_ID NUMBER;
186     L_REPORT_START_DATE DATE;
187   BEGIN
188     L_REPORT_START_DATE := SYSDATE;
189     L_SOB_ID := P_SET_OF_BOOKS_ID;
190     /*SRW.MESSAGE('10'
191                ,'l_sob_id: ' || L_SOB_ID)*/NULL;
192     SELECT
193       NAME,
194       CHART_OF_ACCOUNTS_ID
195     INTO L_NAME,L_CHART_OF_ACCOUNTS_ID
196     FROM
197       GL_SETS_OF_BOOKS
198     WHERE SET_OF_BOOKS_ID = L_SOB_ID;
199     C_COMPANY_NAME_HEADER := L_NAME;
200     C_CHART_OF_ACCOUNTS_ID := L_CHART_OF_ACCOUNTS_ID;
201     C_REPORT_START_DATE := L_REPORT_START_DATE;
202     RETURN (TRUE);
203     RETURN NULL;
204   EXCEPTION
205     WHEN OTHERS THEN
206       RETURN (FALSE);
207   END GET_COMPANY_NAME;
208 
209   FUNCTION GET_LAST_RUN_DATE RETURN BOOLEAN IS
210     L_REQUESTED_START_DATE FND_CONCURRENT_REQUESTS.REQUESTED_START_DATE%TYPE;
211   BEGIN
212     SELECT
213       MAX(REQUESTED_START_DATE)
214     INTO L_REQUESTED_START_DATE
215     FROM
216       FND_CONCURRENT_PROGRAMS FCONC,
217       FND_CONCURRENT_REQUESTS FREQ
218     WHERE FCONC.APPLICATION_ID = FREQ.PROGRAM_APPLICATION_ID
219       AND FCONC.CONCURRENT_PROGRAM_ID = FREQ.CONCURRENT_PROGRAM_ID
220       AND FCONC.CONCURRENT_PROGRAM_NAME = 'APXHRUPD'
221       AND FREQ.STATUS_CODE = 'C';
222     IF (P_FROM_SUPPLIER IS NOT NULL AND P_TO_SUPPLIER IS NOT NULL) THEN
223       C_UPDATE_DATE := NULL;
224     ELSE
225       C_UPDATE_DATE := NVL(L_REQUESTED_START_DATE
226                           ,TO_DATE('01/01/1951'
227                                  ,'DD/MM/YYYY'));
228     END IF;
229     IF (P_DEBUG_SWITCH = 'Y') THEN
230       /*SRW.MESSAGE('2'
231                  ,'C_Update Date: ' || C_UPDATE_DATE)*/NULL;
232     END IF;
233     RETURN (TRUE);
234     RETURN NULL;
235   EXCEPTION
236     WHEN OTHERS THEN
237       RETURN (FALSE);
238   END GET_LAST_RUN_DATE;
239 
240   FUNCTION CALCULATE_RUN_TIME RETURN BOOLEAN IS
241     END_DATE DATE;
242     START_DATE DATE;
243   BEGIN
244     END_DATE := SYSDATE;
245     START_DATE := C_REPORT_START_DATE;
246     C_REPORT_RUN_TIME := TO_CHAR(TO_DATE('01/01/0001'
247                                         ,'DD/MM/YYYY') + ((END_DATE - START_DATE))
248                                 ,'HH24:MI:SS');
249     RETURN (TRUE);
250     RETURN NULL;
251   EXCEPTION
252     WHEN OTHERS THEN
253       /*RAISE SRW.PROGRAM_ABORT*/RAISE_APPLICATION_ERROR(-20101,null);
254   END CALCULATE_RUN_TIME;
255 
256   FUNCTION C_DUPLICATE_YES_NOFORMULA(UPDATED_VALUE IN VARCHAR2
257                                     ,UPDATED_TO IN VARCHAR2) RETURN VARCHAR2 IS
258     L_DUPLICATES VARCHAR2(1);
259     L_COUNT NUMBER;
260   BEGIN
261     /*SRW.REFERENCE(UPDATED_VALUE)*/NULL;
262     IF UPDATED_VALUE = 'Name' THEN
263       BEGIN
264         SELECT
265           'Y'
266         INTO L_DUPLICATES
267         FROM
268           DUAL
269         WHERE EXISTS (
270           SELECT
271             'Duplicates exist in Po_Vendors'
272           FROM
273             AP_SUPPLIERS
274           WHERE VENDOR_NAME = UPDATED_TO );
275         RETURN ('Y');
276       EXCEPTION
277         WHEN NO_DATA_FOUND THEN
278           SELECT
279             COUNT(PERSON_ID)
280           INTO L_COUNT
281           FROM
282             PER_ALL_PEOPLE_F PPF
283           WHERE TRUNC(SYSDATE) between PPF.EFFECTIVE_START_DATE
284             AND PPF.EFFECTIVE_END_DATE
285             AND DECODE(PPF.MIDDLE_NAMES
286                 ,NULL
287                 ,PPF.LAST_NAME || ', ' || PPF.FIRST_NAME
288                 ,PPF.LAST_NAME || ', ' || PPF.FIRST_NAME || ' ' || PPF.MIDDLE_NAMES) = UPDATED_TO;
289           IF L_COUNT > 1 THEN
290             RETURN ('Y');
291           ELSE
292             RETURN ('N');
293           END IF;
294       END;
295     ELSE
296       RETURN ('N');
297     END IF;
298   END C_DUPLICATE_YES_NOFORMULA;
299 
300   FUNCTION SET_SUPPLIER_WHERE RETURN BOOLEAN IS
301   BEGIN
302     IF (P_FROM_SUPPLIER IS NOT NULL AND P_TO_SUPPLIER IS NOT NULL) THEN
303       C_SUPPLIER_PREDICATE := ' and pv1.vendor_name between ' || '''' || P_FROM_SUPPLIER || ''' and ''' || P_TO_SUPPLIER || '''';
304       C_NAME_UPDATE_DATE := ' ';
305       C_INACTIVE_UPDATE_DATE := ' ';
306       C_HOME_UPDATE_DATE := ' ';
307       C_OFFICE_UPDATE_DATE := ' ';
308     ELSE
309       C_SUPPLIER_PREDICATE := ' ';
310       C_NAME_UPDATE_DATE := ' and ppf.last_update_date  > ' || '''' || C_UPDATE_DATE || '''';
311       C_INACTIVE_UPDATE_DATE := ' and ppos.last_update_date > ' || '''' || C_UPDATE_DATE || '''';
312       C_HOME_UPDATE_DATE := ' and pa1.last_update_date > ' || '''' || C_UPDATE_DATE || '''';
313       C_OFFICE_UPDATE_DATE := ' and (trunc(paf.last_update_date) >= ' || '''' || C_UPDATE_DATE || '''' || ' OR ' || 'trunc(locs.last_update_date) >= ' || '''' || C_UPDATE_DATE || '''' || ')';
314     END IF;
315     /*SRW.MESSAGE('4'
316                ,'C_supplier_predicate: ' || C_SUPPLIER_PREDICATE)*/NULL;
317     /*SRW.MESSAGE('5'
318                ,'C_name_update_date: ' || C_NAME_UPDATE_DATE)*/NULL;
319     /*SRW.MESSAGE('6'
320                ,'C_inactive_update_date: ' || C_INACTIVE_UPDATE_DATE)*/NULL;
321     /*SRW.MESSAGE('7'
322                ,'C_home_update_date: ' || C_HOME_UPDATE_DATE)*/NULL;
323     /*SRW.MESSAGE('8'
324                ,'C_office_update_date: ' || C_OFFICE_UPDATE_DATE)*/NULL;
325     RETURN (TRUE);
326     RETURN NULL;
327   EXCEPTION
328     WHEN OTHERS THEN
329       RETURN (FALSE);
330   END SET_SUPPLIER_WHERE;
331 
332   FUNCTION C_NLS_YES_P RETURN VARCHAR2 IS
333   BEGIN
334     RETURN C_NLS_YES;
335   END C_NLS_YES_P;
336 
337   FUNCTION C_NLS_NO_P RETURN VARCHAR2 IS
338   BEGIN
339     RETURN C_NLS_NO;
340   END C_NLS_NO_P;
341 
342   FUNCTION C_NLS_ALL_P RETURN VARCHAR2 IS
343   BEGIN
344     RETURN C_NLS_ALL;
345   END C_NLS_ALL_P;
346 
347   FUNCTION C_NLS_NO_DATA_EXISTS_P RETURN VARCHAR2 IS
348   BEGIN
349     RETURN C_NLS_NO_DATA_EXISTS;
350   END C_NLS_NO_DATA_EXISTS_P;
351 
352   FUNCTION C_NLS_VOID_P RETURN VARCHAR2 IS
353   BEGIN
354     RETURN C_NLS_VOID;
355   END C_NLS_VOID_P;
356 
357   FUNCTION C_NLS_NA_P RETURN VARCHAR2 IS
358   BEGIN
359     RETURN C_NLS_NA;
360   END C_NLS_NA_P;
361 
362   FUNCTION C_NLS_END_OF_REPORT_P RETURN VARCHAR2 IS
363   BEGIN
364     RETURN C_NLS_END_OF_REPORT;
365   END C_NLS_END_OF_REPORT_P;
366 
367   FUNCTION C_REPORT_START_DATE_P RETURN DATE IS
368   BEGIN
369     RETURN C_REPORT_START_DATE;
370   END C_REPORT_START_DATE_P;
371 
372   FUNCTION C_COMPANY_NAME_HEADER_P RETURN VARCHAR2 IS
373   BEGIN
374     RETURN C_COMPANY_NAME_HEADER;
375   END C_COMPANY_NAME_HEADER_P;
376 
377   FUNCTION C_BASE_CURRENCY_CODE_P RETURN VARCHAR2 IS
378   BEGIN
379     RETURN C_BASE_CURRENCY_CODE;
380   END C_BASE_CURRENCY_CODE_P;
381 
382   FUNCTION C_BASE_PRECISION_P RETURN NUMBER IS
383   BEGIN
384     RETURN C_BASE_PRECISION;
385   END C_BASE_PRECISION_P;
386 
387   FUNCTION C_BASE_MIN_ACCT_UNIT_P RETURN NUMBER IS
388   BEGIN
389     RETURN C_BASE_MIN_ACCT_UNIT;
390   END C_BASE_MIN_ACCT_UNIT_P;
391 
392   FUNCTION C_BASE_DESCRIPTION_P RETURN VARCHAR2 IS
393   BEGIN
394     RETURN C_BASE_DESCRIPTION;
395   END C_BASE_DESCRIPTION_P;
396 
397   FUNCTION C_CHART_OF_ACCOUNTS_ID_P RETURN NUMBER IS
398   BEGIN
399     RETURN C_CHART_OF_ACCOUNTS_ID;
400   END C_CHART_OF_ACCOUNTS_ID_P;
401 
402   FUNCTION C_REPORT_RUN_TIME_P RETURN VARCHAR2 IS
403   BEGIN
404     RETURN C_REPORT_RUN_TIME;
405   END C_REPORT_RUN_TIME_P;
406 
407   FUNCTION C_SUPPLIER_PREDICATE_P RETURN VARCHAR2 IS
408   BEGIN
409     RETURN C_SUPPLIER_PREDICATE;
410   END C_SUPPLIER_PREDICATE_P;
411 
412   FUNCTION C_NAME_UPDATE_DATE_P RETURN VARCHAR2 IS
413   BEGIN
414     RETURN C_NAME_UPDATE_DATE;
415   END C_NAME_UPDATE_DATE_P;
416 
417   FUNCTION C_INACTIVE_UPDATE_DATE_P RETURN VARCHAR2 IS
418   BEGIN
419     RETURN C_INACTIVE_UPDATE_DATE;
420   END C_INACTIVE_UPDATE_DATE_P;
421 
422   FUNCTION C_HOME_UPDATE_DATE_P RETURN VARCHAR2 IS
423   BEGIN
424     RETURN C_HOME_UPDATE_DATE;
425   END C_HOME_UPDATE_DATE_P;
426 
427   FUNCTION C_OFFICE_UPDATE_DATE_P RETURN VARCHAR2 IS
428   BEGIN
429     RETURN C_OFFICE_UPDATE_DATE;
430   END C_OFFICE_UPDATE_DATE_P;
431 
432 END AP_APXHRUPD_XMLP_PKG;
433 
434