[Home] [Help]
1495:
1496: -- Introduced the following for bug fix 2890110
1497: CURSOR adj_set_cur IS
1498: SELECT adj_set_number, SUM(distribution_sum)
1499: FROM psp_temp_dest_sumlines ptdl
1500: WHERE ptdl.run_id = p_run_id
1501: AND ptdl.business_group_id = p_business_group_id
1502: AND ptdl.set_of_books_id = p_set_of_books_id
1503: AND ptdl.dr_cr_flag = 'D'
1520: open show_elements;
1521: fetch show_elements into l_adjust_by;
1522: close show_elements;
1523: if l_adjust_by = 'E' then
1524: INSERT into psp_temp_dest_sumlines (
1525: acct_group_id, --- added four fields for DA-ENH
1526: adj_set_number,
1527: original_line_flag,
1528: line_number,
1607: attribute8,
1608: attribute9,
1609: attribute10;
1610: elsif l_adjust_by = 'G' then
1611: INSERT into psp_temp_dest_sumlines (
1612: acct_group_id, --- added four fields for DA-ENH
1613: adj_set_number,
1614: original_line_flag,
1615: line_number,
1696: a.attribute8,
1697: a.attribute9,
1698: a.attribute10;
1699: else
1700: INSERT into psp_temp_dest_sumlines (
1701: acct_group_id, --- added four fields for DA-ENH
1702: adj_set_number,
1703: original_line_flag,
1704: line_number,
1785: open show_elements;
1786: fetch show_elements into l_adjust_by;
1787: close show_elements;
1788: if l_adjust_by = 'E' then
1789: INSERT into psp_temp_dest_sumlines (
1790: acct_group_id, --- added four fields for DA-ENH
1791: adj_set_number,
1792: original_line_flag,
1793: line_number,
1839: award_id,
1840: -- dr_cr_flag, Commented for Bug 3625667
1841: percent;
1842: elsif l_adjust_by = 'G' then
1843: INSERT into psp_temp_dest_sumlines (
1844: acct_group_id, --- added four fields for DA-ENH
1845: adj_set_number,
1846: original_line_flag,
1847: line_number,
1895: a.award_id,
1896: -- a.dr_cr_flag, Commented for bug 3625667
1897: a.percent;
1898: else
1899: INSERT into psp_temp_dest_sumlines (
1900: acct_group_id, --- added four fields for DA-ENH
1901: adj_set_number,
1902: original_line_flag,
1903: line_number,
1958: LOOP
1959: FETCH adj_set_cur INTO l_adj_set_number, l_adj_set_total;
1960: EXIT WHEN adj_set_cur%NOTFOUND;
1961: --- changed g_precision to 2.. below...for 4992668
1962: UPDATE psp_temp_dest_sumlines ptdl
1963: SET distribution_percent = (ROUND((100 * ABS(distribution_sum) / l_adj_set_total), 2))
1964: WHERE ptdl.run_id = p_run_id
1965: AND ptdl.business_group_id = p_business_group_id
1966: AND ptdl.set_of_books_id = p_set_of_books_id
1966: AND ptdl.set_of_books_id = p_set_of_books_id
1967: AND ptdl.adj_set_number = l_adj_set_number;
1968: END LOOP;
1969: CLOSE adj_set_cur;
1970: UPDATE psp_temp_dest_sumlines ptdl
1971: SET distribution_percent = (-1 * distribution_percent)
1972: WHERE ptdl.run_id = p_run_id
1973: AND ptdl.business_group_id = p_business_group_id
1974: AND ptdl.set_of_books_id = p_set_of_books_id
2839: PTOS.element_type_id,
2840: PTOS.time_period_id,
2841: Sum(decode (PTOS.dr_cr_flag, 'D', PTOS.distribution_amount,
2842: -PTOS.distribution_amount))
2843: from psp_temp_dest_sumlines PTDS,
2844: psp_temp_orig_lines PTOS
2845: where PTDS.original_line_flag = 'Y'
2846: and PTDS.acct_group_id = PTOS.acct_group_id
2847: and PTDS.run_id = g_run_id
2924: DEST.distribution_sum,
2925: LINE.adj_set_number,
2926: LINE.line_number
2927: from psp_adjustment_lines LINE,
2928: psp_temp_dest_sumlines DEST
2929: where LINE.batch_name = p_batch_name and
2930: LINE.original_line_flag = 'N' and
2931: DEST.run_id = p_run_id and
2932: DEST.original_line_flag = 'N' and
3237: ptds.attribute7,
3238: ptds.attribute8,
3239: ptds.attribute9,
3240: ptds.attribute10
3241: from psp_temp_orig_lines ptol, psp_temp_dest_sumlines ptds
3242: where ptol.business_group_id = p_business_group_id
3243: and ptol.set_of_books_id = p_set_of_books_id
3244: and ptol.business_group_id = ptds.business_group_id
3245: and ptol.set_of_books_id = ptds.set_of_books_id
3415: dest.attribute7,
3416: dest.attribute8,
3417: dest.attribute9,
3418: dest.attribute10
3419: from psp_temp_dest_sumlines ORIG,
3420: psp_temp_dest_sumlines DEST,
3421: psp_temp_orig_lines LINES,
3422: -- Introduced the following for bug fix 3892097
3423: per_time_periods ptp,
3416: dest.attribute8,
3417: dest.attribute9,
3418: dest.attribute10
3419: from psp_temp_dest_sumlines ORIG,
3420: psp_temp_dest_sumlines DEST,
3421: psp_temp_orig_lines LINES,
3422: -- Introduced the following for bug fix 3892097
3423: per_time_periods ptp,
3424: (SELECT adj_set_number,
3422: -- Introduced the following for bug fix 3892097
3423: per_time_periods ptp,
3424: (SELECT adj_set_number,
3425: COUNT(DISTINCT DECODE(gl_code_combination_id, NULL, 'P', 'G')) transaction_type_count
3426: FROM psp_temp_dest_sumlines ptds
3427: WHERE ptds.run_id = g_run_id
3428: GROUP BY adj_set_number) trans_type
3429: -- End of changes for bug fix 3892097
3430: where ORIG.run_id = g_run_id and
3539: r_sline_actual_amnt_matrix.delta_sum.delete;
3540:
3541: --- bug fix 3470916
3542: -- The net of all differences between RHS amounts in
3543: -- psp_temp_dest_sumlines and psp_adjustment_lines will be zero.
3544: --- Logic is that for each dest sumlines, for wich it's sum doesnot equal
3545: --- the sum of dist amount in psp_adjustment_lines, adjust the difference
3546: -- to the last line in last time period of the element with max element_type_id
3547: --- there is no reason for this criteria in particular, but there should
3938: END IF;
3939:
3940: delete from psp_temp_orig_lines where run_id = p_run_id;
3941: delete from psp_temp_orig_sumlines where run_id = p_run_id;
3942: delete from psp_temp_dest_sumlines where run_id = p_run_id;
3943:
3944: -- 4992668
3945: psp_wf_adj_custom.prorate_dff_hook(p_batch_name,
3946: p_business_group_id,
4028: 2) Doesnt check for proper run_id.
4029: 3) Non-performant SQLs.
4030: delete from psp_temp_orig_lines;
4031: delete from psp_temp_orig_sumlines;
4032: delete from psp_temp_dest_sumlines;
4033: End of comment for bug fix 4507892 *****/
4034: g_error_api_path := 'GENERATE_LINES:'||g_error_api_path||sqlerrm;
4035: fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
4036: p_encoded => FND_API.G_FALSE,