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_GB_FPS_NI_AND_OTHERS dependencies on PAY_GB_BACS_FPS

Line 35: generation of hash code and insertion into PAY_GB_BACS_FPS table.

31: 29-Aug-2012 ssarap 115.6 Used the correct NI Reporting flag.
32: 25-Sep-2012 ssarap 115.7 Corrected the NI Able values to Period Level.
33: 28-Sep-2012 ssarap 115.8 Moved the BACS changes related to random digit generation from procedure
34: fetch_random_digit_bacs to FPS_BACS_PREPROCESS. This would avoid
35: generation of hash code and insertion into PAY_GB_BACS_FPS table.
36: 28-Sep-2012 ssarap 115.9 Implemented the code review comments from Prem.
37: 05-Oct-2012 ssarap 115.10 In FPS_BACS_PREPROCESS procedure used used the config table directly
38: 29-Oct-2012 pbalu 115.13 14797457 When BACS payment exists in a prepayment, BACS should not error
39: 30-Oct-2012 pbalu 115.14 14807372 Master insert should be committed before data insert starts

Line 2910: ) PAA, PAY_GB_BACS_FPS BACS_FPS

2906: WHERE payroll_action_id = L_PAYROLL_ACTION_ID
2907: )
2908: )
2909: )
2910: ) PAA, PAY_GB_BACS_FPS BACS_FPS
2911: WHERE PAA.PRE_PAYMENT_PAYROLL_ACTION_ID is not null
2912: and PAA.PRE_PAYMENT_PAYROLL_ACTION_ID = BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID
2913: and BACS_FPS.PRE_PAYMENT_ID is null
2914: AND nvl(BACS_FPS.RANDOM_STRING,'no data') <> 'COMP';

Line 2938: FROM PAY_GB_BACS_FPS bacs,

2934: */
2935:
2936: SELECT RANDOM_STRING
2937: INTO L_RANDOM
2938: FROM PAY_GB_BACS_FPS bacs,
2939: pay_assignment_actions paa
2940: WHERE paa.payroll_action_id = L_PAYROLL_ACTION_ID -- CONTEXT (BACS payroll action id)
2941: and paa.ASSIGNMENT_action_id = L_ASSIGNMENT_ACTION_ID -- CONTEXT (BACS ASSIGNMENT action id)
2942: AND paa.pre_payment_id = bacs.pre_payment_id

Line 2962: l_lockname VARCHAR2(25) := 'Lock_PAY_GB_BACS_FPS';

2958: IS
2959:
2960: L_DUMMY NUMBER := 0;
2961: --global variables used for locking;
2962: l_lockname VARCHAR2(25) := 'Lock_PAY_GB_BACS_FPS';
2963: l_lockhandle VARCHAR2(200);
2964: l_result PLS_INTEGER;
2965: p_ltype NUMBER :=6; -- Exclusive lock
2966: p_retval NUMBER;

Line 2969: L_PAY_GB_BACS_FPS PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%type;

