[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_select VARCHAR2(2000);
PROCEDURE create_main_query(p_rec_sla_detail IN VARCHAR2, p_select_mod IN VARCHAR2,
p_column_id IN NUMBER, p_main_sql OUT NOCOPY VARCHAR2);
SELECT CHART_OF_ACCOUNTS_ID
INTO g_coa_id
FROM gl_ledgers
where ledger_id = g_ledger_id;
/* SELECT period_year, period_num
INTO g_period_year, g_period_num
FROM gl_period_statuses
WHERE application_id = 101
AND set_of_books_id = g_ledger_id
AND period_name = g_period_name;
SELECT COUNT(1) INTO l_seed_count
FROM Fv_reimb_definitions_lines
WHERE set_of_books_id = g_ledger_id;
SELECT COUNT(1) INTO l_Acct_count
FROM Fv_reimb_definitions_lines fvrd ,
Fv_reimb_def_Acct_assign fvda
WHERE fvrd.column_id=fvda.column_id
AND fvrd.set_of_books_id = g_ledger_id;
DELETE FROM FV_REIMB_ACTIVITY_TEMP;
INSERT INTO FV_REIMB_ACTIVITY_TEMP(Reimb_Agreement_Num,
Contract_Number,
Billing_Limit,
start_date,
end_date,
amount_obligation,
amount_expended,
amount_unfilled_order,
amount_advance_collected,
amount_advance_applied,
amount_advance_balance,
amount_earned,
amount_billed,
amount_receivable_collected,
amount_receivable_balance,
amount_agreement,
REQUEST_ID,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
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,
reported_flag
)
(SELECT Reimb_Agreement_Num,
Contract_Number,
Billing_Limit,
start_date,
end_date,
sum(amount_obligation),
sum(amount_expended),
sum(amount_unfilled_order),
sum(amount_advance_collected),
sum(amount_advance_applied),
sum(amount_advance_balance),
sum(amount_earned),
sum(amount_billed),
sum(amount_receivable_collected),
sum(amount_receivable_balance),
sum(amount_agreement),
REQUEST_ID,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
SYSDATE,
SYSDATE,
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,
'Y'
FROM FV_REIMB_ACTIVITY_TEMP
WHERE reported_flag = 'N'
GROUP BY
Reimb_Agreement_Num,
Contract_Number,
Billing_Limit,
start_date,
end_date,
REQUEST_ID,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
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
having
sum(amount_obligation) <> 0 or
sum(amount_expended)<> 0 or
sum(amount_unfilled_order)<> 0 or
sum(amount_advance_collected)<> 0 or
sum(amount_advance_applied)<> 0 or
sum(amount_advance_balance)<> 0 or
sum(amount_earned)<> 0 or
sum(amount_billed)<> 0 or
sum(amount_receivable_collected)<> 0 or
sum(amount_receivable_balance)<> 0 or
sum(amount_agreement) <> 0
);
DELETE FROM FV_REIMB_ACTIVITY_TEMP WHERE reported_flag = 'N';
SELECT count(*)
INTO l_rec_count
FROM FV_REIMB_ACTIVITY_TEMP;
SELECT period_num, period_year
INTO g_period_num, g_period_year
FROM gl_period_statuses
WHERE period_name = g_period_name
AND application_id = 101
AND ledger_id = g_ledger_id;
SELECT flex_value_set_id
FROM fnd_id_flex_segments
WHERE application_id = 101
AND application_column_name =g_gl_nat_acc_segment
AND id_flex_code = g_id_flex_code
AND id_flex_num = g_coa_id
AND enabled_flag = 'Y' ;
SELECT application_column_name
INTO g_reimb_agreement_segment
FROM FND_ID_FLEX_SEGMENTS_VL
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = g_coa_id
AND enabled_flag = 'Y'
AND segment_name like
(Select REIMB_AGREEMENT_SEGMENT_VALUE
FROM fv_reimb_segment
where set_of_books_id = g_ledger_id);
SELECT application_column_name
FROM Fnd_Id_Flex_Segments
WHERE application_id = g_apps_id
AND id_flex_code = g_id_flex_code
AND id_flex_num = g_coa_id
ORDER BY segment_num;
SELECT segment_num
FROM Fnd_Id_Flex_Segments
WHERE application_id = g_apps_id
AND id_flex_code = g_id_flex_code
AND id_flex_num = g_coa_id
AND application_column_name=p_application_column_name ;
g_select := '';
g_select := g_select||' , glcc.segment'||i;
g_select := g_select||'glcc.segment'||i;
g_select:= g_select||' , glcc.'||gt_seg_name(i);
g_select:= g_select||'glcc.'||gt_seg_name(i);
log(C_STATE_LEVEL, l_module, 'Select statement is ' ||g_select);
l_select_mod VARCHAR2(2000);
SELECT rec_sla_detail, natural_balance_type
INTO l_rec_sla_detail, l_natural_balance_type
FROM fv_reimb_definitions_lines
WHERE column_id = l_column_id and set_of_books_id=g_ledger_id;
SELECT REPLACE(g_select, 'glcc.', '') INTO l_select_mod FROM dual;
log(C_STATE_LEVEL, l_module, 'l_select_mod : '||l_select_mod);
create_main_query(l_rec_sla_detail, l_select_mod, l_column_id, l_main_sql);
WHILE LENGTH(l_select_mod)>9 LOOP
SELECT substr(l_select_mod,1, instr(l_select_mod, ' , ')) INTO l_temp_segnumber FROM dual;
SELECT substr(l_select_mod, instr(l_select_mod, ' , ')+3) INTO l_select_mod FROM dual;
gt_seg_codes((to_number((substr(l_select_mod,8))))) := l_select_mod;
l_trx_sql:='SELECT h.trx_number, h.purchase_order,
h.start_date_commitment,
h.end_date_commitment,
l.extended_amount
FROM ra_customer_trx_all h,
ra_customer_trx_lines_all l
WHERE h.set_of_books_id = :g_ledger_id
AND h.trx_number = :g_agree_num
AND h.customer_trx_id = l.customer_trx_id';
log(C_STATE_LEVEL, l_module, 'Inserting values to FV_REIMB_ACTIVITY_TEMP with reported_flag = N ');
INSERT INTO FV_REIMB_ACTIVITY_TEMP (Reimb_Agreement_Num,
Contract_Number,
Billing_Limit,
start_date,
end_date,
amount_obligation,
amount_expended,
amount_unfilled_order,
amount_advance_collected,
amount_advance_applied,
amount_advance_balance,
amount_earned,
amount_billed,
amount_receivable_collected,
amount_receivable_balance,
amount_agreement,
REQUEST_ID,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
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,
reported_flag)
values(l_trx_number,
l_purchase_order,
l_extended_amount,
l_start_date_commitment,
l_end_date_commitment,
nvl(l_activity_rec.amount_obligation,0),
nvl(l_activity_rec.amount_expended,0),
nvl(l_activity_rec.amount_unfilled_order,0),
nvl(l_activity_rec.amount_advance_collected,0),
nvl(l_activity_rec.amount_advance_applied,0),
nvl(l_activity_rec.amount_advance_balance,0),
nvl(l_activity_rec.amount_earned,0),
nvl(l_activity_rec.amount_billed,0),
nvl(l_activity_rec.amount_receivable_collected,0),
nvl(l_activity_rec.amount_receivable_balance,0),
nvl(l_activity_rec.amount_agreement,0),
fnd_global.conc_request_id,
fnd_global.user_id,
fnd_global.login_id,
fnd_global.user_id,
SYSDATE,
SYSDATE,
l_activity_rec.segment1,
l_activity_rec.segment2,
l_activity_rec.segment3,
l_activity_rec.segment4,
l_activity_rec.segment5,
l_activity_rec.segment6,
l_activity_rec.segment7,
l_activity_rec.segment8,
l_activity_rec.segment9,
l_activity_rec.segment10,
l_activity_rec.segment11,
l_activity_rec.segment12,
l_activity_rec.segment13,
l_activity_rec.segment14,
l_activity_rec.segment15,
l_activity_rec.segment16,
l_activity_rec.segment17,
l_activity_rec.segment18,
l_activity_rec.segment19,
l_activity_rec.segment20,
l_activity_rec.segment21,
l_activity_rec.segment22,
l_activity_rec.segment23,
l_activity_rec.segment24,
l_activity_rec.segment25,
l_activity_rec.segment26,
l_activity_rec.segment27,
l_activity_rec.segment28,
l_activity_rec.segment29,
l_activity_rec.segment30,
'N'
);
SELECT flex_value_set_id
FROM fnd_id_flex_segments
WHERE application_id = 101
AND application_column_name =g_reimb_agreement_segment
AND id_flex_code = g_id_flex_code
AND id_flex_num = g_coa_id
AND enabled_flag = 'Y' ;
SELECT segment_num
FROM Fnd_Id_Flex_Segments
WHERE application_id = g_apps_id
AND id_flex_code = g_id_flex_code
AND id_flex_num = g_coa_id
AND application_column_name=p_application_column_name ;
g_agree_sql:='SELECT f.flex_value
FROM fnd_flex_values_vl f
where flex_value_set_id = :g_flex_reimb_value_id
AND flex_value BETWEEN '||''''||l_low_reimb||''''||' AND '||''''||l_high_reimb||''''||'
AND f.flex_value in (SELECT r.trx_number
FROM ra_customer_trx_all r,
ra_cust_trx_types_all t
WHERE r.set_of_books_id = :g_ledger_id
AND r.invoice_currency_code = :g_currency
AND r.cust_trx_type_id = t.cust_trx_type_id
AND t.type = ''GUAR'' )';
g_agree_sql:= 'SELECT f.flex_value
FROM fnd_flex_values_vl f
where flex_value_set_id = :g_flex_reimb_value_id
AND f.flex_value in(SELECT r.trx_number
FROM ra_customer_trx_all r,
ra_cust_trx_types_all t
WHERE r.set_of_books_id = :g_ledger_id
AND r.invoice_currency_code = :g_currency
AND r.cust_trx_type_id = t.cust_trx_type_id
AND t.type = ''GUAR'' )';
PROCEDURE create_main_query(p_rec_sla_detail IN VARCHAR2, p_select_mod IN VARCHAR2,
p_column_id IN NUMBER, p_main_sql OUT NOCOPY VARCHAR2) IS
l_bal_amount_sql VARCHAR2(1000);
l_glbal_sql := 'SELECT '||g_select||' , '||l_bal_amount_sql||'
FROM gl_balances glb,
gl_code_combinations_kfv glcc,
fv_reimb_definitions_lines frd,
fv_reimb_def_acct_assign fva
WHERE
frd.column_id = fva.column_id
and glb.actual_flag =''A''
--and glb.period_year = :g_period_year
--and glb.period_num = :g_period_num
and glb.ledger_id = :g_ledger_id
AND glb.template_id IS NULL
AND glb.currency_code = ''USD''
AND glb.code_combination_id = glcc.code_combination_id
AND ( glcc.'||g_gl_nat_acc_segment ||
' BETWEEN '|| ' fva.account_from ' ||
' AND '|| ' fva.account_to OR EXISTS '||
' (SELECT 1 FROM fnd_flex_value_hierarchies h '||
' WHERE glcc.'||g_gl_nat_acc_segment ||' BETWEEN' ||
' child_flex_value_low AND child_flex_value_high '||
' AND h.flex_value_set_id = :g_ussgl_flex_value_set_id' ||
' AND h.PARENT_FLEX_VALUE BETWEEN fva.account_from '||
' AND fva.account_to ))
AND frd.set_of_books_id= :g_ledger_id
AND frd.set_of_books_id =fva.set_of_books_id
AND glcc.chart_of_accounts_id = :g_coa_id
AND glcc.' || g_reimb_agreement_segment ||' = :l_agree_num
and frd.column_id = :l_column_id'||
g_where ||' GROUP BY '||g_select;
l_glbc_sql:= 'SELECT '||g_select||' , '||l_bc_amount_sql||'
FROM gl_bc_packets glbc,
gl_code_combinations_kfv glcc,
fv_reimb_definitions_lines frd,
fv_reimb_def_acct_assign fva
WHERE
frd.column_id = fva.column_id
and glbc.actual_flag =''A''
--and glbc.period_year = :g_period_year
--and glbc.period_num = :g_period_num
and glbc.ledger_id = :g_ledger_id
AND glbc.template_id IS NULL
AND glbc.status_code = ''A''
AND glbc.currency_code = ''USD''
AND glbc.code_combination_id = glcc.code_combination_id
AND ( glcc.'||g_gl_nat_acc_segment ||
' BETWEEN '|| ' fva.account_from ' ||
' AND '|| ' fva.account_to OR EXISTS '||
' (SELECT 1 FROM fnd_flex_value_hierarchies h '||
' WHERE glcc.'||g_gl_nat_acc_segment ||' BETWEEN '||
' child_flex_value_low AND child_flex_value_high '||
' AND h.flex_value_set_id = :g_ussgl_flex_value_set_id' ||
' AND h.PARENT_FLEX_VALUE BETWEEN fva.account_from '||
' AND fva.account_to ))
AND frd.set_of_books_id= :g_ledger_id
AND frd.set_of_books_id =fva.set_of_books_id
and glcc.chart_of_accounts_id = :g_coa_id
AND glcc.' || g_reimb_agreement_segment ||' = :l_agree_num
and frd.column_id = :l_column_id'||
g_where ||' GROUP BY '||g_select;
l_main_sql := 'SELECT '||p_select_mod||' , SUM(amount) net_amount FROM ( '
|| l_glbal_sql ||' UNION ALL ' || l_glbc_sql || ' ) GROUP BY ' ||p_select_mod;
'SELECT '||g_select||' , '||l_sla_amount_sql||'
FROM gl_code_combinations_kfv glcc,
fv_reimb_definitions_lines frd,
fv_reimb_def_acct_assign fva,
xla_ae_headers xah,
xla_ae_lines xal
WHERE
frd.column_id = fva.column_id
AND xah.ACCOUNTING_ENTRY_STATUS_CODE = ''F''
--AND xah.period_name = :g_period_name
AND xal.ledger_id = :g_ledger_id
AND xal.CURRENCY_CODE = ''USD''
AND xal.ae_header_id = xah.ae_header_id
AND xal.code_combination_id = glcc.code_combination_id
AND xal.application_id = xah.application_id
AND xal.ledger_id = xah.ledger_id
AND ( glcc.'||g_gl_nat_acc_segment ||
' BETWEEN '|| ' fva.account_from ' ||
' AND '|| ' fva.account_to OR EXISTS '||
' (SELECT 1 FROM fnd_flex_value_hierarchies h '||
' WHERE glcc.'||g_gl_nat_acc_segment
||' BETWEEN '|| ' child_flex_value_low AND child_flex_value_high '||
' AND h.flex_value_set_id = :g_ussgl_flex_value_set_id' ||
' AND h.PARENT_FLEX_VALUE BETWEEN fva.account_from '
|| ' AND fva.account_to ))
AND frd.set_of_books_id= :g_ledger_id
AND frd.set_of_books_id =fva.set_of_books_id
and glcc.chart_of_accounts_id = :g_coa_id
AND glcc.' || g_reimb_agreement_segment ||' = :l_agree_num
and frd.column_id = :l_column_id'
||g_where ||' GROUP BY fva.journal_side , '||g_select;