[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT treasury_symbol_id,
treasury_symbol
FROM fv_treasury_symbols
WHERE set_of_books_id = g_set_of_books_id
AND Treasury_symbol
BETWEEN NVL(p_treasury_symbol_low,treasury_symbol)
AND NVL(p_treasury_symbol_high,treasury_symbol)
ORDER BY Treasury_symbol ;
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_FACTS_TRX_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 ;
l_jrnl_select_gl VARCHAR2(3000);
l_jrnl_select_xla VARCHAR2(5000);
l_jrnl_select VARCHAR2(10000);
l_cohort_select VARCHAR2(100) ;
l_reimb_act_select VARCHAR2(100) ;
SELECT 'X', factsI_journal_attribute,
factsII_pub_law_code_attribute,
factsII_advance_type_attribute,
factsII_tr_main_acct_attribute,
factsII_tr_dept_id_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 := ', GLC.' || g_cohort_seg_name ;
l_cohort_select := ' ' ;
l_reimb_act_select := ', GLC.' || 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;
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.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' || ',
glc.' || g_acct_segment_name ||
', glc.' ||l_fyr_segment_name ||','||
'gjh.je_category ,
gjh.je_source ,
gjl.code_combination_id,
gjl.je_header_id,
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,
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_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations glc,
fv_treasury_symbols fts,
fv_fund_parameters ffp
WHERE gjl.code_combination_id = glc.code_combination_id
AND gjl.ledger_id = :sob_id
AND glc.chart_of_accounts_id= :coa_id
AND gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.currency_code = :currency_code
AND gjh.actual_flag = :actual_flag
AND gjh.posted_date BETWEEN :from_posted_date AND :to_posted_date
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 glc.template_id IS NULL
AND fts.treasury_symbol_id = :treasury_symbol_id
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND glc.'||g_bal_segment_name||' = ffp.fund_value
AND ffp.set_of_books_id = :sob_id
AND fts.set_of_books_id = :sob_id
AND NVL(gjh.je_from_sla_flag, ''N'') = ''N''
'|| l_src || l_cat ;
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' || ',
glc.' || g_acct_segment_name ||
', glc.' ||l_fyr_segment_name ||','||
'gjh.je_category ,
gjh.je_source ,
gjl.code_combination_id,
gjl.je_header_id,
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,
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_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations glc,
fv_treasury_symbols fts,
fv_fund_parameters ffp,
xla_ae_lines xal,
xla_ae_headers xah,
xla_distribution_links xdl,
gl_import_references gir
WHERE gjl.code_combination_id = glc.code_combination_id
AND gjl.ledger_id = :sob_id
AND glc.chart_of_accounts_id= :coa_id
AND gjh.je_header_id = gjl.je_header_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.currency_code = :currency_code
AND gjh.actual_flag = :actual_flag
AND gjh.posted_date BETWEEN :from_posted_date AND :to_posted_date
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 glc.template_id IS NULL
AND fts.treasury_symbol_id = :treasury_symbol_id
AND fts.treasury_symbol_id = ffp.treasury_symbol_id
AND glc.'||g_bal_segment_name||' = ffp.fund_value
AND ffp.set_of_books_id = :sob_id
AND fts.set_of_books_id = :sob_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''
'|| l_src || l_cat ||
' 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);
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.select1',g_error_buf);
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,substr(l_jrnl_select,1,1000));
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,substr(l_jrnl_select,1001,1000));
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,substr(l_jrnl_select,2001,1000));
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,substr(l_jrnl_select,3001,1000));
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,substr(l_jrnl_select,4001,1000));
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,substr(l_jrnl_select,5001,1000));
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,substr(l_jrnl_select,6001,1000));
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,substr(l_jrnl_select,7001,1000));
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,substr(l_jrnl_select,8001,1000));
SELECT 'X'
INTO l_exists
FROM FV_FACTS_ATTRIBUTES
WHERE facts_acct_number = l_account_number
AND set_of_books_id = g_set_of_books_id;
SELECT 'X'
INTO l_exists
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = l_account_number;
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 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 transaction_number
INTO l_reference_4
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
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
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
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
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,
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,
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,
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,
v.vendor_type_lookup_code vendor_type,
fvv.eliminations_id
INTO l_vendor_id, l_vendor_type, l_eliminations_id
FROM ap_invoices_all i,
po_vendors v,
fv_facts_vendors_v fvv
WHERE i.invoice_id = to_number(l_reference_2)
AND i.vendor_id = v.vendor_id
AND fvv.vendor_id = v.vendor_id;
FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.select_1',' NO DATA FOUND !!');
SELECT v.vendor_id VENDOR_ID,
v.vendor_type_lookup_code VENDOR_TYPE,
fvv.eliminations_id
INTO l_vendor_id,l_vendor_type,l_eliminations_id
FROM rcv_transactions rt,
po_vendors v,
po_headers_all ph,
fv_facts_vendors_v fvv
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
AND fvv.vendor_id = ph.vendor_id;
SELECT pov.vendor_id,
pov.vendor_type_lookup_code,
fvv.eliminations_id
INTO l_vendor_id,
l_vendor_type,
l_eliminations_id
FROM po_vendors pov, po_headers_all poh,
fv_facts_vendors_v fvv
WHERE poh.po_header_id = to_number(l_reference_2)
AND pov.vendor_id = poh.vendor_id
AND fvv.vendor_id = poh.vendor_id;
SELECT hzca.customer_class_code, fcv.eliminations_id
INTO l_vendor_type, l_eliminations_id
FROM hz_cust_accounts hzca, fv_facts_customers_v fcv
WHERE hzca.cust_account_id = to_number(l_reference_7)
AND fcv.customer_id = hzca.cust_account_id;
SELECT v.vendor_id vendor_id,
v.vendor_type_lookup_code vendor_type,
fvv.eliminations_id
INTO l_vendor_id,l_vendor_type,l_eliminations_id
FROM ap_checks_all apc,
po_vendors v,
fv_facts_vendors_v fvv
WHERE apc.vendor_id = v.vendor_id
AND apc.check_id = to_number(l_reference_3)
AND fvv.vendor_id = v.vendor_id;
SELECT h.doc_number, d.dept_id||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;
SELECT public_law_code
INTO g_public_law_code_val
FROM fv_be_trx_dtls
WHERE transaction_id = to_number(l_reference_1)
AND set_of_books_id = g_set_of_books_id ;
SELECT transaction_type_id
INTO l_tran_type
FROM Fv_be_trx_dtls
WHERE transaction_id = to_number(l_reference_1)
AND set_of_books_id = g_set_of_books_id ;
SELECT legislative_indicator
INTO g_legis_ind_val
FROM FV_be_transaction_types
WHERE apprn_transaction_type = l_tran_type
AND set_of_books_id = g_set_of_books_id ;
SELECT advance_type
INTO g_advance_type_val
FROM fv_be_trx_dtls
WHERE transaction_id = to_number(l_reference_1)
AND set_of_books_id = g_set_of_books_id ;
SELECT dept_id,
main_account
INTO g_transfer_dept_id,
g_transfer_main_acct
FROM fv_be_trx_dtls
WHERE transaction_id = to_number(l_reference_1)
AND set_of_books_id = g_set_of_books_id ;
SELECT 'X'
INTO l_exists
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = l_account_number;
SELECT disbursements_flag
INTO l_disbursements_flag
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = l_sgl_acct_num;
SELECT FTS.Time_Frame, fts.financing_account
INTO l_time_frame, l_financing_acct
FROM FV_FACTS_FEDERAL_ACCOUNTS FFFA,
FV_TREASURY_SYMBOLS FTS
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 FFFA.set_of_books_id = g_set_of_books_id ;
SELECT fyr_segment_value
INTO l_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_select VARCHAR2(1000);
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 TO_CHAR(rt.transaction_id) = p_reference5;
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 start_date_active, creation_date, created_by
FROM po_requisition_headers_all
WHERE to_char(requisition_header_id) = p_reference2;
SELECT gl_encumbered_date, creation_date, created_by
FROM po_req_distributions
WHERE to_char(distribution_id) = p_reference3;
SELECT start_date, creation_date, created_by
FROM po_headers_all
WHERE segment1 = p_reference2;
SELECT gl_encumbered_date, creation_date, created_by
FROM po_distributions_all
WHERE to_char(po_distribution_id) = p_reference3;
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) ;
FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,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;
-- Select the document info from
-- AR_CASH_RECEIPT_HISTORY_All table
OPEN Receivables_History (TO_NUMBER(l_cash_receipt_hist_id));
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;
-- Select the document info
-- from Ar_Misc_Cash_Distributions_All table
IF (p_rec_public_law_code_col IS NOT NULL) THEN
l_rec_public_law_code := NULL;
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, p_fund_group,
p_dept_id, p_bureau_id,
p_bal_segment
USING p_ccid, g_coa_id, g_set_of_books_id;
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_facts_trx_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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
pya_flag ,
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 )
VALUES
( p_treasury_symbol_id ,
p_set_of_books_id ,
p_code_combination_id ,
p_fund_value ,
p_account_number ,
p_document_source ,
p_document_category ,
p_document_number ,
p_transaction_date ,
p_creation_date_time ,
p_entry_user ,
p_fed_non_fed ,
--Modified for bug 7324241
--SUBSTR(p_trading_partner,1,6),
--populate trading partner only if fed non fed is F
DECODE(p_fed_non_fed, 'F', SUBSTR(p_trading_partner,1,6), NULL),
p_exch_non_exch ,
p_cust_non_cust ,
p_budget_subfunction ,
p_debit ,
p_credit ,
p_transfer_dept_id ,
p_transfer_main_acct ,
p_year_budget_auth ,
p_budget_function ,
p_advance_flag ,
p_cohort ,
p_begin_end ,
p_indef_def_flag ,
p_appor_cat_b_dtl ,
SUBSTR(p_appor_cat_b_txt,1,25),
p_prn_num ,
SUBSTR(p_prn_txt,1,25) ,
p_public_law ,
p_appor_cat_code ,
p_authority_type ,
--p_transaction_partner , bug 7324241
DECODE(p_transaction_partner,'N',NULL,p_transaction_partner) ,
p_reimburseable_flag ,
p_bea_category ,
p_borrowing_source ,
p_def_liquid_flag ,
p_deficiency_flag ,
p_availability_flag ,
p_legislation_flag ,
p_pya_flag,
p_je_line_creation_date ,
p_je_line_modified_date ,
p_je_line_period_name ,
p_gl_date ,
p_gl_posted_date,
NVL(p_reversal_flag, ' ') ,
p_sla_hdr_event_id,
p_sla_hdr_creation_date,
p_sla_entity_id );
SELECT balance_type,
public_law_code,
reimburseable_flag,
availability_time,
bea_category,
apportionment_category,
SUBSTR(transaction_partner,1,1),
borrowing_source,
definite_indefinite_flag,
legislative_indicator,
pya_flag,
authority_type,
deficiency_flag,
function_flag,
advance_flag,
transfer_flag,
govt_non_govt,
exch_non_exch,
cust_non_cust,
budget_subfunction
INTO g_balance_type_flag,
g_public_law_code_flag,
g_reimburseable_flag,
g_availability_flag,
g_bea_category_flag,
g_appor_cat_flag,
g_transaction_partner_val,
g_borrowing_source_flag,
g_def_indef_flag,
g_legis_ind_flag,
g_pya_flag,
g_authority_type_flag,
g_deficiency_flag,
g_function_flag,
g_advance_flag,
g_transfer_ind ,
g_govt_non_govt_ind,
g_exch_non_exch_ind,
g_cust_non_cust_ind,
g_budget_subfunction_ind
FROM FV_FACTS_ATTRIBUTES
WHERE Facts_Acct_Number = acct_num
AND set_of_books_id = g_set_of_books_id;
SELECT UPPER(fts.resource_type),
ffp.def_indef_flag,
ffp.fund_category,
ffp.direct_or_reimb_code
INTO l_resource_type,
g_def_indef_val,
l_fund_category,
g_reimburseable_val
FROM fv_treasury_symbols fts,
fv_fund_parameters ffp
WHERE ffp.treasury_symbol_id = fts.treasury_symbol_id
AND ffp.fund_value = fund_val
AND fts.treasury_symbol_id = g_treasury_symbol_id
AND fts.set_of_books_id = g_set_of_books_id
AND ffp.set_of_books_id = g_set_of_books_id ;
SELECT RPAD(SUBSTR(borrowing_source_code,1,6), 6),
RPAD(SUBSTR(bea_category,1,5), 5)
INTO g_borrowing_source_val, g_bea_category_val
FROM fv_fund_parameters
WHERE fund_value= fund_val
AND treasury_symbol_id = g_treasury_symbol_id
AND set_of_books_id = g_set_of_books_id;
SELECT RPAD(SUBSTR(bea_category,1,5), 5)
INTO g_bea_category_val
FROM fv_fund_parameters
WHERE treasury_symbol_id = g_treasury_symbol_id
AND set_of_books_id = g_set_of_books_id
AND fund_category = l_fund_category;
SELECT RPAD(SUBSTR(ffba.budget_function,1,3), 3)
INTO g_budget_function_val
FROM fv_facts_budget_accounts ffba,
fv_facts_federal_accounts fffa,
fv_treasury_symbols fts ,
fv_facts_bud_fed_accts ffbfa
WHERE fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
AND fts.treasury_symbol_id = g_treasury_symbol_id
AND fts.set_of_books_id = g_set_of_books_id
AND fffa.set_of_books_id = g_set_of_books_id
AND ffbfa.set_of_books_id = g_set_of_books_id
AND ffba.set_of_books_id = g_set_of_books_id ;
SELECT budget_subfunction
INTO l_budget_sub
FROM fv_fund_parameters
WHERE fund_value = FUND_VAL
AND set_of_books_id = g_set_of_books_id;
SELECT fts.cust_non_cust
INTO l_cnc
FROM fv_treasury_symbols fts, fv_fund_parameters ffp
WHERE fts.treasury_symbol_id = ffp.treasury_symbol_id
AND ffp.set_of_books_id = g_set_of_books_id
AND ffp.fund_value = fund_val;
DELETE FROM fv_facts_trx_temp;
SELECT flex_value_set_id
INTO l_acc_val_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = g_acct_segment_name
AND id_flex_code = 'GL#'
AND id_flex_num = g_coa_id ;
SELECT parent_flex_value
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 = l_acc_val_set_id
AND parent_flex_value IN
(SELECT ussgl_account
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = parent_flex_value);
SELECT 'X'
INTO l_exists
FROM fv_facts_attributes
WHERE facts_acct_number = sgl_acct_num
AND 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 ;
SELECT fund_value,
DECODE(fund_category,'S','A','T','B',fund_category) fund_category
FROM fv_fund_parameters ffp
WHERE ffp.treasury_symbol_id = g_treasury_symbol_id
AND ffp.set_of_books_id = g_set_of_books_id
AND ffp.fund_category IN ('A', 'B', 'S', 'T');
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 = g_treasury_symbol_id
AND ffh.set_of_books_id = g_set_of_books_id
AND ffh.code_type = l_code_type
AND ffh.fund_value = fund_rec.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 = g_treasury_symbol_id
AND ffh.set_of_books_id = g_set_of_books_id
AND ffh.code_type = l_code_type
AND ffh.fund_value = 'ALL-A'
AND fund_rec.fund_category = '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 = g_treasury_symbol_id
AND ffh.set_of_books_id = g_set_of_books_id
AND ffh.code_type = l_code_type
AND ffh.fund_value = 'ALL-B'
AND fund_rec.fund_category = '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 = g_treasury_symbol_id
AND ffh.set_of_books_id = g_set_of_books_id
AND ffh.code_type = l_code_type
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.set_of_books_id = g_set_of_books_id
AND ffh.code_type = l_code_type
AND ffh.fund_value = 'ALL-A'
AND fund_rec.fund_category = '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.set_of_books_id = g_set_of_books_id
AND ffh.code_type = l_code_type
AND ffh.fund_value = 'ALL-B'
AND fund_rec.fund_category = '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 = g_set_of_books_id
AND ffh.code_type = l_code_type
AND ffh.fund_value = 'ALL-FUNDS';
vl_program_sel := 'SELECT gcc.'||vl_prg_seg_name||
' FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = '||p_ccid;
SELECT reporting_code, reporting_desc
INTO vl_prc_val, vl_prc_desc
FROM fv_facts_prc_dtl
WHERE prc_header_id = vl_prc_header_id
AND program_value = vl_program_value
AND set_of_books_id = g_set_of_books_id;
SELECT reporting_code, reporting_desc
INTO vl_prc_val, vl_prc_desc
FROM fv_facts_prc_dtl
WHERE prc_header_id = vl_prc_header_id
AND program_value = 'ALL'
AND set_of_books_id = g_set_of_books_id;
SELECT flex_value_set_id
INTO vl_prg_value_set_id
FROM fnd_id_flex_segments
WHERE application_column_name = vl_prg_seg_name
AND application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = g_coa_id ;
SELECT SUBSTR(description, 1, 25)
INTO vl_prc_desc
FROM fnd_flex_values_tl ffvt,fnd_flex_values ffv
WHERE ffvt.flex_value_id = ffv.flex_value_id
AND ffv.flex_value_set_id = vl_prg_value_set_id
AND ffv.flex_value = vl_program_value
AND ffvt.language = userenv('LANG');
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_facts_trx_temp
WHERE document_source = 'Purchasing'
AND document_category = 'Receiving'
GROUP BY document_number;
UPDATE fv_facts_trx_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;
INSERT INTO fv_facts_trx_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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
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 ,
min(transaction_date) ,
min(creation_date_time) ,
entry_user ,
fed_non_fed ,
trading_partner ,
exch_non_exch ,
cust_non_cust ,
budget_subfunction ,
sum(debit) ,
sum(credit) ,
transfer_dept_id ,
transfer_main_acct ,
year_budget_auth ,
budget_function ,
advance_flag ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
min(journal_creation_date) ,
min(journal_modified_date) ,
period_name ,
min(gl_date) ,
min(gl_posted_date),
reversal_flag ,
min(sla_hdr_event_id),
min(sla_hdr_creation_date),
min(sla_entity_id),
-9999
FROM fv_facts_trx_temp
WHERE document_source = 'Purchasing'
AND document_category = 'Receiving'
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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
period_name ,
reversal_flag ,
-9999 ;
DELETE FROM fv_facts_trx_temp
WHERE document_source = 'Purchasing'
AND document_category = 'Receiving'
AND nvl(period_activity,-3333) <> -9999;
UPDATE fv_facts_trx_temp
SET period_activity = null
WHERE period_activity = -9999;
l_select :=
' insert into fv_facts_trx_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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
pya_flag ,
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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
pya_flag ,
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_facts_trx_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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
pya_flag ,
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_facts_trx_temp
SET period_activity = -1111
WHERE --document_source = 'Receivables'
--AND
document_category = 'Trade Receipts';
DELETE FROM fv_facts_trx_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_facts_trx_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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
pya_flag ,
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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
pya_flag ,
(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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
pya_flag ,
(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_facts_trx_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 ,
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 ,
cohort ,
begin_end ,
indef_def_flag ,
appor_cat_b_dtl ,
appor_cat_b_txt ,
PROGRAM_RPT_CAT_NUM ,
PROGRAM_RPT_CAT_TXT ,
public_law ,
appor_cat_code ,
authority_type ,
transaction_partner ,
reimburseable_flag ,
bea_category ,
borrowing_source ,
def_liquid_flag ,
deficiency_flag ,
availability_flag ,
legislation_flag ,
pya_flag ,
period_name ,
reversal_flag ,
transaction_date ,
creation_date_time ,
journal_creation_date ,
journal_modified_date ,
gl_date ,
gl_posted_date ,
sla_hdr_event_id,
sla_hdr_creation_date,
sla_entity_id
);
DELETE from fv_facts_trx_temp
WHERE document_source = 'Payables'
AND document_category = 'Purchase Invoices'
and period_activity <> -8888;