[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE delete_exception_recs;
PROCEDURE insert_exception_rec
(p_set_of_books_id IN NUMBER, p_fund_value IN VARCHAR2,
p_account_number IN VARCHAR2,
p_sgl_account_number IN VARCHAR2,
p_je_source IN VARCHAR2,
p_je_category IN VARCHAR2,
p_je_header_id IN NUMBER, p_je_line_num IN NUMBER,
p_amount IN NUMBER, p_ccid IN NUMBER, p_exception_category IN VARCHAR2,
p_doc_num in VARCHAR2, p_party_name IN VARCHAR2,
p_agency_id IN VARCHAR2, p_main_acct IN VARCHAR2);
PROCEDURE update_gtas_run;
PROCEDURE delete_prior_gtas_activity;
SELECT COUNT(*)
INTO l_no_fed_account
FROM fv_gtas_fed_accounts
WHERE set_of_books_id = p_sob_id
AND fiscal_year = gbl_period_year;
delete_exception_recs;
delete_prior_gtas_activity;
update_gtas_run;
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 chart_of_accounts_id
INTO gbl_coa_id
FROM gl_ledgers_public_v
WHERE ledger_id = gbl_sob_id;
SELECT flex_value_set_id
INTO gbl_acc_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = gbl_acc_segment
AND id_flex_code = 'GL#'
AND id_flex_num = gbl_coa_id;
l_select_stmt VARCHAR2(20000);
INSERT INTO fv_gtas_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_gtas_processed_je_hdrs e
WHERE e.set_of_books_id = gjh.ledger_id
AND e.je_header_id = gjh.je_header_id
) ;
log(l_module_name,'Inserted ' || SQL%ROWCOUNT);
SELECT factsI_journal_attribute
INTO gbl_jrnl_attribute
FROM fv_system_parameters;
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_name,
NULL agency_id,
NULL main_account,
NVL(fgf.fed_non_fed_flag,''N''),
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,
NVL(fgf.authority_type_flag,''N''),
NVL(fgf.budget_impact_ind_flag,''N''),
NVL(fgf.trading_partner_flag,''N''),
fgf.sgl_account_number
';
l_select_stmt :=
'SELECT /*+ ORDERED INDEX(gjh GL_JE_HEADERS_U1, ftt
FV_GTAS_HEADER_ID_GT_U1, gjl GL_JE_LINES_U1,
glcc GL_CODE_COMBINATIONS_U1,
fgf FV_GTAS_FED_ACCOUNTS_U1) */ ' ||
l_select_stmt ||
' FROM fv_gtas_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_gtas_fed_accounts fgf
, fv_fund_parameters ffp,
fv_treasury_symbols fts
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 gjh.je_header_id = gjl.je_header_id
AND glcc.code_combination_id = gjl.code_combination_id
AND glcc.chart_of_accounts_id = :gbl_coa_id
AND fgf.account_number = glcc.'||gbl_acc_segment || '
AND fgf.set_of_books_id = :gbl_sob_id
AND fgf.fiscal_year = :gbl_period_year
AND ffp.fund_value = glcc.'||gbl_bal_segment || '
AND ffp.treasury_symbol_id = fts.treasury_symbol_id
AND fts.gtas_reportable_indicator = ''Y''
AND fts.set_of_books_id = :gbl_sob_id
AND (fgf.authority_type_flag = ''Y''
OR fgf.fed_non_fed_flag = ''Y''
OR fgf.trading_partner_flag = ''Y''
OR fgf.budget_impact_ind_flag = ''Y'')
';
log(l_module_name, l_select_stmt);
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,
gbl_sob_id;
l_party_info_tab.delete();
SELECT gtas_acct_number
INTO l_account_number
FROM fv_gtas_attributes
WHERE gtas_acct_number = account_number_list(i);
SELECT TO_NUMBER(l_party_info_tab(j).agency_id)
INTO l_dummy_num
FROM dual
WHERE TO_NUMBER(l_party_info_tab(j).agency_id)
BETWEEN 0 AND 999;
log(l_module_name,'Agency ID is null or not valid, inserting exception.');
insert_exception_rec(gbl_sob_id, fund_value_list(i), account_number_list(i),
sgl_account_number_list(i), je_source_list(i), je_category_list(i), NULL, NULL,
amount_list_2(k), ccid_list(i), l_exception_category,
l_party_info_tab(j).doc_num, l_party_info_tab(j).party_name,
l_party_info_tab(j).agency_id, l_party_info_tab(j).main_account);
log(l_module_name,'Inserting into activity balances');
INSERT
--INTO fv_gtas_line_balances
INTO fv_gtas_activity_balances
(
ccid ,
period_num ,
set_of_books_id ,
period_year ,
account_number ,
fund_value ,
amount ,
d_c_indicator ,
fed_non_fed ,
trading_partner_agency_id ,
trading_partner_main_account ,
record_category ,
trading_partner_name ,
period_name ,
je_header_id ,
je_line_num ,
je_category ,
je_source ,
trading_partner_id ,
trading_partner_type ,
--party_classification,
--attribute_value ,
balance_type ,
--feeder_flag ,
gl_period ,
creation_date ,
authority_type_code ,
budget_impact_ind
)
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) ,
fed_nonfed_code_list_2(i) ,
agency_id_list_2(i),
main_account_list_2(i),
record_category_list_2(i) ,
party_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 ,
authority_type_code_list_2(i),
budget_impact_ind_list_2(i)
);
SELECT xd.source_distribution_id_num_1 transaction_id,
xte.transaction_number
--, (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,
xla_transaction_entities xte
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
AND xh.entity_id = xte.entity_id;
l_auth_select VARCHAR2(200);
SELECT bd.TRANSACTION_TYPE_ID, bd.SUB_TYPE
INTO l_trx_type_id, l_sub_type
FROM fv_be_trx_dtls bd, fv_be_trx_hdrs bh, fv_budget_levels bl
WHERE bd.transaction_id = l_be_trx_id
AND bd.doc_id = bh.doc_id
AND bd.set_of_books_id = bh.set_of_books_id
AND bl.budget_level_id = bh.budget_level_id
AND bl.set_of_books_id = bh.set_of_books_id
AND bh.set_of_books_id = gbl_sob_id
AND (
(bl.budget_level_id = 1 and bh.source <> 'RPR') OR
(bh.source = 'RPR')
);
SELECT authority_type
INTO l_authority_type_code_sub
FROM fv_be_trx_sub_types bs
WHERE bs.be_tt_id = l_trx_type_id
AND bs.sub_type = l_sub_type
AND bs.ledger_id = gbl_sob_id;
SELECT authority_type
INTO l_authority_type_code_trx
FROM fv_be_transaction_types bt
WHERE bt.be_tt_id = l_trx_type_id
AND bt.set_of_books_id = gbl_sob_id;
SELECT 'Y'
INTO l_authority_type_found
FROM fv_gtas_attributes
WHERE set_of_books_id = gbl_sob_id
AND gtas_acct_number = l_account_number --p_account_number
AND (authority_type1 = l_authority_type_code
OR authority_type2 = l_authority_type_code
OR authority_type3 = l_authority_type_code
OR authority_type4 = l_authority_type_code
OR authority_type5 = l_authority_type_code
OR authority_type6 = l_authority_type_code);
insert_exception_rec(p_sob_id, p_fund_value, p_account_number,p_sgl_account_number,
p_je_source, p_je_category, p_je_header_id,
p_je_line_num, p_amount, p_ccid, l_exception_category,
l_be_doc_num, p_party_name, p_agency_id, p_main_account);
l_auth_select := 'SELECT authority_type'||i||
' from fv_gtas_attributes
where gtas_acct_number = '||l_account_number; --p_account_number;
EXECUTE IMMEDIATE l_auth_select INTO l_authority_type_code_tmp;
insert_exception_rec(p_sob_id, p_fund_value, p_account_number,p_sgl_account_number,
p_je_source, p_je_category, p_je_header_id,
p_je_line_num, p_amount, p_ccid, l_exception_category,
l_be_doc_num, p_party_name, p_agency_id,
p_main_account);
insert_exception_rec(p_sob_id, p_fund_value, p_account_number,p_sgl_account_number,
p_je_source, p_je_category, p_je_header_id,
p_je_line_num, p_amount, p_ccid, l_exception_category,
l_be_doc_num, p_party_name, p_agency_id,
p_main_account);
PROCEDURE DELETE_EXCEPTION_RECS IS
l_module_name VARCHAR2(200) := g_module_name||'DELETE_EXCEPTION_RECS';
DELETE FROM fv_gtas_exceptions
WHERE set_of_books_id = gbl_sob_id
AND period_year = gbl_period_year;
log(l_module_name, 'Deleted Exception Rows.');
END DELETE_EXCEPTION_RECS;
PROCEDURE INSERT_EXCEPTION_REC(
p_set_of_books_id IN NUMBER, p_fund_value IN VARCHAR2,
p_account_number IN VARCHAR2,
p_sgl_account_number IN VARCHAR2,
p_je_source IN VARCHAR2,
p_je_category IN VARCHAR2,
p_je_header_id IN NUMBER, p_je_line_num IN NUMBER,
p_amount IN NUMBER, p_ccid IN NUMBER, p_exception_category IN VARCHAR2,
p_doc_num IN VARCHAR2, p_party_name IN VARCHAR2,
p_agency_id IN VARCHAR2, p_main_acct IN VARCHAR2) IS
l_module_name VARCHAR2(200) := g_module_name||'INSERT_EXCEPTION_REC';
INSERT INTO fv_gtas_exceptions
(set_of_books_id, fund_value, period_year, period_num, account_number,
sgl_acct_num,
je_source, je_category,
je_header_id, je_line_num, amount, ccid,
exception_category, creation_date, doc_num, trading_partner_name,
trading_partner_agency_id, trading_partner_main_account)
VALUES
(p_set_of_books_id, p_fund_value, gbl_period_year, gbl_period_num_high,
p_account_number , p_sgl_account_number, p_je_source, p_je_category,
p_je_header_id, p_je_line_num, p_amount,p_ccid,
p_exception_category, sysdate, p_doc_num, p_party_name,
p_agency_id, p_main_acct);
log(l_module_name,'Inserted Acct Num: '||p_account_number||'-Exception: '||p_exception_category);
END INSERT_EXCEPTION_REC;
SELECT SUBSTR(name,1,30)
INTO p_party_info_tab(i).doc_num
FROM gl_je_headers
WHERE je_header_id = p_je_header_id;
l_select VARCHAR2(300);
SELECT ael.party_id,
NVL(ael.accounted_dr,0) - NVL(ael.accounted_cr,0) amount,
xte.transaction_number
FROM gl_import_references i,
xla_ae_lines ael,
xla_ae_headers aeh,
xla_transaction_entities xte
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
and ael.ae_header_id = aeh.ae_header_id
and aeh.entity_id = xte.entity_id;
SELECT pc.customer_id party_id,
NVL(ael.accounted_dr,0) - NVL(ael.accounted_cr,0) amount,
xte.transaction_number
FROM gl_je_lines je ,
gl_import_references gir ,
xla_ae_lines ael ,
xla_ae_headers aeh ,
pa_draft_revenues_all pdr,
pa_agreements_all agr ,
pa_project_customers pc,
xla_transaction_entities xte
WHERE je.je_header_id = gir.je_header_id
AND je.je_line_num = gir.je_line_num
AND ael.gl_sl_link_id = gir.gl_sl_link_id
AND ael.ae_header_id = aeh.ae_header_id
AND aeh.event_id = pdr.event_id
AND agr.agreement_id = pdr.agreement_id
AND pc.project_id = pdr.project_id
AND pc.customer_id = agr.customer_id
AND pc.customer_id IS NOT NULL
AND gir.je_header_id =p_je_header_id
AND ael.application_id = p_application_id
AND gir.je_line_num = p_je_line_num
AND gir.je_batch_id = p_je_batch_id
and xte.entity_id = aeh.entity_id;
SELECT (NVL(xd.unrounded_accounted_dr,0) - NVL(xd.unrounded_accounted_cr,0)) amount,
bd.transaction_id,
bd.dept_id, bd.main_account,
bh.doc_number
FROM gl_import_references gli,
xla_ae_lines xl ,
xla_ae_headers xh ,
xla_distribution_links xd,
xla_transaction_entities xte,
fv_be_trx_dtls bd,
fv_be_trx_hdrs bh
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
AND xh.entity_id = xte.entity_id
and bd.transaction_id = xd.source_distribution_id_num_1
and bh.doc_id = bd.doc_id;
SELECT
(NVL(xd.unrounded_accounted_dr,0)-NVL(xd.unrounded_accounted_cr,0)) amount,
reference4 doc_num, poh.vendor_id, rc.rcv_transaction_id
FROM gl_import_references gli,
xla_ae_lines xl ,
xla_ae_headers xh ,
xla_distribution_links xd,
rcv_receiving_sub_ledger rc,
po_headers_all poh
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 = 707
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
AND rc.rcv_sub_ledger_id = xd.source_distribution_id_num_1
AND poh.po_header_id = rc.reference2;
SELECT
(NVL(xd.unrounded_accounted_dr,0)-NVL(xd.unrounded_accounted_cr,0)) amount,
aip.check_id
FROM gl_import_references gli,
xla_ae_lines xl ,
xla_ae_headers xh ,
xla_distribution_links xd,
ap_invoice_payments_all aip,
ap_payment_hist_dists aphd
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
AND aphd.payment_hist_dist_id = xd.source_distribution_id_num_1
AND aip.invoice_payment_id = aphd.invoice_payment_id;
SELECT vendor_id, 'S',
v.vendor_name,
NVL(v.global_attribute4,'N') "fed_nonfed_code",
v.global_attribute5 "agency_id" ,
other_source_rec.amount,
other_source_rec.transaction_number
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type ,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
P_party_info_tab(i).agency_id ,
P_party_info_tab(i).party_line_amount,
P_party_info_tab(i).doc_num
FROM ap_suppliers v
WHERE v.vendor_id = other_source_rec.party_id;
log(l_module_name,'12.2 or above, using dynamic select');
l_select := ' SELECT cust_account_id, account_name,
NVL(federal_entity_type,''N''),
trading_partner_agency_id
FROM hz_cust_accounts_all
WHERE cust_account_id = :party_id';
EXECUTE IMMEDIATE l_select
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code,
p_party_info_tab(i).agency_id
USING other_source_rec.party_id;
SELECT c.cust_account_id, 'C', c.account_name,
DECODE(c.customer_class_code, 'FEDERAL','F',
'GENERAL FUND', 'G',
'NON-FEDERAL EXCEPTION', 'E',
'N') "fed_nonfed_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) "agency_id",
other_source_rec.amount,
other_source_rec.transaction_number
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
p_party_info_tab(i).agency_id ,
P_party_info_tab(i).party_line_amount,
p_party_info_tab(i).doc_num
FROM hz_cust_accounts_all c
WHERE c.cust_account_id = other_source_rec.party_id;
l_select := ' SELECT cust_account_id, account_name,
federal_entity_type, trading_partner_agency_id
FROM hz_cust_accunts
WEHRE cust_account_id = :party_id';
EXECUTE IMMEDIATE l_select
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code,
p_party_info_tab(i).agency_id
USING project_source_rec.party_id;
SELECT c.cust_account_id, 'C', c.account_name,
DECODE(c.customer_class_code, 'FEDERAL','F',
'GENERAL FUND', 'G',
'NON-FEDERAL EXCEPTION', 'E',
'N') "fed_nonfed_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) "agency_id",
project_source_rec.amount,
project_source_rec.transaction_number
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
p_party_info_tab(i).agency_id ,
p_party_info_tab(i).party_line_amount,
p_party_info_tab(i).doc_num
FROM hz_cust_accounts_all c
WHERE c.cust_account_id = project_source_rec.party_id;
SELECT federal_acct_symbol_name, 'F'
INTO p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code
FROM fv_tp_treasury_symbols
WHERE agency_id = be_trx_rec.dept_id
AND main_acct_code = be_trx_rec.main_account
AND rownum = 1;
insert_exception_rec(
gbl_sob_id, p_fund_value, '**NULL**',NULL,
p_je_source, p_je_category, p_je_header_id,
p_je_line_num, be_trx_rec.amount, 0, 'FED_ACCT_SYM_NOT_FOUND',
be_trx_rec.doc_number, NULL, be_trx_rec.dept_id,
be_trx_rec.main_account);
SELECT aps.vendor_id, 'S', aps.vendor_name,
NVL(aps.global_attribute4,'N') "fed_nonfed_code",
aps.global_attribute5 "agency_id" ,
cst_rec.amount, cst_rec.rcv_transaction_id
--, cst_rec.doc_num
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type ,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
P_party_info_tab(i).agency_id ,
P_party_info_tab(i).party_line_amount,
l_rcv_transaction_id
--,P_party_info_tab(i).doc_num
FROM --rcv_transactions rt,
--rcv_shipment_headers rcv,
ap_suppliers aps
WHERE aps.vendor_id = cst_rec.vendor_id;
SELECT h.receipt_num
INTO p_party_info_tab(i).doc_num
FROM rcv_shipment_headers h, rcv_transactions t
WHERE t.transaction_id = l_rcv_transaction_id
AND h.shipment_header_id = t.shipment_header_id;
insert_exception_rec(
gbl_sob_id, p_fund_value, '**NULL**',NULL,
p_je_source, p_je_category, p_je_header_id,
p_je_line_num, cst_rec.amount, 0, 'PARTY_INFO_NOT_FOUND',
cst_rec.doc_num, NULL, NULL, NULL);
SELECT v.vendor_id, 'S', v.vendor_name,
v.global_attribute4 "fed_nonfed_code",
v.global_attribute5 "agency_id",
trs_confirm_rec.amount,
apc.checkrun_name
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type ,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
p_party_info_tab(i).agency_id,
p_party_info_tab(i).party_line_amount,
p_party_info_tab(i).doc_num
FROM ap_checks_all apc,
ap_suppliers v
WHERE apc.vendor_id = v.vendor_id
AND apc.check_id = trs_confirm_rec.check_id;
insert_exception_rec(
gbl_sob_id, p_fund_value, '**NULL**',NULL,
p_je_source, p_je_category, p_je_header_id,
p_je_line_num, trs_confirm_rec.amount, 0, 'PARTY_INFO_NOT_FOUND',
p_party_info_tab(i).doc_num, NULL, NULL, NULL);
l_select VARCHAR2(250);
SELECT e.vendor_id ven_id
FROM gl_je_lines je ,
gl_je_headers jh ,
pa_cost_distribution_lines_all cdl,
pa_expenditure_items_all ei ,
pa_expenditures_all e
WHERE je.je_header_id = jh.je_header_id
AND je.reference_1 IS NOT NULL
AND e.vendor_id IS NOT NULL
AND je.reference_1 = p_ref1
AND je.reference_1 = cdl.batch_name
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND ei.expenditure_id = e.expenditure_id;
SELECT v.vendor_id, 'S', v.vendor_name,
v.global_attribute4 "fed_nonfed_code",
v.global_attribute5 "agency_id",
i.invoice_num
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type ,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
p_party_info_tab(i).agency_id,
p_party_info_tab(i).doc_num
FROM ap_invoices_all i,
ap_suppliers v
WHERE i.invoice_id = to_number(p_reference2)
AND i.vendor_id = v.vendor_id;
SELECT v.vendor_id, 'S', v.vendor_name,
v.global_attribute4 "fed_nonfed_code" ,
v.global_attribute5 "agency_id",
ph.segment1
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type ,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
p_party_info_tab(i).agency_id,
p_party_info_tab(i).doc_num
FROM rcv_transactions rt,
ap_suppliers v ,
po_headers_all ph
WHERE rt.po_header_id = to_number(p_reference2)
AND rt.transaction_id = to_number(p_reference5)
AND rt.po_header_id = ph.po_header_id
AND v.vendor_id = ph.vendor_id;
SELECT v.vendor_id, 'S', v.vendor_name,
v.global_attribute4 "fed_nonfed_code",
v.global_attribute5 "agency_id",
poh.segment1
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type ,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
p_party_info_tab(i).agency_id,
p_party_info_tab(i).doc_num
FROM ap_suppliers v,
po_headers_all poh
WHERE poh.po_header_id = to_number(p_reference2)
AND v.vendor_id = poh.vendor_id;
l_select := ' SELECT cust_account_id, ''S'', account_name,
federal_entity_type, trading_partner_agency_id
FROM hz_cust_accunts
WEHRE cust_account_id = :reference ';
EXECUTE IMMEDIATE l_select
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type ,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
p_party_info_tab(i).agency_id
USING p_reference7;
SELECT c.cust_account_id, 'C', c.account_name,
DECODE(c.customer_class_code, 'FEDERAL','F',
'GENERAL FUND', 'G',
'NON-FEDERAL EXCEPTION', 'E',
'N') "fed_nonfed_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) "agency_id"
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type ,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
p_party_info_tab(i).agency_id
FROM hz_cust_accounts_all c
WHERE c.cust_account_id = to_number(p_reference7);
SELECT SUBSTR(p_reference2, 0, decode(INSTR(p_reference2, 'C'), 0,
LENGTH(p_reference2),INSTR(p_reference2,'C')-1))
INTO l_temp_reference_2
FROM dual;
SELECT receipt_number
INTO p_party_info_tab(i).doc_num
FROM ar_cash_receipts_all
WHERE cash_receipt_id = to_number(l_temp_reference_2);
SELECT trx_number
INTO p_party_info_tab(i).doc_num
FROM ra_customer_trx_all
WHERE customer_trx_id = to_number(p_reference2);
SELECT v.vendor_id, 'S', v.vendor_name,
v.global_attribute4 "fed_nonfed_code",
v.global_attribute5 "agency_id",
apc.checkrun_name
INTO p_party_info_tab(i).party_id,
p_party_info_tab(i).party_type ,
p_party_info_tab(i).party_name,
p_party_info_tab(i).fed_nonfed_code ,
p_party_info_tab(i).agency_id,
p_party_info_tab(i).doc_num
FROM ap_checks_all apc,
ap_suppliers v
WHERE apc.vendor_id = v.vendor_id
AND apc.check_id = to_number(p_reference3);
SELECT application_short_name
INTO l_ar_schema
FROM fnd_application
WHERE application_id = 222;
SELECT 'Y'
INTO gbl_cust_col_exists
FROM all_tab_columns
WHERE table_name = 'HZ_CUST_ACCOUNTS'
AND owner = l_ar_schema
AND column_name = 'FEDERAL_ENTITY_TYPE';
SELECT gtas_customer_attribute
INTO gbl_cust_attribute
FROM fv_system_parameters;
gbl_err_buff := 'Please select GTAS Customer Trading Partner Attribute in the '||
'Federal System Parameters form.';
l_acct_select VARCHAR2(1000);
SELECT event_id
INTO l_sla_event_id
FROM gl_import_references gli,
xla_ae_lines l,
xla_ae_headers h
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 l.gl_sl_link_id = gli.gl_sl_link_id
AND h.ae_header_id = l.ae_header_id;
SELECT event_id
INTO l_sla_event_id
FROM gl_import_references gli,
xla_ae_lines l,
xla_ae_headers h
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;
l_acct_select := 'SELECT SUBSTR(ffv.compiled_value_attributes,5,1)
FROM xla_ae_headers xh,
xla_ae_lines xl,
gl_code_combinations gcc,
fnd_flex_values ffv
WHERE xh.event_id = '||sla_event.event_id||
' AND xh.ae_header_id = xl.ae_header_id
AND gcc.code_combination_id = xl.code_combination_id
AND gcc.'||gbl_acc_segment||' = ffv.flex_value
AND ffv.flex_value_set_id = '||gbl_acc_value_set_id
;
OPEN l_acct_type_cur FOR l_acct_select;
SELECT COUNT(*)
INTO l_bud_imp_count
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number--p_account_number
AND (NVL(bud_impact_ind1,'-X') = 'D'
OR NVL(bud_impact_ind2,'-X') = 'D');
' inserting exception');
insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
p_sgl_account_number, p_je_source,
p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_BUD_IMPACT_IND',
p_doc_num, p_party_name, p_agency_id, p_main_account
);
SELECT COUNT(*)
INTO l_bud_imp_count
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number--p_account_number
AND (NVL(bud_impact_ind1,'-X') = 'E'
OR NVL(bud_impact_ind2,'-X') = 'E');
' inserting exception');
insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
p_sgl_account_number, p_je_source,
p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_BUD_IMPACT_IND',
p_doc_num, p_party_name, p_agency_id, p_main_account
);
SELECT COUNT(*)
INTO l_fednonfed_count
FROM (
SELECT fed_non_fed1
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number
AND fed_non_fed1 IS NOT NULL
AND set_of_books_id = gbl_sob_id
UNION
SELECT fed_non_fed2
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number
AND fed_non_fed2 IS NOT NULL
AND set_of_books_id = gbl_sob_id
UNION
SELECT fed_non_fed3
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number
AND fed_non_fed3 IS NOT NULL
AND set_of_books_id = gbl_sob_id);
SELECT COUNT(*)
INTO l_fednonfed_count
FROM (
SELECT fed_non_fed1
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number
AND fed_non_fed1 IS NOT NULL
AND set_of_books_id = gbl_sob_id
UNION
SELECT fed_non_fed2
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number
AND fed_non_fed2 IS NOT NULL
AND set_of_books_id = gbl_sob_id
UNION
SELECT fed_non_fed3
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number
AND fed_non_fed3 IS NOT NULL
AND set_of_books_id = gbl_sob_id);
' inserting exception');
insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
p_sgl_account_number, p_je_source,
p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE',
p_doc_num, p_party_name, p_agency_id, p_main_account);
' inserting exception');
insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
p_sgl_account_number, p_je_source,
p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE',
p_doc_num, p_party_name, p_agency_id, p_main_account);
' inserting exception');
insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
p_sgl_account_number, p_je_source,
p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE',
p_doc_num, p_party_name, p_agency_id, p_main_account);
SELECT NVL(ffa.non_fed_exc_flag,'N')
INTO l_non_fed_exc_flag
FROM fv_facts_federal_accounts ffa,
fv_treasury_symbols fts,
fv_fund_parameters ffp
WHERE ffp.set_of_books_id = gbl_sob_id
AND ffp.fund_value = p_fund_value
AND ffp.treasury_symbol_id = fts.treasury_symbol_id
AND fts.federal_acct_symbol_id = ffa.federal_acct_symbol_id;
' inserting exception');
insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
p_sgl_account_number, p_je_source,
p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE_TAS',
p_doc_num, p_party_name, p_agency_id, p_main_account);
' inserting exception');
insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
p_sgl_account_number, p_je_source,
p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE_TAS',
p_doc_num, p_party_name, p_agency_id, p_main_account);
log(l_module_name,'Fed nonfed not found inserting exception');
insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
p_sgl_account_number, p_je_source,
p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE',
p_doc_num, p_party_name, p_agency_id, p_main_account);
SELECT SUBSTR(compiled_value_attributes,5,1)
INTO p_account_type
FROM fnd_flex_values
WHERE flex_value = p_account_number
AND flex_value_set_id = gbl_acc_value_set_id;
SELECT LOWER(fnl.iso_language), fnl.iso_territory
INTO lc_language, lc_territory
FROM fnd_languages fnl
WHERE fnl.language_code = USERENV ('LANG');
PROCEDURE UPDATE_GTAS_RUN
IS
l_module_name VARCHAR2(80) ;
l_module_name := g_module_name||'UPDATE_GTAS_RUN';
INSERT INTO fv_gtas_processed_je_hdrs
(je_header_id,set_of_books_id)
SELECT DISTINCT je_header_id,set_of_books_id FROM fv_gtas_header_id_gt;
log(l_module_name,'Inserted in fv_gtas_processed_je_hdrs: '||SQL%ROWCOUNT);
UPDATE fv_gtas_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;
UPDATE fv_gtas_fed_accounts
SET jc_flag = 'Y'
WHERE set_of_books_id = gbl_sob_id
AND fiscal_year = gbl_period_year;
END UPDATE_GTAS_RUN;
l_fednonfed_select VARCHAR2(200);
SELECT 'Y'
INTO p_code_exists
FROM fv_gtas_attributes
WHERE gtas_acct_number = p_account_number
AND set_of_books_id = gbl_sob_id
AND (fed_non_fed1 = p_fed_nonfed_type OR
fed_non_fed2 = p_fed_nonfed_type OR
fed_non_fed3 = p_fed_nonfed_type );
PROCEDURE DELETE_PRIOR_GTAS_ACTIVITY
IS
l_module_name VARCHAR2(80) := g_module_name||'DELETE_PRIOR_GTAS_ACTIVITY';
DELETE FROM fv_gtas_activity_balances
WHERE period_year = gbl_period_year
AND set_of_books_id = gbl_sob_id;
log(l_module_name, 'Deleted : '||sql%rowcount||' row/s.');
DELETE fv_gtas_processed_je_hdrs
WHERE set_of_books_id = gbl_sob_id
AND je_header_id IN (SELECT je_header_id
FROM gl_je_headers
WHERE ledger_id = gbl_sob_id
AND period_name IN
(SELECT period_name
FROM gl_period_statuses
WHERE ledger_id = gbl_sob_id
AND period_year = gbl_period_year));
log(l_module_name, 'Deleted : '||sql%rowcount||' row/s.');
UPDATE fv_gtas_run
SET process_date = NULL,
jc_run_month = NULL,
run_fed_flag = 'A'
WHERE set_of_books_id = gbl_sob_id
AND fiscal_year = gbl_period_year;
END DELETE_PRIOR_GTAS_ACTIVITY;
SELECT COUNT(*)
INTO l_exception_count
FROM fv_gtas_exceptions
WHERE set_of_books_id = gbl_sob_id
AND period_year = gbl_period_year;
SELECT gtas_acct_number
INTO p_account_num
FROM fv_gtas_attributes
WHERE set_of_books_id = gbl_sob_id
AND gtas_acct_number = p_gtas_acct_num;