[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT fund_value,
fund_category,
treasury_symbol_id
From Fv_Fund_Parameters
WHERE fund_value = p_fund_value
AND set_of_books_id = p_sob_id;
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
From Fv_Facts_Prc_Hdr Ffh
WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
AND ffh.code_type = vl_code_type
AND ffh.set_of_books_id = p_sob_id
AND ffh.fund_value = p_fund_value;
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
AND ffh.code_type = vl_code_type
And Ffh.Set_Of_Books_Id = P_Sob_Id
AND ffh.fund_value = 'ALL-A';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
AND ffh.code_type = vl_code_type
And Ffh.Set_Of_Books_Id = P_Sob_Id
AND ffh.fund_value = 'ALL-B';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = vl_treasury_symbol_id
AND ffh.code_type = vl_code_type
AND ffh.set_of_books_id = p_sob_id
AND ffh.fund_value = 'ALL-FUNDS';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = -1
AND ffh.code_type = vl_code_type
And Ffh.Set_Of_Books_Id = P_Sob_Id
AND ffh.fund_value = 'ALL-A';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = -1
AND ffh.code_type = vl_code_type
And Ffh.Set_Of_Books_Id = P_Sob_Id
AND ffh.fund_value = 'ALL-B';
SELECT program_segment,
prc_mapping_flag, prc_header_id
INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
FROM fv_facts_prc_hdr ffh
WHERE ffh.treasury_symbol_id = -1
AND ffh.set_of_books_id = p_sob_id
AND ffh.code_type = vl_code_type
AND ffh.fund_value = 'ALL-FUNDS';
Select_Stmt varchar2(100);
Select_Stmt := 'SELECT ' || p_Segment_Name ||
' from gl_code_combinations
where code_combination_id=' || p_Ccid;
log(l_module, 'Select_Stmt:'||Select_Stmt);
EXECUTE IMMEDIATE Select_Stmt INTO l_prg_val;
SELECT reporting_code
INTO p_prc_val
From Fv_Facts_Prc_Dtl
WHERE prc_header_id = P_Prc_Header_Id
And Program_Value = l_prg_val
And Set_Of_Books_Id = G_Set_Of_Books_Id;
SELECT reporting_code
INTO p_prc_val
FROM fv_facts_prc_dtl
WHERE prc_header_id = P_Prc_Header_Id
And Program_Value = 'ALL'
And Set_Of_Books_Id = G_Set_Of_Books_Id;
SELECT ffp.fund_value,fts.treasury_symbol_id
FROM fv_fund_parameters ffp, fv_treasury_symbols fts
WHERE ffp.set_of_books_id = g_set_of_books_id
AND fts.set_of_books_id = g_set_of_books_id
AND ffp.treasury_symbol_id = fts.treasury_symbol_id
--AND fund_value
-- BETWEEN NVL(p_fund_low,fund_value)
-- AND NVL(p_fund_high,fund_value)
ORDER BY fund_value ;
SELECT count(*)
INTO l_prc_map_count
FROM fv_facts_prc_hdr
WHERE set_of_books_id = g_set_of_books_id;
SELECT count(*)
INTO l_count
FROM FV_GTAS_TRX_REG_TEMP;
SELECT period_set_name
INTO l_period_set_name
FROM gl_sets_of_books
WHERE set_of_books_id = g_set_of_books_id;
SELECT period_year,adjustment_period_flag
INTO g_period_year,g_adjustment_flag
FROM gl_periods
WHERE period_set_name = l_period_set_name
AND period_name = p_period_from;
SELECT start_date
INTO g_start_date
FROM gl_period_statuses
WHERE ledger_id = g_set_of_books_id
AND application_id = 101
AND period_year = g_period_year
AND period_name = p_period_from;
SELECT end_date
INTO g_end_date
FROM gl_period_statuses
WHERE ledger_id = g_set_of_books_id
AND application_id = 101
AND period_year = g_period_year
AND period_name = p_period_to;
SELECT MIN(period_num)
INTO g_period_num_low
FROM gl_period_statuses
WHERE period_name = g_from_period_name
AND application_id = 101
AND ledger_id = g_set_of_books_id
AND period_year = g_period_year;
SELECT max(period_num)
INTO g_period_num_high
FROM gl_period_statuses
WHERE period_name = g_to_period_name
AND application_id = 101
AND ledger_id = g_set_of_books_id
AND period_year = g_period_year;
SELECT flex_value_set_id
INTO g_acc_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = g_acct_segment_name
AND application_id = g_apps_id
AND id_flex_code = g_id_flex_code
AND id_flex_num = g_coa_id ;
select_stmt VARCHAR2(10000);
Select treasury_symbol_id,
Outlays_Bea_Category_Code, Bea_Category,
Fund_Category,
Cust_Non_Cust, Borrowing_Source_Code,
Direct_Or_Reimb_Code
into l_treasury_symbol_id,
l_Outlays_Bea_Category_Code, l_Bea_Category,
l_appor_cat,
l_c_nc, l_bsc, l_drc
From Fv_Fund_Parameters
Where Fund_Value =p_fund_val
and set_of_books_id=g_set_of_books_id;
Select count(*) into cnt
From Fv_Gtas_Attributes
Where GTAS_ACCT_NUMBER = l_Acct_Num;
select decode(BALANCE_TYPE, 'BE','Y','E','Y','N')
into g_end_bal_ind
from FV_GTAS_ATTRIBUTES
Where GTAS_ACCT_NUMBER = l_Acct_Num;
Select Exch_Non_Exch1,
Pya_Code1,
Cust_Non_Cust1, Cust_Non_Cust2,
Borrowing_Source_flag,
Direct_or_Reimb_flag,
Apportionment_Category1, Apportionment_Category2, Apportionment_Category3,
Bea_Category_Acct_Type, bea_category_flag,
prog_cost_ind1, prog_cost_ind2,
new_or_bal_flag,
Cur_Sub_Code1, prog_rep_cat_flag, appor_cat_b_prog_flag,
program_cost_ind_flag,
trading_partner_flag
Into L_Exch_Non_Exch1,
L_Pya_Code1,
L_Cust_Non_Cust1, L_Cust_Non_Cust2,
L_Borrowing_Source_flag,
L_Direct_or_Reimb_flag,
L_Apportionment_Category1,L_Apportionment_Category2, L_Apportionment_Category3,
L_Bea_Category_Acct_Type, l_bea_category_flag,
l_prog_cost_ind1, l_prog_cost_ind2,
l_new_or_bal_flag,
l_Cur_Sub_Code1, l_prog_rep_cat_flag,
l_appor_cat_b_prog_flag, l_program_cost_ind_flag,
g_trading_partner_flag
From Fv_Gtas_Attributes
WHERE GTAS_ACCT_NUMBER = l_Acct_Num
AND set_of_books_id = g_set_of_books_id;
select Financing_Account into l_Financing_Account
from fv_treasury_symbols
where treasury_symbol_id = l_treasury_symbol_id;
SELECT cohort_segment_name
INTO l_cohort_seg_name
FROM fv_pya_fiscalyear_segment
Where Set_Of_Books_Id = g_set_of_books_id;
Select_Stmt := 'SELECT ' || l_cohort_seg_name ||'
from gl_code_combinations
where code_combination_id=' || g_ccid;
Dbms_Sql.Parse(vl_cursor,Select_Stmt, Dbms_Sql.V7);
SELECT fyr_segment_value
INTO vl_fyr_segment_value
FROM fv_pya_fiscalyear_map
Where Period_Year = g_period_year
AND set_of_books_id = g_set_of_books_id;
l_jrnl_select_gl VARCHAR2(5000);
l_jrnl_select_xla VARCHAR2(7000);
l_jrnl_select VARCHAR2(12000);
l_cohort_select VARCHAR2(100) ;
l_reimb_act_select VARCHAR2(100) ;
l_select VARCHAR2(300);
SELECT 'X', factsI_journal_attribute,
factsII_pub_law_code_attribute,
req_date_seg, pur_order_date_seg,
rec_trxn_date_seg, factsii_pub_law_rec_attribute
INTO l_exists, l_jrnl_att,
l_pl_code_col, --l_advance_type_col,
--l_tr_main_acct_col, l_tr_dept_id_col,
g_req_date_seg, g_pur_order_date_seg,
g_rec_trxn_date_seg, l_factsii_pub_law_rec_col
FROM fv_system_parameters;
l_cohort_select := ', GCC.' || g_cohort_seg_name ;
l_cohort_select := ' ' ;
l_reimb_act_select := ', GCC.' || g_reimb_agree_seg_name ;
l_reimb_act_select := ' ' ;
SELECT application_column_name
INTO l_fyr_segment_name
FROM fv_pya_fiscalyear_segment
WHERE set_of_books_id = g_set_of_books_id;
log(l_module, l_module||'.select1'||g_error_buf);
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'.select1',g_error_buf);
l_jrnl_select_gl:=
'SELECT gjl.entered_dr ENTERED_DR,
gjl.entered_cr ENTERED_CR,
NVL(gjl.reference_1, ''-100''),
NVL(gjl.reference_2, ''-100''),
NVL(gjl.reference_3, ''-100''),
NVL(gjl.reference_4, ''-100''),
NVL(gjl.reference_5, ''-100''),
NVL(gjl.reference_6, ''-100''),
NVL(gjl.reference_7, ''-100''),
NVL(gjl.reference_8, ''-100''),
NVL(gjl.reference_9, ''-100''),
NVL(gjl.reference_10,''-100''),
gjl.gl_sl_link_id,
gjh.je_from_sla_flag,
NULL,
NULL,
NULL,
NULL,
NULL,
gjb.name' || ',
gcc.' || g_acct_segment_name ||
', gcc.' ||l_fyr_segment_name ||','||
'gjh.je_category ,
gjh.je_source ,
gjl.code_combination_id,
gjl.je_header_id,
gjl.je_line_num,
gjl.creation_date,
gjl.last_update_date,
gjl.period_name,
gjh.date_created,
gjh.creation_date,
gjh.created_by ,
ffp.fund_value,
gjl.effective_date,
gjh.posted_date,
gjh.je_batch_id,
gjl.je_header_id,
gjl.creation_date,
NULL '||
l_jrnl ||
l_cohort_select ||
l_reimb_act_select ||
l_pl_code_col || l_advance_type_col ||
l_tr_dept_id_col || l_tr_main_acct_col ||
' FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations gcc,
fv_fund_parameters ffp,
fv_treasury_symbols fts
WHERE gjl.ledger_id = :sob_id
AND gjl.status = :status
AND gjl.period_name IN
(SELECT period_name
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = :sob_id
AND period_num BETWEEN :period_num_low
AND :period_num_high
AND period_year = :period_year)
AND gjh.je_header_id = gjl.je_header_id
AND gjh.currency_code <> ''STAT''
AND gjh.actual_flag = :actual_flag
AND gjh.posted_date BETWEEN :from_posted_date AND :to_posted_date
AND NVL(gjh.je_from_sla_flag, ''N'') = ''N''
AND gjb.je_batch_id = gjh.je_batch_id
AND gcc.chart_of_accounts_id= :coa_id
AND gcc.code_combination_id = gjl.code_combination_id
AND gcc.template_id IS NULL
AND ffp.set_of_books_id = :sob_id
AND gcc.'||g_bal_segment_name||' = ffp.fund_value
AND fts.set_of_books_id = :sob_id
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
'|| g_src_where ||' '||g_cat_where ||' '||g_segs_where||' '||
g_aid_where||' '||g_main_acct_where;
l_jrnl_select_xla :=
'SELECT xdl.unrounded_accounted_dr ENTERED_DR,
xdl.unrounded_accounted_cr ENTERED_CR,
''-100'',
''-100'',
''-100'',
''-100'',
''-100'',
''-100'',
''-100'',
''-100'',
''-100'',
''-100'',
gjl.gl_sl_link_id,
gjh.je_from_sla_flag,
xdl.source_distribution_id_num_1,
xdl.source_distribution_type,
xdl.applied_to_source_id_num_1,
xdl.applied_to_dist_id_num_1,
xah.event_type_code,
gjb.name' || ',
gcc.' || g_acct_segment_name ||
', gcc.' ||l_fyr_segment_name ||','||
'gjh.je_category ,
gjh.je_source ,
gjl.code_combination_id,
gjl.je_header_id,
gjl.je_line_num,
gjl.creation_date,
gjl.last_update_date,
gjl.period_name,
gjh.date_created,
gjh.creation_date,
gjh.created_by ,
ffp.fund_value,
gjl.effective_date,
gjh.posted_date,
gjh.je_batch_id,
xah.event_id,
xah.creation_date,
xah.entity_id '||
l_jrnl ||
l_cohort_select ||
l_reimb_act_select ||
l_pl_code_col || l_advance_type_col ||
l_tr_dept_id_col || l_tr_main_acct_col ||
' FROM gl_je_lines gjl,
gl_je_headers gjh,
gl_je_batches gjb,
gl_code_combinations gcc,
fv_fund_parameters ffp,
fv_treasury_symbols fts,
xla_ae_lines xal,
xla_ae_headers xah,
xla_distribution_links xdl,
gl_import_references gir
WHERE gjl.ledger_id = :sob_id
AND gjl.status = :status
AND gjl.period_name IN
(SELECT period_name
FROM gl_period_statuses
WHERE application_id = 101
AND ledger_id = :sob_id
AND period_num BETWEEN :period_num_low
AND :period_num_high
AND period_year = :period_year)
AND gjh.je_header_id = gjl.je_header_id
AND gjh.currency_code <> ''STAT''
AND gjh.actual_flag = :actual_flag
AND gjh.posted_date BETWEEN :from_posted_date AND :to_posted_date
AND gjb.je_batch_id = gjh.je_batch_id
AND gcc.chart_of_accounts_id= :coa_id
AND gjl.code_combination_id = gcc.code_combination_id
AND gcc.template_id IS NULL
AND ffp.set_of_books_id = :sob_id
AND ffp.fund_value = gcc.'||g_bal_segment_name||'
AND fts.set_of_books_id = :sob_id
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND gir.je_batch_id = gjb.je_batch_id
AND gir.je_header_id = gjh.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND xdl.ae_line_num = xal.ae_line_num
AND xdl.ae_header_id = xal.ae_header_id
AND xah.ae_header_id = xal.ae_header_id
and (NVL(gjl.entered_dr,0) <> 0 OR
NVL(gjl.entered_cr,0) <> 0)
and (NVL(xal.entered_dr,0) <> 0 OR
NVL(xal.entered_cr,0) <> 0)
and xdl.accounting_line_code NOT LIKE ''FV_REQ_ADJ%'''||
' AND gjh.je_from_sla_flag = ''Y''
'||' '||g_src_where||' '||g_cat_where||' '||g_segs_where||
' '||g_aid_where||' '||g_main_acct_where||
' ORDER BY fund_value , ' || g_acct_segment_name ;
l_jrnl_select := l_jrnl_select_gl||' UNION ALL '||l_jrnl_select_xla;
DBMS_SQL.PARSE(l_jrnl_cursor, l_jrnl_select, DBMS_SQL.V7);
log(l_module, l_module||'.select1'||g_error_buf);
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module||'.select1',g_error_buf);
SELECT 'X', ussgl_enabled_flag
INTO l_exists, l_ussgl_enabled
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = l_account_number;
SELECT 'X'
INTO l_exists
FROM FV_GTAS_ATTRIBUTES
WHERE gtas_acct_number = l_account_number
AND set_of_books_id = g_set_of_books_id;
SELECT aid.invoice_id,
aid.distribution_line_number
INTO l_reference_2,
l_reference_8
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id = l_source_distribution_id_num_1;
SELECT aid.invoice_id,
aid.distribution_line_number
INTO l_reference_2,
l_reference_8
FROM ap_invoice_distributions_all aid
WHERE aid.invoice_distribution_id = l_applied_to_dist_id_num_1;
SELECT aip.check_id,
aid.invoice_id,
aid.accounting_date
INTO l_reference_3,
l_reference_4,
l_reference_6
FROM ap_payment_hist_dists aphd,
ap_invoice_distributions_all aid,
ap_invoice_payments_all aip
WHERE aphd.payment_hist_dist_id = l_source_distribution_id_num_1
AND aid.invoice_distribution_id = aphd.invoice_distribution_id
AND aip.invoice_payment_id = aphd.invoice_payment_id;
SELECT poh.po_header_id,
poh.segment1
INTO l_reference_2,
l_reference_4
FROM po_distributions_all pod,
po_headers_all poh
WHERE pod.po_distribution_id = l_source_distribution_id_num_1
AND pod.po_header_id = poh.po_header_id;
SELECT alloc_to_dist_id_num_1
INTO l_reference_3 --po_dist_id
FROM xla_distribution_links xd
WHERE xd.source_distribution_id_num_1 = l_source_distribution_id_num_1
AND xd.event_id = l_sla_hdr_event_id
AND ROWNUM = 1;
SELECT poh.po_header_id,
poh.segment1
INTO l_reference_2,
l_reference_4
FROM po_distributions_all pod,
po_headers_all poh
WHERE pod.po_distribution_id = l_reference_3
AND pod.po_header_id = poh.po_header_id;
SELECT transaction_number
INTO l_reference_4 -- po number
FROM xla_transaction_entities
WHERE entity_id = l_sla_entity_id;
SELECT poh.requisition_header_id,
poh.segment1
INTO l_reference_2,
l_reference_4
FROM po_req_distributions_all pod,
po_requisition_headers_all poh,
po_requisition_lines_all pol
WHERE pod.distribution_id = l_source_distribution_id_num_1
AND pol.requisition_header_id = poh.requisition_header_id
AND pod.requisition_line_id = pol.requisition_line_id;
SELECT aip.check_id,
aid.invoice_id,
aid.accounting_date
INTO l_reference_3,
l_reference_4,
l_reference_6
FROM ap_payment_hist_dists aphd,
ap_invoice_distributions_all aid,
ap_invoice_payments_all aip
WHERE aphd.payment_hist_dist_id = l_source_distribution_id_num_1
AND aid.invoice_distribution_id = aphd.invoice_distribution_id
AND aip.invoice_payment_id = aphd.invoice_payment_id;
SELECT poh.po_header_id,
poh.segment1
INTO l_reference_2,
l_reference_4
FROM po_distributions_all pod,
po_headers_all poh
WHERE pod.po_distribution_id = l_applied_to_dist_id_num_1
AND pod.po_header_id = poh.po_header_id;
SELECT rcv_transaction_id
INTO l_reference_5
FROM rcv_receiving_sub_ledger
WHERE rcv_sub_ledger_id = l_source_distribution_id_num_1;
SELECT source_id,
source_table,
source_type
INTO l_ar_source_id,
l_ar_source_table,
l_ar_source_type
FROM ar_distributions_all
WHERE line_id = l_source_distribution_id_num_1;
SELECT receipt_number,
--hca.party_id
to_char(hca.cust_account_id)
INTO l_reference_4,
l_reference_7
FROM ar_receivable_applications_all ara,
ar_cash_receipts_all acr,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca
WHERE ara.receivable_application_id = l_ar_source_id
AND ara.cash_receipt_id = acr.cash_receipt_id
AND hcsu.site_use_id = acr.customer_site_use_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hca.cust_account_id = hcas.cust_account_id;
SELECT receipt_number,
--hca.party_id
to_char(hca.cust_account_id)
INTO l_reference_4,
l_reference_7
FROM ar_cash_receipt_history_all ara,
ar_cash_receipts_all acr,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca
WHERE ara.cash_receipt_history_id = l_ar_source_id
AND ara.cash_receipt_id = acr.cash_receipt_id
AND hcsu.site_use_id = acr.customer_site_use_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hca.cust_account_id = hcas.cust_account_id;
SELECT receipt_number,
--hca.party_id
to_char(hca.cust_account_id)
INTO l_reference_4,
l_reference_7
FROM ar_adjustments_all ara,
ar_cash_receipts_all acr,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca
WHERE ara.adjustment_id = l_ar_source_id
AND ara.associated_cash_receipt_id = acr.cash_receipt_id
AND hcsu.site_use_id = acr.customer_site_use_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hca.cust_account_id = hcas.cust_account_id;
SELECT receipt_number,
--hca.party_id
to_char(hca.cust_account_id)
INTO l_reference_4,
l_reference_7
FROM ar_misc_cash_distributions_all ara,
ar_cash_receipts_all acr,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts hca
WHERE ara.misc_cash_distribution_id = l_ar_source_id
AND ara.cash_receipt_id = acr.cash_receipt_id
AND hcsu.site_use_id = acr.customer_site_use_id
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hca.cust_account_id = hcas.cust_account_id;
SELECT rcth.trx_number,
to_char(rcth.bill_to_customer_id)
INTO l_reference_4,
l_reference_7
FROM ar_transaction_history_all ara,
ra_customer_trx_all rcth
WHERE ara.transaction_history_id = l_ar_source_id
AND ara.customer_trx_id = rcth.customer_trx_id;
SELECT account_class
INTO l_account_class
FROM ra_cust_trx_line_gl_dist_all
WHERE cust_trx_line_gl_dist_id = l_source_distribution_id_num_1;
SELECT rcth.trx_number,
to_char(rcth.bill_to_customer_id),
rcth.customer_trx_id
INTO l_reference_4,
l_reference_7,
l_reference_2
FROM ra_cust_trx_line_gl_dist_all rctgl,
ra_customer_trx_lines_all rctl,
ra_customer_trx_all rcth
WHERE rctgl.cust_trx_line_gl_dist_id = l_source_distribution_id_num_1
AND rctl.customer_trx_line_id = rctgl.customer_trx_line_id
AND rcth.customer_trx_id = rctl.customer_trx_id;
SELECT rcth.trx_number,
to_char(rcth.bill_to_customer_id),
rcth.customer_trx_id
INTO l_reference_4,
l_reference_7,
l_reference_2
FROM ra_cust_trx_line_gl_dist_all rctgl,
ra_customer_trx_all rcth
WHERE rctgl.cust_trx_line_gl_dist_id = l_source_distribution_id_num_1
AND rcth.customer_trx_id = rctgl.customer_trx_id;
SELECT source_id,
source_table,
source_type
INTO l_ar_source_id,
l_ar_source_table,
l_ar_source_type
FROM ar_distributions_all
WHERE line_id = l_source_distribution_id_num_1;
--Restricting the select to fetch the most recent
--row from cash receipt history.
SELECT acr.cash_receipt_id,
max(acrh.cash_receipt_history_id),
acr.receipt_number
INTO l_reference_2,
l_reference_5,
l_reference_4
FROM ar_misc_cash_distributions_all ara,
ar_cash_receipt_history_all acrh,
ar_cash_receipts_all acr
WHERE ara.misc_cash_distribution_id = l_ar_source_id
AND ara.cash_receipt_id = acr.cash_receipt_id
AND ara.cash_receipt_id = acrh.cash_receipt_id
group by acr.cash_receipt_id, acr.receipt_number;
SELECT SUBSTR(l_refer2, 0, decode(INSTR(l_refer2, 'C'), 0,
LENGTH(l_refer2),INSTR(l_refer2,'C')-1))
INTO l_reference_2
FROM dual;
SELECT v.vendor_id vendor_id,
NVL(v.global_attribute4,'N') vendor_type,
SUBSTR(v.global_attribute5,1,3)
INTO l_vendor_id, l_vendor_type, l_eliminations_id
FROM ap_invoices_all i,
po_vendors v
WHERE i.invoice_id = to_number(l_reference_2)
AND i.vendor_id = v.vendor_id;
log(l_module,'.select_1: NO DATA FOUND !!');
SELECT v.vendor_id VENDOR_ID,
NVL(v.global_attribute4,'N') vendor_type,
substr(v.global_attribute5,1,3)
INTO l_vendor_id,l_vendor_type,l_eliminations_id
FROM rcv_transactions rt,
po_vendors v,
po_headers_all ph
WHERE rt.po_header_id = to_number(l_reference_2)
AND rt.transaction_id = to_number(l_reference_5)
AND rt.po_header_id = ph.po_header_id
AND v.vendor_id = ph.vendor_id;
SELECT pov.vendor_id,
NVL(pov.global_attribute4,'N') vendor_type,
SUBSTR(pov.global_attribute5,1,3)
INTO l_vendor_id,
l_vendor_type,
l_eliminations_id
FROM po_vendors pov, po_headers_all poh
WHERE poh.po_header_id = to_number(l_reference_2)
AND pov.vendor_id = poh.vendor_id;
log(l_module,'12.2 or above, using dynamic select');
l_select := ' SELECT cust_account_id,
NVL(federal_entity_type,''N''),
SUBSTR(trading_partner_agency_id,1,3)
FROM hz_cust_accounts_all
WHERE cust_account_id = :party_id';
EXECUTE IMMEDIATE l_select
INTO l_vendor_id,
l_vendor_type,
l_eliminations_id
USING to_number(l_reference_7);
SELECT c.cust_account_id,
DECODE(c.customer_class_code, 'FEDERAL','F',
'GENERAL FUND', 'G',
'NON-FEDERAL EXCEPTION', 'E',
'N'),
DECODE(g_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
INTO l_vendor_id,
l_vendor_type,
l_eliminations_id
FROM hz_cust_accounts_all c
WHERE c.cust_account_id = to_number(l_reference_7);
SELECT v.vendor_id vendor_id,
NVL(v.global_attribute4,'N') vendor_type,
SUBSTR(v.global_attribute5,1,3)
INTO l_vendor_id,l_vendor_type,l_eliminations_id
FROM ap_checks_all apc,
po_vendors v
WHERE apc.vendor_id = v.vendor_id
AND apc.check_id = to_number(l_reference_3);
SELECT h.doc_number, SUBSTR(d.dept_id,1,3), d.main_account
FROM fv_be_trx_hdrs h,
fv_be_trx_dtls d
WHERE d.transaction_id = to_number(l_reference_1)
AND h.doc_id = d.doc_id;
--SELECT dept_id||main_account
--FROM fv_be_trx_dtls
--WHERE transaction_id = to_number(l_reference_1);
SELECT hzca.customer_class_code
INTO l_cust_class_code
FROM ra_customer_trx rct,
hz_cust_accounts hzca
WHERE rct.trx_number = l_doc_number
AND rct.set_of_books_id = g_set_of_books_id
AND hzca.cust_account_id = rct.bill_to_customer_id;
l_select VARCHAR2(1000);
SELECT inv.invoice_num,
inv.invoice_date,
inv.creation_date,
inv.created_by
FROM ap_invoices_all inv
WHERE inv.invoice_id = to_number(p_reference2);
SELECT DISTINCT api.invoice_num,
DECODE(apc.payment_type_flag,'A',apc.check_date,
NVL(apc.treasury_pay_date, apc.check_date)) check_date,
apip.creation_date,
apip.created_by
FROM ap_checks_all apc,
ap_invoices_all api,
ap_invoice_payments_all apip
WHERE apc.check_id = to_number(p_reference3)
AND api.invoice_id = to_number(p_reference2)
AND apc.check_id = apip.check_id
AND api.invoice_id = apip.invoice_id;
SELECT DECODE(l_rev_exists, 'Y', reversal_date, receipt_date),
DECODE(l_rev_exists, 'Y', l_doc_creation_date_d,creation_date),
DECODE(l_rev_exists, 'Y', l_doc_created_by_d, created_by)
FROM ar_cash_receipts_all
WHERE cash_receipt_id = to_number(l_refer2);
SELECT 'Y'
FROM ar_cash_receipt_history_all
WHERE cash_receipt_history_id = TO_NUMBER(l_cash_receipt_hist_id);
SELECT cash_receipt_history_id
FROM ar_receivable_applications_all
WHERE receivable_application_id = TO_NUMBER(l_cash_receipt_hist_id);
SELECT 'Y', status
FROM ar_cash_receipt_history_all
WHERE cash_receipt_history_id = c_cash_receipt_hist_id
AND cash_receipt_id = c_cash_receipt_id;
SELECT 'Y', creation_date, created_by
FROM ar_cash_receipt_history_all
WHERE reversal_cash_receipt_hist_id = c_cash_receipt_hist_id;
SELECT void_date, checkrun_name
FROM ap_checks_all
WHERE check_id = p_reference3;
SELECT creation_date, created_by
FROM ap_invoice_payments_all
WHERE check_id = p_reference3
AND invoice_payment_id = (SELECT max(invoice_payment_id)
FROM ap_invoice_payments_all
WHERE check_id = p_reference3);
SELECT ftc.checkrun_name,
ftc.treasury_doc_date,
ftc.creation_date,
ftc.created_by
FROM fv_treasury_confirmations_all ftc
WHERE ftc.treasury_confirmation_id = to_number(p_reference1);
SELECT void_date,
DECODE(payment_type_flag,'A',check_date,
NVL(treasury_pay_date,check_date)) check_date
FROM ap_checks_all
WHERE check_id = p_reference3;
SELECT NVL(MAX(invoice_payment_id),0)
FROM ap_invoice_payments_all
WHERE invoice_id = NVL(p_reference2, 0)
AND check_id = NVL(p_reference3,0)
AND invoice_payment_id > p_reference9;
SELECT api.invoice_num, apip.creation_date,
apip.created_by
FROM ap_invoice_payments_all apip,
ap_invoices_all api
WHERE api.invoice_id = NVL(p_reference2, 0)
AND api.invoice_id = apip.invoice_id
AND apip.check_id = NVL(p_reference3,0)
AND apip.invoice_payment_id = p_reference9;
SELECT api.invoice_num, apc.creation_date,
apc.created_by
FROM ap_checks_all apc,
ap_invoices_all api,
ap_invoice_payments_all apip
WHERE apc.check_id = to_number(p_reference3)
AND api.invoice_id = to_number(p_reference2)
AND apc.check_id = apip.check_id
AND api.invoice_id = apip.invoice_id;
SELECT h.doc_number, d.gl_date, d.creation_date,
d.created_by
FROM fv_be_trx_dtls d, fv_be_trx_hdrs h
WHERE d.transaction_id = to_number(p_reference1)
AND h.doc_id = d.doc_id;
SELECT default_effective_date
FROM gl_je_headers
WHERE je_header_id = p_je_header_id;
SELECT apply_date, creation_date, created_by
FROM ar_adjustments_all
WHERE adjustment_id = l_refer2;
SELECT apply_date, creation_date, created_by
FROM ar_receivable_applications_all
WHERE receivable_application_id = l_refer2;
SELECT trx_date, creation_date, created_by
FROM ra_customer_trx_all
WHERE customer_trx_id = l_refer2;
-- system parameters form, then select that
-- column's value from req headers. If that value
-- is not a date, then select creation date as the
-- doc date. If an attribute col has not been
-- defined, then select creation date as the doc
-- date.
IF g_req_date_seg IS NOT NULL THEN
l_select :=
'SELECT '||g_req_date_seg||',
creation_date, created_by
FROM po_requisition_headers_all
WHERE requisition_header_id = '||
to_number(p_reference2) ;
OPEN pur_req FOR l_select ;
SELECT to_date(l_temp_doc_date, 'mm/dd/yyrr')
INTO l_doc_date
FROM DUAL;
l_select :=
'SELECT creation_date, creation_date, created_by
FROM po_requisition_headers_all
WHERE requisition_header_id = '
||to_number(p_reference2) ;
OPEN pur_req FOR l_select ;
l_select :=
'SELECT creation_date, creation_date, created_by
FROM po_requisition_headers_all
WHERE requisition_header_id = '||to_number(p_reference2) ;
OPEN pur_req FOR l_select ;
l_select :=
'SELECT h.'||g_pur_order_date_seg||', h.creation_date,
h.created_by, d.gl_encumbered_date
FROM po_headers_all h,
po_distributions_all d
WHERE h.po_header_id = '||p_reference2 ||'
AND h.po_header_id = d.po_header_id
AND d.po_distribution_id = '||p_reference3;
OPEN pur_pur FOR l_select ;
SELECT to_date(l_temp_doc_date, 'mm/dd/yyrr')
INTO l_doc_date
FROM DUAL;
l_select :=
'SELECT h.creation_date, h.creation_date,
h.created_by, d.gl_encumbered_date
FROM po_headers_all h,
po_distributions_all d
WHERE h.po_header_id = '||p_reference2 ||'
AND h.po_header_id = d.po_header_id
AND d.po_distribution_id = '||p_reference3;
OPEN pur_pur FOR l_select ;
l_select :=
'SELECT h.creation_date, h.creation_date,
h.created_by, d.gl_encumbered_date
FROM po_headers_all h,
po_distributions_all d
WHERE h.po_header_id = '||p_reference2 ||'
AND h.po_header_id = d.po_header_id
AND d.po_distribution_id = '||p_reference3;
OPEN pur_pur FOR l_select ;
l_select :=
'SELECT rt.'||g_rec_trxn_date_seg||',
rcv.receipt_num,
rt.creation_date,
rt.created_by
FROM rcv_transactions rt,
rcv_shipment_headers rcv
WHERE rt.shipment_header_id = rcv.shipment_header_Id
AND rt.transaction_id = '||to_number(p_reference5) ;
log(l_module,L_SELECT);
OPEN pur_rec FOR l_select ;
SELECT to_date(l_temp_doc_date, 'mm/dd/yyrr')
INTO l_doc_date
FROM DUAL;
l_select :=
'SELECT rt.transaction_date,
rcv.receipt_num,
rt.creation_date,
rt.created_by
FROM rcv_transactions rt,
rcv_shipment_headers rcv
WHERE rt.shipment_header_id = rcv.shipment_header_Id
AND rt.transaction_id = '||to_number(p_reference5);
OPEN pur_rec FOR l_select ;
l_select :=
'SELECT rt.transaction_date,
rcv.receipt_num,
rt.creation_date,
rt.created_by
FROM rcv_transactions rt,
rcv_shipment_headers rcv
WHERE rt.shipment_header_id = rcv.shipment_header_Id
AND rt.transaction_id = '||to_number(p_reference5) ;
OPEN pur_rec FOR l_select ;
SELECT e.event_type_code
INTO l_event_type_code
FROM ap_ae_lines_all l,
ap_ae_headers_all h,
ap_accounting_events_all e
WHERE l.source_table = 'AP_INVOICES'
AND l.source_id = p_reference2
AND l.ae_header_id = h.ae_header_id
AND l.gl_sl_link_id = p_gl_sl_link_id
AND e.accounting_event_id = h.accounting_event_id;
SELECT a.parent_reversal_id
INTO l_parent_reversal_id
FROM ap_invoice_distributions a
WHERE a.invoice_id = p_reference2
AND a.distribution_line_number = p_reference3;
SELECT e.event_type_code
INTO l_event_type_code
FROM ap_ae_lines_all l,
ap_ae_headers_all h,
ap_accounting_events_all e
WHERE l.source_table = 'AP_INVOICE_PAYMENTS'
AND l.source_id = p_reference9
AND l.ae_header_id = h.ae_header_id
AND l.gl_sl_link_id = p_gl_sl_link_id
AND e.accounting_event_id = h.accounting_event_id;
SELECT invoice_num
INTO l_document_num
FROM ap_invoices_all
WHERE invoice_id = p_reference4;
l_select := 'SELECT ''Y'', '||p_rec_public_law_code_col||'
FROM ar_misc_cash_distributions_all
WHERE misc_cash_distribution_id = '||to_number(l_cash_receipt_hist_id);
OPEN Receivables_Distrib FOR l_select;
l_select := 'SELECT ''Y''
FROM ar_misc_cash_distributions_all
WHERE misc_cash_distribution_id = '||to_number(l_cash_receipt_hist_id);
OPEN Receivables_Distrib FOR l_select;
l_select := ' SELECT ''Y'', creation_date, created_by, '||
p_rec_public_law_code_col||'
FROM ar_misc_cash_distributions_all
WHERE misc_cash_distribution_id = '||
l_cash_receipt_hist_id||'
AND created_from = ''ARP_REVERSE_RECEIPT.REVERSE''';
OPEN Receivables_Misc FOR l_select;
l_select := ' SELECT ''Y'', creation_date, created_by
FROM ar_misc_cash_distributions_all
WHERE misc_cash_distribution_id = '||
l_cash_receipt_hist_id||'
AND created_from = ''ARP_REVERSE_RECEIPT.REVERSE''';
OPEN Receivables_Misc FOR l_select;
l_select := 'SELECT '||p_rec_public_law_code_col||'
FROM ar_misc_cash_distributions_all
WHERE misc_cash_distribution_id = '||
to_number(l_cash_receipt_hist_id);
OPEN Receivables_Distrib FOR l_select;
SELECT user_name
INTO p_entry_user
FROM fnd_user
WHERE user_id = p_created_by;
SELECT ussgl_enabled_flag,
reporting_type
INTO l_enabled_flag,
l_reporting_type
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = p_ussgl_acct_num;
l_fund_select VARCHAR2(2000);
l_fund_select := 'SELECT ''X'', fts.fund_group_code,
fts.department_id, ' ||
'fts.bureau_id, ' ||
'glc.' || g_bal_segment_name || ' ' ||
'FROM gl_code_combinations glc,
fv_fund_parameters ffp, ' ||
'fv_treasury_symbols fts ' ||
'WHERE glc.code_combination_id = :ccid
AND glc.chart_of_accounts_id = :coa_id
AND ffp.treasury_symbol_id = fts.treasury_symbol_id
AND ffp.set_of_books_id = :set_of_books_id
AND glc.' || g_bal_segment_name || ' = ffp.fund_value';
EXECUTE IMMEDIATE l_fund_select
INTO l_row_exists, l_fund_group,
l_dept_id, l_bureau_id,
l_bal_segment
USING p_ccid, g_coa_id, g_set_of_books_id;
l_bal_select Varchar2(2000);
l_bal_select := 'SELECT glc.' || g_bal_segment_name || ' '
||'FROM gl_code_combinations glc '
||'WHERE glc.code_combination_id = ' || to_char(p_ccid);
EXECUTE IMMEDIATE l_bal_select INTO p_bal_segment;
INSERT INTO fv_gtas_trx_reg_temp
(ACCOUNT_NUMBER ,
TREASURY_SYMBOL_ID ,
SET_OF_BOOKS_ID ,
CODE_COMBINATION_ID,
EXCH_NON_EXCH,
CUST_NON_CUST,
fund_value,
DIRECT_OR_REIMB_CODE ,
APPOR_CAT_CODE,
APPOR_CAT_B_CODE,
DOCUMENT_SOURCE ,
DOCUMENT_CATEGORY,
DOCUMENT_NUMBER,
DOCUMENT_DATE ,
TRANSACTION_DATE,
ENTRY_USER ,
PROGRAM_REPT_CODE,
debit,
credit,
TRANSFER_DEPT_ID,
TRANSFER_MAIN_ACCT,
YEAR_BUDGET_AUTH ,
NEW_BAL_CODE,
CUR_SUBSEQUENT_CODE,
ADVANCE_FLAG,
CREDIT_COHORT_YR,
PROGRAM_COST_IND,
TRANSACTION_PARTNER,
REIMBURSEABLE_FLAG ,
BEA_CAT_CODE,
BORR_SRC_CODE,
DEF_LIQUID_FLAG,
DEFICIENCY_FLAG,
AVAILABILITY_FLAG,
LEGISLATION_FLAG ,
INDEF_DEF_FLAG,
PYA_CODE ,
JOURNAL_CREATION_DATE,
JOURNAL_MODIFIED_DATE,
PERIOD_NAME ,
GL_DATE ,
GL_POSTED_DATE ,
REVERSAL_FLAG ,
SLA_HDR_EVENT_ID,
SLA_HDR_CREATION_DATE ,
SLA_ENTITY_ID,
fed_non_fed,
authority_type_code,
trading_partner_agency_id,
trading_partner_main_account,
budget_impact_ind,
ussgl_account)
VALUES
( p_account_number ,
p_treasury_symbol_id ,
p_set_of_books_id ,
p_code_combination_id,
p_exch_non_exch ,
p_cust_non_cust ,
p_bal_segment ,
p_DIRECT_OR_REIMB_CODE ,
p_APPOR_CAT_CODE ,
p_APPOR_CAT_B_CODE ,
p_source ,
p_category ,
p_doc_num ,
p_doc_date ,
p_doc_creation_date ,
p_entry_user ,
p_PROGRAM_REPT_CODE ,
p_entered_dr ,
p_entered_cr ,
p_transfer_dept_id ,
p_transfer_main_acct ,
p_year_budget_auth ,
p_new_bal_code ,
p_cur_subsequent_code,
p_advance_type_val ,
p_credit_cohort_yr ,
p_program_cost_ind ,
p_transaction_partner_val,
p_reimburseable_val ,
p_BEA_CAT_CODE ,
p_BORR_SRC_CODE ,
p_def_liquid_flag ,
p_deficiency_flag ,
p_availability_val ,
p_legis_ind_val ,
p_def_indef_val ,
p_PYA_CODE ,
p_je_line_creation_date ,
p_je_line_modified_date ,
p_je_line_period_name ,
p_gl_date ,
p_gl_posted_date ,
p_reversal_flag ,
p_sla_hdr_event_id ,
p_sla_hdr_creation_date,
p_sla_entity_id,
p_fed_non_fed,
p_authority_type_val,
p_tp_agency_id,
p_tp_main_acct,
p_budget_impact_ind,
p_ussgl_account
);
DELETE FROM fv_gtas_trx_reg_temp;
SELECT SUBSTR(parent_flex_value,1,6)
Into sgl_acct_num
From fnd_flex_value_hierarchies
WHERE (acct_num BETWEEN child_flex_value_low
AND child_flex_value_high)
AND parent_flex_value <> 'T'
AND flex_value_set_id = g_acc_value_set_id
AND SUBSTR(parent_flex_value,1,6) IN
(SELECT ussgl_account
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = SUBSTR(parent_flex_value,1,6)
AND ussgl_enabled_flag = 'Y')
AND parent_flex_value IN
(
SELECT flex_value
FROM fnd_flex_values
WHERE flex_value_set_id = g_acc_value_set_id
AND enabled_flag = 'Y'
);
SELECT 'X'
INTO l_exists
FROM fv_gtas_attributes
WHERE gtas_acct_number = sgl_acct_num
AND set_of_books_id = g_set_of_books_id;
SELECT fpfs.cohort_segment_name
INTO g_cohort_seg_name
FROM FV_PYA_FISCALYEAR_SEGMENT fpfs
WHERE fpfs.set_of_books_id = g_set_of_books_id;
SELECT fts.financing_account,
fpfs.cohort_segment_name
INTO l_financing_acct,
g_cohort_seg_name
FROM FV_FACTS_FEDERAL_ACCOUNTS FFFA,
FV_TREASURY_SYMBOLS FTS,
FV_PYA_FISCALYEAR_SEGMENT fpfs
WHERE FFFA.Federal_acct_symbol_id = FTS.Federal_acct_symbol_id
AND FTS.treasury_symbol_id = g_treasury_symbol_id
AND FTS.set_of_books_id = g_set_of_books_id
AND fpfs.set_of_books_id = g_set_of_books_id
AND FFFA.set_of_books_id = g_set_of_books_id ;
l_select VARCHAR2(32767);
SELECT document_number,
min(transaction_date) transaction_date ,
min(creation_date_time) creation_date_time ,
min(journal_creation_date) journal_creation_date ,
min(journal_modified_date) journal_modified_date ,
min(gl_date) gl_date ,
min(gl_posted_date) gl_posted_date,
min(sla_hdr_event_id) sla_hdr_event_id,
min(sla_hdr_creation_date) sla_hdr_creation_date,
min(sla_entity_id) sla_entity_id
FROM fv_gtas_trx_reg_temp
WHERE document_source = 'Purchasing'
AND document_category = 'Receiving'
GROUP BY document_number;
UPDATE fv_gtas_trx_reg_temp
SET transaction_date = pur_recpt.transaction_date,
creation_date_time = pur_recpt.creation_date_time,
journal_creation_date = pur_recpt.journal_creation_date,
journal_modified_date = pur_recpt.journal_modified_date,
gl_date = pur_recpt.gl_date,
gl_posted_date = pur_recpt.gl_posted_date,
sla_hdr_event_id = pur_recpt.sla_hdr_event_id,
sla_hdr_creation_date = pur_recpt.sla_hdr_creation_date,
sla_entity_id = pur_recpt.sla_entity_id
WHERE document_source = 'Purchasing'
AND document_category = 'Receiving'
AND document_number = pur_recpt.document_number;
l_select :=
' insert into fv_gtas_trx_reg_temp (
treasury_symbol_id ,
set_of_books_id ,
code_combination_id ,
fund_value ,
account_number ,
document_source ,
document_category ,
document_number ,
transaction_date ,
creation_date_time ,
entry_user ,
fed_non_fed ,
-- trading_partner ,
exch_non_exch ,
cust_non_cust ,
-- budget_subfunction ,
debit ,
credit ,
transfer_dept_id ,
transfer_main_acct ,
year_budget_auth ,
-- budget_function ,
advance_flag ,
CREDIT_COHORT_YR ,
begin_end ,
indef_def_flag ,
APPOR_CAT_CODE ,
APPOR_CAT_B_CODE ,
PROGRAM_REPT_CODE ,
-- PROGRAM_RPT_CAT_TXT ,
--public_law ,
--appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
BEA_CAT_CODE ,
BORR_SRC_CODE ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
PYA_CODE ,
journal_creation_date ,
journal_modified_date ,
period_name ,
gl_date ,
gl_posted_date,
reversal_flag ,
sla_hdr_event_id,
sla_hdr_creation_date,
sla_entity_id, period_activity )
SELECT
treasury_symbol_id ,
set_of_books_id ,
code_combination_id ,
fund_value ,
account_number ,
document_source ,
document_category ,
document_number ,
transaction_date ,
creation_date_time ,
entry_user ,
fed_non_fed ,
-- trading_partner ,
exch_non_exch ,
cust_non_cust ,
-- budget_subfunction ,
'||l_var1 ||' ,
transfer_dept_id ,
transfer_main_acct ,
year_budget_auth ,
-- budget_function ,
advance_flag ,
CREDIT_COHORT_YR ,
begin_end ,
indef_def_flag ,
APPOR_CAT_CODE ,
APPOR_CAT_B_CODE ,
PROGRAM_REPT_CODE ,
-- PROGRAM_RPT_CAT_TXT ,
--public_law ,
-- appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
BEA_CAT_CODE ,
BORR_SRC_CODE ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
PYA_CODE ,
journal_creation_date ,
journal_modified_date ,
period_name ,
gl_date ,
gl_posted_date,
reversal_flag ,
sla_hdr_event_id,
sla_hdr_creation_date,
sla_entity_id,
-1111
FROM fv_gtas_trx_reg_temp
WHERE '||l_var2 || ' <> 0
and --(document_source <> ''Receivables'' and
document_category <> ''Trade Receipts''
GROUP BY treasury_symbol_id ,
set_of_books_id ,
code_combination_id ,
fund_value ,
account_number ,
document_source ,
document_category ,
document_number ,
entry_user ,
fed_non_fed ,
-- trading_partner ,
exch_non_exch ,
cust_non_cust ,
-- budget_subfunction ,
transfer_dept_id ,
transfer_main_acct ,
year_budget_auth ,
-- budget_function ,
advance_flag ,
CREDIT_COHORT_YR ,
begin_end ,
indef_def_flag ,
APPOR_CAT_CODE ,
APPOR_CAT_B_CODE ,
PROGRAM_REPT_CODE ,
-- PROGRAM_RPT_CAT_TXT ,
-- public_law ,
-- appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
BEA_CAT_CODE ,
BORR_SRC_CODE ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
PYA_CODE ,
period_name ,
reversal_flag ,
-1111 ,
transaction_date ,
creation_date_time , gl_date ,
gl_posted_date, journal_creation_date ,
journal_modified_date, sla_hdr_event_id,
sla_hdr_creation_date,
sla_entity_id ';
EXECUTE IMMEDIATE l_select;
UPDATE fv_gtas_trx_reg_temp
SET period_activity = -1111
WHERE --document_source = 'Receivables'
--AND
document_category = 'Trade Receipts';
DELETE FROM fv_gtas_trx_reg_temp
WHERE NVL(period_activity, -0000) <> -1111;
SELECT hzca.customer_class_code
INTO l_cust_class_code
FROM ra_customer_trx_all rct,
hz_cust_accounts_all hzca
WHERE rct.trx_number = p_reimb_agree_seg_val
AND rct.set_of_books_id = g_set_of_books_id
AND hzca.cust_account_id = rct.bill_to_customer_id;
SELECT hzca.customer_class_code
INTO l_cust_class_code
FROM ra_customer_trx_all rct,
hz_cust_accounts_all hzca
WHERE rct.trx_number = p_reimb_agree_seg_val
AND rct.set_of_books_id = g_set_of_books_id
AND hzca.cust_account_id = rct.bill_to_customer_id;
INSERT INTO fv_gtas_trx_reg_temp (
treasury_symbol_id ,
set_of_books_id ,
code_combination_id ,
fund_value ,
account_number ,
document_source ,
document_category ,
document_number ,
transaction_date ,
creation_date_time ,
entry_user ,
fed_non_fed ,
-- trading_partner ,
exch_non_exch ,
cust_non_cust ,
-- budget_subfunction ,
debit ,
credit ,
transfer_dept_id ,
transfer_main_acct ,
year_budget_auth ,
-- budget_function ,
advance_flag ,
CREDIT_COHORT_YR ,
begin_end ,
indef_def_flag ,
APPOR_CAT_CODE ,
APPOR_CAT_B_CODE ,
PROGRAM_REPT_CODE ,
-- PROGRAM_RPT_CAT_TXT ,
-- public_law ,
-- appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
BEA_CAT_CODE ,
BORR_SRC_CODE ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
PYA_CODE ,
journal_creation_date ,
journal_modified_date ,
period_name ,
gl_date ,
gl_posted_date,
reversal_flag ,
sla_hdr_event_id,
sla_hdr_creation_date,
sla_entity_id, period_activity )
select
treasury_symbol_id ,
set_of_books_id ,
code_combination_id ,
fund_value ,
account_number ,
document_source ,
document_category ,
document_number ,
(transaction_date) ,
(creation_date_time) ,
entry_user ,
fed_non_fed ,
-- trading_partner ,
exch_non_exch ,
cust_non_cust ,
-- budget_subfunction ,
(case when (debit-credit) >= 0 then (debit-credit) else 0 end) debit,
(case when (debit-credit) < 0 then abs(debit-credit) else 0 end) credit,
transfer_dept_id ,
transfer_main_acct ,
year_budget_auth ,
-- budget_function ,
advance_flag ,
CREDIT_COHORT_YR ,
begin_end ,
indef_def_flag ,
APPOR_CAT_CODE ,
APPOR_CAT_B_CODE ,
PROGRAM_REPT_CODE ,
-- PROGRAM_RPT_CAT_TXT ,
-- public_law ,
-- appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
BEA_CAT_CODE ,
BORR_SRC_CODE ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
PYA_CODE ,
(journal_creation_date) ,
(journal_modified_date) ,
period_name ,
(gl_date) ,
(gl_posted_date),
reversal_flag ,
(sla_hdr_event_id),
(sla_hdr_creation_date),
(sla_entity_id) ,
-8888
from (
SELECT
treasury_symbol_id,
set_of_books_id ,
code_combination_id ,
fund_value ,
account_number ,
document_source ,
document_category ,
document_number ,
(transaction_date) ,
(creation_date_time) ,
entry_user ,
fed_non_fed ,
-- trading_partner ,
exch_non_exch ,
cust_non_cust ,
-- budget_subfunction ,
sum(debit) debit,
sum(credit) credit ,
transfer_dept_id ,
transfer_main_acct ,
year_budget_auth ,
-- budget_function ,
advance_flag ,
CREDIT_COHORT_YR ,
begin_end ,
indef_def_flag ,
APPOR_CAT_CODE ,
APPOR_CAT_B_CODE ,
PROGRAM_REPT_CODE ,
-- PROGRAM_RPT_CAT_TXT ,
-- public_law ,
-- appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
BEA_CAT_CODE ,
BORR_SRC_CODE ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
PYA_CODE ,
(journal_creation_date) ,
(journal_modified_date) ,
period_name ,
(gl_date) ,
(gl_posted_date),
reversal_flag ,
(sla_hdr_event_id),
(sla_hdr_creation_date),
(sla_entity_id)
FROM fv_gtas_trx_reg_temp
WHERE document_source = 'Payables'
AND document_category = 'Purchase Invoices'
group by treasury_symbol_id, set_of_books_id,
code_combination_id, fund_value, account_number,
document_source, document_category, document_number,
(transaction_date), (creation_date_time), entry_user, fed_non_fed,
exch_non_exch, cust_non_cust, transfer_dept_id, transfer_main_acct,
year_budget_auth, advance_flag, CREDIT_COHORT_YR, begin_end, indef_def_flag,
APPOR_CAT_CODE, APPOR_CAT_B_CODE, PROGRAM_REPT_CODE, authority_type, transaction_partner,
reimburseable_flag, BEA_CAT_CODE, BORR_SRC_CODE, def_liquid_flag, deficiency_flag,
availability_flag, legislation_flag, PYA_CODE, (journal_creation_date),
(journal_modified_date), period_name, (gl_date), (gl_posted_date), reversal_flag,
(sla_hdr_event_id), (sla_hdr_creation_date), (sla_entity_id) );
DELETE from fv_gtas_trx_reg_temp
WHERE document_source = 'Payables'
AND document_category = 'Purchase Invoices'
and period_activity <> -8888;
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 = g_set_of_books_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 = g_set_of_books_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 = g_set_of_books_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 = g_set_of_books_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 = g_set_of_books_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 = g_set_of_books_id);
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 = g_set_of_books_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;
SELECT gtas_acct_number
INTO p_account_num
FROM fv_gtas_attributes
WHERE set_of_books_id = g_set_of_books_id
AND gtas_acct_number = p_gtas_acct_num;
SELECT 'Y'
INTO p_code_exists
FROM fv_gtas_attributes
WHERE gtas_acct_number = p_account_number
AND set_of_books_id = g_set_of_books_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 );
SELECT application_short_name
INTO l_ar_schema
FROM fnd_application
WHERE application_id = 222;
SELECT 'Y'
INTO g_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 g_cust_attribute
FROM fv_system_parameters;
g_error_buf := 'Please select GTAS Customer Trading Partner Attribute in the '||
'Federal System Parameters form.';
SELECT xd.source_distribution_id_num_1 transaction_id,
xte.transaction_number
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 = g_set_of_books_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;
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 = g_set_of_books_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 = g_set_of_books_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 = g_set_of_books_id;
SELECT authority_type1
INTO l_authority_type_code_tmp
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number; --p_account_number;
SELECT authority_type1
INTO p_authority_type_code
FROM fv_gtas_attributes
WHERE gtas_acct_number = l_account_number; --p_account_number;
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;
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.'||g_acct_segment_name||' = ffv.flex_value
AND ffv.flex_value_set_id = '||g_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');
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');
SELECT SUBSTR(compiled_value_attributes, 5, 1)
INTO l_account_type
FROM fnd_flex_values
WHERE flex_value = p_account_number
AND flex_value_set_id = g_acc_value_set_id;