[Home] [Help]
1504:
1505: -- Introduced the following for bug fix 2890110
1506: CURSOR adj_set_cur IS
1507: SELECT adj_set_number, SUM(distribution_sum)
1508: FROM psp_temp_dest_sumlines ptdl
1509: WHERE ptdl.run_id = p_run_id
1510: AND ptdl.business_group_id = p_business_group_id
1511: AND ptdl.set_of_books_id = p_set_of_books_id
1512: AND ptdl.dr_cr_flag = 'D'
1529: open show_elements;
1530: fetch show_elements into l_adjust_by;
1531: close show_elements;
1532: if l_adjust_by = 'E' then
1533: INSERT into psp_temp_dest_sumlines (
1534: acct_group_id, --- added four fields for DA-ENH
1535: adj_set_number,
1536: original_line_flag,
1537: line_number,
1616: attribute8,
1617: attribute9,
1618: attribute10;
1619: elsif l_adjust_by = 'G' then
1620: INSERT into psp_temp_dest_sumlines (
1621: acct_group_id, --- added four fields for DA-ENH
1622: adj_set_number,
1623: original_line_flag,
1624: line_number,
1705: a.attribute8,
1706: a.attribute9,
1707: a.attribute10;
1708: else
1709: INSERT into psp_temp_dest_sumlines (
1710: acct_group_id, --- added four fields for DA-ENH
1711: adj_set_number,
1712: original_line_flag,
1713: line_number,
1794: open show_elements;
1795: fetch show_elements into l_adjust_by;
1796: close show_elements;
1797: if l_adjust_by = 'E' then
1798: INSERT into psp_temp_dest_sumlines (
1799: acct_group_id, --- added four fields for DA-ENH
1800: adj_set_number,
1801: original_line_flag,
1802: line_number,
1848: award_id,
1849: -- dr_cr_flag, Commented for Bug 3625667
1850: percent;
1851: elsif l_adjust_by = 'G' then
1852: INSERT into psp_temp_dest_sumlines (
1853: acct_group_id, --- added four fields for DA-ENH
1854: adj_set_number,
1855: original_line_flag,
1856: line_number,
1904: a.award_id,
1905: -- a.dr_cr_flag, Commented for bug 3625667
1906: a.percent;
1907: else
1908: INSERT into psp_temp_dest_sumlines (
1909: acct_group_id, --- added four fields for DA-ENH
1910: adj_set_number,
1911: original_line_flag,
1912: line_number,
1967: LOOP
1968: FETCH adj_set_cur INTO l_adj_set_number, l_adj_set_total;
1969: EXIT WHEN adj_set_cur%NOTFOUND;
1970: --- changed g_precision to 2.. below...for 4992668
1971: UPDATE psp_temp_dest_sumlines ptdl
1972: SET distribution_percent = (ROUND((100 * ABS(distribution_sum) / l_adj_set_total), 2))
1973: WHERE ptdl.run_id = p_run_id
1974: AND ptdl.business_group_id = p_business_group_id
1975: AND ptdl.set_of_books_id = p_set_of_books_id
1975: AND ptdl.set_of_books_id = p_set_of_books_id
1976: AND ptdl.adj_set_number = l_adj_set_number;
1977: END LOOP;
1978: CLOSE adj_set_cur;
1979: UPDATE psp_temp_dest_sumlines ptdl
1980: SET distribution_percent = (-1 * distribution_percent)
1981: WHERE ptdl.run_id = p_run_id
1982: AND ptdl.business_group_id = p_business_group_id
1983: AND ptdl.set_of_books_id = p_set_of_books_id
2848: PTOS.element_type_id,
2849: PTOS.time_period_id,
2850: Sum(decode (PTOS.dr_cr_flag, 'D', PTOS.distribution_amount,
2851: -PTOS.distribution_amount))
2852: from psp_temp_dest_sumlines PTDS,
2853: psp_temp_orig_lines PTOS
2854: where PTDS.original_line_flag = 'Y'
2855: and PTDS.acct_group_id = PTOS.acct_group_id
2856: and PTDS.run_id = g_run_id
2933: DEST.distribution_sum,
2934: LINE.adj_set_number,
2935: LINE.line_number
2936: from psp_adjustment_lines LINE,
2937: psp_temp_dest_sumlines DEST
2938: where LINE.batch_name = p_batch_name and
2939: LINE.original_line_flag = 'N' and
2940: DEST.run_id = p_run_id and
2941: DEST.original_line_flag = 'N' and
3246: ptds.attribute7,
3247: ptds.attribute8,
3248: ptds.attribute9,
3249: ptds.attribute10
3250: from psp_temp_orig_lines ptol, psp_temp_dest_sumlines ptds
3251: where ptol.business_group_id = p_business_group_id
3252: and ptol.set_of_books_id = p_set_of_books_id
3253: and ptol.business_group_id = ptds.business_group_id
3254: and ptol.set_of_books_id = ptds.set_of_books_id
3424: dest.attribute7,
3425: dest.attribute8,
3426: dest.attribute9,
3427: dest.attribute10
3428: from psp_temp_dest_sumlines ORIG,
3429: psp_temp_dest_sumlines DEST,
3430: psp_temp_orig_lines LINES,
3431: -- Introduced the following for bug fix 3892097
3432: per_time_periods ptp,
3425: dest.attribute8,
3426: dest.attribute9,
3427: dest.attribute10
3428: from psp_temp_dest_sumlines ORIG,
3429: psp_temp_dest_sumlines DEST,
3430: psp_temp_orig_lines LINES,
3431: -- Introduced the following for bug fix 3892097
3432: per_time_periods ptp,
3433: (SELECT adj_set_number,
3431: -- Introduced the following for bug fix 3892097
3432: per_time_periods ptp,
3433: (SELECT adj_set_number,
3434: COUNT(DISTINCT DECODE(gl_code_combination_id, NULL, 'P', 'G')) transaction_type_count
3435: FROM psp_temp_dest_sumlines ptds
3436: WHERE ptds.run_id = g_run_id
3437: GROUP BY adj_set_number) trans_type
3438: -- End of changes for bug fix 3892097
3439: where ORIG.run_id = g_run_id and
3548: r_sline_actual_amnt_matrix.delta_sum.delete;
3549:
3550: --- bug fix 3470916
3551: -- The net of all differences between RHS amounts in
3552: -- psp_temp_dest_sumlines and psp_adjustment_lines will be zero.
3553: --- Logic is that for each dest sumlines, for wich it's sum doesnot equal
3554: --- the sum of dist amount in psp_adjustment_lines, adjust the difference
3555: -- to the last line in last time period of the element with max element_type_id
3556: --- there is no reason for this criteria in particular, but there should
3947: END IF;
3948:
3949: delete from psp_temp_orig_lines where run_id = p_run_id;
3950: delete from psp_temp_orig_sumlines where run_id = p_run_id;
3951: delete from psp_temp_dest_sumlines where run_id = p_run_id;
3952:
3953: -- 4992668
3954: psp_wf_adj_custom.prorate_dff_hook(p_batch_name,
3955: p_business_group_id,
4037: 2) Doesnt check for proper run_id.
4038: 3) Non-performant SQLs.
4039: delete from psp_temp_orig_lines;
4040: delete from psp_temp_orig_sumlines;
4041: delete from psp_temp_dest_sumlines;
4042: End of comment for bug fix 4507892 *****/
4043: g_error_api_path := 'GENERATE_LINES:'||g_error_api_path||sqlerrm;
4044: fnd_msg_pub.get(p_msg_index => FND_MSG_PUB.G_FIRST,
4045: p_encoded => FND_API.G_FALSE,