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.

APPS.PAY_US_PSD_UPGRADE_PKG dependencies on PAY_PATCH_STATUS

Line 31: pay_patch_status pps

27: WHERE process_type = 'PA'
28: AND NOT EXISTS
29: (SELECT NULL
30: FROM pay_us_geo_update pugu,
31: pay_patch_status pps
32: WHERE pugu.process_type = 'PA'
33: AND pugu.process_mode = 'UPGRADE'
34: AND pugu.process_date = pps.applied_date
35: AND pugu.old_juri_code = substr(pumg.city_name,1,6)

Line 127: FUNCTION get_pay_patch_status_entry(p_mode VARCHAR2)

123:
124: END upgrade_needed;
125:
126:
127: FUNCTION get_pay_patch_status_entry(p_mode VARCHAR2)
128: RETURN NUMBER
129: IS
130:
131: l_prev_upgrade_id pay_patch_status.id%TYPE;

Line 131: l_prev_upgrade_id pay_patch_status.id%TYPE;

127: FUNCTION get_pay_patch_status_entry(p_mode VARCHAR2)
128: RETURN NUMBER
129: IS
130:
131: l_prev_upgrade_id pay_patch_status.id%TYPE;
132: l_mode VARCHAR2(8);
133: l_id pay_patch_status.id%TYPE;
134: l_phase pay_patch_status.phase%TYPE;
135: l_date DATE;

Line 133: l_id pay_patch_status.id%TYPE;

129: IS
130:
131: l_prev_upgrade_id pay_patch_status.id%TYPE;
132: l_mode VARCHAR2(8);
133: l_id pay_patch_status.id%TYPE;
134: l_phase pay_patch_status.phase%TYPE;
135: l_date DATE;
136: l_generate_id pay_patch_status.id%TYPE;
137: l_generate_applied_date DATE;

Line 134: l_phase pay_patch_status.phase%TYPE;

130:
131: l_prev_upgrade_id pay_patch_status.id%TYPE;
132: l_mode VARCHAR2(8);
133: l_id pay_patch_status.id%TYPE;
134: l_phase pay_patch_status.phase%TYPE;
135: l_date DATE;
136: l_generate_id pay_patch_status.id%TYPE;
137: l_generate_applied_date DATE;
138: l_patch_name pay_patch_status.patch_name%TYPE;

Line 136: l_generate_id pay_patch_status.id%TYPE;

132: l_mode VARCHAR2(8);
133: l_id pay_patch_status.id%TYPE;
134: l_phase pay_patch_status.phase%TYPE;
135: l_date DATE;
136: l_generate_id pay_patch_status.id%TYPE;
137: l_generate_applied_date DATE;
138: l_patch_name pay_patch_status.patch_name%TYPE;
139: l_status pay_patch_status.status%TYPE;
140:

Line 138: l_patch_name pay_patch_status.patch_name%TYPE;

134: l_phase pay_patch_status.phase%TYPE;
135: l_date DATE;
136: l_generate_id pay_patch_status.id%TYPE;
137: l_generate_applied_date DATE;
138: l_patch_name pay_patch_status.patch_name%TYPE;
139: l_status pay_patch_status.status%TYPE;
140:
141: BEGIN
142:

Line 139: l_status pay_patch_status.status%TYPE;

135: l_date DATE;
136: l_generate_id pay_patch_status.id%TYPE;
137: l_generate_applied_date DATE;
138: l_patch_name pay_patch_status.patch_name%TYPE;
139: l_status pay_patch_status.status%TYPE;
140:
141: BEGIN
142:
143: hr_utility.trace('Entering pay_us_psd_upgrade_pkg.get_pay_patch_status_entry');

Line 143: hr_utility.trace('Entering pay_us_psd_upgrade_pkg.get_pay_patch_status_entry');

139: l_status pay_patch_status.status%TYPE;
140:
141: BEGIN
142:
143: hr_utility.trace('Entering pay_us_psd_upgrade_pkg.get_pay_patch_status_entry');
144:
145: l_mode := p_mode;
146:
147: /*Get the PAY_PATCH_STATUS ID of the Previous Successful execution of

Line 147: /*Get the PAY_PATCH_STATUS ID of the Previous Successful execution of

143: hr_utility.trace('Entering pay_us_psd_upgrade_pkg.get_pay_patch_status_entry');
144:
145: l_mode := p_mode;
146:
147: /*Get the PAY_PATCH_STATUS ID of the Previous Successful execution of
148: UPGRADE mode of PSDUPGRADE*/
149:
150: SELECT nvl(MAX(id),0)
151: INTO l_prev_upgrade_id

Line 152: FROM pay_patch_status

148: UPGRADE mode of PSDUPGRADE*/
149:
150: SELECT nvl(MAX(id),0)
151: INTO l_prev_upgrade_id
152: FROM pay_patch_status
153: WHERE patch_name LIKE 'PSDUPGRADE%'
154: AND description = 'UPGRADE'
155: AND status = 'C';
156:

Line 172: FROM pay_patch_status

168: hr_utility.trace('Check if there are any incomplete Upgrade Runs');
169:
170: SELECT id,phase,applied_date
171: INTO l_id,l_phase,l_date
172: FROM pay_patch_status
173: WHERE patch_name LIKE '%PSDUPGRADE%'
174: AND description = 'UPGRADE'
175: AND id > l_prev_upgrade_id
176: AND nvl(status,'P') <> 'C';

Line 178: UPDATE pay_patch_status

174: AND description = 'UPGRADE'
175: AND id > l_prev_upgrade_id
176: AND nvl(status,'P') <> 'C';
177:
178: UPDATE pay_patch_status
179: SET status = 'P'
180: WHERE id = l_id;
181:
182: UPDATE pay_us_geo_update

Line 204: hr_utility.trace('Get the Generate Run pay_patch_status table ID');

200: WHEN NO_DATA_FOUND THEN
201:
202: BEGIN
203:
204: hr_utility.trace('Get the Generate Run pay_patch_status table ID');
205:
206: SELECT MAX(pps.id)
207: INTO l_generate_id
208: FROM pay_patch_status pps

Line 208: FROM pay_patch_status pps

204: hr_utility.trace('Get the Generate Run pay_patch_status table ID');
205:
206: SELECT MAX(pps.id)
207: INTO l_generate_id
208: FROM pay_patch_status pps
209: WHERE pps.patch_name LIKE 'PSDUPGRADE%'
210: AND pps.description = 'GENERATE'
211: AND pps.id > l_prev_upgrade_id
212: AND status = 'C';

Line 216: FROM pay_patch_status pps

212: AND status = 'C';
213:
214: SELECT applied_date
215: INTO l_generate_applied_date
216: FROM pay_patch_status pps
217: WHERE id = l_generate_id;
218:
219: EXCEPTION
220:

Line 224: hr_utility.trace('Not able to find the PAY_PATCH_STATUS entry corresponding to GENERATE Mode execution.');

220:
221: WHEN NO_DATA_FOUND THEN
222:
223: hr_utility.trace('UPGRADE Mode can be executed only after running the process in GENERATE Mode successfully.');
224: hr_utility.trace('Not able to find the PAY_PATCH_STATUS entry corresponding to GENERATE Mode execution.');
225: fnd_file.put_line(fnd_file.log,'UPGRADE Mode can be executed only after running the process in GENERATE Mode successfully.');
226: fnd_file.put_line(fnd_file.log,'Not able to find the PAY_PATCH_STATUS entry corresponding to GENERATE Mode execution.');
227: hr_utility.raise_error;
228:

Line 226: fnd_file.put_line(fnd_file.log,'Not able to find the PAY_PATCH_STATUS entry corresponding to GENERATE Mode execution.');

222:
223: hr_utility.trace('UPGRADE Mode can be executed only after running the process in GENERATE Mode successfully.');
224: hr_utility.trace('Not able to find the PAY_PATCH_STATUS entry corresponding to GENERATE Mode execution.');
225: fnd_file.put_line(fnd_file.log,'UPGRADE Mode can be executed only after running the process in GENERATE Mode successfully.');
226: fnd_file.put_line(fnd_file.log,'Not able to find the PAY_PATCH_STATUS entry corresponding to GENERATE Mode execution.');
227: hr_utility.raise_error;
228:
229: END;
230:

Line 231: SELECT pay_patch_status_s.NEXTVAL,

227: hr_utility.raise_error;
228:
229: END;
230:
231: SELECT pay_patch_status_s.NEXTVAL,
232: 'PSDUPGRADE_'||to_char(sysdate,'DDMONYYYYHHMISS'),
233: TRUNC(sysdate),
234: 'P'
235: INTO l_id,l_patch_name,l_date,l_status

Line 238: INSERT INTO pay_patch_status

234: 'P'
235: INTO l_id,l_patch_name,l_date,l_status
236: FROM DUAL;
237:
238: INSERT INTO pay_patch_status
239: (id,patch_number,patch_name,phase,status,applied_date,legislation_code,description)
240: VALUES
241: (l_id,'1111111',l_patch_name,'UPGRADE',l_status,l_date,'US',l_mode);
242:

Line 243: hr_utility.trace('Created UPGRADE pay_patch_status entry with ID : '||l_id);

239: (id,patch_number,patch_name,phase,status,applied_date,legislation_code,description)
240: VALUES
241: (l_id,'1111111',l_patch_name,'UPGRADE',l_status,l_date,'US',l_mode);
242:
243: hr_utility.trace('Created UPGRADE pay_patch_status entry with ID : '||l_id);
244:
245: UPDATE /*+PARALLEL*/ pay_us_geo_update
246: SET id = l_id,
247: process_date = l_date,

Line 284: FROM pay_patch_status

280: hr_utility.trace('Checking if there are any incomplete UPGRADE Runs from earlier');
281:
282: SELECT id,phase,applied_date
283: INTO l_id,l_phase,l_date
284: FROM pay_patch_status
285: WHERE patch_name LIKE '%PSDUPGRADE%'
286: AND description = 'UPGRADE'
287: AND id > l_prev_upgrade_id
288: AND nvl(status,'P') <> 'C';

Line 311: FROM pay_patch_status

307: WHERE process_type = 'PA'
308: AND process_mode = 'GENERATE'
309: AND id IN
310: (SELECT id
311: FROM pay_patch_status
312: WHERE patch_name LIKE 'PSDUPGRADE%'
313: AND legislation_code = 'US'
314: AND description = 'GENERATE'
315: AND id > l_prev_upgrade_id);

Line 317: /*Update the Status of Previous GENERATE pay_patch_status entry to D indicating

313: AND legislation_code = 'US'
314: AND description = 'GENERATE'
315: AND id > l_prev_upgrade_id);
316:
317: /*Update the Status of Previous GENERATE pay_patch_status entry to D indicating
318: deletion of the Generated Data.*/
319:
320: UPDATE pay_patch_status
321: SET status = 'D',

Line 320: UPDATE pay_patch_status

316:
317: /*Update the Status of Previous GENERATE pay_patch_status entry to D indicating
318: deletion of the Generated Data.*/
319:
320: UPDATE pay_patch_status
321: SET status = 'D',
322: phase = NULL
323: WHERE patch_name LIKE 'PSDUPGRADE%'
324: AND legislation_code = 'US'

Line 328: SELECT pay_patch_status_s.NEXTVAL,

324: AND legislation_code = 'US'
325: AND id > l_prev_upgrade_id
326: AND description = 'GENERATE';
327:
328: SELECT pay_patch_status_s.NEXTVAL,
329: 'PSDUPGRADE_'||to_char(sysdate,'DDMONYYYYHHMISS'),
330: TRUNC(sysdate),
331: 'P'
332: INTO l_id,l_patch_name,l_date,l_status

Line 335: INSERT INTO pay_patch_status

331: 'P'
332: INTO l_id,l_patch_name,l_date,l_status
333: FROM DUAL;
334:
335: INSERT INTO pay_patch_status
336: (id,patch_number,patch_name,phase,status,applied_date,legislation_code,description)
337: VALUES
338: (l_id,'1111111',l_patch_name,'VALIDATE',l_status,l_date,'US',l_mode);
339:

Line 340: hr_utility.trace('Created GENERATE pay_patch_status entry with ID : '||l_id);

336: (id,patch_number,patch_name,phase,status,applied_date,legislation_code,description)
337: VALUES
338: (l_id,'1111111',l_patch_name,'VALIDATE',l_status,l_date,'US',l_mode);
339:
340: hr_utility.trace('Created GENERATE pay_patch_status entry with ID : '||l_id);
341:
342: /*End of GENARATE Mode*/
343:
344: END IF; /*l_mode IF */

Line 346: hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.get_pay_patch_status_entry');

342: /*End of GENARATE Mode*/
343:
344: END IF; /*l_mode IF */
345:
346: hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.get_pay_patch_status_entry');
347:
348: RETURN l_id;
349:
350: END get_pay_patch_status_entry;

Line 350: END get_pay_patch_status_entry;

346: hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.get_pay_patch_status_entry');
347:
348: RETURN l_id;
349:
350: END get_pay_patch_status_entry;
351:
352: PROCEDURE create_jsd_element_set (p_pay_patch_status_id pay_patch_status.id%TYPE)
353: IS
354: --

Line 352: PROCEDURE create_jsd_element_set (p_pay_patch_status_id pay_patch_status.id%TYPE)

348: RETURN l_id;
349:
350: END get_pay_patch_status_entry;
351:
352: PROCEDURE create_jsd_element_set (p_pay_patch_status_id pay_patch_status.id%TYPE)
353: IS
354: --
355: --
356: l_element_set_id pay_element_sets.element_set_id%TYPE;

Line 376: 'US_JSD_ELEMENTS_'||p_pay_patch_status_id,

372: element_set_type)
373: VALUES
374: (l_element_set_id,
375: 'US',
376: 'US_JSD_ELEMENTS_'||p_pay_patch_status_id,
377: 'D');
378:
379: INSERT INTO pay_element_type_rules
380: (element_type_id,

Line 397: hr_utility.trace('Created Element Set with Name : US_JSD_ELEMENTS_'||p_pay_patch_status_id||', Element Set ID :'||l_element_set_id);

393: >= TO_DATE('01-01-2012','DD-MM-YYYY')
394: AND piv.element_type_id = pet.element_type_id
395: AND piv.name = 'Jurisdiction';
396:
397: hr_utility.trace('Created Element Set with Name : US_JSD_ELEMENTS_'||p_pay_patch_status_id||', Element Set ID :'||l_element_set_id);
398:
399: hr_utility.trace('Leaving pay_us_psd_upgrade_pkg.create_jsd_element_set');
400:
401: END create_jsd_element_set;

Line 407: l_pay_patch_status_id pay_patch_status.id%TYPE;

403: PROCEDURE perform_initial_setup(p_payroll_action_id NUMBER) IS
404:
405: l_upgrade_needed VARCHAR2(2);
406: l_mode VARCHAR2(8);
407: l_pay_patch_status_id pay_patch_status.id%TYPE;
408:
409: BEGIN
410:
411: hr_utility.trace('Entering pay_us_psd_upgrade_pkg.perform_initial_setup');

Line 431: l_pay_patch_status_id := get_pay_patch_status_entry(l_mode);

427: WHERE ppa.payroll_action_id = p_payroll_action_id;
428:
429: hr_utility.trace('Mode of Execution ' ||l_mode);
430:
431: l_pay_patch_status_id := get_pay_patch_status_entry(l_mode);
432:
433: hr_utility.trace('PAY_PATCH_STATUS Entry for processing : ' ||l_pay_patch_status_id);
434:
435: IF l_mode = 'GENERATE' THEN

Line 433: hr_utility.trace('PAY_PATCH_STATUS Entry for processing : ' ||l_pay_patch_status_id);

429: hr_utility.trace('Mode of Execution ' ||l_mode);
430:
431: l_pay_patch_status_id := get_pay_patch_status_entry(l_mode);
432:
433: hr_utility.trace('PAY_PATCH_STATUS Entry for processing : ' ||l_pay_patch_status_id);
434:
435: IF l_mode = 'GENERATE' THEN
436:
437: create_jsd_element_set(l_pay_patch_status_id);

Line 437: create_jsd_element_set(l_pay_patch_status_id);

433: hr_utility.trace('PAY_PATCH_STATUS Entry for processing : ' ||l_pay_patch_status_id);
434:
435: IF l_mode = 'GENERATE' THEN
436:
437: create_jsd_element_set(l_pay_patch_status_id);
438:
439: END IF;
440:
441: ELSE

Line 455: p_id pay_patch_status.id%TYPE,

451:
452: END perform_initial_setup;
453:
454: PROCEDURE generate_assignment(p_assignment_id per_all_assignments_f.assignment_id%TYPE,
455: p_id pay_patch_status.id%TYPE,
456: p_date DATE,
457: p_mode VARCHAR2,
458: p_element_set_id pay_element_sets.element_set_id%TYPE) IS
459:

Line 1004: p_id pay_patch_status.id%TYPE,

1000:
1001: END;
1002:
1003: PROCEDURE upgrade_assignment(p_assignment_id per_all_assignments_f.assignment_id%TYPE,
1004: p_id pay_patch_status.id%TYPE,
1005: p_date DATE,
1006: p_mode VARCHAR2) IS
1007: PRAGMA AUTONOMOUS_TRANSACTION;
1008:

Line 1177: l_id pay_patch_status.id%TYPE;

1173: --
1174:
1175: l_mode VARCHAR2(8);
1176: l_upgrade_needed VARCHAR2(2);
1177: l_id pay_patch_status.id%TYPE;
1178: l_date DATE;
1179:
1180: BEGIN
1181:

Line 1231: FROM pay_patch_status

1227: table. So we will use the pay_us_geo_update table as source for Range Cursor*/
1228:
1229: SELECT id,applied_date
1230: INTO l_id,l_date
1231: FROM pay_patch_status
1232: WHERE patch_name LIKE 'PSDUPGRADE%'
1233: AND legislation_code = 'US'
1234: AND status = 'P'
1235: AND description = l_mode;

Line 1321: l_id pay_patch_status.id%TYPE;

1317: l_mode VARCHAR2(8);
1318: l_assignment_id NUMBER;
1319: l_payact_id NUMBER;
1320: l_locking_action_id NUMBER;
1321: l_id pay_patch_status.id%TYPE;
1322: l_date DATE;
1323:
1324: BEGIN
1325:

Line 1370: FROM pay_patch_status

1366: ELSE
1367:
1368: SELECT id,applied_date
1369: INTO l_id,l_date
1370: FROM pay_patch_status
1371: WHERE patch_name LIKE 'PSDUPGRADE%'
1372: AND legislation_code = 'US'
1373: AND status = 'P'
1374: AND description = l_mode;

Line 1434: l_id pay_patch_status.id%TYPE;

1430: l_object_id pay_assignment_actions.object_id%TYPE;
1431: l_object_type pay_assignment_actions.object_type%TYPE;
1432: l_mode VARCHAR2(8);
1433: l_element_set_id pay_element_sets.element_set_id%TYPE;
1434: l_id pay_patch_status.id%TYPE;
1435: l_date DATE;
1436: l_upgrade_needed VARCHAR2(2);
1437:
1438: BEGIN

Line 1460: FROM pay_patch_status

1456: hr_utility.trace(' Mode : '||l_mode);
1457:
1458: SELECT id,applied_date
1459: INTO l_id,l_date
1460: FROM pay_patch_status
1461: WHERE patch_name LIKE 'PSDUPGRADE%'
1462: AND legislation_code = 'US'
1463: AND status = 'P'
1464: AND description = l_mode;

Line 1466: hr_utility.trace(' PAY_PATCH_STATUS Id : '||l_id);

1462: AND legislation_code = 'US'
1463: AND status = 'P'
1464: AND description = l_mode;
1465:
1466: hr_utility.trace(' PAY_PATCH_STATUS Id : '||l_id);
1467:
1468: /*l_object_id will be assignment_id */
1469:
1470: IF l_mode = 'GENERATE' THEN

Line 1568: l_id pay_patch_status.id%TYPE;

1564: AND pdb.balance_dimension_id = pbd.balance_dimension_id
1565: ORDER BY pbt.balance_name,pbd.dimension_name;
1566:
1567: l_upgrade_needed VARCHAR2(2);
1568: l_id pay_patch_status.id%TYPE;
1569: l_date DATE;
1570: l_business_group_id pay_balance_validation.business_group_id%TYPE;
1571: l_print_business_group_id pay_balance_validation.business_group_id%TYPE;
1572: l_business_group_name per_business_groups.name%TYPE;

Line 1586: FROM pay_patch_status

1582: l_print_business_group_id := -1;
1583:
1584: SELECT id,applied_date
1585: INTO l_id,l_date
1586: FROM pay_patch_status
1587: WHERE patch_name LIKE 'PSDUPGRADE%'
1588: AND legislation_code = 'US'
1589: AND status = 'P'
1590: AND description = p_mode;

Line 1735: l_id pay_patch_status.id%TYPE;

1731: AND nvl(pugu.status,'P') <> 'C';
1732:
1733: l_upgrade_needed VARCHAR2(2);
1734: l_mode VARCHAR2(8);
1735: l_id pay_patch_status.id%TYPE;
1736: l_date DATE;
1737: l_total_assignments NUMBER;
1738: l_success_assignments NUMBER;
1739: l_error_assignments NUMBER;

Line 1763: FROM pay_patch_status

1759: IF l_upgrade_needed = 'Y' THEN
1760:
1761: SELECT id,applied_date
1762: INTO l_id,l_date
1763: FROM pay_patch_status
1764: WHERE patch_name LIKE 'PSDUPGRADE%'
1765: AND legislation_code = 'US'
1766: AND status = 'P'
1767: AND description = l_mode;

Line 1876: UPDATE pay_patch_status

1872: DELETE FROM pay_element_sets
1873: WHERE legislation_code = 'US'
1874: AND element_set_name = 'US_JSD_ELEMENTS_'||l_id;
1875:
1876: UPDATE pay_patch_status
1877: SET phase = NULL,
1878: status = 'C'
1879: WHERE legislation_code = 'US'
1880: AND patch_name like 'PSDUPGRADE%'

Line 2000: UPDATE pay_patch_status

1996: pumg.county_code,
1997: pumg.old_city_code,
1998: pumg.city_name)='Y';
1999:
2000: UPDATE pay_patch_status
2001: SET phase = NULL,
2002: status = 'C'
2003: WHERE legislation_code = 'US'
2004: AND patch_name like 'PSDUPGRADE%'

