[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_EXCEPTION_RECS;
PROCEDURE UPDATE_FACTS1_RUN;
SELECT period_year
INTO l_period_year
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = p_sob_id
AND period_name = p_period_name;
SELECT count(*)
INTO l_no_fed_account
FROM fv_facts1_fed_accounts
WHERE set_of_books_id = p_sob_id
AND fiscal_year = l_period_year;
insert_exception_recs;
update_facts1_run;
DELETE FROM fv_facts_report_t2
WHERE set_of_books_id = gbl_sob_id;
SELECT period_year
INTO gbl_period_year
FROM gl_period_statuses p
WHERE p.application_id = 101
AND p.ledger_id = gbl_sob_id
AND p.period_name = gbl_period_name;
SELECT MIN(period_num)
INTO gbl_period_num_low
FROM gl_period_statuses
WHERE period_year = gbl_period_year
AND application_id = 101
AND closing_status <> 'F'
AND closing_status <> 'N'
AND adjustment_period_flag = 'N'
AND ledger_id = gbl_sob_id;
SELECT period_num
INTO gbl_period_num_high
FROM gl_period_statuses p
WHERE period_name = gbl_period_name
AND p.application_id = 101
AND p.ledger_id = gbl_sob_id
AND p.period_year = gbl_period_year;
SELECT factsI_journal_attribute
INTO gbl_jrnl_attribute
FROM fv_system_parameters;
SELECT factsI_vendor_attribute, factsI_customer_attribute
INTO gbl_vend_attribute, gbl_cust_attribute
FROM fv_system_parameters;
SELECT currency_code, chart_of_accounts_id
INTO gbl_currency_code, gbl_coa_id
FROM gl_ledgers_public_v
WHERE ledger_id = gbl_sob_id;
l_select_stmt VARCHAR2(20000);
FV_UTILITY.LOG_MESG('Inserting into fv_facts1_header_id_gt');
INSERT INTO fv_facts1_header_id_gt(je_header_id,set_of_books_id)
select gjh.je_header_id , gjh.ledger_id
from
(SELECT period_num, period_name
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = gbl_sob_id
AND period_num BETWEEN gbl_period_num_low AND gbl_period_num_high
AND period_year = gbl_period_year) gps,
gl_je_headers gjh
where gjh.period_name = gps.period_name
and gjh.ledger_id = gbl_sob_id
AND gjh.status = 'P'
AND gjh.actual_flag = 'A'
and not exists (select 'x' from fv_facts1_processed_je_hdrs e
where e.set_of_books_id = gjh.ledger_id
and e.je_header_id = gjh.je_header_id) ;
FV_UTILITY.LOG_MESG('Inserted ' || SQL%ROWCOUNT);
l_select_stmt :=
' gjl.code_combination_id,
gjh.ledger_id,
glcc.'||gbl_acc_segment|| ',
(NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0) ) amount,
DECODE( SIGN (NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0)) , -1, ''C'', ''D'') d_c_indicator,
gjh.je_header_id,
gjl.je_line_num,
gjh.je_category,
gjh.je_source,
gjl.reference_1,
gjl.reference_2,
gjl.reference_3,
gjl.reference_5,
gjl.reference_7,
gjl.'||gbl_jrnl_attribute|| ',
glcc.'||gbl_bal_segment|| ',
gjl.period_name,
NULL party_id,
NULL party_type,
NULL party_classification,
NULL recipient_name,
NULL eliminations_dept,
fff.fed_non_fed,
NULL reported_status,
NULL record_category,
NULL feeder_flag,
gps.period_num,
NULL g_ng_indicator,
NVL(gjh.je_from_sla_flag, ''N''),
gjh.je_batch_id ';
l_select_stmt := 'SELECT ' || l_select_stmt ||
' FROM
fv_facts1_fed_accounts fff,
gl_code_combinations glcc,
( SELECT period_num, period_name
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = :gbl_sob_id
AND period_num BETWEEN :gbl_period_num_low AND :gbl_period_num_high
AND period_year = :gbl_period_year ) gps,
gl_je_lines gjl,
gl_je_headers gjh
WHERE fff.jc_flag = ''N''
AND fff.set_of_books_id = :gbl_sob_id
AND fff.fiscal_year = :gbl_period_year
AND glcc.'||gbl_acc_segment || ' = fff.account_number
AND glcc.chart_of_accounts_id = :gbl_coa_id
AND gjl.code_combination_id = glcc.code_combination_id
AND gjl.period_name = gps.period_name
AND gjl.ledger_id = :gbl_sob_id
AND gjl.je_header_id = gjh.je_header_id
AND gjh.currency_code <> ''STAT''
AND gjh.ledger_id = :gbl_sob_id
AND gjh.status = ''P''
AND gjh.actual_flag = ''A'' ' ;
OPEN l_gl_lines_cur FOR l_select_stmt using
gbl_sob_id,gbl_period_num_low ,gbl_period_num_high,gbl_period_year,
gbl_sob_id,gbl_period_year ,gbl_coa_id,gbl_sob_id,gbl_sob_id;
fv_utility.log_mesg(l_select_stmt);
l_select_stmt := 'SELECT /*+ ORDERED INDEX(gjh GL_JE_HEADERS_U1, ftt
FV_FACTS1_HEADER_ID_GT_U1, gjl GL_JE_LINES_U1,
glcc GL_CODE_COMBINATIONS_U1,
fff FV_FACTS1_FED_ACCOUNTS_U1) */ ' ||l_select_stmt ||
' FROM fv_facts1_header_id_gt ftt
, gl_je_headers gjh
, gl_je_lines gjl
, (SELECT period_num
, period_name
FROM gl_period_statuses ps
WHERE application_id = 101
AND ledger_id = :gbl_sob_id
AND period_num BETWEEN :gbl_period_num_low AND :gbl_period_num_high
AND period_year = :gbl_period_year) gps
, gl_code_combinations glcc
, fv_facts1_fed_accounts fff
WHERE gjh.period_name = gps.period_name
AND gjl.ledger_id = :gbl_sob_id
AND gjl.je_header_id = ftt.je_header_id
AND gjh.currency_code <> ''STAT''
AND gjh.status = ''P''
AND gjh.actual_flag = ''A''
and gjh.je_header_id = ftt.je_header_id
and ftt.set_of_books_id = :gbl_sob_id
AND gjh.ledger_id = :gbl_sob_id
AND glcc.code_combination_id = gjl.code_combination_id
AND glcc.chart_of_accounts_id = :gbl_coa_id
AND fff.account_number = glcc.' ||gbl_acc_segment || '
AND fff.set_of_books_id = :gbl_sob_id
AND fff.fiscal_year = :gbl_period_year' ;
fv_utility.log_mesg(l_select_stmt);
OPEN l_gl_lines_cur FOR l_select_stmt using
gbl_sob_id,gbl_period_num_low ,gbl_period_num_high,gbl_period_year,
gbl_sob_id,gbl_sob_id, gbl_sob_id, gbl_coa_id,
gbl_sob_id, gbl_period_year;
INSERT INTO fv_facts1_line_balances
( ccid,
period_num,
set_of_books_id,
period_year,
account_number,
fund_value,
amount,
d_c_indicator,
g_ng_indicator,
eliminations_dept,
record_category,
recipient_name,
period_name,
je_header_id,
je_line_num,
je_category,
je_source,
party_id,
party_type,
party_classification,
attribute_value,
balance_type,
feeder_flag,
gl_period,
creation_date)
VALUES (
ccid_list_2(i),
period_num_list_2(i),
gbl_sob_id,
gbl_period_year,
account_number_list_2(i),
fund_value_list_2(i),
amount_list_2(i),
d_c_indicator_list_2(i),
g_ng_indicator_list_2(i),
eliminations_dept_list_2(i),
record_category_list_2(i),
recipient_name_list_2(i),
gbl_period_name,
je_header_id_list_2(i),
je_line_num_list_2(i),
je_category_list_2(i),
je_source_list_2(i),
party_id_list_2(i),
party_type_list_2(i),
vendor_type_list_2(i),
attribute_value_list_2(i),
'L',
feeder_flag_list_2(i),
gl_period_list_2(i),
sysdate);
INSERT INTO fv_facts1_header_id_gt
( je_header_id,
set_of_books_id)
VALUES
(je_header_id_list_new(i),
gbl_sob_id);
SELECT v.vendor_id, v.vendor_type_lookup_code,
DECODE(gbl_vend_attribute, 'ATTRIBUTE1', V.ATTRIBUTE1,
'ATTRIBUTE2', V.ATTRIBUTE2, 'ATTRIBUTE3', V.ATTRIBUTE3,
'ATTRIBUTE4', V.ATTRIBUTE4, 'ATTRIBUTE5', V.ATTRIBUTE5,
'ATTRIBUTE6', V.ATTRIBUTE6, 'ATTRIBUTE7', V.ATTRIBUTE7,
'ATTRIBUTE8', V.ATTRIBUTE8, 'ATTRIBUTE9', V.ATTRIBUTE9,
'ATTRIBUTE10', V.ATTRIBUTE10, 'ATTRIBUTE11', V.ATTRIBUTE11,
'ATTRIBUTE12', V.ATTRIBUTE12, 'ATTRIBUTE13', V.ATTRIBUTE13,
'ATTRIBUTE14', V.ATTRIBUTE14, 'ATTRIBUTE15', V.ATTRIBUTE15)
eliminations_id,
v.vendor_name
INTO l_vendor_id, l_vendor_type,
l_elim_dept,
l_recipient_name
FROM ap_invoices_all i,
po_vendors v
WHERE i.invoice_id = to_number(p_reference_2)
AND i.vendor_id = v.vendor_id;
SELECT v.vendor_id,
v.vendor_type_lookup_code,
DECODE(gbl_vend_attribute, 'ATTRIBUTE1', V.ATTRIBUTE1,
'ATTRIBUTE2', V.ATTRIBUTE2, 'ATTRIBUTE3', V.ATTRIBUTE3,
'ATTRIBUTE4', V.ATTRIBUTE4, 'ATTRIBUTE5', V.ATTRIBUTE5,
'ATTRIBUTE6', V.ATTRIBUTE6, 'ATTRIBUTE7', V.ATTRIBUTE7,
'ATTRIBUTE8', V.ATTRIBUTE8, 'ATTRIBUTE9', V.ATTRIBUTE9,
'ATTRIBUTE10', V.ATTRIBUTE10, 'ATTRIBUTE11', V.ATTRIBUTE11,
'ATTRIBUTE12', V.ATTRIBUTE12, 'ATTRIBUTE13', V.ATTRIBUTE13,
'ATTRIBUTE14', V.ATTRIBUTE14, 'ATTRIBUTE15', V.ATTRIBUTE15)
eliminations_id, v.vendor_name
INTO l_vendor_id, l_vendor_type, l_elim_dept,
l_recipient_name
FROM rcv_transactions rt,
po_vendors v,
po_headers_all ph
WHERE rt.po_header_id = to_number(p_reference_2)
AND rt.transaction_id = to_number(p_reference_5)
AND rt.po_header_id = ph.po_header_id
AND v.vendor_id = ph.vendor_id;
SELECT v.vendor_id,
v.vendor_type_lookup_code,
DECODE(gbl_vend_attribute, 'ATTRIBUTE1', V.ATTRIBUTE1,
'ATTRIBUTE2', V.ATTRIBUTE2, 'ATTRIBUTE3', V.ATTRIBUTE3,
'ATTRIBUTE4', V.ATTRIBUTE4, 'ATTRIBUTE5', V.ATTRIBUTE5,
'ATTRIBUTE6', V.ATTRIBUTE6, 'ATTRIBUTE7', V.ATTRIBUTE7,
'ATTRIBUTE8', V.ATTRIBUTE8, 'ATTRIBUTE9', V.ATTRIBUTE9,
'ATTRIBUTE10', V.ATTRIBUTE10, 'ATTRIBUTE11', V.ATTRIBUTE11,
'ATTRIBUTE12', V.ATTRIBUTE12, 'ATTRIBUTE13', V.ATTRIBUTE13,
'ATTRIBUTE14', V.ATTRIBUTE14,
'ATTRIBUTE15', V.ATTRIBUTE15) eliminations_id,
v.vendor_name
INTO l_vendor_id,
l_vendor_type,
l_elim_dept,
l_recipient_name
FROM po_vendors v, po_headers_all poh
WHERE poh.po_header_id = to_number(p_reference_2)
AND v.vendor_id = poh.vendor_id;
SELECT c.customer_class_code,
DECODE(gbl_cust_attribute, 'ATTRIBUTE1', C.ATTRIBUTE1,
'ATTRIBUTE2', C.ATTRIBUTE2, 'ATTRIBUTE3', C.ATTRIBUTE3,
'ATTRIBUTE4', C.ATTRIBUTE4, 'ATTRIBUTE5', C.ATTRIBUTE5,
'ATTRIBUTE6', C.ATTRIBUTE6, 'ATTRIBUTE7', C.ATTRIBUTE7,
'ATTRIBUTE8', C.ATTRIBUTE8, 'ATTRIBUTE9', C.ATTRIBUTE9,
'ATTRIBUTE10', C.ATTRIBUTE10, 'ATTRIBUTE11', C.ATTRIBUTE11,
'ATTRIBUTE12', C.ATTRIBUTE12, 'ATTRIBUTE13', C.ATTRIBUTE13,
'ATTRIBUTE14', C.ATTRIBUTE14,
'ATTRIBUTE15', C.ATTRIBUTE15) eliminations_id,
c.account_name
INTO l_vendor_type, l_elim_dept, l_recipient_name
FROM hz_cust_accounts_all c
WHERE c.cust_account_id = to_number(p_reference_7);
SELECT v.vendor_id,
v.vendor_type_lookup_code,
DECODE(gbl_vend_attribute, 'ATTRIBUTE1', V.ATTRIBUTE1,
'ATTRIBUTE2', V.ATTRIBUTE2, 'ATTRIBUTE3', V.ATTRIBUTE3,
'ATTRIBUTE4', V.ATTRIBUTE4, 'ATTRIBUTE5', V.ATTRIBUTE5,
'ATTRIBUTE6', V.ATTRIBUTE6, 'ATTRIBUTE7', V.ATTRIBUTE7,
'ATTRIBUTE8', V.ATTRIBUTE8, 'ATTRIBUTE9', V.ATTRIBUTE9,
'ATTRIBUTE10', V.ATTRIBUTE10, 'ATTRIBUTE11', V.ATTRIBUTE11,
'ATTRIBUTE12', V.ATTRIBUTE12, 'ATTRIBUTE13', V.ATTRIBUTE13,
'ATTRIBUTE14', V.ATTRIBUTE14,
'ATTRIBUTE15', V.ATTRIBUTE15) eliminations_id,
v.vendor_name
INTO l_vendor_id, l_vendor_type, l_elim_dept,
l_recipient_name
FROM ap_checks_all apc,
po_vendors v
WHERE apc.vendor_id = v.vendor_id
AND apc.check_id = to_number(p_reference_3);
SELECT decode(gbl_trading_partner_att,
null , dept_id|| main_account,
SUBSTR(DECODE(upper(gbl_trading_partner_att),
'ATTRIBUTE1', attribute1,
'ATTRIBUTE2', attribute2,
'ATTRIBUTE3', attribute3,
'ATTRIBUTE4', attribute4,
'ATTRIBUTE5', attribute5,
'ATTRIBUTE6', attribute6,
'ATTRIBUTE7', attribute7,
'ATTRIBUTE8', attribute8,
'ATTRIBUTE9', attribute9,
'ATTRIBUTE10', attribute10,
'ATTRIBUTE11', attribute11,
'ATTRIBUTE12', attribute12,
'ATTRIBUTE13', attribute13,
'ATTRIBUTE14', attribute14,
'ATTRIBUTE15', attribute15),1,6))
INTO l_elim_dept
FROM fv_be_trx_dtls
WHERE transaction_id = l_be_trx_id;
party_info_tab.delete;
PROCEDURE INSERT_EXCEPTION_RECS
IS
l_module_name VARCHAR2(100);
l_module_name := g_module_name||'INSERT_EXCEPTION_RECS';
l_stmt := ' select substr(rpad(l.record_category, 14,'' ''),1,14) || '' '' ||
substr(rpad(b.name, 40, '' ''),1,40) || '' '' ||
substr(rpad(h.je_category, 15, '' ''),1,15) || '' '' ||
substr(rpad(l.account_number, 9, '' ''),1,9) || '' '' ||
substr(rpad(l.fund_value, 20, '' ''),1,20) || '' '' ||
to_char( amount,''99,999,999,999,999,999.99'') ' ||
' FROM fv_facts1_header_id_gt f ,
gl_je_headers h,
fv_facts1_line_balances l,
gl_je_batches b
where h.je_header_id = f.je_header_id
and l.je_header_id = h.je_header_id
and l.record_category IN (''NO_VENDOR'', ''G_NONFED_VENDOR'')
AND l.set_of_books_id = ' || gbl_sob_id || '
AND l.period_year = ' || gbl_period_year || '
and b.je_batch_id = h.je_batch_id
ORDER BY l.record_category, h.je_category ';
END insert_exception_recs;
PROCEDURE UPDATE_FACTS1_RUN IS
l_module_name VARCHAR2(200);
l_module_name := g_module_name || 'UPDATE_FACTS1_RUN';
fv_utility.log_mesg('Inserting processed headers ' );
INSERT INTO fv_facts1_processed_je_hdrs(je_header_id,set_of_books_id)
SELECT DISTINCT je_header_id,set_of_books_id
FROM fv_facts1_header_id_gt;
Insert into fv_facts1_processed_je_hdrs(je_header_id,set_of_books_id)
select je_header_id,set_of_books_id
from fv_facts1_header_id_gt;
fv_utility.log_mesg('Inserted ' || SQL%ROWCOUNT);
UPDATE fv_facts1_run
SET process_date = sysdate,
jc_run_month = gbl_period_num_high,
run_fed_flag = 'J'
WHERE set_of_books_id = gbl_sob_id
AND fiscal_year = gbl_period_year
AND table_indicator = 'N';
UPDATE fv_facts1_fed_accounts
SET jc_flag = 'Y'
WHERE set_of_books_id = gbl_sob_id
AND fiscal_year = gbl_period_year;
'When others error in UPDATE_FACTS1_RUN - '||SQLERRM;
END UPDATE_FACTS1_RUN;
SELECT xd.source_distribution_id_num_1 transaction_id,
(NVL(xd.unrounded_accounted_dr,0) -
NVL(xd.unrounded_accounted_cr,0)) amount
FROM gl_import_references gli,
xla_ae_lines xl,
xla_ae_headers xh,
xla_distribution_links xd
WHERE gli.je_batch_id = p_je_batch_id
AND gli.je_header_id = p_je_header_id
AND gli.je_line_num = p_je_line_num
AND xl.gl_sl_link_id = gli.gl_sl_link_id
AND xl.application_id = 8901
AND xh.ae_header_id = xl.ae_header_id
AND xl.ledger_id = gbl_sob_id
AND xd.event_id = xh.event_id
and xd.ae_header_id = xh.ae_header_id
and xd.ae_line_num = xl.ae_line_num;
SELECT ael.party_id,
NVL(ael.accounted_dr,0) -
NVL(ael.accounted_cr,0) amount
FROM gl_import_references i,
xla_ae_lines ael
WHERE i.je_batch_id = p_je_batch_id
AND i.je_header_id = p_je_header_id
AND i.je_line_num = p_je_line_num
AND i.gl_sl_link_id = ael.gl_sl_link_id
AND ael.application_id = p_application_id
AND ael.ledger_id = gbl_sob_id;
SELECT vendor_id, vendor_type_lookup_code,
DECODE(gbl_vend_attribute, 'ATTRIBUTE1', ATTRIBUTE1,
'ATTRIBUTE2', ATTRIBUTE2, 'ATTRIBUTE3', ATTRIBUTE3,
'ATTRIBUTE4', ATTRIBUTE4, 'ATTRIBUTE5', ATTRIBUTE5,
'ATTRIBUTE6', ATTRIBUTE6, 'ATTRIBUTE7', ATTRIBUTE7,
'ATTRIBUTE8', ATTRIBUTE8, 'ATTRIBUTE9', ATTRIBUTE9,
'ATTRIBUTE10', ATTRIBUTE10, 'ATTRIBUTE11', ATTRIBUTE11,
'ATTRIBUTE12', ATTRIBUTE12, 'ATTRIBUTE13', ATTRIBUTE13,
'ATTRIBUTE14', ATTRIBUTE14, 'ATTRIBUTE15', ATTRIBUTE15)
eliminations_id, vendor_name, other_source_rec.amount
INTO p_party_info_tab(i).party_id, p_party_info_tab(i).vendor_type,
P_party_info_tab(i).elim_dept, p_party_info_tab(i).recipient_name,
P_party_info_tab(i).party_line_amount
FROM ap_suppliers
WHERE vendor_id = other_source_rec.party_id;
SELECT c.party_id, c.customer_class_code,
DECODE(gbl_cust_attribute, 'ATTRIBUTE1', C.ATTRIBUTE1,
'ATTRIBUTE2', C.ATTRIBUTE2, 'ATTRIBUTE3', C.ATTRIBUTE3,
'ATTRIBUTE4', C.ATTRIBUTE4, 'ATTRIBUTE5', C.ATTRIBUTE5,
'ATTRIBUTE6', C.ATTRIBUTE6, 'ATTRIBUTE7', C.ATTRIBUTE7,
'ATTRIBUTE8', C.ATTRIBUTE8, 'ATTRIBUTE9', C.ATTRIBUTE9,
'ATTRIBUTE10', C.ATTRIBUTE10, 'ATTRIBUTE11', C.ATTRIBUTE11,
'ATTRIBUTE12', C.ATTRIBUTE12, 'ATTRIBUTE13', C.ATTRIBUTE13,
'ATTRIBUTE14', C.ATTRIBUTE14,
'ATTRIBUTE15', C.ATTRIBUTE15) eliminations_id,
c.account_name, other_source_rec.amount
INTO p_party_info_tab(i).party_id, p_party_info_tab(i).vendor_type,
p_party_info_tab(i).elim_dept, p_party_info_tab(i).recipient_name,
P_party_info_tab(i).party_line_amount
FROM hz_cust_accounts_all c
WHERE c.cust_account_id = other_source_rec.party_id;
SELECT decode(gbl_trading_partner_att, null,
dept_id|| main_account,
SUBSTR(DECODE(UPPER(gbl_trading_partner_att),
'ATTRIBUTE1', attribute1,
'ATTRIBUTE2', attribute2,
'ATTRIBUTE3', attribute3,
'ATTRIBUTE4', attribute4,
'ATTRIBUTE5', attribute5,
'ATTRIBUTE6', attribute6,
'ATTRIBUTE7', attribute7,
'ATTRIBUTE8', attribute8,
'ATTRIBUTE9', attribute9,
'ATTRIBUTE10', attribute10,
'ATTRIBUTE11', attribute11,
'ATTRIBUTE12', attribute12,
'ATTRIBUTE13', attribute13,
'ATTRIBUTE14', attribute14,
'ATTRIBUTE15', attribute15),1,6))
INTO p_party_info_tab(i).elim_dept
FROM fv_be_trx_dtls
WHERE transaction_id = be_rec.transaction_id;