[Home] [Help]
1039: and paa.payroll_action_id = ppa.payroll_action_id
1040: and ACTION_TYPE in ('V');
1041:
1042: BEGIN
1043: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_balances_for_asg.');
1044: load_defined_balances_aggr();
1045: -- fetch NI x Total/Able balances for checking whether this assignment
1046: -- reported in the P35 report or not. -- Bug 6271548
1047: hr_utility.trace('fetching NI x Total/Able balances');
1043: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_balances_for_asg.');
1044: load_defined_balances_aggr();
1045: -- fetch NI x Total/Able balances for checking whether this assignment
1046: -- reported in the P35 report or not. -- Bug 6271548
1047: hr_utility.trace('fetching NI x Total/Able balances');
1048: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
1049: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
1050: CLOSE csr_get_taxable_pay;
1051: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1050: CLOSE csr_get_taxable_pay;
1051: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1052: FETCH csr_tax_pay_value INTO l_tax_pay_val;
1053: CLOSE csr_tax_pay_value;
1054: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1055: -- Test whether we can get run level value with parent action id.
1056: -- If not pass the child assignment action id.
1057: IF l_tax_pay_val = 0 THEN
1058: OPEN csr_child_act_id(l_last_asg_action_id);
1060: CLOSE csr_child_act_id;
1061: ELSE
1062: l_asg_action_id := l_last_asg_action_id;
1063: END IF;
1064: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1065: if l_asg_action_id is null then
1066: l_asg_action_id := l_last_asg_action_id;
1067: end if;
1068:
1147:
1148:
1149: IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 ) or
1150: ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
1151: hr_utility.trace('fetching NI A Total/Able balances');
1152: p_ni_a_balance.assignment_id := p_asg_id;
1153: p_ni_a_balance.act_info1 := 'A';
1154: p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
1155: p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
1164: l_count_values := l_count_values +1;
1165: END IF;
1166: IF (NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 ) or
1167: ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
1168: hr_utility.trace('fetching NI B Total/Able balances');
1169: p_ni_b_balance.assignment_id := p_asg_id;
1170: p_ni_b_balance.act_info1 := 'B';
1171: p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
1172: p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
1181: l_count_values := l_count_values +1;
1182: END IF;
1183: IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 ) or
1184: ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
1185: hr_utility.trace('fetching NI C Total/Able balances');
1186: p_ni_c_balance.assignment_id := p_asg_id;
1187: p_ni_c_balance.act_info1 := 'C';
1188: p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
1189: p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
1198: l_count_values := l_count_values +1;
1199: END IF;
1200: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1201: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1202: hr_utility.trace('fetching NI D Total/Able balances');
1203: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1204: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1205: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1206: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1199: END IF;
1200: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1201: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1202: hr_utility.trace('fetching NI D Total/Able balances');
1203: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1204: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1205: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1206: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1207: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1200: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1201: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1202: hr_utility.trace('fetching NI D Total/Able balances');
1203: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1204: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1205: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1206: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1207: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1208: p_ni_d_balance.assignment_id := p_asg_id;
1201: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1202: hr_utility.trace('fetching NI D Total/Able balances');
1203: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1204: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1205: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1206: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1207: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1208: p_ni_d_balance.assignment_id := p_asg_id;
1209: p_ni_d_balance.act_info1 := 'D';
1202: hr_utility.trace('fetching NI D Total/Able balances');
1203: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1204: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1205: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1206: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1207: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1208: p_ni_d_balance.assignment_id := p_asg_id;
1209: p_ni_d_balance.act_info1 := 'D';
1210: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1203: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1204: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1205: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1206: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1207: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1208: p_ni_d_balance.assignment_id := p_asg_id;
1209: p_ni_d_balance.act_info1 := 'D';
1210: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1211: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1220: l_count_values := l_count_values +1;
1221: END IF;
1222: IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 ) or
1223: ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
1224: hr_utility.trace('fetching NI E Total/Able balances');
1225: p_ni_e_balance.assignment_id := p_asg_id;
1226: p_ni_e_balance.act_info1 := 'E';
1227: p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
1228: p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
1237: l_count_values := l_count_values +1;
1238: END IF;
1239: IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 ) or
1240: ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
1241: hr_utility.trace('fetching NI J Total/Able balances');
1242: p_ni_j_balance.assignment_id := p_asg_id;
1243: p_ni_j_balance.act_info1 := 'J';
1244: p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
1245: p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
1254: l_count_values := l_count_values +1;
1255: END IF;
1256: IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 ) or
1257: ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
1258: hr_utility.trace('fetching NI L Total/Able balances');
1259: p_ni_l_balance.assignment_id := p_asg_id;
1260: p_ni_l_balance.act_info1 := 'L';
1261: p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
1262: p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
1270: p_ni_l_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
1271: l_count_values := l_count_values +1;
1272: END IF;
1273:
1274: hr_utility.trace('Number of categories: ' || l_count_values);
1275: END get_ni_bal_paye_aggr_for_asg;
1276:
1277: PROCEDURE get_ni_only_bal_rti_rpt(
1278: l_last_asg_action_id IN NUMBER,
1370: and paa.payroll_action_id = ppa.payroll_action_id
1371: and ACTION_TYPE in ('V');
1372:
1373: BEGIN
1374: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_bal_rti_rpt.');
1375: load_defined_balances_aggr();
1376: -- fetch NI x Total/Able balances for checking whether this assignment
1377: -- reported in the P35 report or not. -- Bug 6271548
1378: hr_utility.trace('fetching NI x Total/Able balances');
1374: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_bal_rti_rpt.');
1375: load_defined_balances_aggr();
1376: -- fetch NI x Total/Able balances for checking whether this assignment
1377: -- reported in the P35 report or not. -- Bug 6271548
1378: hr_utility.trace('fetching NI x Total/Able balances');
1379: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
1380: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
1381: CLOSE csr_get_taxable_pay;
1382: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1381: CLOSE csr_get_taxable_pay;
1382: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1383: FETCH csr_tax_pay_value INTO l_tax_pay_val;
1384: CLOSE csr_tax_pay_value;
1385: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1386: -- Test whether we can get run level value with parent action id.
1387: -- If not pass the child assignment action id.
1388: IF l_tax_pay_val = 0 THEN
1389: OPEN csr_child_act_id(l_last_asg_action_id);
1391: CLOSE csr_child_act_id;
1392: ELSE
1393: l_asg_action_id := l_last_asg_action_id;
1394: END IF;
1395: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1396: if l_asg_action_id is null then
1397: l_asg_action_id := l_last_asg_action_id;
1398: end if;
1399:
1477: end if;
1478:
1479: IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 ) or
1480: ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
1481: hr_utility.trace('fetching NI A Total/Able balances');
1482: p_ni_a_balance.act_info1 := 'A';
1483: p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
1484: p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
1485: p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
1493: l_count_values := l_count_values +1;
1494: END IF;
1495: IF (NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 ) or
1496: ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
1497: hr_utility.trace('fetching NI B Total/Able balances');
1498: p_ni_b_balance.act_info1 := 'B';
1499: p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
1500: p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
1501: p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
1509: l_count_values := l_count_values +1;
1510: END IF;
1511: IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 ) or
1512: ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
1513: hr_utility.trace('fetching NI C Total/Able balances');
1514: p_ni_c_balance.act_info1 := 'C';
1515: p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
1516: p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
1517: p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
1525: l_count_values := l_count_values +1;
1526: END IF;
1527: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1528: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1529: hr_utility.trace('fetching NI D Total/Able balances');
1530: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1531: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1532: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1533: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1526: END IF;
1527: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1528: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1529: hr_utility.trace('fetching NI D Total/Able balances');
1530: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1531: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1532: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1533: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1534: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1527: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1528: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1529: hr_utility.trace('fetching NI D Total/Able balances');
1530: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1531: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1532: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1533: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1534: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1535: p_ni_d_balance.act_info1 := 'D';
1528: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1529: hr_utility.trace('fetching NI D Total/Able balances');
1530: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1531: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1532: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1533: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1534: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1535: p_ni_d_balance.act_info1 := 'D';
1536: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1529: hr_utility.trace('fetching NI D Total/Able balances');
1530: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1531: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1532: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1533: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1534: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1535: p_ni_d_balance.act_info1 := 'D';
1536: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1537: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1530: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1531: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1532: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1533: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1534: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1535: p_ni_d_balance.act_info1 := 'D';
1536: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1537: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1538: p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
1546: l_count_values := l_count_values +1;
1547: END IF;
1548: IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 ) or
1549: ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
1550: hr_utility.trace('fetching NI E Total/Able balances');
1551: p_ni_e_balance.act_info1 := 'E';
1552: p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
1553: p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
1554: p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
1562: l_count_values := l_count_values +1;
1563: END IF;
1564: IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 ) or
1565: ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
1566: hr_utility.trace('fetching NI J Total/Able balances');
1567: p_ni_j_balance.act_info1 := 'J';
1568: p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
1569: p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
1570: p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
1578: l_count_values := l_count_values +1;
1579: END IF;
1580: IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 ) or
1581: ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
1582: hr_utility.trace('fetching NI L Total/Able balances');
1583: p_ni_l_balance.act_info1 := 'L';
1584: p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
1585: p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
1586: p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
1592: p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
1593: p_ni_l_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id);
1594: l_count_values := l_count_values +1;
1595: END IF;
1596: hr_utility.trace('Number of categories: ' || l_count_values);
1597: END get_ni_only_bal_rti_rpt;
1598:
1599: /*
1600: procedure get_ni_balances_for_asg(
1708: and paa.payroll_action_id = ppa.payroll_action_id
1709: and ACTION_TYPE in ('V');
1710:
1711: BEGIN
1712: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_balances_for_asg.');
1713: load_defined_balances();
1714: -- fetch NI x Total/Able balances for checking whether this assignment
1715: -- reported in the P35 report or not. -- Bug 6271548
1716: hr_utility.trace('fetching NI x Total/Able balances');
1712: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_balances_for_asg.');
1713: load_defined_balances();
1714: -- fetch NI x Total/Able balances for checking whether this assignment
1715: -- reported in the P35 report or not. -- Bug 6271548
1716: hr_utility.trace('fetching NI x Total/Able balances');
1717:
1718: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
1719: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
1720: CLOSE csr_get_taxable_pay;
1722: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1723: FETCH csr_tax_pay_value INTO l_tax_pay_val;
1724: CLOSE csr_tax_pay_value;
1725:
1726: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1727: -- Test whether we can get run level value with parent action id.
1728: -- If not pass the child assignment action id.
1729:
1730: hr_utility.trace('l_child_flag := '||l_child_flag);
1726: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1727: -- Test whether we can get run level value with parent action id.
1728: -- If not pass the child assignment action id.
1729:
1730: hr_utility.trace('l_child_flag := '||l_child_flag);
1731:
1732: IF l_tax_pay_val = 0 THEN
1733: OPEN csr_child_act_id(l_last_asg_action_id);
1734: FETCH csr_child_act_id INTO l_asg_action_id;
1749: close csr_reversal;
1750:
1751: if l_child_flag = 'N' then
1752:
1753: hr_utility.trace('Processing NI with Master Action ID.');
1754: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1755: -- NI A
1756: l_nia_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
1757: l_nia_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1750:
1751: if l_child_flag = 'N' then
1752:
1753: hr_utility.trace('Processing NI with Master Action ID.');
1754: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1755: -- NI A
1756: l_nia_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
1757: l_nia_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1758: l_nia_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_tot_id_run);
1829:
1830:
1831: IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 ) or
1832: ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
1833: hr_utility.trace('fetching NI A Total/Able balances');
1834: p_ni_a_balance.act_info1 := 'A';
1835: p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
1836: p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
1837: p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
1845: l_count_values := l_count_values +1;
1846: END IF;
1847: IF (NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 ) or
1848: ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
1849: hr_utility.trace('fetching NI B Total/Able balances');
1850: p_ni_b_balance.act_info1 := 'B';
1851: p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
1852: p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
1853: p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
1861: l_count_values := l_count_values +1;
1862: END IF;
1863: IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 ) or
1864: ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
1865: hr_utility.trace('fetching NI C Total/Able balances');
1866: p_ni_c_balance.act_info1 := 'C';
1867: p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
1868: p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
1869: p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
1877: l_count_values := l_count_values +1;
1878: END IF;
1879: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1880: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1881: hr_utility.trace('fetching NI D Total/Able balances');
1882: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1883: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1884: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1885: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1878: END IF;
1879: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1880: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1881: hr_utility.trace('fetching NI D Total/Able balances');
1882: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1883: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1884: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1885: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1886: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1879: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
1880: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1881: hr_utility.trace('fetching NI D Total/Able balances');
1882: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1883: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1884: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1885: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1886: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1887: p_ni_d_balance.act_info1 := 'D';
1880: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1881: hr_utility.trace('fetching NI D Total/Able balances');
1882: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1883: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1884: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1885: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1886: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1887: p_ni_d_balance.act_info1 := 'D';
1888: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1881: hr_utility.trace('fetching NI D Total/Able balances');
1882: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1883: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1884: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1885: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1886: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1887: p_ni_d_balance.act_info1 := 'D';
1888: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1889: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1882: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1883: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1884: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1885: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1886: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1887: p_ni_d_balance.act_info1 := 'D';
1888: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1889: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1890: p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
1898: l_count_values := l_count_values +1;
1899: END IF;
1900: IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 ) or
1901: ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
1902: hr_utility.trace('fetching NI E Total/Able balances');
1903: p_ni_e_balance.act_info1 := 'E';
1904: p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
1905: p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
1906: p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
1914: l_count_values := l_count_values +1;
1915: END IF;
1916: IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 ) or
1917: ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
1918: hr_utility.trace('fetching NI J Total/Able balances');
1919: p_ni_j_balance.act_info1 := 'J';
1920: p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
1921: p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
1922: p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
1930: l_count_values := l_count_values +1;
1931: END IF;
1932: IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 ) or
1933: ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
1934: hr_utility.trace('fetching NI L Total/Able balances');
1935: p_ni_l_balance.act_info1 := 'L';
1936: p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
1937: p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
1938: p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
1991:
1992: l_asg_action_id := child.ASSIGNMENT_ACTION_ID;
1993: l_count_values :=0;
1994:
1995: hr_utility.trace('Processing NI with Child Action ID.');
1996: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1997:
1998: -- NI A
1999: l_nia_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nia_tot_id);
1992: l_asg_action_id := child.ASSIGNMENT_ACTION_ID;
1993: l_count_values :=0;
1994:
1995: hr_utility.trace('Processing NI with Child Action ID.');
1996: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1997:
1998: -- NI A
1999: l_nia_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nia_tot_id);
2000: l_nia_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_able_id);
2046: l_nil_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2047:
2048:
2049: IF NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 THEN
2050: hr_utility.trace('fetching NI A Total/Able balances');
2051: p_ni_a_balance.act_info1 := 'A';
2052: p_ni_a_balance.act_info2 := 100 * l_nia_able_run;
2053: p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
2054: p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
2061: p_ni_a_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
2062: l_count_values := l_count_values +1;
2063: END IF;
2064: IF NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 THEN
2065: hr_utility.trace('fetching NI B Total/Able balances');
2066: p_ni_b_balance.act_info1 := 'B';
2067: p_ni_b_balance.act_info2 := 100 * l_nib_able_run;
2068: p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
2069: p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
2076: p_ni_b_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id);
2077: l_count_values := l_count_values +1;
2078: END IF;
2079: IF NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 THEN
2080: hr_utility.trace('fetching NI C Total/Able balances');
2081: p_ni_c_balance.act_info1 := 'C';
2082: p_ni_c_balance.act_info2 := 100 * l_nic_able_run;
2083: p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
2084: p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
2091: p_ni_c_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id);
2092: l_count_values := l_count_values +1;
2093: END IF;
2094: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 THEN
2095: hr_utility.trace('fetching NI D Total/Able balances');
2096: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2097: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2098: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2099: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2092: l_count_values := l_count_values +1;
2093: END IF;
2094: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 THEN
2095: hr_utility.trace('fetching NI D Total/Able balances');
2096: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2097: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2098: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2099: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2100: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2093: END IF;
2094: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 THEN
2095: hr_utility.trace('fetching NI D Total/Able balances');
2096: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2097: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2098: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2099: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2100: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2101: p_ni_d_balance.act_info1 := 'D';
2094: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 THEN
2095: hr_utility.trace('fetching NI D Total/Able balances');
2096: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2097: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2098: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2099: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2100: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2101: p_ni_d_balance.act_info1 := 'D';
2102: p_ni_d_balance.act_info2 := 100 * l_nid_able_run;
2095: hr_utility.trace('fetching NI D Total/Able balances');
2096: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2097: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2098: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2099: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2100: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2101: p_ni_d_balance.act_info1 := 'D';
2102: p_ni_d_balance.act_info2 := 100 * l_nid_able_run;
2103: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
2096: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2097: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2098: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2099: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2100: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2101: p_ni_d_balance.act_info1 := 'D';
2102: p_ni_d_balance.act_info2 := 100 * l_nid_able_run;
2103: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
2104: p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
2108: p_ni_d_balance.act_info8 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_e_ytd_id);
2109: p_ni_d_balance.act_info9 := p_ni_d_balance.act_info9 + (100 * l_nid_r_ptd);
2110: p_ni_d_balance.act_info10 := p_ni_d_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id));
2111: p_ni_d_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
2112: hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2113: hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2114: hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2115: hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2116:
2109: p_ni_d_balance.act_info9 := p_ni_d_balance.act_info9 + (100 * l_nid_r_ptd);
2110: p_ni_d_balance.act_info10 := p_ni_d_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id));
2111: p_ni_d_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
2112: hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2113: hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2114: hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2115: hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2116:
2117: l_count_values := l_count_values +1;
2110: p_ni_d_balance.act_info10 := p_ni_d_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id));
2111: p_ni_d_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
2112: hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2113: hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2114: hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2115: hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2116:
2117: l_count_values := l_count_values +1;
2118: END IF;
2111: p_ni_d_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id);
2112: hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2113: hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2114: hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2115: hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2116:
2117: l_count_values := l_count_values +1;
2118: END IF;
2119: IF NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 THEN
2116:
2117: l_count_values := l_count_values +1;
2118: END IF;
2119: IF NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 THEN
2120: hr_utility.trace('fetching NI E Total/Able balances');
2121: p_ni_e_balance.act_info1 := 'E';
2122: p_ni_e_balance.act_info2 := 100 * l_nie_able_run;
2123: p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
2124: p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
2131: p_ni_e_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id);
2132: l_count_values := l_count_values +1;
2133: END IF;
2134: IF NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 THEN
2135: hr_utility.trace('fetching NI J Total/Able balances');
2136: p_ni_j_balance.act_info1 := 'J';
2137: p_ni_j_balance.act_info2 := 100 * l_nij_able_run;
2138: p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
2139: p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
2146: p_ni_j_balance.act_info11 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id);
2147: l_count_values := l_count_values +1;
2148: END IF;
2149: IF NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 THEN
2150: hr_utility.trace('fetching NI L Total/Able balances');
2151: p_ni_l_balance.act_info1 := 'L';
2152: p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
2153: p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
2154: p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
2163: END IF;
2164: end loop; -- Child Loop Ends
2165: END IF; -- If Ends for Child flag
2166:
2167: hr_utility.trace('Number of categories: ' || l_count_values);
2168:
2169:
2170: END get_ni_balances_for_asg;
2171:
2176: L_bal_type_id number;
2177: L_value number;
2178: begin
2179:
2180: hr_utility.trace('f_asg_act_id :' || f_asg_act_id);
2181: hr_utility.trace('f_balance_name : '|| f_balance_name);
2182:
2183: select balance_type_id into l_bal_type_id from pay_balance_types where balance_name = f_balance_name
2184: and legislation_code = 'GB';
2177: L_value number;
2178: begin
2179:
2180: hr_utility.trace('f_asg_act_id :' || f_asg_act_id);
2181: hr_utility.trace('f_balance_name : '|| f_balance_name);
2182:
2183: select balance_type_id into l_bal_type_id from pay_balance_types where balance_name = f_balance_name
2184: and legislation_code = 'GB';
2185:
2182:
2183: select balance_type_id into l_bal_type_id from pay_balance_types where balance_name = f_balance_name
2184: and legislation_code = 'GB';
2185:
2186: hr_utility.trace('l_bal_type_id :' || l_bal_type_id);
2187:
2188: select sum(target.result_value)
2189: into
2190: l_value
2267: )
2268: and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
2269: and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
2270:
2271: hr_utility.trace('l_value :' || l_value);
2272:
2273: return l_value;
2274:
2275: exception
2273: return l_value;
2274:
2275: exception
2276: when others then
2277: hr_utility.trace('Exception in get_ni_only_agg_route_ytd: ' || SQLCODE || SQLERRM );
2278: return 0;-- returning zero on exception
2279:
2280: end get_ni_only_agg_route_ytd;
2281:
2378: and paa.payroll_action_id = ppa.payroll_action_id
2379: and ACTION_TYPE in ('V');
2380:
2381: BEGIN
2382: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_agg_bal_for_asg.');
2383: load_defined_balances();
2384: -- fetch NI x Total/Able balances for checking whether this assignment
2385: -- reported in the P35 report or not. -- Bug 6271548
2386: hr_utility.trace('fetching NI x Total/Able balances');
2382: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_agg_bal_for_asg.');
2383: load_defined_balances();
2384: -- fetch NI x Total/Able balances for checking whether this assignment
2385: -- reported in the P35 report or not. -- Bug 6271548
2386: hr_utility.trace('fetching NI x Total/Able balances');
2387: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
2388: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
2389: CLOSE csr_get_taxable_pay;
2390: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2389: CLOSE csr_get_taxable_pay;
2390: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2391: FETCH csr_tax_pay_value INTO l_tax_pay_val;
2392: CLOSE csr_tax_pay_value;
2393: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
2394: -- Test whether we can get run level value with parent action id.
2395: -- If not pass the child assignment action id.
2396: IF l_tax_pay_val = 0 THEN
2397: OPEN csr_child_act_id(l_last_asg_action_id);
2400: ELSE
2401: l_asg_action_id := l_last_asg_action_id;
2402: END IF;
2403:
2404: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2405: if l_asg_action_id is null then
2406: l_asg_action_id := l_last_asg_action_id;
2407: end if;
2408:
2486: end if;
2487:
2488: IF (NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 OR NVL(l_nia_tot_run,0) <> 0 OR NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_r_ytd,0) <> 0 OR NVL(l_nia_r_ptd,0) <> 0 ) or
2489: ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
2490: hr_utility.trace('fetching NI A Total/Able balances');
2491: p_ni_a_balance.act_info1 := 'A';
2492: p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
2493: p_ni_a_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able LEL');
2494: p_ni_a_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able ET');
2502: l_count_values := l_count_values +1;
2503: END IF;
2504: IF (NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 OR NVL(l_nib_tot_run,0) <> 0 OR NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_r_ytd,0) <> 0 OR NVL(l_nib_r_ptd,0) <> 0 ) or
2505: ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
2506: hr_utility.trace('fetching NI B Total/Able balances');
2507: p_ni_b_balance.act_info1 := 'B';
2508: p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
2509: p_ni_b_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able LEL');
2510: p_ni_b_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able ET');
2518: l_count_values := l_count_values +1;
2519: END IF;
2520: IF( NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 OR NVL(l_nic_tot_run,0) <> 0 OR NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_r_ytd,0) <> 0 OR NVL(l_nic_r_ptd,0) <> 0 ) or
2521: ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
2522: hr_utility.trace('fetching NI C Total/Able balances');
2523: p_ni_c_balance.act_info1 := 'C';
2524: p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
2525: p_ni_c_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able LEL');
2526: p_ni_c_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able ET');
2534: l_count_values := l_count_values +1;
2535: END IF;
2536: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
2537: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2538: hr_utility.trace('fetching NI D Total/Able balances');
2539: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2540: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2541: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2542: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2535: END IF;
2536: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
2537: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2538: hr_utility.trace('fetching NI D Total/Able balances');
2539: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2540: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2541: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2542: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2543: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2536: IF ( NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 OR NVL(l_nid_tot_run,0) <> 0 OR NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_r_ytd,0) <> 0 OR NVL(l_nid_r_ptd,0) <> 0 ) or
2537: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2538: hr_utility.trace('fetching NI D Total/Able balances');
2539: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2540: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2541: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2542: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2543: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2544: p_ni_d_balance.act_info1 := 'D';
2537: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2538: hr_utility.trace('fetching NI D Total/Able balances');
2539: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2540: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2541: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2542: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2543: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2544: p_ni_d_balance.act_info1 := 'D';
2545: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2538: hr_utility.trace('fetching NI D Total/Able balances');
2539: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2540: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2541: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2542: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2543: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2544: p_ni_d_balance.act_info1 := 'D';
2545: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2546: p_ni_d_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able LEL');
2539: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2540: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2541: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2542: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2543: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2544: p_ni_d_balance.act_info1 := 'D';
2545: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2546: p_ni_d_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able LEL');
2547: p_ni_d_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able ET');
2555: l_count_values := l_count_values +1;
2556: END IF;
2557: IF( NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 OR NVL(l_nie_tot_run,0) <> 0 OR NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_r_ytd,0) <> 0 OR NVL(l_nie_r_ptd,0) <> 0 ) or
2558: ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
2559: hr_utility.trace('fetching NI E Total/Able balances');
2560: p_ni_e_balance.act_info1 := 'E';
2561: p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
2562: p_ni_e_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able LEL');
2563: p_ni_e_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able ET');
2571: l_count_values := l_count_values +1;
2572: END IF;
2573: IF( NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 OR NVL(l_nij_tot_run,0) <> 0 OR NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_r_ytd,0) <> 0 OR NVL(l_nij_r_ptd,0) <> 0 ) or
2574: ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
2575: hr_utility.trace('fetching NI J Total/Able balances');
2576: p_ni_j_balance.act_info1 := 'J';
2577: p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
2578: p_ni_j_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able LEL');
2579: p_ni_j_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able ET');
2587: l_count_values := l_count_values +1;
2588: END IF;
2589: IF( NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 OR NVL(l_nil_tot_run,0) <> 0 OR NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_r_ytd,0) <> 0 OR NVL(l_nil_r_ptd,0) <> 0 ) or
2590: ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
2591: hr_utility.trace('fetching NI L Total/Able balances');
2592: p_ni_l_balance.act_info1 := 'L';
2593: p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
2594: p_ni_l_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able LEL');
2595: p_ni_l_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able ET');
2601: p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2602: p_ni_l_balance.act_info11 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able');
2603: l_count_values := l_count_values +1;
2604: END IF;
2605: hr_utility.trace('Number of categories: ' || l_count_values);
2606: END get_ni_only_agg_bal_for_asg;
2607:
2608:
2609: -- new cursor for NI only aggregation case:
2656: IS
2657: SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
2658: FROM dual;
2659: BEGIN
2660: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_agg_bal_sum_asg.');
2661: load_defined_balances();
2662: -- fetch NI x Total/Able balances for checking whether this assignment
2663: -- reported in the P35 report or not. -- Bug 6271548
2664: hr_utility.trace('fetching NI x Total/Able balances');
2660: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_agg_bal_sum_asg.');
2661: load_defined_balances();
2662: -- fetch NI x Total/Able balances for checking whether this assignment
2663: -- reported in the P35 report or not. -- Bug 6271548
2664: hr_utility.trace('fetching NI x Total/Able balances');
2665: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
2666: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
2667: CLOSE csr_get_taxable_pay;
2668: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2667: CLOSE csr_get_taxable_pay;
2668: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2669: FETCH csr_tax_pay_value INTO l_tax_pay_val;
2670: CLOSE csr_tax_pay_value;
2671: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
2672: -- Test whether we can get run level value with parent action id.
2673: -- If not pass the child assignment action id.
2674: IF l_tax_pay_val = 0 THEN
2675: OPEN csr_child_act_id(l_last_asg_action_id);
2678: ELSE
2679: l_asg_action_id := l_last_asg_action_id;
2680: END IF;
2681:
2682: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2683: if l_asg_action_id is null then
2684: l_asg_action_id := l_last_asg_action_id;
2685: end if;
2686: -- NI A
2704: -- NI L
2705: l_nil_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Total');
2706: l_nil_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Able');
2707: IF NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 THEN
2708: hr_utility.trace('fetching NI A Total/Able balances');
2709: p_ni_a_balance.act_info1 := 'A';
2710: -- p_ni_a_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2711: p_ni_a_balance.act_info3 := nvl(p_ni_a_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able LEL');
2712: p_ni_a_balance.act_info4 := nvl(p_ni_a_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able ET');
2718: -- p_ni_a_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2719: l_count_values := l_count_values +1;
2720: END IF;
2721: IF NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 THEN
2722: hr_utility.trace('fetching NI B Total/Able balances');
2723: p_ni_b_balance.act_info1 := 'B';
2724: -- p_ni_b_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2725: p_ni_b_balance.act_info3 := nvl(p_ni_b_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able LEL');
2726: p_ni_b_balance.act_info4 := nvl(p_ni_b_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able ET');
2732: --p_ni_b_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2733: l_count_values := l_count_values +1;
2734: END IF;
2735: IF NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 THEN
2736: hr_utility.trace('fetching NI C Total/Able balances');
2737: p_ni_c_balance.act_info1 := 'C';
2738: -- p_ni_c_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2739: p_ni_c_balance.act_info3 := nvl(p_ni_c_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able LEL');
2740: p_ni_c_balance.act_info4 := nvl(p_ni_c_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able ET');
2746: --p_ni_c_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2747: l_count_values := l_count_values +1;
2748: END IF;
2749: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2750: hr_utility.trace('fetching NI D Total/Able balances');
2751: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2752: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2753: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2754: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2747: l_count_values := l_count_values +1;
2748: END IF;
2749: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2750: hr_utility.trace('fetching NI D Total/Able balances');
2751: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2752: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2753: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2754: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2755: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2748: END IF;
2749: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2750: hr_utility.trace('fetching NI D Total/Able balances');
2751: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2752: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2753: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2754: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2755: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2756: p_ni_d_balance.act_info1 := 'D';
2749: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2750: hr_utility.trace('fetching NI D Total/Able balances');
2751: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2752: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2753: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2754: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2755: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2756: p_ni_d_balance.act_info1 := 'D';
2757: -- p_ni_d_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2750: hr_utility.trace('fetching NI D Total/Able balances');
2751: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2752: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2753: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2754: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2755: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2756: p_ni_d_balance.act_info1 := 'D';
2757: -- p_ni_d_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2758: p_ni_d_balance.act_info3 := nvl(p_ni_d_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able LEL');
2751: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2752: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2753: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2754: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2755: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2756: p_ni_d_balance.act_info1 := 'D';
2757: -- p_ni_d_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2758: p_ni_d_balance.act_info3 := nvl(p_ni_d_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able LEL');
2759: p_ni_d_balance.act_info4 := nvl(p_ni_d_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able ET');
2765: --p_ni_d_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2766: l_count_values := l_count_values +1;
2767: END IF;
2768: IF NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 THEN
2769: hr_utility.trace('fetching NI E Total/Able balances');
2770: p_ni_e_balance.act_info1 := 'E';
2771: -- p_ni_e_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2772: p_ni_e_balance.act_info3 := nvl(p_ni_e_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able LEL');
2773: p_ni_e_balance.act_info4 := nvl(p_ni_e_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able ET');
2779: --p_ni_e_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2780: l_count_values := l_count_values +1;
2781: END IF;
2782: IF NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 THEN
2783: hr_utility.trace('fetching NI J Total/Able balances');
2784: p_ni_j_balance.act_info1 := 'J';
2785: --p_ni_j_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2786: p_ni_j_balance.act_info3 := nvl(p_ni_j_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able LEL');
2787: p_ni_j_balance.act_info4 := nvl(p_ni_j_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able ET');
2793: --p_ni_j_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2794: l_count_values := l_count_values +1;
2795: END IF;
2796: IF NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 THEN
2797: hr_utility.trace('fetching NI L Total/Able balances');
2798: p_ni_l_balance.act_info1 := 'L';
2799: --p_ni_l_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2800: p_ni_l_balance.act_info3 := nvl(p_ni_l_balance.act_info3,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able LEL');
2801: p_ni_l_balance.act_info4 := nvl(p_ni_l_balance.act_info4,0) + 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able ET');
2806: --p_ni_l_balance.act_info9 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2807: --p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2808: l_count_values := l_count_values +1;
2809: END IF;
2810: hr_utility.trace('Number of categories: ' || l_count_values);
2811: hr_utility.trace('Leaving PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_agg_bal_sum_asg.');
2812: END get_ni_only_agg_bal_sum_asg;
2813: -- end aggregated assignments
2814: FUNCTION Bacs_random_digit
2807: --p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2808: l_count_values := l_count_values +1;
2809: END IF;
2810: hr_utility.trace('Number of categories: ' || l_count_values);
2811: hr_utility.trace('Leaving PAY_GB_FPS_NI_AND_OTHERS_13.get_ni_only_agg_bal_sum_asg.');
2812: END get_ni_only_agg_bal_sum_asg;
2813: -- end aggregated assignments
2814: FUNCTION Bacs_random_digit
2815: RETURN VARCHAR2
2842: l_payment_amount VARCHAR2(11) := p_payment_amount;
2843: L_HASH_STR VARCHAR2(27);
2844: l_hash VARCHAR2(64);
2845: BEGIN
2846: hr_utility.trace(' Entered FPS_hash ');
2847: l_hash_str := l_random_string||l_originator_bank||l_recipient_bank||l_payment_amount;
2848: hr_utility.trace(' input for hash '||l_hash_str );
2849: L_HASH := FPS_BACS_SHA_256(L_HASH_STR);
2850: hr_utility.trace(' Generated hash '||L_HASH );
2844: l_hash VARCHAR2(64);
2845: BEGIN
2846: hr_utility.trace(' Entered FPS_hash ');
2847: l_hash_str := l_random_string||l_originator_bank||l_recipient_bank||l_payment_amount;
2848: hr_utility.trace(' input for hash '||l_hash_str );
2849: L_HASH := FPS_BACS_SHA_256(L_HASH_STR);
2850: hr_utility.trace(' Generated hash '||L_HASH );
2851: RETURN l_hash;
2852: END FPS_hash;
2846: hr_utility.trace(' Entered FPS_hash ');
2847: l_hash_str := l_random_string||l_originator_bank||l_recipient_bank||l_payment_amount;
2848: hr_utility.trace(' input for hash '||l_hash_str );
2849: L_HASH := FPS_BACS_SHA_256(L_HASH_STR);
2850: hr_utility.trace(' Generated hash '||L_HASH );
2851: RETURN l_hash;
2852: END FPS_hash;
2853: FUNCTION fetch_random_digit_bacs(
2854: p_ORG_SORT_CODE IN VARCHAR2,
2862:
2863: L_DUMMY number;
2864: L_COUNT_CHECK NUMBER := 0;
2865: BEGIN
2866: hr_utility.trace(' Entered Fetch random digit for BACS ');
2867: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2868: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2869: hr_utility.trace(' p_amount : '||p_amount);
2870: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2863: L_DUMMY number;
2864: L_COUNT_CHECK NUMBER := 0;
2865: BEGIN
2866: hr_utility.trace(' Entered Fetch random digit for BACS ');
2867: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2868: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2869: hr_utility.trace(' p_amount : '||p_amount);
2870: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2871: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2864: L_COUNT_CHECK NUMBER := 0;
2865: BEGIN
2866: hr_utility.trace(' Entered Fetch random digit for BACS ');
2867: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2868: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2869: hr_utility.trace(' p_amount : '||p_amount);
2870: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2871: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2872: L_ASSIGNMENT_ACTION_ID := pay_magtape_generic.get_parameter_value('TRANSFER_ASG_ACTION_ID');
2865: BEGIN
2866: hr_utility.trace(' Entered Fetch random digit for BACS ');
2867: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2868: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2869: hr_utility.trace(' p_amount : '||p_amount);
2870: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2871: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2872: L_ASSIGNMENT_ACTION_ID := pay_magtape_generic.get_parameter_value('TRANSFER_ASG_ACTION_ID');
2873: hr_utility.trace(' L_ASSIGNMENT_ACTION_ID : '||L_ASSIGNMENT_ACTION_ID);
2867: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2868: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2869: hr_utility.trace(' p_amount : '||p_amount);
2870: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2871: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2872: L_ASSIGNMENT_ACTION_ID := pay_magtape_generic.get_parameter_value('TRANSFER_ASG_ACTION_ID');
2873: hr_utility.trace(' L_ASSIGNMENT_ACTION_ID : '||L_ASSIGNMENT_ACTION_ID);
2874:
2875: /*
2869: hr_utility.trace(' p_amount : '||p_amount);
2870: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2871: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2872: L_ASSIGNMENT_ACTION_ID := pay_magtape_generic.get_parameter_value('TRANSFER_ASG_ACTION_ID');
2873: hr_utility.trace(' L_ASSIGNMENT_ACTION_ID : '||L_ASSIGNMENT_ACTION_ID);
2874:
2875: /*
2876: --16510056 Begin
2877: The below fix is not needed for BACS now.
2910:
2911: if l_dummy > 0 then
2912: L_COUNT_CHECK := L_COUNT_CHECK +1;
2913: IF L_COUNT_CHECK < 200 THEN
2914: HR_UTILITY.TRACE('All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2915: fnd_file.put_line(FND_FILE.LOG,'All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2916: dbms_lock.sleep(3); --Sleep for three seconds
2917: Else
2918: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2914: HR_UTILITY.TRACE('All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2915: fnd_file.put_line(FND_FILE.LOG,'All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2916: dbms_lock.sleep(3); --Sleep for three seconds
2917: Else
2918: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2919: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2920: hr_utility.raise_error;
2921: End If;
2922: else
2916: dbms_lock.sleep(3); --Sleep for three seconds
2917: Else
2918: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2919: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2920: hr_utility.raise_error;
2921: End If;
2922: else
2923: exit;
2924: End if;
2937: AND paa.pre_payment_id = bacs.pre_payment_id
2938: AND BACS.ORG_SORT_CODE = P_ORG_SORT_CODE --PARAMETER
2939: AND BACS.RECEIPIENT_SORT_CODE = P_RECEIPIENT_SORT_CODE --PARAMETER
2940: AND BACS.AMOUNT = P_AMOUNT ; --PARAMETER
2941: hr_utility.trace(' Fetched random digit for BACS '||L_random);
2942: RETURN L_random;
2943: EXCEPTION
2944: WHEN no_data_found THEN
2945: L_random := ' ';
2962: l_count_lock_failures NUMBER :=0;
2963: l_message VARCHAR2(150);
2964: L_PAY_GB_BACS_FPS PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%type;
2965: BEGIN
2966: hr_utility.trace(' Inserting Master Record in PAY_GB_BACS_FPS ');
2967: fnd_file.put_line(FND_FILE.LOG,' Master Insert start at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
2968: --15903040 Begin
2969: IF l_lockhandle IS NULL THEN
2970: LOOP
2975: fnd_file.put_line(FND_FILE.LOG,' Lock granted at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
2976: EXIT;
2977: elsif p_retval =1 THEN
2978: L_MESSAGE := 'Timeout at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2979: HR_UTILITY.TRACE(L_MESSAGE);
2980: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2981: l_count_lock_failures := l_count_lock_failures +1;
2982: elsif p_retval =2 THEN
2983: L_MESSAGE := 'Deadlock at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2980: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2981: l_count_lock_failures := l_count_lock_failures +1;
2982: elsif p_retval =2 THEN
2983: L_MESSAGE := 'Deadlock at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2984: HR_UTILITY.TRACE(L_MESSAGE);
2985: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2986: l_count_lock_failures := l_count_lock_failures +1;
2987: elsif p_retval =3 THEN
2988: L_MESSAGE := 'Parameter Error at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2985: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2986: l_count_lock_failures := l_count_lock_failures +1;
2987: elsif p_retval =3 THEN
2988: L_MESSAGE := 'Parameter Error at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2989: HR_UTILITY.TRACE(L_MESSAGE);
2990: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2991: hr_utility.raise_error;
2992: elsif p_retval=4 THEN
2993: L_MESSAGE := 'Already owned at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2987: elsif p_retval =3 THEN
2988: L_MESSAGE := 'Parameter Error at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2989: HR_UTILITY.TRACE(L_MESSAGE);
2990: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2991: hr_utility.raise_error;
2992: elsif p_retval=4 THEN
2993: L_MESSAGE := 'Already owned at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2994: HR_UTILITY.TRACE(L_MESSAGE);
2995: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2990: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2991: hr_utility.raise_error;
2992: elsif p_retval=4 THEN
2993: L_MESSAGE := 'Already owned at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2994: HR_UTILITY.TRACE(L_MESSAGE);
2995: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2996: hr_utility.raise_error;
2997: elsif p_retval=5 THEN
2998: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2992: elsif p_retval=4 THEN
2993: L_MESSAGE := 'Already owned at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2994: HR_UTILITY.TRACE(L_MESSAGE);
2995: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2996: hr_utility.raise_error;
2997: elsif p_retval=5 THEN
2998: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2999: HR_UTILITY.TRACE(L_MESSAGE);
3000: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2995: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2996: hr_utility.raise_error;
2997: elsif p_retval=5 THEN
2998: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2999: HR_UTILITY.TRACE(L_MESSAGE);
3000: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3001: hr_utility.raise_error;
3002: END IF;
3003: IF L_COUNT_LOCK_FAILURES < 50 THEN
2997: elsif p_retval=5 THEN
2998: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2999: HR_UTILITY.TRACE(L_MESSAGE);
3000: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3001: hr_utility.raise_error;
3002: END IF;
3003: IF L_COUNT_LOCK_FAILURES < 50 THEN
3004: HR_UTILITY.TRACE('Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3005: fnd_file.put_line(FND_FILE.LOG,'Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3000: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3001: hr_utility.raise_error;
3002: END IF;
3003: IF L_COUNT_LOCK_FAILURES < 50 THEN
3004: HR_UTILITY.TRACE('Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3005: fnd_file.put_line(FND_FILE.LOG,'Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3006: dbms_lock.sleep(1);
3007: ELSE
3008: L_MESSAGE := 'Lock cannot be obtained in 50 attempts hence error - '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3005: fnd_file.put_line(FND_FILE.LOG,'Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3006: dbms_lock.sleep(1);
3007: ELSE
3008: L_MESSAGE := 'Lock cannot be obtained in 50 attempts hence error - '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3009: HR_UTILITY.TRACE(L_MESSAGE);
3010: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3011: hr_utility.raise_error;
3012: END IF;
3013: END LOOP;
3007: ELSE
3008: L_MESSAGE := 'Lock cannot be obtained in 50 attempts hence error - '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3009: HR_UTILITY.TRACE(L_MESSAGE);
3010: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3011: hr_utility.raise_error;
3012: END IF;
3013: END LOOP;
3014: END IF;
3015: --The below section (till release lock) will run in single threaded mode eventhough called from multiple threads.
3017: SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3018: INTO L_PAY_GB_BACS_FPS
3019: FROM PAY_GB_BACS_FPS
3020: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = P_PAYROLL_ACTION_ID;
3021: hr_utility.trace(' Second check - Data present in PAY_GB_BACS_FPS - No insert happened');
3022: 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: EXCEPTION
3024: WHEN no_data_found THEN
3025: INSERT
3042: IF l_lockhandle IS NOT NULL THEN
3043: p_retval := dbms_lock.release(l_lockhandle);
3044: IF p_retval =3 THEN
3045: L_MESSAGE := 'Parameter Error at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3046: HR_UTILITY.TRACE(L_MESSAGE);
3047: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3048: hr_utility.raise_error;
3049: elsif p_retval=4 THEN
3050: L_MESSAGE := 'Do not own lock specified by id or lockhandle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3044: IF p_retval =3 THEN
3045: L_MESSAGE := 'Parameter Error at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3046: HR_UTILITY.TRACE(L_MESSAGE);
3047: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3048: hr_utility.raise_error;
3049: elsif p_retval=4 THEN
3050: L_MESSAGE := 'Do not own lock specified by id or lockhandle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3051: HR_UTILITY.TRACE(L_MESSAGE);
3052: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3047: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3048: hr_utility.raise_error;
3049: elsif p_retval=4 THEN
3050: L_MESSAGE := 'Do not own lock specified by id or lockhandle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3051: HR_UTILITY.TRACE(L_MESSAGE);
3052: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3053: hr_utility.raise_error;
3054: elsif p_retval=5 THEN
3055: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3049: elsif p_retval=4 THEN
3050: L_MESSAGE := 'Do not own lock specified by id or lockhandle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3051: HR_UTILITY.TRACE(L_MESSAGE);
3052: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3053: hr_utility.raise_error;
3054: elsif p_retval=5 THEN
3055: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3056: HR_UTILITY.TRACE(L_MESSAGE);
3057: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3052: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3053: hr_utility.raise_error;
3054: elsif p_retval=5 THEN
3055: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3056: HR_UTILITY.TRACE(L_MESSAGE);
3057: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3058: hr_utility.raise_error;
3059: END IF;
3060: END IF;
3054: elsif p_retval=5 THEN
3055: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3056: HR_UTILITY.TRACE(L_MESSAGE);
3057: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3058: hr_utility.raise_error;
3059: END IF;
3060: END IF;
3061: --15903040 End
3062: hr_utility.trace(' Master Record insert completed ');
3058: hr_utility.raise_error;
3059: END IF;
3060: END IF;
3061: --15903040 End
3062: hr_utility.trace(' Master Record insert completed ');
3063: fnd_file.put_line(FND_FILE.LOG,' Master Insert completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3064: RETURN L_DUMMY;
3065: END INSERT_MASTER;
3066: FUNCTION fetch_HASH_FPS_ASG
3076:
3077: L_DUMMY PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%TYPE;
3078: L_COUNT_CHECK NUMBER := 0;
3079: BEGIN
3080: hr_utility.trace(' Entered Fetch hash for FPS - ASSIGNMENT');
3081: --15903040 Begin
3082: LOOP
3083: --This is added to check whether the HASH insert is completed before the HASH fetch starts
3084: --This is needed because the calling program(FPS) runs in multithread where the
3093: Exception
3094: When no_data_found then
3095: L_COUNT_CHECK := L_COUNT_CHECK +1;
3096: IF L_COUNT_CHECK < 200 THEN
3097: HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3098: fnd_file.put_line(FND_FILE.LOG,L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3099: dbms_lock.sleep(3); --Sleep for three seconds
3100: Else
3101: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3097: HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3098: fnd_file.put_line(FND_FILE.LOG,L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3099: dbms_lock.sleep(3); --Sleep for three seconds
3100: Else
3101: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3102: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3103: hr_utility.raise_error;
3104: End If;
3105: End;
3099: dbms_lock.sleep(3); --Sleep for three seconds
3100: Else
3101: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3102: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3103: hr_utility.raise_error;
3104: End If;
3105: End;
3106: END LOOP;
3107: --15903040 End
3111: L_AMOUNT
3112: FROM PAY_GB_BACS_FPS
3113: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
3114: AND ASSIGNMENT_ID = p_ASSIGNMENT_ID ;
3115: hr_utility.trace(' Fetched hash for FPS '||L_HASH);
3116: hr_utility.trace(' Fetched amount for FPS '||L_AMOUNT);
3117: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3118: RETURN L_HASH;
3119: --NO DATA FOUND OR MORE ROWS FETCHED SHOULD STOP THE PROGRAM hence exception not handled
3112: FROM PAY_GB_BACS_FPS
3113: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
3114: AND ASSIGNMENT_ID = p_ASSIGNMENT_ID ;
3115: hr_utility.trace(' Fetched hash for FPS '||L_HASH);
3116: hr_utility.trace(' Fetched amount for FPS '||L_AMOUNT);
3117: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3118: RETURN L_HASH;
3119: --NO DATA FOUND OR MORE ROWS FETCHED SHOULD STOP THE PROGRAM hence exception not handled
3120: END fetch_HASH_FPS_ASG;
3132: L_ASG_ID NUMBER;
3133: L_DUMMY PAY_GB_BACS_FPS.PRE_PAYMENT_PAYROLL_ACTION_ID%TYPE;
3134: L_COUNT_CHECK NUMBER := 0;
3135: BEGIN
3136: hr_utility.trace(' Entered Fetch hash for FPS - PERSON');
3137: --15903040 Begin
3138: LOOP
3139: --This is added to check whether the HASH insert is completed before the HASH fetch starts
3140: --This is needed because the calling program(FPS) runs in multithread where the
3149: Exception
3150: When no_data_found then
3151: L_COUNT_CHECK := L_COUNT_CHECK +1;
3152: IF L_COUNT_CHECK < 200 THEN
3153: HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3154: fnd_file.put_line(FND_FILE.LOG,L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3155: dbms_lock.sleep(3); --Sleep for three seconds
3156: Else
3157: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3153: HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3154: fnd_file.put_line(FND_FILE.LOG,L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3155: dbms_lock.sleep(3); --Sleep for three seconds
3156: Else
3157: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3158: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3159: hr_utility.raise_error;
3160: End If;
3161: End;
3155: dbms_lock.sleep(3); --Sleep for three seconds
3156: Else
3157: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3158: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3159: hr_utility.raise_error;
3160: End If;
3161: End;
3162: END LOOP;
3163: --15903040 End
3165: INTO L_HASH,L_AMOUNT,L_ASG_ID
3166: FROM PAY_GB_BACS_FPS
3167: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
3168: AND PERSON_ID = p_PERSON_ID ;
3169: hr_utility.trace(' Fetched hash for FPS '||L_HASH);
3170: hr_utility.trace(' Fetched amount for FPS '||L_AMOUNT);
3171: hr_utility.trace(' Fetched amount for FPS for Assignment '||L_ASG_ID);
3172: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3173: P_ASG_ID := L_ASG_ID;
3166: FROM PAY_GB_BACS_FPS
3167: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
3168: AND PERSON_ID = p_PERSON_ID ;
3169: hr_utility.trace(' Fetched hash for FPS '||L_HASH);
3170: hr_utility.trace(' Fetched amount for FPS '||L_AMOUNT);
3171: hr_utility.trace(' Fetched amount for FPS for Assignment '||L_ASG_ID);
3172: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3173: P_ASG_ID := L_ASG_ID;
3174: RETURN L_HASH;
3167: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = p_PAYROLL_ACTION_ID
3168: AND PERSON_ID = p_PERSON_ID ;
3169: hr_utility.trace(' Fetched hash for FPS '||L_HASH);
3170: hr_utility.trace(' Fetched amount for FPS '||L_AMOUNT);
3171: hr_utility.trace(' Fetched amount for FPS for Assignment '||L_ASG_ID);
3172: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3173: P_ASG_ID := L_ASG_ID;
3174: RETURN L_HASH;
3175: --NO DATA FOUND OR MORE ROWS FETCHED SHOULD STOP THE PROGRAM hence exception not handled
3217: REF
3218: CURSOR;
3219: C_PREPAY_PAYROLL_ACTN_ID r_cursor;
3220: BEGIN
3221: hr_utility.trace(' Entered FPS_BACS_PREPROCESS at'||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3222: fnd_file.put_line(FND_FILE.LOG,' Entered FPS_BACS_PREPROCESS at'||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3223: -- Get the profile value
3224: fnd_profile.get('GB RTI Uptake', l_uptake_value);
3225: fnd_file.put_line(FND_FILE.LOG,' l_uptake_value : '||l_uptake_value);
3251: )
3252: )
3253: ORDER BY payroll_action_id Nulls Last;
3254: ELSE
3255: hr_utility.trace(' Called from FPS with Pre Payment payroll action id ');
3256: OPEN C_PREPAY_PAYROLL_ACTN_ID FOR
3257: SELECT DISTINCT payroll_action_id
3258: FROM pay_payroll_actions
3259: WHERE payroll_action_id = P_PAYROLL_ACTION_ID
3260: ORDER BY payroll_action_id Nulls Last;
3261: END IF;
3262: LOOP
3263: FETCH C_PREPAY_PAYROLL_ACTN_ID INTO L_PAYROLL_ACTION_ID;
3264: hr_utility.trace(' Operation started for '||L_PAYROLL_ACTION_ID);
3265: fnd_file.put_line(FND_FILE.LOG,' Operation started for '||L_PAYROLL_ACTION_ID);
3266:
3267: --14797457 - If no BACS payment then exit the loop
3268: IF L_PAYROLL_ACTION_ID IS NULL THEN
3301: EXCEPTION
3302: WHEN OTHERS THEN
3303:
3304:
3305: hr_utility.trace(' No Config found for the paye reference:' || l_tax_ref);
3306: fnd_file.put_line(FND_FILE.LOG,' No Config found for the paye reference:' || l_tax_ref);
3307: RETURN l_return;
3308: END;
3309:
3316: SELECT DISTINCT PRE_PAYMENT_PAYROLL_ACTION_ID
3317: INTO L_PAY_GB_BACS_FPS
3318: FROM PAY_GB_BACS_FPS
3319: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
3320: hr_utility.trace(' Data present in PAY_GB_BACS_FPS - No insert happened');
3321: fnd_file.put_line(FND_FILE.LOG,' Data present in PAY_GB_BACS_FPS - No insert happened');
3322: RETURN l_return;
3323: EXCEPTION
3324: WHEN no_data_found THEN
3404: (SELECT COUNT(*)
3405: FROM PAY_GB_BACS_FPS
3406: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID
3407: );
3408: hr_utility.trace(' All insert completed for '||L_PAYROLL_ACTION_ID);
3409: fnd_file.put_line(FND_FILE.LOG,' Main insert completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3410: --15903040 Begin
3411: --This update statement will be fired once.
3412: --This will indicate all inserts for the payroll action id is completed.
3443: ASSIGNMENT_ID,
3444: AMOUNT
3445: HAVING COUNT(*) > 1
3446: );
3447: hr_utility.trace(' Deleted Non aggregated duplicate rows for '||L_PAYROLL_ACTION_ID);
3448: DELETE
3449: FROM PAY_GB_BACS_FPS
3450: WHERE rowid NOT IN
3451: (SELECT MIN(rowid)
3469: AMOUNT
3470: HAVING COUNT(*) > 1
3471: );
3472: fnd_file.put_line(FND_FILE.LOG,' Sanity check completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3473: hr_utility.trace(' Deleted aggregated duplicate rows for '||L_PAYROLL_ACTION_ID);
3474: L_PAYROLL_ACTION_ID := NULL;
3475: --14807372
3476: EXCEPTION
3477: WHEN OTHERS THEN
3481: DELETE
3482: FROM PAY_GB_BACS_FPS
3483: WHERE PRE_PAYMENT_PAYROLL_ACTION_ID = L_PAYROLL_ACTION_ID;
3484: COMMIT;
3485: hr_utility.raise_error;
3486: END;
3487: END;
3488: COMMIT;
3489: END IF; -- end for if l_insert_bacs = true OR l_uptake_value = 'ALL'
3509: FROM per_assignment_extra_info
3510: WHERE assignment_id = p_assig_id
3511: AND information_type = p_type;
3512: BEGIN
3513: hr_utility.set_location('Entering: '||l_proc,1);
3514: OPEN csr_ovn;
3515: FETCH csr_ovn INTO l_ovn, p_aei_id;
3516: CLOSE csr_ovn;
3517: RETURN l_ovn;
3514: OPEN csr_ovn;
3515: FETCH csr_ovn INTO l_ovn, p_aei_id;
3516: CLOSE csr_ovn;
3517: RETURN l_ovn;
3518: hr_utility.set_location('Leaving: '||l_proc,999);
3519: END;
3520: PROCEDURE restored(
3521: p_pactid IN NUMBER)
3522: IS
3543: AND paa.assignment_action_id = pai.action_context_id
3544: AND pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
3545: AND pai.action_context_type = 'AAP';
3546: BEGIN
3547: hr_utility.set_location('Entering: '||l_proc,1);
3548: FOR archive_rec IN csr_archive_details
3549: LOOP
3550: -- Number of Periods Covered
3551: l_ovn := get_version(archive_rec.assignment_id,'GB_PAY_RTI',l_aei_id);
3590: IS
3591: l_proc CONSTANT VARCHAR2(50):= 'fps_rollback';
3592: l_id NUMBER;
3593: BEGIN
3594: hr_utility.set_location('Entering: '||l_proc,1);
3595: hr_utility.set_location('Year : ' || p_year, 10);
3596: hr_utility.set_location('Action : ' || p_actid, 10);
3597: restored(p_actid);
3598: -- Next called the Core's ROLLBACK routine
3591: l_proc CONSTANT VARCHAR2(50):= 'fps_rollback';
3592: l_id NUMBER;
3593: BEGIN
3594: hr_utility.set_location('Entering: '||l_proc,1);
3595: hr_utility.set_location('Year : ' || p_year, 10);
3596: hr_utility.set_location('Action : ' || p_actid, 10);
3597: restored(p_actid);
3598: -- Next called the Core's ROLLBACK routine
3599: l_id := fnd_request.submit_request(application => 'PAY', program => 'ROLLBACK', argument1 => 'ROLLBACK', argument2 => NULL, argument3 => NULL, argument4 => p_year, argument5 => 'X', -- magnetic report
3592: l_id NUMBER;
3593: BEGIN
3594: hr_utility.set_location('Entering: '||l_proc,1);
3595: hr_utility.set_location('Year : ' || p_year, 10);
3596: hr_utility.set_location('Action : ' || p_actid, 10);
3597: restored(p_actid);
3598: -- Next called the Core's ROLLBACK routine
3599: l_id := fnd_request.submit_request(application => 'PAY', program => 'ROLLBACK', argument1 => 'ROLLBACK', argument2 => NULL, argument3 => NULL, argument4 => p_year, argument5 => 'X', -- magnetic report
3600: argument6 => p_actid, -- payroll action_id
3599: l_id := fnd_request.submit_request(application => 'PAY', program => 'ROLLBACK', argument1 => 'ROLLBACK', argument2 => NULL, argument3 => NULL, argument4 => p_year, argument5 => 'X', -- magnetic report
3600: argument6 => p_actid, -- payroll action_id
3601: argument7 => NULL, -- assignmenet_set
3602: argument8 => 'PAYROLL_ACTION_ID='||p_actid, argument9 => NULL);
3603: hr_utility.set_location('Leaving: '||l_proc,999);
3604: END fps_rollback;
3605: --FPS RollBack End
3606: END PAY_GB_FPS_NI_AND_OTHERS_13;