[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT flex.application_column_name
FROM fnd_id_flex_segments flex
WHERE flex.application_id = 101
AND flex.id_flex_code = 'GL#'
AND flex.id_flex_num = p_chart_accounts_id
AND flex.enabled_flag='Y'
AND flex.display_flag='Y'
order by segment_num;
PROCEDURE updateAccountValuesForForms(
p_report_header_id IN expDist_report_header_ID,
p_report_line_id IN expDist_REPORT_LINE_ID,
p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
p_ccid IN expDist_CODE_COMBINATION_ID,
p_line_amount IN NUMBER);
SELECT code_combination_id
INTO l_line_ccid
FROM ap_exp_report_dists aerd
WHERE aerd.report_distribution_id = p_report_distribution_id
AND aerd.code_combination_id is not null;
SELECT 'Y'
INTO l_temp
FROM ap_exp_report_dists rd
WHERE rd.report_distribution_id = p_report_distribution_id;
UPDATE ap_exp_report_dists RD
SET RD.code_combination_id = p_exp_line_ccid
WHERE RD.report_header_id = p_report_header_id
AND RD.report_line_id = p_report_line_id;
FUNCTION UpdateDistCCID(
p_report_header_id IN expDist_REPORT_HEADER_ID,
p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
p_exp_dist_ccid IN expDist_CODE_COMBINATION_ID
) RETURN BOOLEAN IS
--------------------------------------------------------------------------------
BEGIN
UPDATE ap_exp_report_dists RD
SET RD.code_combination_id = p_exp_dist_ccid
WHERE RD.report_header_id = p_report_header_id
AND RD.report_distribution_id = p_report_distribution_id;
AP_WEB_DB_UTIL_PKG.RaiseException('UpdateDistCCID');
END UpdateDistCCID;
PROCEDURE DeleteReportDistributions(P_ReportID IN expDist_report_header_ID) IS
--------------------------------------------------------------------------------
l_TempReportHeaderID expDist_report_header_ID;
l_curr_calling_sequence VARCHAR2(100) := 'DeleteReportDistributions';
SELECT REPORT_HEADER_ID
FROM AP_EXP_REPORT_DISTS
WHERE (REPORT_HEADER_ID = P_ReportID)
FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
DELETE AP_EXP_REPORT_DISTS WHERE CURRENT OF ReportDistributions;
END DeleteReportDistributions;
SELECT report_line_id
INTO l_has_dist
FROM ap_exp_report_dists
WHERE report_line_id = p_source_report_line_id
AND ROWNUM = 1;
UPDATE AP_EXP_REPORT_DISTS
SET report_header_id = p_target_report_header_id,
report_line_id = p_target_report_line_id
WHERE report_line_id = p_source_report_line_id;
INSERT INTO AP_EXP_REPORT_DISTS
(
report_header_id,
report_line_id,
report_distribution_id,
org_id,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
code_combination_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
amount,
project_id,
task_id,
award_id,
expenditure_organization_id,
cost_center,
preparer_modified_flag
)
SELECT
p_target_report_header_id AS report_header_id,
p_target_report_line_id AS report_line_id,
AP_EXP_REPORT_DISTS_S.NEXTVAL AS report_distribution_id,
org_id,
sequence_num,
SYSDATE AS last_update_date,
nvl(p_user_id,last_updated_by) AS last_updated_by,
SYSDATE AS creation_date,
nvl(p_user_id, created_by) AS created_by,
code_combination_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
amount,
project_id,
task_id,
award_id,
expenditure_organization_id,
cost_center,
preparer_modified_flag
FROM AP_EXP_REPORT_DISTS
WHERE
report_line_id = p_source_report_line_id;
PROCEDURE updateAccountValues(
p_report_header_id IN expDist_report_header_ID,
p_report_line_id IN expDist_REPORT_LINE_ID,
p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
p_ccid IN expDist_CODE_COMBINATION_ID)
IS
--------------------------------------------------------------------------------
l_sequence_num NUMBER := 0;
SELECT report_distribution_id
INTO l_report_distribution_id
FROM ap_exp_report_dists
WHERE report_header_id = p_report_header_id
AND report_line_id = p_report_line_id
AND rownum = 1;
AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
UPDATE ap_exp_report_dists
SET (code_combination_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
cost_center) = (SELECT nvl(code_combination_id,p_ccid),
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
AP_WEB_DB_EXPDIST_PKG.GetCCSegmentValue(p_ccid) -- Bug 7395568
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id = p_ccid)
WHERE report_distribution_id = l_report_distribution_id
AND report_header_id = p_report_header_id;
AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
END updateAccountValues;
PROCEDURE updateAccountValuesForForms(
p_report_header_id IN expDist_report_header_ID,
p_report_line_id IN expDist_REPORT_LINE_ID,
p_report_distribution_id IN expDist_REPORT_DISTRIBUTION_ID,
p_ccid IN expDist_CODE_COMBINATION_ID,
p_line_amount IN NUMBER)
IS
--------------------------------------------------------------------------------
l_sequence_num NUMBER := 0;
SELECT report_distribution_id
INTO l_report_distribution_id
FROM ap_exp_report_dists
WHERE report_header_id = p_report_header_id
AND report_line_id = p_report_line_id
AND rownum = 1;
AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
UPDATE ap_exp_report_dists
SET (code_combination_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
cost_center) = (SELECT nvl(code_combination_id,p_ccid),
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
AP_WEB_DB_EXPDIST_PKG.GetCCSegmentValue(p_ccid) -- Bug 7395568
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id = p_ccid),
amount = p_line_amount
WHERE report_distribution_id = l_report_distribution_id
AND report_header_id = p_report_header_id;
AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValuesForForms');
END updateAccountValuesForForms;
PROCEDURE updateDistAccountValues(
p_report_header_id IN expDist_report_header_ID)
IS
--------------------------------------------------------------------------------
CURSOR dist_lines_c IS
SELECT *
FROM AP_EXP_REPORT_DISTS_ALL
WHERE REPORT_HEADER_ID = p_report_header_id
FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
UPDATE ap_exp_report_dists_all
SET (segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
cost_center) = (SELECT segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
AP_WEB_DB_EXPDIST_PKG.GetCCSegmentValue(dist_lines_rec.CODE_COMBINATION_ID) -- Bug 7395568
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id = dist_lines_rec.CODE_COMBINATION_ID)
WHERE CURRENT OF dist_lines_c;
AP_WEB_DB_UTIL_PKG.RaiseException('updateDistAccountValues');
END updateDistAccountValues;
PROCEDURE updateDistAcctValuesForForms(
p_report_header_id IN expDist_report_header_ID)
IS
--------------------------------------------------------------------------------
l_report_distribution_id expDist_REPORT_DISTRIBUTION_ID;
SELECT report_distribution_id
INTO l_dist_id
FROM ap_exp_report_dists
WHERE report_header_id = p_report_header_id
AND report_line_id = l_report_line_id;
AP_WEB_DB_UTIL_PKG.RaiseException('updateAccountValues');
updateAccountValuesForForms(p_report_header_id, l_report_line_id, l_dist_id, l_line_ccid, l_line_amount);
END updateDistAcctValuesForForms;
INSERT INTO AP_EXP_REPORT_DISTS
(
report_header_id,
report_line_id,
report_distribution_id,
sequence_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
code_combination_id,
segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30,
org_id,
amount,
project_id,
task_id,
award_id,
expenditure_organization_id,
cost_center,
web_parameter_id
)
SELECT
XL.report_header_id,
XL.report_line_id,
AP_EXP_REPORT_DISTS_S.NEXTVAL,
l_sequence_num,
SYSDATE,
XL.last_updated_by,
SYSDATE,
XL.created_by,
XL.code_combination_id,
GL.segment1,
GL.segment2,
GL.segment3,
GL.segment4,
GL.segment5,
GL.segment6,
GL.segment7,
GL.segment8,
GL.segment9,
GL.segment10,
GL.segment11,
GL.segment12,
GL.segment13,
GL.segment14,
GL.segment15,
GL.segment16,
GL.segment17,
GL.segment18,
GL.segment19,
GL.segment20,
GL.segment21,
GL.segment22,
GL.segment23,
GL.segment24,
GL.segment25,
GL.segment26,
GL.segment27,
GL.segment28,
GL.segment29,
GL.segment30,
XL.org_id,
XL.amount,
XL.project_id,
XL.task_id,
XL.award_id,
XL.expenditure_organization_id,
XH.flex_concatenated, -- Bug: 6735020, flex_concatenated should be fetched from headers
XL.web_parameter_id
FROM AP_EXPENSE_REPORT_LINES XL,
AP_EXPENSE_REPORT_HEADERS XH,
GL_CODE_COMBINATIONS GL
WHERE XL.report_line_id = p_report_line_id
AND XL.report_header_id = XH.report_header_id
AND GL.code_combination_id(+) = XL.code_combination_id;
l_last_updated_by NUMBER;
l_debug_info := 'select from ap_expense_report_lines';
SELECT XL.report_header_id,
XL.code_combination_id,
XL.last_updated_by,
XL.created_by,
XL.org_id,
XL.amount,
XL.project_id,
XL.task_id,
XL.award_id,
XL.expenditure_organization_id,
XH.flex_concatenated -- Bug 6735020, flex_concatenated should be fetched from headers
INTO l_report_header_id,
l_code_combination_id,
l_last_updated_by,
l_created_by,
l_org_id,
l_amount,
l_project_id,
l_task_id,
l_award_id,
l_expenditure_organization_id,
l_cost_center
FROM ap_expense_report_lines XL,
ap_expense_report_headers XH
WHERE XL.report_line_id = p_report_line_id
AND XL.report_header_id = XH.report_header_id;
SELECT AP_EXP_REPORT_DISTS_S.NEXTVAL
INTO l_report_distribution_id
FROM DUAL;
l_sqlstmt := 'INSERT INTO AP_EXP_REPORT_DISTS ( ';
l_sqlstmt := l_sqlstmt || 'last_update_date, ';
l_sqlstmt := l_sqlstmt || 'last_updated_by, ';
/* BUG 7025517 -- refer to bind vars in values clause of insert statement */
/* l_sqlstmt := l_sqlstmt || l_report_header_id;
** l_sqlstmt := l_sqlstmt || ', ' || l_last_updated_by;
/* BUG 7025517 -- refer to bind vars in values clause of insert statement */
l_sqlstmt := l_sqlstmt || ' :l_report_header_id' ;
l_sqlstmt := l_sqlstmt || ', :l_last_updated_by' ;
dbms_sql.bind_variable(l_cursor,':l_last_updated_by', l_last_updated_by) ;
SELECT 'dist exists'
INTO V_Temp
FROM AP_EXP_REPORT_DISTS
WHERE REPORT_HEADER_ID = p_report_header_id
AND PROJECT_ID IS NOT NULL
AND TASK_ID IS NOT NULL;
SELECT 'dist exists'
INTO V_Temp
FROM AP_EXP_REPORT_DISTS
WHERE REPORT_HEADER_ID = p_report_header_id
AND PROJECT_ID IS NULL
AND TASK_ID IS NULL;
select report_distribution_id, amount
from ap_exp_report_dists_all
where report_line_id = p_report_line_id;
select sum(amount) into l_dist_sum
from ap_exp_report_dists_all
where report_line_id = p_report_line_id;
update ap_exp_report_dists_all set amount = l_dist_amt
where report_distribution_id = l_dist_id;
update ap_exp_report_dists_all set amount = (amount + l_remainder)
where report_distribution_id = l_last_dist_id;
EXECUTE IMMEDIATE 'select '|| l_cc_segment_name || ' from gl_code_combinations where code_combination_id = :1'
into l_segment_value
using p_ccid;