2965: p_ltype NUMBER :=6; -- Exclusive lock
2966: p_retval NUMBER;
2967: l_count_lock_failures NUMBER :=0;
2968: l_message VARCHAR2(150);
2969: L_PAY_GB_BACS_FPS PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%type;
2970: BEGIN
2971: hr_utility.trace(' Inserting Master Record in PAY_GB_BACS_FPS ');
2972: fnd_file.put_line(FND_FILE.LOG,' Master Insert start at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
2973: --15903040 Begin

Line 2971: hr_utility.trace(' Inserting Master Record in PAY_GB_BACS_FPS ');

2967: l_count_lock_failures NUMBER :=0;
2968: l_message VARCHAR2(150);
2969: L_PAY_GB_BACS_FPS PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%type;
2970: BEGIN
2971: hr_utility.trace(' Inserting Master Record in PAY_GB_BACS_FPS ');
2972: fnd_file.put_line(FND_FILE.LOG,' Master Insert start at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
2973: --15903040 Begin
2974: IF l_lockhandle IS NULL THEN
2975: LOOP

Line 3023: INTO L_PAY_GB_BACS_FPS

3019: END IF;
3020: --The below section (till release lock) will run in single threaded mode eventhough called from multiple threads.
3021: BEGIN
3022: SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3023: INTO L_PAY_GB_BACS_FPS
3024: FROM PAY_GB_BACS_FPS
3025: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID;
3026: hr_utility.trace(' Second check - Data present in PAY_GB_BACS_FPS - No insert happened');
3027: fnd_file.put_line(FND_FILE.LOG,' Second check - Data present in PAY_GB_BACS_FPS - No insert happened at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));

Line 3024: FROM PAY_GB_BACS_FPS

3020: --The below section (till release lock) will run in single threaded mode eventhough called from multiple threads.
3021: BEGIN
3022: SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3023: INTO L_PAY_GB_BACS_FPS
3024: FROM PAY_GB_BACS_FPS
3025: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID;
3026: hr_utility.trace(' Second check - Data present in PAY_GB_BACS_FPS - No insert happened');
3027: fnd_file.put_line(FND_FILE.LOG,' Second check - Data present in PAY_GB_BACS_FPS - No insert happened at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3028: EXCEPTION

Line 3026: hr_utility.trace(' Second check - Data present in PAY_GB_BACS_FPS - No insert happened');

3022: SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3023: INTO L_PAY_GB_BACS_FPS
3024: FROM PAY_GB_BACS_FPS
3025: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID;
3026: hr_utility.trace(' Second check - Data present in PAY_GB_BACS_FPS - No insert happened');
3027: fnd_file.put_line(FND_FILE.LOG,' Second check - Data present in PAY_GB_BACS_FPS - No insert happened at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3028: EXCEPTION
3029: WHEN no_data_found THEN
3030: INSERT

Line 3027: fnd_file.put_line(FND_FILE.LOG,' Second check - Data present in PAY_GB_BACS_FPS - No insert happened at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));

3023: INTO L_PAY_GB_BACS_FPS
3024: FROM PAY_GB_BACS_FPS
3025: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID;
3026: hr_utility.trace(' Second check - Data present in PAY_GB_BACS_FPS - No insert happened');
3027: fnd_file.put_line(FND_FILE.LOG,' Second check - Data present in PAY_GB_BACS_FPS - No insert happened at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3028: EXCEPTION
3029: WHEN no_data_found THEN
3030: INSERT
3031: INTO PAY_GB_BACS_FPS

Line 3031: INTO PAY_GB_BACS_FPS

3027: fnd_file.put_line(FND_FILE.LOG,' Second check - Data present in PAY_GB_BACS_FPS - No insert happened at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3028: EXCEPTION
3029: WHEN no_data_found THEN
3030: INSERT
3031: INTO PAY_GB_BACS_FPS
3032: (
3033: PRE_PAYMENT_PAYROLL_ACTION_ID,
3034: CREATION_DATE,
3035: TRIGGERED_BY

Line 3082: L_DUMMY PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%TYPE;

3078: IS
3079: L_HASH VARCHAR2(64);
3080: L_AMOUNT NUMBER;
3081:
3082: L_DUMMY PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%TYPE;
3083: L_COUNT_CHECK NUMBER := 0;
3084: BEGIN
3085: hr_utility.trace(' Entered Fetch hash for FPS - ASSIGNMENT');
3086: --15903040 Begin

Line 3093: FROM PAY_GB_BACS_FPS

3089: --This is needed because the calling program(FPS) runs in multithread where the
3090: --call to fetch the Hash string may be executed before the insert completed.
3091: Begin
3092: SELECT PRE_PAYMENT_PAYROLL_ACTION_ID INTO L_DUMMY
3093: FROM PAY_GB_BACS_FPS
3094: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID
3095: AND PRE_PAYMENT_ID is null
3096: AND RANDOM_STRING = 'COMP';
3097: exit;

Line 3117: FROM PAY_GB_BACS_FPS

3113: SELECT HASH,
3114: AMOUNT
3115: INTO L_HASH,
3116: L_AMOUNT
3117: FROM PAY_GB_BACS_FPS
3118: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
3119: AND ASSIGNMENT_ID = p_ASSIGNMENT_ID ;
3120: hr_utility.trace(' Fetched hash for FPS '||L_HASH);
3121: hr_utility.trace(' Fetched amount for FPS '||L_AMOUNT);

Line 3138: L_DUMMY PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%TYPE;

3134: L_HASH VARCHAR2(64);
3135:
3136: L_AMOUNT NUMBER;
3137: L_ASG_ID NUMBER;
3138: L_DUMMY PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%TYPE;
3139: L_COUNT_CHECK NUMBER := 0;
3140: BEGIN
3141: hr_utility.trace(' Entered Fetch hash for FPS - PERSON');
3142: --15903040 Begin

Line 3149: FROM PAY_GB_BACS_FPS

3145: --This is needed because the calling program(FPS) runs in multithread where the
3146: --call to fetch the Hash string may be executed before the insert completed.
3147: Begin
3148: SELECT PRE_PAYMENT_PAYROLL_ACTION_ID INTO L_DUMMY
3149: FROM PAY_GB_BACS_FPS
3150: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID
3151: AND PRE_PAYMENT_ID is null
3152: AND RANDOM_STRING = 'COMP';
3153: exit;

Line 3171: FROM PAY_GB_BACS_FPS

3167: END LOOP;
3168: --15903040 End
3169: SELECT HASH,AMOUNT,ASSIGNMENT_ID
3170: INTO L_HASH,L_AMOUNT,L_ASG_ID
3171: FROM PAY_GB_BACS_FPS
3172: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
3173: AND PERSON_ID = p_PERSON_ID ;
3174: hr_utility.trace(' Fetched hash for FPS '||L_HASH);
3175: hr_utility.trace(' Fetched amount for FPS '||L_AMOUNT);

Line 3190: L_PAY_GB_BACS_FPS PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%type;

3186: RETURN NUMBER
3187: IS
3188: PRAGMA AUTONOMOUS_TRANSACTION;
3189: --This program will be called from FPS and BACS
3190: L_PAY_GB_BACS_FPS PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%type;
3191: L_PAYROLL_ACTION_ID NUMBER;
3192: B_PAYROLL_ACTION_ID NUMBER;
3193: l_return NUMBER := 0;
3194: l_insert_bacs NUMBER;

Line 3322: INTO L_PAY_GB_BACS_FPS

3318: IF l_insert_bacs IS NOT NULL OR l_uptake_value = 'ALL' THEN
3319: BEGIN
3320: fnd_file.put_line(FND_FILE.LOG,' Check for PAYROLL_ACTION_ID '||L_PAYROLL_ACTION_ID);
3321: SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3322: INTO L_PAY_GB_BACS_FPS
3323: FROM PAY_GB_BACS_FPS
3324: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
3325: hr_utility.trace(' Data present in PAY_GB_BACS_FPS - No insert happened');
3326: fnd_file.put_line(FND_FILE.LOG,' Data present in PAY_GB_BACS_FPS - No insert happened');

Line 3323: FROM PAY_GB_BACS_FPS

3319: BEGIN
3320: fnd_file.put_line(FND_FILE.LOG,' Check for PAYROLL_ACTION_ID '||L_PAYROLL_ACTION_ID);
3321: SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3322: INTO L_PAY_GB_BACS_FPS
3323: FROM PAY_GB_BACS_FPS
3324: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
3325: hr_utility.trace(' Data present in PAY_GB_BACS_FPS - No insert happened');
3326: fnd_file.put_line(FND_FILE.LOG,' Data present in PAY_GB_BACS_FPS - No insert happened');
3327: RETURN l_return;

Line 3325: hr_utility.trace(' Data present in PAY_GB_BACS_FPS - No insert happened');

3321: SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3322: INTO L_PAY_GB_BACS_FPS
3323: FROM PAY_GB_BACS_FPS
3324: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
3325: hr_utility.trace(' Data present in PAY_GB_BACS_FPS - No insert happened');
3326: fnd_file.put_line(FND_FILE.LOG,' Data present in PAY_GB_BACS_FPS - No insert happened');
3327: RETURN l_return;
3328: EXCEPTION
3329: WHEN no_data_found THEN

Line 3326: fnd_file.put_line(FND_FILE.LOG,' Data present in PAY_GB_BACS_FPS - No insert happened');

3322: INTO L_PAY_GB_BACS_FPS
3323: FROM PAY_GB_BACS_FPS
3324: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
3325: hr_utility.trace(' Data present in PAY_GB_BACS_FPS - No insert happened');
3326: fnd_file.put_line(FND_FILE.LOG,' Data present in PAY_GB_BACS_FPS - No insert happened');
3327: RETURN l_return;
3328: EXCEPTION
3329: WHEN no_data_found THEN
3330: l_insert_status := 0;

Line 3332: fnd_file.put_line(FND_FILE.LOG,' Data not present in PAY_GB_BACS_FPS- Calling Master insert');

3328: EXCEPTION
3329: WHEN no_data_found THEN
3330: l_insert_status := 0;
3331: --Insert the master row.
3332: fnd_file.put_line(FND_FILE.LOG,' Data not present in PAY_GB_BACS_FPS- Calling Master insert');
3333: l_return := Insert_master(L_PAYROLL_ACTION_ID,P_PROGRAM,l_insert_status);
3334: fnd_file.put_line(FND_FILE.LOG,' Master insert completed - Moving to other inserts');
3335: --Insert the computed rows with random digits and hash value
3336: BEGIN --14807372

Line 3342: INTO PAY_GB_BACS_FPS

3338: --l_insert_status = 5 means Insert_master inserted the master row in this thread/session
3339: --so below insert should be executed.
3340: fnd_file.put_line(FND_FILE.LOG,' Main insert started at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3341: INSERT
3342: INTO PAY_GB_BACS_FPS
3343: (
3344: PRE_PAYMENT_PAYROLL_ACTION_ID,
3345: PRE_PAYMENT_ID,
3346: PERSON_ID,

Line 3410: FROM PAY_GB_BACS_FPS

3406: ) BACS
3407: WHERE BACS.highest = 1
3408: AND 1 = -- This check is to prevent inserts from multiple programs at the same time
3409: (SELECT COUNT(*)
3410: FROM PAY_GB_BACS_FPS
3411: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID
3412: );
3413: hr_utility.trace(' All insert completed for '||L_PAYROLL_ACTION_ID);
3414: fnd_file.put_line(FND_FILE.LOG,' Main insert completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));

Line 3418: update PAY_GB_BACS_FPS

3414: fnd_file.put_line(FND_FILE.LOG,' Main insert completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3415: --15903040 Begin
3416: --This update statement will be fired once.
3417: --This will indicate all inserts for the payroll action id is completed.
3418: update PAY_GB_BACS_FPS
3419: set RANDOM_STRING = 'COMP'
3420: where PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID
3421: and RANDOM_STRING is null
3422: and PRE_PAYMENT_ID is null;

Line 3428: FROM PAY_GB_BACS_FPS

3424: END IF;-- check for l_insert_status = 5
3425: fnd_file.put_line(FND_FILE.LOG,' Sanity check start at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3426:
3427: DELETE
3428: FROM PAY_GB_BACS_FPS
3429: WHERE rowid NOT IN
3430: (SELECT MIN(rowid)
3431: FROM PAY_GB_BACS_FPS
3432: WHERE NVL(AGGREGATION_FLAG,'N')= 'N'

Line 3431: FROM PAY_GB_BACS_FPS

3427: DELETE
3428: FROM PAY_GB_BACS_FPS
3429: WHERE rowid NOT IN
3430: (SELECT MIN(rowid)
3431: FROM PAY_GB_BACS_FPS
3432: WHERE NVL(AGGREGATION_FLAG,'N')= 'N'
3433: GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
3434: PERSON_ID,
3435: ASSIGNMENT_ID,

Line 3444: FROM PAY_GB_BACS_FPS

3440: (SELECT PRE_PAYMENT_PAYROLL_ACTION_ID,
3441: PERSON_ID,
3442: ASSIGNMENT_ID,
3443: AMOUNT
3444: FROM PAY_GB_BACS_FPS
3445: WHERE NVL(AGGREGATION_FLAG,'N')= 'N'
3446: GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
3447: PERSON_ID,
3448: ASSIGNMENT_ID,

Line 3454: FROM PAY_GB_BACS_FPS

3450: HAVING COUNT(*) > 1
3451: );
3452: hr_utility.trace(' Deleted Non aggregated duplicate rows for '||L_PAYROLL_ACTION_ID);
3453: DELETE
3454: FROM PAY_GB_BACS_FPS
3455: WHERE rowid NOT IN
3456: (SELECT MIN(rowid)
3457: FROM PAY_GB_BACS_FPS
3458: WHERE NVL(AGGREGATION_FLAG,'N') = 'Y'

Line 3457: FROM PAY_GB_BACS_FPS

3453: DELETE
3454: FROM PAY_GB_BACS_FPS
3455: WHERE rowid NOT IN
3456: (SELECT MIN(rowid)
3457: FROM PAY_GB_BACS_FPS
3458: WHERE NVL(AGGREGATION_FLAG,'N') = 'Y'
3459: -- AND PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID -- To Delete all Payroll actions' duplicates
3460: GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
3461: PERSON_ID,

Line 3469: FROM PAY_GB_BACS_FPS

3465: AND (PRE_PAYMENT_PAYROLL_ACTION_ID, PERSON_ID, AMOUNT) IN
3466: (SELECT PRE_PAYMENT_PAYROLL_ACTION_ID,
3467: PERSON_ID,
3468: AMOUNT
3469: FROM PAY_GB_BACS_FPS
3470: WHERE NVL(AGGREGATION_FLAG,'N') = 'Y'
3471: -- AND PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID -- To Delete all Payroll actions' duplicates
3472: GROUP BY PRE_PAYMENT_PAYROLL_ACTION_ID,
3473: PERSON_ID,

Line 3487: FROM PAY_GB_BACS_FPS

3483:
3484: --Master insert completed but actual data inserts failed so rollback Master also
3485: --This is needed as commit is done after master insert
3486: DELETE
3487: FROM PAY_GB_BACS_FPS
3488: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
3489: COMMIT;
3490: hr_utility.raise_error;
3491: END;