[Home] [Help]
1075: and paa.payroll_action_id = ppa.payroll_action_id
1076: and ACTION_TYPE in ('V');
1077:
1078: BEGIN
1079: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_balances_for_asg.');
1080: load_defined_balances_aggr();
1081: -- fetch NI x Total/Able balances for checking whether this assignment
1082: -- reported in the P35 report or not. -- Bug 6271548
1083: hr_utility.trace('fetching NI x Total/Able balances');
1079: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_balances_for_asg.');
1080: load_defined_balances_aggr();
1081: -- fetch NI x Total/Able balances for checking whether this assignment
1082: -- reported in the P35 report or not. -- Bug 6271548
1083: hr_utility.trace('fetching NI x Total/Able balances');
1084: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
1085: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
1086: CLOSE csr_get_taxable_pay;
1087: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1086: CLOSE csr_get_taxable_pay;
1087: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1088: FETCH csr_tax_pay_value INTO l_tax_pay_val;
1089: CLOSE csr_tax_pay_value;
1090: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1091: -- Test whether we can get run level value with parent action id.
1092: -- If not pass the child assignment action id.
1093: IF l_tax_pay_val = 0 THEN
1094: OPEN csr_child_act_id(l_last_asg_action_id);
1096: CLOSE csr_child_act_id;
1097: ELSE
1098: l_asg_action_id := l_last_asg_action_id;
1099: END IF;
1100: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1101: if l_asg_action_id is null then
1102: l_asg_action_id := l_last_asg_action_id;
1103: end if;
1104:
1183:
1184:
1185: 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
1186: ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
1187: hr_utility.trace('fetching NI A Total/Able balances');
1188: p_ni_a_balance.assignment_id := p_asg_id;
1189: p_ni_a_balance.act_info1 := 'A';
1190: p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
1191: p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
1199: l_count_values := l_count_values +1;
1200: END IF;
1201: 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
1202: ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
1203: hr_utility.trace('fetching NI B Total/Able balances');
1204: p_ni_b_balance.assignment_id := p_asg_id;
1205: p_ni_b_balance.act_info1 := 'B';
1206: p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
1207: p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
1215: l_count_values := l_count_values +1;
1216: END IF;
1217: 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
1218: ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
1219: hr_utility.trace('fetching NI C Total/Able balances');
1220: p_ni_c_balance.assignment_id := p_asg_id;
1221: p_ni_c_balance.act_info1 := 'C';
1222: p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
1223: p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
1231: l_count_values := l_count_values +1;
1232: END IF;
1233: 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
1234: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1235: hr_utility.trace('fetching NI D Total/Able balances');
1236: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1237: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1238: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1239: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1232: END IF;
1233: 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
1234: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1235: hr_utility.trace('fetching NI D Total/Able balances');
1236: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1237: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1238: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1239: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1240: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1233: 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
1234: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1235: hr_utility.trace('fetching NI D Total/Able balances');
1236: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1237: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1238: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1239: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1240: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1241: p_ni_d_balance.assignment_id := p_asg_id;
1234: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1235: hr_utility.trace('fetching NI D Total/Able balances');
1236: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1237: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1238: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1239: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1240: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1241: p_ni_d_balance.assignment_id := p_asg_id;
1242: p_ni_d_balance.act_info1 := 'D';
1235: hr_utility.trace('fetching NI D Total/Able balances');
1236: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1237: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1238: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1239: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1240: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1241: p_ni_d_balance.assignment_id := p_asg_id;
1242: p_ni_d_balance.act_info1 := 'D';
1243: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1236: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1237: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1238: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1239: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1240: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1241: p_ni_d_balance.assignment_id := p_asg_id;
1242: p_ni_d_balance.act_info1 := 'D';
1243: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1244: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1252: l_count_values := l_count_values +1;
1253: END IF;
1254: 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
1255: ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
1256: hr_utility.trace('fetching NI E Total/Able balances');
1257: p_ni_e_balance.assignment_id := p_asg_id;
1258: p_ni_e_balance.act_info1 := 'E';
1259: p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
1260: p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
1268: l_count_values := l_count_values +1;
1269: END IF;
1270: 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
1271: ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
1272: hr_utility.trace('fetching NI J Total/Able balances');
1273: p_ni_j_balance.assignment_id := p_asg_id;
1274: p_ni_j_balance.act_info1 := 'J';
1275: p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
1276: p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
1284: l_count_values := l_count_values +1;
1285: END IF;
1286: 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
1287: ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
1288: hr_utility.trace('fetching NI L Total/Able balances');
1289: p_ni_l_balance.assignment_id := p_asg_id;
1290: p_ni_l_balance.act_info1 := 'L';
1291: p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
1292: p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
1299: p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
1300: l_count_values := l_count_values +1;
1301: END IF;
1302:
1303: hr_utility.trace('Number of categories: ' || l_count_values);
1304: END get_ni_bal_paye_aggr_for_asg;
1305:
1306: PROCEDURE get_ni_only_bal_rti_rpt(
1307: l_last_asg_action_id IN NUMBER,
1399: and paa.payroll_action_id = ppa.payroll_action_id
1400: and ACTION_TYPE in ('V');
1401:
1402: BEGIN
1403: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_only_bal_rti_rpt.');
1404: load_defined_balances_aggr();
1405: -- fetch NI x Total/Able balances for checking whether this assignment
1406: -- reported in the P35 report or not. -- Bug 6271548
1407: hr_utility.trace('fetching NI x Total/Able balances');
1403: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_only_bal_rti_rpt.');
1404: load_defined_balances_aggr();
1405: -- fetch NI x Total/Able balances for checking whether this assignment
1406: -- reported in the P35 report or not. -- Bug 6271548
1407: hr_utility.trace('fetching NI x Total/Able balances');
1408: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
1409: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
1410: CLOSE csr_get_taxable_pay;
1411: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1410: CLOSE csr_get_taxable_pay;
1411: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1412: FETCH csr_tax_pay_value INTO l_tax_pay_val;
1413: CLOSE csr_tax_pay_value;
1414: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1415: -- Test whether we can get run level value with parent action id.
1416: -- If not pass the child assignment action id.
1417: IF l_tax_pay_val = 0 THEN
1418: OPEN csr_child_act_id(l_last_asg_action_id);
1420: CLOSE csr_child_act_id;
1421: ELSE
1422: l_asg_action_id := l_last_asg_action_id;
1423: END IF;
1424: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1425: if l_asg_action_id is null then
1426: l_asg_action_id := l_last_asg_action_id;
1427: end if;
1428:
1506: end if;
1507:
1508: 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
1509: ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
1510: hr_utility.trace('fetching NI A Total/Able balances');
1511: p_ni_a_balance.act_info1 := 'A';
1512: p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
1513: p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
1514: p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
1521: l_count_values := l_count_values +1;
1522: END IF;
1523: 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
1524: ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
1525: hr_utility.trace('fetching NI B Total/Able balances');
1526: p_ni_b_balance.act_info1 := 'B';
1527: p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
1528: p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
1529: p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
1536: l_count_values := l_count_values +1;
1537: END IF;
1538: 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
1539: ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
1540: hr_utility.trace('fetching NI C Total/Able balances');
1541: p_ni_c_balance.act_info1 := 'C';
1542: p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
1543: p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
1544: p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
1551: l_count_values := l_count_values +1;
1552: END IF;
1553: 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
1554: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1555: hr_utility.trace('fetching NI D Total/Able balances');
1556: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1557: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1558: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1559: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1552: END IF;
1553: 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
1554: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1555: hr_utility.trace('fetching NI D Total/Able balances');
1556: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1557: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1558: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1559: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1560: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1553: 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
1554: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1555: hr_utility.trace('fetching NI D Total/Able balances');
1556: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1557: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1558: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1559: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1560: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1561: p_ni_d_balance.act_info1 := 'D';
1554: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1555: hr_utility.trace('fetching NI D Total/Able balances');
1556: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1557: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1558: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1559: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1560: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1561: p_ni_d_balance.act_info1 := 'D';
1562: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1555: hr_utility.trace('fetching NI D Total/Able balances');
1556: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1557: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1558: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1559: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1560: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1561: p_ni_d_balance.act_info1 := 'D';
1562: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1563: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1556: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1557: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1558: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1559: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1560: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1561: p_ni_d_balance.act_info1 := 'D';
1562: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1563: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1564: p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
1571: l_count_values := l_count_values +1;
1572: END IF;
1573: 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
1574: ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
1575: hr_utility.trace('fetching NI E Total/Able balances');
1576: p_ni_e_balance.act_info1 := 'E';
1577: p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
1578: p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
1579: p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
1586: l_count_values := l_count_values +1;
1587: END IF;
1588: 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
1589: ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
1590: hr_utility.trace('fetching NI J Total/Able balances');
1591: p_ni_j_balance.act_info1 := 'J';
1592: p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
1593: p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
1594: p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
1601: l_count_values := l_count_values +1;
1602: END IF;
1603: 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
1604: ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
1605: hr_utility.trace('fetching NI L Total/Able balances');
1606: p_ni_l_balance.act_info1 := 'L';
1607: p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
1608: p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
1609: p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
1614: p_ni_l_balance.act_info9 := 100 * l_nil_r_ptd;
1615: p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
1616: l_count_values := l_count_values +1;
1617: END IF;
1618: hr_utility.trace('Number of categories: ' || l_count_values);
1619: END get_ni_only_bal_rti_rpt;
1620:
1621: /*
1622: procedure get_ni_balances_for_asg(
1733: and ACTION_TYPE in ('V');
1734:
1735:
1736: BEGIN
1737: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_balances_for_asg.');
1738: load_defined_balances();
1739: -- fetch NI x Total/Able balances for checking whether this assignment
1740: -- reported in the P35 report or not. -- Bug 6271548
1741: hr_utility.trace('fetching NI x Total/Able balances');
1737: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_balances_for_asg.');
1738: load_defined_balances();
1739: -- fetch NI x Total/Able balances for checking whether this assignment
1740: -- reported in the P35 report or not. -- Bug 6271548
1741: hr_utility.trace('fetching NI x Total/Able balances');
1742:
1743: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
1744: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
1745: CLOSE csr_get_taxable_pay;
1747: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
1748: FETCH csr_tax_pay_value INTO l_tax_pay_val;
1749: CLOSE csr_tax_pay_value;
1750:
1751: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1752: -- Test whether we can get run level value with parent action id.
1753: -- If not pass the child assignment action id.
1754:
1755: hr_utility.trace('l_child_flag := '||l_child_flag);
1751: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
1752: -- Test whether we can get run level value with parent action id.
1753: -- If not pass the child assignment action id.
1754:
1755: hr_utility.trace('l_child_flag := '||l_child_flag);
1756:
1757: IF l_tax_pay_val = 0 THEN
1758: OPEN csr_child_act_id(l_last_asg_action_id);
1759: FETCH csr_child_act_id INTO l_asg_action_id;
1774: close csr_reversal;
1775:
1776: if l_child_flag = 'N' then
1777:
1778: hr_utility.trace('Processing NI with Master Action ID.');
1779: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1780: -- NI A
1781: l_nia_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
1782: l_nia_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1775:
1776: if l_child_flag = 'N' then
1777:
1778: hr_utility.trace('Processing NI with Master Action ID.');
1779: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1780: -- NI A
1781: l_nia_tot := 100 * hr_dirbal.get_balance(l_asg_action_id,g_nia_tot_id);
1782: l_nia_able := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id);
1783: l_nia_tot_run := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_tot_id_run);
1855:
1856:
1857: 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
1858: ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
1859: hr_utility.trace('fetching NI A Total/Able balances');
1860: p_ni_a_balance.act_info1 := 'A';
1861: p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
1862: p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
1863: p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
1870: l_count_values := l_count_values +1;
1871: END IF;
1872: 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
1873: ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
1874: hr_utility.trace('fetching NI B Total/Able balances');
1875: p_ni_b_balance.act_info1 := 'B';
1876: p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
1877: p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
1878: p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
1885: l_count_values := l_count_values +1;
1886: END IF;
1887: 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
1888: ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
1889: hr_utility.trace('fetching NI C Total/Able balances');
1890: p_ni_c_balance.act_info1 := 'C';
1891: p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
1892: p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
1893: p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
1900: l_count_values := l_count_values +1;
1901: END IF;
1902: 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
1903: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1904: hr_utility.trace('fetching NI D Total/Able balances');
1905: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1906: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1907: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1908: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1901: END IF;
1902: 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
1903: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1904: hr_utility.trace('fetching NI D Total/Able balances');
1905: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1906: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1907: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1908: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1909: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1902: 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
1903: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1904: hr_utility.trace('fetching NI D Total/Able balances');
1905: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1906: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1907: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1908: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1909: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1910: p_ni_d_balance.act_info1 := 'D';
1903: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
1904: hr_utility.trace('fetching NI D Total/Able balances');
1905: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1906: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1907: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1908: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1909: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1910: p_ni_d_balance.act_info1 := 'D';
1911: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1904: hr_utility.trace('fetching NI D Total/Able balances');
1905: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1906: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1907: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1908: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1909: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1910: p_ni_d_balance.act_info1 := 'D';
1911: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1912: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1905: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
1906: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
1907: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
1908: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
1909: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
1910: p_ni_d_balance.act_info1 := 'D';
1911: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
1912: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
1913: p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
1920: l_count_values := l_count_values +1;
1921: END IF;
1922: 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
1923: ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
1924: hr_utility.trace('fetching NI E Total/Able balances');
1925: p_ni_e_balance.act_info1 := 'E';
1926: p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
1927: p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
1928: p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
1935: l_count_values := l_count_values +1;
1936: END IF;
1937: 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
1938: ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
1939: hr_utility.trace('fetching NI J Total/Able balances');
1940: p_ni_j_balance.act_info1 := 'J';
1941: p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
1942: p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
1943: p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
1950: l_count_values := l_count_values +1;
1951: END IF;
1952: 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
1953: ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
1954: hr_utility.trace('fetching NI L Total/Able balances');
1955: p_ni_l_balance.act_info1 := 'L';
1956: p_ni_l_balance.act_info2 := 100 * l_nil_able_run;
1957: p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
1958: p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
2010:
2011: l_asg_action_id := child.ASSIGNMENT_ACTION_ID;
2012: l_count_values :=0;
2013:
2014: hr_utility.trace('Processing NI with Child Action ID.');
2015: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2016:
2017: -- NI A
2018: l_nia_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nia_tot_id);
2011: l_asg_action_id := child.ASSIGNMENT_ACTION_ID;
2012: l_count_values :=0;
2013:
2014: hr_utility.trace('Processing NI with Child Action ID.');
2015: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2016:
2017: -- NI A
2018: l_nia_tot := 100 * hr_dirbal.get_balance(l_last_asg_action_id,g_nia_tot_id);
2019: l_nia_able := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_able_id);
2065: l_nil_r_ptd := hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2066:
2067:
2068: 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
2069: hr_utility.trace('fetching NI A Total/Able balances');
2070: p_ni_a_balance.act_info1 := 'A';
2071: p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
2072: p_ni_a_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_lel_id);
2073: p_ni_a_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nia_et_id);
2079: p_ni_a_balance.act_info10 := p_ni_a_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id));
2080: l_count_values := l_count_values +1;
2081: END IF;
2082: 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
2083: hr_utility.trace('fetching NI B Total/Able balances');
2084: p_ni_b_balance.act_info1 := 'B';
2085: p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
2086: p_ni_b_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_lel_id);
2087: p_ni_b_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nib_et_id);
2093: p_ni_b_balance.act_info10 := p_ni_b_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id));
2094: l_count_values := l_count_values +1;
2095: END IF;
2096: 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
2097: hr_utility.trace('fetching NI C Total/Able balances');
2098: p_ni_c_balance.act_info1 := 'C';
2099: p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
2100: p_ni_c_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_lel_id);
2101: p_ni_c_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nic_et_id);
2107: p_ni_c_balance.act_info10 := p_ni_c_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id));
2108: l_count_values := l_count_values +1;
2109: END IF;
2110: 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
2111: hr_utility.trace('fetching NI D Total/Able balances');
2112: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2113: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2114: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2115: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2108: l_count_values := l_count_values +1;
2109: END IF;
2110: 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
2111: hr_utility.trace('fetching NI D Total/Able balances');
2112: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2113: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2114: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2115: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2116: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2109: END IF;
2110: 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
2111: hr_utility.trace('fetching NI D Total/Able balances');
2112: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2113: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2114: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2115: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2116: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2117: p_ni_d_balance.act_info1 := 'D';
2110: 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
2111: hr_utility.trace('fetching NI D Total/Able balances');
2112: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2113: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2114: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2115: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2116: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2117: p_ni_d_balance.act_info1 := 'D';
2118: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2111: hr_utility.trace('fetching NI D Total/Able balances');
2112: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2113: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2114: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2115: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2116: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2117: p_ni_d_balance.act_info1 := 'D';
2118: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2119: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
2112: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2113: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2114: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2115: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2116: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2117: p_ni_d_balance.act_info1 := 'D';
2118: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2119: p_ni_d_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_lel_id);
2120: p_ni_d_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_et_id);
2123: p_ni_d_balance.act_info7 := 100 * l_nid_r_ytd ;
2124: p_ni_d_balance.act_info8 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_e_ytd_id);
2125: p_ni_d_balance.act_info9 := p_ni_d_balance.act_info9 + (100 * l_nid_r_ptd);
2126: 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));
2127: hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2128: hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2129: hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2130: hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2131:
2124: p_ni_d_balance.act_info8 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nid_e_ytd_id);
2125: p_ni_d_balance.act_info9 := p_ni_d_balance.act_info9 + (100 * l_nid_r_ptd);
2126: 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));
2127: hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2128: hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2129: hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2130: hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2131:
2132: l_count_values := l_count_values +1;
2125: p_ni_d_balance.act_info9 := p_ni_d_balance.act_info9 + (100 * l_nid_r_ptd);
2126: 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));
2127: hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2128: hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2129: hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2130: hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2131:
2132: l_count_values := l_count_values +1;
2133: END IF;
2126: 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));
2127: hr_utility.trace('p_ni_d_balance.act_info7 D'||p_ni_d_balance.act_info7);
2128: hr_utility.trace('p_ni_d_balance.act_info8 D'||p_ni_d_balance.act_info8);
2129: hr_utility.trace('p_ni_d_balance.act_info9 D'||p_ni_d_balance.act_info9);
2130: hr_utility.trace('p_ni_d_balance.act_info10 D'||p_ni_d_balance.act_info10);
2131:
2132: l_count_values := l_count_values +1;
2133: END IF;
2134: 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
2131:
2132: l_count_values := l_count_values +1;
2133: END IF;
2134: 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
2135: hr_utility.trace('fetching NI E Total/Able balances');
2136: p_ni_e_balance.act_info1 := 'E';
2137: p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
2138: p_ni_e_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_lel_id);
2139: p_ni_e_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nie_et_id);
2145: p_ni_e_balance.act_info10 := p_ni_e_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id));
2146: l_count_values := l_count_values +1;
2147: END IF;
2148: 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
2149: hr_utility.trace('fetching NI J Total/Able balances');
2150: p_ni_j_balance.act_info1 := 'J';
2151: p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
2152: p_ni_j_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_lel_id);
2153: p_ni_j_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nij_et_id);
2159: p_ni_j_balance.act_info10 := p_ni_j_balance.act_info10 + (100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id));
2160: l_count_values := l_count_values +1;
2161: END IF;
2162: 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
2163: hr_utility.trace('fetching NI L Total/Able balances');
2164: p_ni_l_balance.act_info1 := 'L';
2165: p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
2166: p_ni_l_balance.act_info3 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_lel_id);
2167: p_ni_l_balance.act_info4 := 100 * hr_dirbal.get_balance(l_last_asg_action_id, g_nil_et_id);
2175: END IF;
2176: end loop; -- Child Loop Ends
2177: END IF; -- If Ends for Child flag
2178:
2179: hr_utility.trace('Number of categories: ' || l_count_values);
2180:
2181:
2182: END get_ni_balances_for_asg;
2183:
2188: L_bal_type_id number;
2189: L_value number;
2190: begin
2191:
2192: hr_utility.trace('f_asg_act_id :' || f_asg_act_id);
2193: hr_utility.trace('f_balance_name : '|| f_balance_name);
2194:
2195: select balance_type_id into l_bal_type_id from pay_balance_types where balance_name = f_balance_name
2196: and legislation_code = 'GB';
2189: L_value number;
2190: begin
2191:
2192: hr_utility.trace('f_asg_act_id :' || f_asg_act_id);
2193: hr_utility.trace('f_balance_name : '|| f_balance_name);
2194:
2195: select balance_type_id into l_bal_type_id from pay_balance_types where balance_name = f_balance_name
2196: and legislation_code = 'GB';
2197:
2194:
2195: select balance_type_id into l_bal_type_id from pay_balance_types where balance_name = f_balance_name
2196: and legislation_code = 'GB';
2197:
2198: hr_utility.trace('l_bal_type_id :' || l_bal_type_id);
2199:
2200: select sum(target.result_value)
2201: into
2202: l_value
2279: )
2280: and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
2281: and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
2282:
2283: hr_utility.trace('l_value :' || l_value);
2284:
2285: return l_value;
2286:
2287: exception
2285: return l_value;
2286:
2287: exception
2288: when others then
2289: hr_utility.trace('Exception in get_ni_only_agg_route_ytd: ' || SQLCODE || SQLERRM );
2290: return 0;-- returning zero on exception
2291:
2292: end get_ni_only_agg_route_ytd;
2293:
2390: and paa.payroll_action_id = ppa.payroll_action_id
2391: and ACTION_TYPE in ('V');
2392:
2393: BEGIN
2394: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_only_agg_bal_for_asg.');
2395: load_defined_balances();
2396: -- fetch NI x Total/Able balances for checking whether this assignment
2397: -- reported in the P35 report or not. -- Bug 6271548
2398: hr_utility.trace('fetching NI x Total/Able balances');
2394: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_only_agg_bal_for_asg.');
2395: load_defined_balances();
2396: -- fetch NI x Total/Able balances for checking whether this assignment
2397: -- reported in the P35 report or not. -- Bug 6271548
2398: hr_utility.trace('fetching NI x Total/Able balances');
2399: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
2400: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
2401: CLOSE csr_get_taxable_pay;
2402: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2401: CLOSE csr_get_taxable_pay;
2402: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2403: FETCH csr_tax_pay_value INTO l_tax_pay_val;
2404: CLOSE csr_tax_pay_value;
2405: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
2406: -- Test whether we can get run level value with parent action id.
2407: -- If not pass the child assignment action id.
2408: IF l_tax_pay_val = 0 THEN
2409: OPEN csr_child_act_id(l_last_asg_action_id);
2412: ELSE
2413: l_asg_action_id := l_last_asg_action_id;
2414: END IF;
2415:
2416: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2417: if l_asg_action_id is null then
2418: l_asg_action_id := l_last_asg_action_id;
2419: end if;
2420:
2498: end if;
2499:
2500: 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
2501: ( l_reversal is not null and NVL(l_nia_able_run,0) <> 0 OR NVL(l_nia_e_ptd,0) <> 0) THEN
2502: hr_utility.trace('fetching NI A Total/Able balances');
2503: p_ni_a_balance.act_info1 := 'A';
2504: p_ni_a_balance.act_info2 := 100 * l_nia_able_run ;
2505: p_ni_a_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able LEL');
2506: p_ni_a_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI A Able ET');
2513: l_count_values := l_count_values +1;
2514: END IF;
2515: 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
2516: ( l_reversal is not null and NVL(l_nib_able_run,0) <> 0 OR NVL(l_nib_e_ptd,0) <> 0) THEN
2517: hr_utility.trace('fetching NI B Total/Able balances');
2518: p_ni_b_balance.act_info1 := 'B';
2519: p_ni_b_balance.act_info2 := 100 * l_nib_able_run ;
2520: p_ni_b_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able LEL');
2521: p_ni_b_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI B Able ET');
2528: l_count_values := l_count_values +1;
2529: END IF;
2530: 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
2531: ( l_reversal is not null and NVL(l_nic_able_run,0) <> 0 OR NVL(l_nic_e_ptd,0) <> 0) THEN
2532: hr_utility.trace('fetching NI C Total/Able balances');
2533: p_ni_c_balance.act_info1 := 'C';
2534: p_ni_c_balance.act_info2 := 100 * l_nic_able_run ;
2535: p_ni_c_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able LEL');
2536: p_ni_c_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI C Able ET');
2543: l_count_values := l_count_values +1;
2544: END IF;
2545: 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
2546: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2547: hr_utility.trace('fetching NI D Total/Able balances');
2548: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2549: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2550: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2551: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2544: END IF;
2545: 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
2546: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2547: hr_utility.trace('fetching NI D Total/Able balances');
2548: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2549: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2550: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2551: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2552: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2545: 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
2546: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2547: hr_utility.trace('fetching NI D Total/Able balances');
2548: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2549: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2550: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2551: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2552: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2553: p_ni_d_balance.act_info1 := 'D';
2546: ( l_reversal is not null and NVL(l_nid_able_run,0) <> 0 OR NVL(l_nid_e_ptd,0) <> 0) THEN
2547: hr_utility.trace('fetching NI D Total/Able balances');
2548: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2549: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2550: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2551: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2552: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2553: p_ni_d_balance.act_info1 := 'D';
2554: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2547: hr_utility.trace('fetching NI D Total/Able balances');
2548: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2549: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2550: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2551: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2552: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2553: p_ni_d_balance.act_info1 := 'D';
2554: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2555: p_ni_d_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able LEL');
2548: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2549: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2550: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2551: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2552: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2553: p_ni_d_balance.act_info1 := 'D';
2554: p_ni_d_balance.act_info2 := 100 * l_nid_able_run ;
2555: p_ni_d_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able LEL');
2556: p_ni_d_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI D Able ET');
2563: l_count_values := l_count_values +1;
2564: END IF;
2565: 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
2566: ( l_reversal is not null and NVL(l_nie_able_run,0) <> 0 OR NVL(l_nie_e_ptd,0) <> 0) THEN
2567: hr_utility.trace('fetching NI E Total/Able balances');
2568: p_ni_e_balance.act_info1 := 'E';
2569: p_ni_e_balance.act_info2 := 100 * l_nie_able_run ;
2570: p_ni_e_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able LEL');
2571: p_ni_e_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI E Able ET');
2578: l_count_values := l_count_values +1;
2579: END IF;
2580: 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
2581: ( l_reversal is not null and NVL(l_nij_able_run,0) <> 0 OR NVL(l_nij_e_ptd,0) <> 0) THEN
2582: hr_utility.trace('fetching NI J Total/Able balances');
2583: p_ni_j_balance.act_info1 := 'J';
2584: p_ni_j_balance.act_info2 := 100 * l_nij_able_run ;
2585: p_ni_j_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able LEL');
2586: p_ni_j_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI J Able ET');
2593: l_count_values := l_count_values +1;
2594: END IF;
2595: 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
2596: ( l_reversal is not null and NVL(l_nil_able_run,0) <> 0 OR NVL(l_nil_e_ptd,0) <> 0) THEN
2597: hr_utility.trace('fetching NI L Total/Able balances');
2598: p_ni_l_balance.act_info1 := 'L';
2599: p_ni_l_balance.act_info2 := 100 * l_nil_able_run ;
2600: p_ni_l_balance.act_info3 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able LEL');
2601: p_ni_l_balance.act_info4 := 100 * get_ni_only_agg_route_ytd(l_last_asg_action_id, 'NI L Able ET');
2606: p_ni_l_balance.act_info9 := 100 * l_nil_r_ptd ;
2607: p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2608: l_count_values := l_count_values +1;
2609: END IF;
2610: hr_utility.trace('Number of categories: ' || l_count_values);
2611: END get_ni_only_agg_bal_for_asg;
2612:
2613:
2614: -- new cursor for NI only aggregation case:
2661: IS
2662: SELECT hr_dirbal.get_balance(p_last_asg_action_id,l_tax_pay_def_bal_id)
2663: FROM dual;
2664: BEGIN
2665: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_only_agg_bal_sum_asg.');
2666: load_defined_balances();
2667: -- fetch NI x Total/Able balances for checking whether this assignment
2668: -- reported in the P35 report or not. -- Bug 6271548
2669: hr_utility.trace('fetching NI x Total/Able balances');
2665: hr_utility.trace('Entering PAY_GB_FPS_NI_AND_OTHERS.get_ni_only_agg_bal_sum_asg.');
2666: load_defined_balances();
2667: -- fetch NI x Total/Able balances for checking whether this assignment
2668: -- reported in the P35 report or not. -- Bug 6271548
2669: hr_utility.trace('fetching NI x Total/Able balances');
2670: OPEN csr_get_taxable_pay('Taxable Pay', '_ASG_RUN');
2671: FETCH csr_get_taxable_pay INTO l_tax_pay_def_bal_id;
2672: CLOSE csr_get_taxable_pay;
2673: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2672: CLOSE csr_get_taxable_pay;
2673: OPEN csr_tax_pay_value(l_last_asg_action_id,l_tax_pay_def_bal_id);
2674: FETCH csr_tax_pay_value INTO l_tax_pay_val;
2675: CLOSE csr_tax_pay_value;
2676: hr_utility.trace('l_last_asg_action_id: ' || l_last_asg_action_id);
2677: -- Test whether we can get run level value with parent action id.
2678: -- If not pass the child assignment action id.
2679: IF l_tax_pay_val = 0 THEN
2680: OPEN csr_child_act_id(l_last_asg_action_id);
2683: ELSE
2684: l_asg_action_id := l_last_asg_action_id;
2685: END IF;
2686:
2687: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2688: if l_asg_action_id is null then
2689: l_asg_action_id := l_last_asg_action_id;
2690: end if;
2691: -- NI A
2709: -- NI L
2710: l_nil_tot := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Total');
2711: l_nil_able := 100 * get_ni_only_agg_route_ytd(l_asg_action_id, 'NI L Able');
2712: IF NVL(l_nia_tot,0) <> 0 OR NVL(l_nia_able,0) <> 0 THEN
2713: hr_utility.trace('fetching NI A Total/Able balances');
2714: p_ni_a_balance.act_info1 := 'A';
2715: -- p_ni_a_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_able_id_run);
2716: 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');
2717: 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');
2723: -- p_ni_a_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nia_e_ptd_id);
2724: l_count_values := l_count_values +1;
2725: END IF;
2726: IF NVL(l_nib_tot,0) <> 0 OR NVL(l_nib_able,0) <> 0 THEN
2727: hr_utility.trace('fetching NI B Total/Able balances');
2728: p_ni_b_balance.act_info1 := 'B';
2729: -- p_ni_b_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_able_id_run);
2730: 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');
2731: 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');
2737: --p_ni_b_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nib_e_ptd_id);
2738: l_count_values := l_count_values +1;
2739: END IF;
2740: IF NVL(l_nic_tot,0) <> 0 OR NVL(l_nic_able,0) <> 0 THEN
2741: hr_utility.trace('fetching NI C Total/Able balances');
2742: p_ni_c_balance.act_info1 := 'C';
2743: -- p_ni_c_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_able_id_run);
2744: 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');
2745: 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');
2751: --p_ni_c_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nic_e_ptd_id);
2752: l_count_values := l_count_values +1;
2753: END IF;
2754: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2755: hr_utility.trace('fetching NI D Total/Able balances');
2756: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2757: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2758: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2759: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2752: l_count_values := l_count_values +1;
2753: END IF;
2754: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2755: hr_utility.trace('fetching NI D Total/Able balances');
2756: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2757: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2758: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2759: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2760: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2753: END IF;
2754: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2755: hr_utility.trace('fetching NI D Total/Able balances');
2756: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2757: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2758: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2759: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2760: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2761: p_ni_d_balance.act_info1 := 'D';
2754: IF NVL(l_nid_tot,0) <> 0 OR NVL(l_nid_able,0) <> 0 THEN
2755: hr_utility.trace('fetching NI D Total/Able balances');
2756: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2757: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2758: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2759: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2760: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2761: p_ni_d_balance.act_info1 := 'D';
2762: -- p_ni_d_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2755: hr_utility.trace('fetching NI D Total/Able balances');
2756: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2757: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2758: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2759: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2760: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2761: p_ni_d_balance.act_info1 := 'D';
2762: -- p_ni_d_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2763: 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');
2756: hr_utility.trace('l_asg_action_id: ' || l_asg_action_id);
2757: hr_utility.trace('g_nid_r_ytd_id: ' || g_nid_r_ytd_id);
2758: hr_utility.trace('g_nid_e_ytd_id: ' || g_nid_e_ytd_id);
2759: hr_utility.trace('g_nid_r_ptd_id: ' || g_nid_r_ptd_id);
2760: hr_utility.trace('g_nid_e_ptd_id: ' || g_nid_e_ptd_id);
2761: p_ni_d_balance.act_info1 := 'D';
2762: -- p_ni_d_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_able_id_run);
2763: 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');
2764: 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');
2770: --p_ni_d_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nid_e_ptd_id);
2771: l_count_values := l_count_values +1;
2772: END IF;
2773: IF NVL(l_nie_tot,0) <> 0 OR NVL(l_nie_able,0) <> 0 THEN
2774: hr_utility.trace('fetching NI E Total/Able balances');
2775: p_ni_e_balance.act_info1 := 'E';
2776: -- p_ni_e_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_able_id_run);
2777: 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');
2778: 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');
2784: --p_ni_e_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nie_e_ptd_id);
2785: l_count_values := l_count_values +1;
2786: END IF;
2787: IF NVL(l_nij_tot,0) <> 0 OR NVL(l_nij_able,0) <> 0 THEN
2788: hr_utility.trace('fetching NI J Total/Able balances');
2789: p_ni_j_balance.act_info1 := 'J';
2790: --p_ni_j_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_able_id_run);
2791: 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');
2792: 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');
2798: --p_ni_j_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nij_e_ptd_id);
2799: l_count_values := l_count_values +1;
2800: END IF;
2801: IF NVL(l_nil_tot,0) <> 0 OR NVL(l_nil_able,0) <> 0 THEN
2802: hr_utility.trace('fetching NI L Total/Able balances');
2803: p_ni_l_balance.act_info1 := 'L';
2804: --p_ni_l_balance.act_info2 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_able_id_run);
2805: 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');
2806: 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');
2811: --p_ni_l_balance.act_info9 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_r_ptd_id);
2812: --p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2813: l_count_values := l_count_values +1;
2814: END IF;
2815: hr_utility.trace('Number of categories: ' || l_count_values);
2816: hr_utility.trace('Leaving PAY_GB_FPS_NI_AND_OTHERS.get_ni_only_agg_bal_sum_asg.');
2817: END get_ni_only_agg_bal_sum_asg;
2818: -- end aggregated assignments
2819: FUNCTION Bacs_random_digit
2812: --p_ni_l_balance.act_info10 := 100 * hr_dirbal.get_balance(l_asg_action_id, g_nil_e_ptd_id);
2813: l_count_values := l_count_values +1;
2814: END IF;
2815: hr_utility.trace('Number of categories: ' || l_count_values);
2816: hr_utility.trace('Leaving PAY_GB_FPS_NI_AND_OTHERS.get_ni_only_agg_bal_sum_asg.');
2817: END get_ni_only_agg_bal_sum_asg;
2818: -- end aggregated assignments
2819: FUNCTION Bacs_random_digit
2820: RETURN VARCHAR2
2847: l_payment_amount VARCHAR2(11) := p_payment_amount;
2848: L_HASH_STR VARCHAR2(27);
2849: l_hash VARCHAR2(64);
2850: BEGIN
2851: hr_utility.trace(' Entered FPS_hash ');
2852: l_hash_str := l_random_string||l_originator_bank||l_recipient_bank||l_payment_amount;
2853: hr_utility.trace(' input for hash '||l_hash_str );
2854: L_HASH := FPS_BACS_SHA_256(L_HASH_STR);
2855: hr_utility.trace(' Generated hash '||L_HASH );
2849: l_hash VARCHAR2(64);
2850: BEGIN
2851: hr_utility.trace(' Entered FPS_hash ');
2852: l_hash_str := l_random_string||l_originator_bank||l_recipient_bank||l_payment_amount;
2853: hr_utility.trace(' input for hash '||l_hash_str );
2854: L_HASH := FPS_BACS_SHA_256(L_HASH_STR);
2855: hr_utility.trace(' Generated hash '||L_HASH );
2856: RETURN l_hash;
2857: END FPS_hash;
2851: hr_utility.trace(' Entered FPS_hash ');
2852: l_hash_str := l_random_string||l_originator_bank||l_recipient_bank||l_payment_amount;
2853: hr_utility.trace(' input for hash '||l_hash_str );
2854: L_HASH := FPS_BACS_SHA_256(L_HASH_STR);
2855: hr_utility.trace(' Generated hash '||L_HASH );
2856: RETURN l_hash;
2857: END FPS_hash;
2858: FUNCTION fetch_random_digit_bacs(
2859: p_ORG_SORT_CODE IN VARCHAR2,
2867:
2868: L_DUMMY number;
2869: L_COUNT_CHECK NUMBER := 0;
2870: BEGIN
2871: hr_utility.trace(' Entered Fetch random digit for BACS ');
2872: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2873: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2874: hr_utility.trace(' p_amount : '||p_amount);
2875: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2868: L_DUMMY number;
2869: L_COUNT_CHECK NUMBER := 0;
2870: BEGIN
2871: hr_utility.trace(' Entered Fetch random digit for BACS ');
2872: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2873: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2874: hr_utility.trace(' p_amount : '||p_amount);
2875: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2876: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2869: L_COUNT_CHECK NUMBER := 0;
2870: BEGIN
2871: hr_utility.trace(' Entered Fetch random digit for BACS ');
2872: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2873: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2874: hr_utility.trace(' p_amount : '||p_amount);
2875: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2876: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2877: L_ASSIGNMENT_ACTION_ID := pay_magtape_generic.get_parameter_value('TRANSFER_ASG_ACTION_ID');
2870: BEGIN
2871: hr_utility.trace(' Entered Fetch random digit for BACS ');
2872: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2873: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2874: hr_utility.trace(' p_amount : '||p_amount);
2875: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2876: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2877: L_ASSIGNMENT_ACTION_ID := pay_magtape_generic.get_parameter_value('TRANSFER_ASG_ACTION_ID');
2878: hr_utility.trace(' L_ASSIGNMENT_ACTION_ID : '||L_ASSIGNMENT_ACTION_ID);
2872: hr_utility.trace(' p_ORG_SORT_CODE : '||p_ORG_SORT_CODE);
2873: hr_utility.trace(' p_RECEIPIENT_SORT_CODE : '||p_RECEIPIENT_SORT_CODE);
2874: hr_utility.trace(' p_amount : '||p_amount);
2875: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2876: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2877: L_ASSIGNMENT_ACTION_ID := pay_magtape_generic.get_parameter_value('TRANSFER_ASG_ACTION_ID');
2878: hr_utility.trace(' L_ASSIGNMENT_ACTION_ID : '||L_ASSIGNMENT_ACTION_ID);
2879:
2880: /*
2874: hr_utility.trace(' p_amount : '||p_amount);
2875: L_PAYROLL_ACTION_ID := pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID');
2876: hr_utility.trace(' L_PAYROLL_ACTION_ID : '||L_PAYROLL_ACTION_ID);
2877: L_ASSIGNMENT_ACTION_ID := pay_magtape_generic.get_parameter_value('TRANSFER_ASG_ACTION_ID');
2878: hr_utility.trace(' L_ASSIGNMENT_ACTION_ID : '||L_ASSIGNMENT_ACTION_ID);
2879:
2880: /*
2881: --16510056 Begin
2882: The below fix is not needed for BACS now.
2915:
2916: if l_dummy > 0 then
2917: L_COUNT_CHECK := L_COUNT_CHECK +1;
2918: IF L_COUNT_CHECK < 200 THEN
2919: HR_UTILITY.TRACE('All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2920: fnd_file.put_line(FND_FILE.LOG,'All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2921: dbms_lock.sleep(3); --Sleep for three seconds
2922: Else
2923: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2919: HR_UTILITY.TRACE('All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2920: fnd_file.put_line(FND_FILE.LOG,'All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2921: dbms_lock.sleep(3); --Sleep for three seconds
2922: Else
2923: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2924: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2925: hr_utility.raise_error;
2926: End If;
2927: else
2921: dbms_lock.sleep(3); --Sleep for three seconds
2922: Else
2923: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2924: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
2925: hr_utility.raise_error;
2926: End If;
2927: else
2928: exit;
2929: End if;
2942: AND paa.pre_payment_id = bacs.pre_payment_id
2943: AND BACS.ORG_SORT_CODE = P_ORG_SORT_CODE --PARAMETER
2944: AND BACS.RECEIPIENT_SORT_CODE = P_RECEIPIENT_SORT_CODE --PARAMETER
2945: AND BACS.AMOUNT = P_AMOUNT ; --PARAMETER
2946: hr_utility.trace(' Fetched random digit for BACS '||L_random);
2947: RETURN L_random;
2948: EXCEPTION
2949: WHEN no_data_found THEN
2950: L_random := ' ';
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
2980: fnd_file.put_line(FND_FILE.LOG,' Lock granted at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
2981: EXIT;
2982: elsif p_retval =1 THEN
2983: L_MESSAGE := 'Timeout 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 =2 THEN
2988: L_MESSAGE := 'Deadlock 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 =2 THEN
2988: L_MESSAGE := 'Deadlock at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2989: HR_UTILITY.TRACE(L_MESSAGE);
2990: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2991: l_count_lock_failures := l_count_lock_failures +1;
2992: elsif p_retval =3 THEN
2993: L_MESSAGE := 'Parameter Error at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2990: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
2991: l_count_lock_failures := l_count_lock_failures +1;
2992: elsif p_retval =3 THEN
2993: L_MESSAGE := 'Parameter Error 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=4 THEN
2998: L_MESSAGE := 'Already owned at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2992: elsif p_retval =3 THEN
2993: L_MESSAGE := 'Parameter Error 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=4 THEN
2998: L_MESSAGE := 'Already owned 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=4 THEN
2998: L_MESSAGE := 'Already owned 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: elsif p_retval=5 THEN
3003: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
2997: elsif p_retval=4 THEN
2998: L_MESSAGE := 'Already owned 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: elsif p_retval=5 THEN
3003: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3004: HR_UTILITY.TRACE(L_MESSAGE);
3005: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3000: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3001: hr_utility.raise_error;
3002: elsif p_retval=5 THEN
3003: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3004: HR_UTILITY.TRACE(L_MESSAGE);
3005: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3006: hr_utility.raise_error;
3007: END IF;
3008: IF L_COUNT_LOCK_FAILURES < 50 THEN
3002: elsif p_retval=5 THEN
3003: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3004: HR_UTILITY.TRACE(L_MESSAGE);
3005: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3006: hr_utility.raise_error;
3007: END IF;
3008: IF L_COUNT_LOCK_FAILURES < 50 THEN
3009: HR_UTILITY.TRACE('Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3010: fnd_file.put_line(FND_FILE.LOG,'Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3005: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3006: hr_utility.raise_error;
3007: END IF;
3008: IF L_COUNT_LOCK_FAILURES < 50 THEN
3009: HR_UTILITY.TRACE('Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3010: fnd_file.put_line(FND_FILE.LOG,'Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3011: dbms_lock.sleep(1);
3012: ELSE
3013: L_MESSAGE := 'Lock cannot be obtained in 50 attempts hence error - '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3010: fnd_file.put_line(FND_FILE.LOG,'Sleeping for a second'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3011: dbms_lock.sleep(1);
3012: ELSE
3013: L_MESSAGE := 'Lock cannot be obtained in 50 attempts hence error - '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3014: HR_UTILITY.TRACE(L_MESSAGE);
3015: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3016: hr_utility.raise_error;
3017: END IF;
3018: END LOOP;
3012: ELSE
3013: L_MESSAGE := 'Lock cannot be obtained in 50 attempts hence error - '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3014: HR_UTILITY.TRACE(L_MESSAGE);
3015: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3016: hr_utility.raise_error;
3017: END IF;
3018: END LOOP;
3019: END IF;
3020: --The below section (till release lock) will run in single threaded mode eventhough called from multiple threads.
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
3047: IF l_lockhandle IS NOT NULL THEN
3048: p_retval := dbms_lock.release(l_lockhandle);
3049: IF p_retval =3 THEN
3050: L_MESSAGE := 'Parameter Error 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=4 THEN
3055: L_MESSAGE := 'Do not own lock specified by id or lockhandle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3049: IF p_retval =3 THEN
3050: L_MESSAGE := 'Parameter Error 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=4 THEN
3055: L_MESSAGE := 'Do not own lock specified by id or lockhandle 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=4 THEN
3055: L_MESSAGE := 'Do not own lock specified by id or lockhandle 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: elsif p_retval=5 THEN
3060: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3054: elsif p_retval=4 THEN
3055: L_MESSAGE := 'Do not own lock specified by id or lockhandle 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: elsif p_retval=5 THEN
3060: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3061: HR_UTILITY.TRACE(L_MESSAGE);
3062: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3057: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3058: hr_utility.raise_error;
3059: elsif p_retval=5 THEN
3060: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3061: HR_UTILITY.TRACE(L_MESSAGE);
3062: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3063: hr_utility.raise_error;
3064: END IF;
3065: END IF;
3059: elsif p_retval=5 THEN
3060: L_MESSAGE := 'Illegal Lock Handle at '||FND_DATE.DATE_TO_CANONICAL(SYSDATE);
3061: HR_UTILITY.TRACE(L_MESSAGE);
3062: fnd_file.put_line(FND_FILE.LOG,L_MESSAGE);
3063: hr_utility.raise_error;
3064: END IF;
3065: END IF;
3066: --15903040 End
3067: hr_utility.trace(' Master Record insert completed ');
3063: hr_utility.raise_error;
3064: END IF;
3065: END IF;
3066: --15903040 End
3067: hr_utility.trace(' Master Record insert completed ');
3068: fnd_file.put_line(FND_FILE.LOG,' Master Insert completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3069: RETURN L_DUMMY;
3070: END INSERT_MASTER;
3071: FUNCTION fetch_HASH_FPS_ASG
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
3087: LOOP
3088: --This is added to check whether the HASH insert is completed before the HASH fetch starts
3089: --This is needed because the calling program(FPS) runs in multithread where the
3098: Exception
3099: When no_data_found then
3100: L_COUNT_CHECK := L_COUNT_CHECK +1;
3101: IF L_COUNT_CHECK < 200 THEN
3102: HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3103: 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));
3104: dbms_lock.sleep(3); --Sleep for three seconds
3105: Else
3106: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3102: HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3103: 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));
3104: dbms_lock.sleep(3); --Sleep for three seconds
3105: Else
3106: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3107: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3108: hr_utility.raise_error;
3109: End If;
3110: End;
3104: dbms_lock.sleep(3); --Sleep for three seconds
3105: Else
3106: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3107: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3108: hr_utility.raise_error;
3109: End If;
3110: End;
3111: END LOOP;
3112: --15903040 End
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);
3122: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3123: RETURN L_HASH;
3124: --NO DATA FOUND OR MORE ROWS FETCHED SHOULD STOP THE PROGRAM hence exception not handled
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);
3122: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3123: RETURN L_HASH;
3124: --NO DATA FOUND OR MORE ROWS FETCHED SHOULD STOP THE PROGRAM hence exception not handled
3125: END fetch_HASH_FPS_ASG;
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
3143: LOOP
3144: --This is added to check whether the HASH insert is completed before the HASH fetch starts
3145: --This is needed because the calling program(FPS) runs in multithread where the
3154: Exception
3155: When no_data_found then
3156: L_COUNT_CHECK := L_COUNT_CHECK +1;
3157: IF L_COUNT_CHECK < 200 THEN
3158: HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3159: 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));
3160: dbms_lock.sleep(3); --Sleep for three seconds
3161: Else
3162: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3158: HR_UTILITY.TRACE(L_COUNT_CHECK||'- All insert not completed hence sleeping for 3 seconds'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3159: 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));
3160: dbms_lock.sleep(3); --Sleep for three seconds
3161: Else
3162: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3163: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3164: hr_utility.raise_error;
3165: End If;
3166: End;
3160: dbms_lock.sleep(3); --Sleep for three seconds
3161: Else
3162: HR_UTILITY.TRACE('Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3163: fnd_file.put_line(FND_FILE.LOG,'Insert is running for long time !!!!!!!!!!!! Hence quiting'||FND_DATE.DATE_TO_CANONICAL(SYSDATE));
3164: hr_utility.raise_error;
3165: End If;
3166: End;
3167: END LOOP;
3168: --15903040 End
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);
3176: hr_utility.trace(' Fetched amount for FPS for Assignment '||L_ASG_ID);
3177: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3178: P_ASG_ID := 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);
3176: hr_utility.trace(' Fetched amount for FPS for Assignment '||L_ASG_ID);
3177: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3178: P_ASG_ID := L_ASG_ID;
3179: RETURN L_HASH;
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);
3176: hr_utility.trace(' Fetched amount for FPS for Assignment '||L_ASG_ID);
3177: P_LARGE_BACS_AMT := NVL(L_AMOUNT,0);
3178: P_ASG_ID := L_ASG_ID;
3179: RETURN L_HASH;
3180: --NO DATA FOUND OR MORE ROWS FETCHED SHOULD STOP THE PROGRAM hence exception not handled
3222: REF
3223: CURSOR;
3224: C_PREPAY_PAYROLL_ACTN_ID r_cursor;
3225: BEGIN
3226: hr_utility.trace(' Entered FPS_BACS_PREPROCESS at'||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3227: fnd_file.put_line(FND_FILE.LOG,' Entered FPS_BACS_PREPROCESS at'||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3228: -- Get the profile value
3229: fnd_profile.get('GB RTI Uptake', l_uptake_value);
3230: fnd_file.put_line(FND_FILE.LOG,' l_uptake_value : '||l_uptake_value);
3256: )
3257: )
3258: ORDER BY payroll_action_id Nulls Last;
3259: ELSE
3260: hr_utility.trace(' Called from FPS with Pre Payment payroll action id ');
3261: OPEN C_PREPAY_PAYROLL_ACTN_ID FOR
3262: SELECT DISTINCT payroll_action_id
3263: FROM pay_payroll_actions
3264: WHERE payroll_action_id = P_PAYROLL_ACTION_ID
3265: ORDER BY payroll_action_id Nulls Last;
3266: END IF;
3267: LOOP
3268: FETCH C_PREPAY_PAYROLL_ACTN_ID INTO L_PAYROLL_ACTION_ID;
3269: hr_utility.trace(' Operation started for '||L_PAYROLL_ACTION_ID);
3270: fnd_file.put_line(FND_FILE.LOG,' Operation started for '||L_PAYROLL_ACTION_ID);
3271:
3272: --14797457 - If no BACS payment then exit the loop
3273: IF L_PAYROLL_ACTION_ID IS NULL THEN
3306: EXCEPTION
3307: WHEN OTHERS THEN
3308:
3309:
3310: hr_utility.trace(' No Config found for the paye reference:' || l_tax_ref);
3311: fnd_file.put_line(FND_FILE.LOG,' No Config found for the paye reference:' || l_tax_ref);
3312: RETURN l_return;
3313: END;
3314:
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
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'));
3415: --15903040 Begin
3416: --This update statement will be fired once.
3417: --This will indicate all inserts for the payroll action id is completed.
3448: ASSIGNMENT_ID,
3449: AMOUNT
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)
3474: AMOUNT
3475: HAVING COUNT(*) > 1
3476: );
3477: fnd_file.put_line(FND_FILE.LOG,' Sanity check completed at '||TO_CHAR(sysdate,'ddmmyyyy hh24:mi:ss'));
3478: hr_utility.trace(' Deleted aggregated duplicate rows for '||L_PAYROLL_ACTION_ID);
3479: L_PAYROLL_ACTION_ID := NULL;
3480: --14807372
3481: EXCEPTION
3482: WHEN OTHERS THEN
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;
3492: END;
3493: COMMIT;
3494: END IF; -- end for if l_insert_bacs = true OR l_uptake_value = 'ALL'
3514: FROM per_assignment_extra_info
3515: WHERE assignment_id = p_assig_id
3516: AND information_type = p_type;
3517: BEGIN
3518: hr_utility.set_location('Entering: '||l_proc,1);
3519: OPEN csr_ovn;
3520: FETCH csr_ovn INTO l_ovn, p_aei_id;
3521: CLOSE csr_ovn;
3522: RETURN l_ovn;
3519: OPEN csr_ovn;
3520: FETCH csr_ovn INTO l_ovn, p_aei_id;
3521: CLOSE csr_ovn;
3522: RETURN l_ovn;
3523: hr_utility.set_location('Leaving: '||l_proc,999);
3524: END;
3525: PROCEDURE restored(
3526: p_pactid IN NUMBER)
3527: IS
3548: AND paa.assignment_action_id = pai.action_context_id
3549: AND pai.action_information_category = 'GB_RTI_FPS_ASG_DET1'
3550: AND pai.action_context_type = 'AAP';
3551: BEGIN
3552: hr_utility.set_location('Entering: '||l_proc,1);
3553: FOR archive_rec IN csr_archive_details
3554: LOOP
3555: -- Number of Periods Covered
3556: l_ovn := get_version(archive_rec.assignment_id,'GB_PAY_RTI',l_aei_id);
3595: IS
3596: l_proc CONSTANT VARCHAR2(50):= 'fps_rollback';
3597: l_id NUMBER;
3598: BEGIN
3599: hr_utility.set_location('Entering: '||l_proc,1);
3600: hr_utility.set_location('Year : ' || p_year, 10);
3601: hr_utility.set_location('Action : ' || p_actid, 10);
3602: restored(p_actid);
3603: -- Next called the Core's ROLLBACK routine
3596: l_proc CONSTANT VARCHAR2(50):= 'fps_rollback';
3597: l_id NUMBER;
3598: BEGIN
3599: hr_utility.set_location('Entering: '||l_proc,1);
3600: hr_utility.set_location('Year : ' || p_year, 10);
3601: hr_utility.set_location('Action : ' || p_actid, 10);
3602: restored(p_actid);
3603: -- Next called the Core's ROLLBACK routine
3604: l_id := fnd_request.submit_request(application => 'PAY', program => 'ROLLBACK', argument1 => 'ROLLBACK', argument2 => NULL, argument3 => NULL, argument4 => p_year, argument5 => 'X', -- magnetic report
3597: l_id NUMBER;
3598: BEGIN
3599: hr_utility.set_location('Entering: '||l_proc,1);
3600: hr_utility.set_location('Year : ' || p_year, 10);
3601: hr_utility.set_location('Action : ' || p_actid, 10);
3602: restored(p_actid);
3603: -- Next called the Core's ROLLBACK routine
3604: l_id := fnd_request.submit_request(application => 'PAY', program => 'ROLLBACK', argument1 => 'ROLLBACK', argument2 => NULL, argument3 => NULL, argument4 => p_year, argument5 => 'X', -- magnetic report
3605: argument6 => p_actid, -- payroll action_id
3604: l_id := fnd_request.submit_request(application => 'PAY', program => 'ROLLBACK', argument1 => 'ROLLBACK', argument2 => NULL, argument3 => NULL, argument4 => p_year, argument5 => 'X', -- magnetic report
3605: argument6 => p_actid, -- payroll action_id
3606: argument7 => NULL, -- assignmenet_set
3607: argument8 => 'PAYROLL_ACTION_ID='||p_actid, argument9 => NULL);
3608: hr_utility.set_location('Leaving: '||l_proc,999);
3609: END fps_rollback;
3610: --FPS RollBack End
3611: END PAY_GB_FPS_NI_AND_OTHERS;