Line 2010: UPDATE pay_patch_status

2006: AND description = l_mode;
2007:
2008: ELSE
2009:
2010: UPDATE pay_patch_status
2011: SET phase = NULL,
2012: status = 'E'
2013: WHERE legislation_code = 'US'
2014: AND patch_name like 'PSDUPGRADE%'

Line 2174: l_id pay_patch_status.id%TYPE;

2170: AND pugu.table_name ='FF_ARCHIVE_ITEMS'
2171: AND NVL(pugu.status,'NULL') = DECODE(p_status,'ALL',NVL(pugu.status,'NULL'),p_status));
2172:
2173: l_upgrade_needed VARCHAR2(2);
2174: l_id pay_patch_status.id%TYPE;
2175: l_date DATE;
2176: l_old_psd_code VARCHAR2(6);
2177: l_new_psd_code VARCHAR2(6);
2178: l_school_dsts_code VARCHAR2(5);

Line 2228: FROM pay_patch_status

2224: IF l_upgrade_needed = 'Y' THEN
2225:
2226: SELECT id,applied_date
2227: INTO l_id,l_date
2228: FROM pay_patch_status
2229: WHERE patch_name LIKE 'PSDUPGRADE%'
2230: AND legislation_code = 'US'
2231: AND status = 'P'
2232: AND description = p_mode;