[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_cohort_select Varchar2(20) ;
Select Treasury_Symbol_id
Into v_treasury_symbol_id
From fv_treasury_symbols
where treasury_symbol = vp_treasury_symbol
And set_of_books_id = vp_set_of_books_id;
DELETE FROM FV_FACTS_TEMP
Where treasury_symbol_id = v_treasury_symbol_id
And fct_int_record_category = 'TRIAL_BALANCE';
SELECT chart_of_accounts_id
INTO v_chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = vp_set_of_books_id ;
-- Select Period Information for Beginning Period
Select period_name,
period_year,
period_num
Into v_begin_period_name,
v_fiscal_year,
v_begin_period_num
From gl_period_statuses
Where trunc(start_date) = trunc(vp_start_date)
and application_id = 101
and adjustment_period_flag = 'N'
and set_of_books_id = vp_set_of_books_id ;
Select FFFA.financing_account,
FFFA.cohort_segment_name
Into vl_financing_acct,
v_cohort_seg_name
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 = vp_treasury_symbol
AND FTS.set_of_books_id = vp_set_of_books_id
AND FFFA.set_of_books_id = vp_set_of_books_id ;
vl_main_select Varchar2(2000) ;
vl_legis_select Varchar2(2000) ;
vl_cohort_select Varchar2(25) ;
vl_Amount_select Varchar2(2000) ;
fnd_message.set_Name('FV', 'Selecting FACTS Trans...') ;
v_cohort_select := ', GLCC.' || v_cohort_seg_name ;
v_cohort_select := ' ' ;
vl_main_select := 'SELECT DISTINCT GLCC.'||v_acc_seg_name||
', GLCC.'||v_bal_seg_name||v_cohort_select||
' FROM GL_CODE_COMBINATIONS GLCC,
FV_FUND_PARAMETERS FFP,
FV_TREASURY_SYMBOLS FTS
WHERE FTS.TREASURY_SYMBOL = ' ||''''||vp_treasury_symbol||'''' ||
--pkpatel :changed to fix Bug 1575992
' AND FTS.TREASURY_SYMBOL_ID = FFP.TREASURY_SYMBOL_ID
AND GLCC.'|| v_bal_seg_name ||'= FFP.FUND_VALUE
AND FFP.SET_OF_BOOKS_ID = ' || vp_set_of_books_id ||
' AND FTS.SET_OF_BOOKS_ID = ' || vp_set_of_books_id ;
vl_main_select := vl_main_select ||
' AND EXISTS (SELECT 1 FROM gl_code_combinations glcc2
WHERE glcc.code_combination_id = glcc2.code_combination_id '
|| ' AND glcc2.' || vl_pagebreak1_seg ||
' BETWEEN '|| ''''|| v_pagebreak1_low || '''' || ' AND '
|| ''''|| v_pagebreak1_high || '''' ;
vl_main_select := vl_main_select || ' AND glcc2.' || vl_pagebreak2_seg ||
' BETWEEN '|| ''''|| v_pagebreak2_low || '''' || ' AND '
|| ''''|| v_pagebreak2_high || '''' ;
vl_main_select := vl_main_select || ' AND glcc2.' || vl_pagebreak3_seg ||
' BETWEEN '|| ''''|| v_pagebreak3_low || '''' || ' AND '
|| ''''|| v_pagebreak3_high || '''' ;
vl_main_select := vl_main_select || ')';
vl_main_select := vl_main_select || ' ORDER BY GLCC.' || v_acc_seg_name;
fnd_file.put_line(fnd_file.log,vl_main_select);
dbms_sql.parse(vl_main_cursor, vl_main_select, DBMS_SQL.V7) ;
LOOP --Main Select Loop
-- Reset the Cursor Variable
v_fund_val := Null ;
vl_amount_select :=
'SELECT NVL(SUM(nvl(BEGIN_BALANCE_DR,0) - nvl(BEGIN_BALANCE_CR,0)),0)
FROM GL_BALANCES GLB,
GL_CODE_COMBINATIONS GLCC
WHERE GLB.code_combination_id = GLCC.code_combination_id
AND GLB.TEMPLATE_ID IS NULL
AND GLB.actual_flag = '||''''||vl_actual_flag||''''
|| ' AND GLB.SET_OF_BOOKS_ID = ' || vp_set_of_books_id
|| ' AND GLB.PERIOD_NUM = ' || v_begin_period_num ||
' AND GLB.PERIOD_YEAR = ' || v_fiscal_year||
' AND GLCC.'||v_acc_seg_name||' = '||''''||v_acct_num||'''' ||
' AND GLCC.'||v_bal_seg_name||' = '||''''||v_fund_val||'''' ||
v_cohort_stmt||
-- Start Added Where condintion for Bug 1553095 by SMBHAGAV on 19-04-2001
' AND glb.currency_code = ' || '''' || vp_currency_code || '''' ;
fnd_file.put_line(fnd_file.log, vl_amount_select) ;
dbms_sql.parse(vl_amount_cursor,vl_amount_select,DBMS_SQL.V7);
Select 'X'
into vl_exists
from FV_FACTS_ATTRIBUTES
where facts_acct_number = v_acct_num
and set_of_books_id = vp_set_of_books_id;
-- Select the records for other Legislative Indicator values,
-- derived from Budget Execution tables and store them in a
-- cursor. Then roll them up and insert the summarized record
-- into the temp table. Dynamic SQL used for implementation.
Begin
vl_legis_cursor := DBMS_SQL.OPEN_CURSOR ;
Modified the select statement to select gjl.entered_dr , gjl.entered_cr
Seperately. these two column values would be stored in the
table fv_facts_temp in AMOUNT1 and AMOUNT2 columns respectively */
-- Bug 2512646 START
Begin
/* Getting Fiscal year segment name frmo fv_pya_fiscal_year_segment */
SELECT application_column_name
INTO v_fyr_segment_name
FROM fv_pya_fiscalyear_segment
WHERE set_of_books_id = vp_set_of_books_id;
vl_legis_select :=
'Select gjl.je_header_id,substr(gjl.reference_6,1,7),
gjl.reference_1,
gjl.reference_2 ,gjl.reference_3, gjl.reference_4,
gjl.reference_5, gjl.reference_9,
glh.date_created ,glc.user_Je_Category_name,
glh.Je_Source,
gjl.entered_dr entered_dr,
gjl.entered_cr entered_cr,
glb.Name, '||
'GLCC.'||v_acc_seg_name||
',GLCC.'||v_bal_seg_name||
',glcc.code_combination_id,glh.created_by,glh.creation_date'||
',GLCC.' ||v_fyr_segment_name ||
v_cohort_select||
' From gl_je_lines gjl,
gl_je_headers glh,
gl_je_batches glb,
gl_je_categories glc,
gl_code_combinations glcc,
fv_fund_parameters ffp
Where gjl.code_combination_id = glcc.code_combination_id
and gjl.Period_Name NOT IN (Select Period_Name From GL_Period_Statuses
Where Adjustment_Period_Flag = '||''''||'Y'||''''||
' And set_of_books_id = ' || vp_set_of_books_id || ')
AND gjl.Je_Header_Id = glh.Je_Header_Id
and glh.je_Category = glc.Je_Category_Name
and glb.je_batch_id = glh.je_batch_id
and glh.Je_Source = nvl('||''''||vp_source||''''||',glh.Je_Source)
and glc.Je_Category_name = nvl('||''''||vp_category||''''||',glc.Je_Category_Name)
AND gjl.status = ' || '''' || 'P' || '''' ||
' AND (gjl.effective_date between to_date('||''''
||vl_period_start_dt|| ''''||') AND to_date('|| ''''
||vl_period_end_dt || '''' ||
')) AND gjl.set_of_books_id = ' || vp_set_of_books_id ||
' AND glcc.' || v_bal_seg_name || ' = ffp.fund_value ' ||
' and ffp.treasury_symbol_id = ' || v_treasury_symbol_id ||
' AND glh.currency_code = ' || '''' || vp_currency_code || '''' ;
vl_legis_select := vl_legis_select ||
' AND EXISTS (SELECT 1 FROM gl_code_combinations glcc2
WHERE glcc.code_combination_id = glcc2.code_combination_id '
|| ' AND glcc2.' || vl_pagebreak1_seg ||
' BETWEEN '|| ''''|| v_pagebreak1_low || '''' || ' AND '
|| ''''|| v_pagebreak1_high || '''';
vl_legis_select := vl_legis_select || ' AND glcc2.' || vl_pagebreak2_seg ||
' BETWEEN '|| ''''|| v_pagebreak2_low || '''' || ' AND '
|| ''''|| v_pagebreak2_high || '''';
vl_legis_select := vl_legis_select || ' AND glcc2.' || vl_pagebreak3_seg ||
' BETWEEN '|| ''''|| v_pagebreak3_low || '''' || ' AND '
|| ''''|| v_pagebreak3_high || '''';
vl_legis_select := vl_legis_select || ')';
fnd_file.put_line(fnd_file.log,vl_legis_select);
dbms_sql.parse(vl_legis_cursor,vl_legis_select,DBMS_SQL.V7);
Select 'X'
into vl_exists
from FV_FACTS_ATTRIBUTES
where facts_acct_number = v_acct_num
and set_of_books_id = vp_set_of_books_id;
/* Commented OUT NOCOPY as the logic has been added to the select statement
IF upper(va_source) = 'MANUAL' THEN
Begin
Select User_Je_Category_Name
Into va_user_category
From GL_JE_Categories
Where Je_Category_Name = va_category;
SELECT SUBSTR(p_refer2, 0, decode(INSTR(p_refer2, 'C'), 0, LENGTH(p_refer2),
INSTR(p_refer2,'C')-1))
INTO vl_legis_ref2
FROM dual;
SELECT public_law_code
INTO va_public_law_code_val
FROM fv_be_trx_dtls
WHERE transaction_id = vl_legis_ref1
AND set_of_books_id = vp_set_of_books_id ;
Select transaction_type_id
Into vl_tran_type
From Fv_be_trx_dtls
where transaction_id = vl_legis_ref1
and set_of_books_id = vp_set_of_books_id ;
Select legislative_indicator
into va_legis_ind_val
From FV_be_transaction_types
where apprn_transaction_type = vl_tran_type
and set_of_books_id = vp_set_of_books_id ;
SELECT advance_type
INTO va_advance_type_val
FROM fv_be_trx_dtls
WHERE transaction_id = vl_legis_ref1
AND set_of_books_id = vp_set_of_books_id ;
SELECT dept_id,
main_account
INTO va_transfer_dept_id,
va_transfer_main_acct
FROM fv_be_trx_dtls
WHERE transaction_id = vl_legis_ref1
AND set_of_books_id = vp_set_of_books_id ;
SELECT balance_type
INTO va_balance_type_flag
FROM FV_FACTS_ATTRIBUTES
WHERE Facts_Acct_Number = vl_sgl_acct_num
and set_of_books_id = vp_set_of_books_id ;
SELECT disbursements_flag
INTO vl_disbursements_flag
FROM fv_facts_ussgl_accounts
WHERE ussgl_account = v_acct_num;
Select FTS.Time_Frame, FFFA.financing_account
INTO v_time_frame, v_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 = vp_treasury_symbol
AND FTS.set_of_books_id = vp_set_of_books_id
AND FFFA.set_of_books_id = vp_set_of_books_id ;
SELECT fyr_segment_value
INTO vl_fyr_segment_value
FROM fv_pya_fiscalyear_map
WHERE period_year = v_fiscal_year
AND set_of_books_id = vp_set_of_books_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,
authority_type,
deficiency_flag,
function_flag,
advance_flag, /* Bug No: 2494754 */
transfer_flag
INTO va_balance_type_flag,
va_public_law_code_flag,
va_reimburseable_flag,
va_availability_flag,
va_bea_category_flag,
va_appor_cat_flag,
va_transaction_partner_val,
va_borrowing_source_flag,
va_def_indef_flag,
va_legis_ind_flag,
va_authority_type_flag,
va_deficiency_flag,
va_function_flag,
va_advance_flag, /* Bug No: 2494754 */
va_transfer_ind
FROM FV_FACTS_ATTRIBUTES
WHERE Facts_Acct_Number = acct_num
AND set_of_books_id = vp_set_of_books_id;
Select UPPER(fts.resource_type),
def_indef_flag,
ffp.fund_category
INTO vl_resource_type,
va_def_indef_val,
vl_fund_category
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 = vp_treasury_symbol
AND fts.set_of_books_id = vp_set_of_books_id
AND ffp.set_of_books_id = vp_set_of_books_id ;
Select RPAD(substr(ffba.bea_category,1,5), 5),
RPAD(substr(ffba.borrowing_source,1,6), 6)
Into va_bea_category_val,
va_borrowing_source_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 = vp_treasury_symbol
AND fts.set_of_books_id = vp_set_of_books_id
AND fffa.set_of_books_id = vp_set_of_books_id
AND ffbfa.set_of_books_id = vp_set_of_books_id
AND ffba.set_of_books_id = vp_set_of_books_id ;
Select RPAD(substr(ffba.budget_function,1,3), 3)
Into va_budget_function
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 = vp_treasury_symbol
AND fts.set_of_books_id = vp_set_of_books_id
AND fffa.set_of_books_id = vp_set_of_books_id
AND ffbfa.set_of_books_id = vp_set_of_books_id
AND ffba.set_of_books_id = vp_set_of_books_id ;
select flex_value_set_id
into vl_acc_val_set_id
from fnd_id_flex_segments
where application_column_name = v_acc_seg_name
and id_flex_code = 'GL#'
and id_flex_num = v_chart_of_accounts_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 = vl_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 vl_exists
From fv_facts_attributes
where facts_acct_number = sgl_acct_num
and set_of_books_id = vp_set_of_books_id;
The following insert statement would store data into two columns.
i.e. amount1 and amount2 for v_amount_dr and v_amount_cr respectively*/
/* Bug No : 2494754 */
INSERT INTO FV_FACTS_TEMP
(TBAL_ACCT_NUM ,
TBAL_FUND_VALUE ,
COHORT ,
BEGIN_END ,
INDEF_DEF_FLAG ,
PUBLIC_LAW ,
APPOR_CAT_CODE ,
AUTHORITY_TYPE ,
TRANSACTION_PARTNER ,
REIMBURSEABLE_FLAG ,
BEA_CATEGORY ,
BORROWING_SOURCE ,
AVAILABILITY_FLAG ,
LEGISLATION_FLAG ,
AMOUNT ,
TREASURY_SYMBOL_ID ,
FCT_INT_RECORD_CATEGORY ,
DOCUMENT_NUMBER ,
DOCUMENT_DATE ,
SGL_ACCT_NUMBER ,
APPOR_CAT_B_TXT ,
BUDGET_FUNCTION ,
ADVANCE_FLAG ,
TRANSFER_DEPT_ID ,
TRANSFER_MAIN_ACCT ,
YEAR_BUDGET_AUTH ,
CODE_COMBINATION_ID ,
DOCUMENT_CREATED_BY ,
DOCUMENT_CREATION_DATE ,
DOCUMENT_SOURCE ,
AMOUNT1 ,
AMOUNT2 )
Values (v_acct_num ,
v_fund_val ,
va_cohort ,
va_balance_type_val ,
va_def_indef_val ,
va_public_law_code_val ,
va_appor_cat_val ,
va_authority_type_val ,
va_transaction_partner_val,
va_reimburseable_val ,
va_bea_category_val ,
va_borrowing_source_val ,
va_availability_flag ,
va_legis_ind_val ,
v_amount ,
v_treasury_symbol_id ,
'TRIAL_BALANCE' ,
va_document_number ,
va_document_date ,
va_source ,
va_category ,
va_budget_function ,
va_advance_type_val ,
va_transfer_dept_id ,
va_transfer_main_acct ,
v_year_budget_auth ,
v_ccid ,
v_doc_created_by ,
v_doc_creation_date ,
va_source ,
v_amount_dr ,
v_amount_cr ) ;
Select rt.Transaction_Date
,rcv.Receipt_Num,rcv.created_by,rcv.creation_date
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.created_by,inv.creation_date
From AP_Invoices_all inv
Where to_char(inv.Invoice_Id) = P_Reference2;
Select Distinct api.invoice_num,
apc.check_date,api.created_by,api.creation_date
From ap_checks_all apc,
ap_invoices_all api,
ap_invoice_payments_all apip
where to_char(apc.check_id) = p_reference3
and to_char(api.invoice_id) = 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_created_by_d, created_by),
DECODE(l_rev_exists, 'Y', l_doc_creation_date_d, creation_date)
From AR_Cash_Receipts_All
Where to_char(Cash_Receipt_Id) = p_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'
FROM ar_cash_receipt_history_all
WHERE cash_receipt_history_id = to_number(l_cash_receipt_hist_id);
SELECT 'Y', creation_date, created_by
FROM ar_cash_receipt_history_all
WHERE reversal_cash_receipt_hist_id = to_number(l_cash_receipt_hist_id);
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';
SELECT 'Y'
FROM ar_misc_cash_distributions_all
WHERE misc_cash_distribution_id = to_number(l_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 to_char(ftc.treasury_confirmation_id) = p_reference1;
SELECT void_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 to_char(apc.check_id) = p_reference3
AND to_char(api.invoice_id) = 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 to_char(d.transaction_id) = p_reference1
AND h.doc_id = d.doc_id;
Select Start_Date_Active
,created_by
,creation_date
From PO_Requisition_Headers_All
Where to_Char(Requisition_Header_Id) = P_Reference2;
Select Start_Date
,created_by
,creation_date
From PO_Headers_all
Where Segment1 = P_Reference4;
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 = p_refer2;
SELECT apply_date, creation_date, created_by
FROM ar_receivable_applications_all
WHERE receivable_application_id = p_refer2;
SELECT trx_date, creation_date, created_by
FROM ra_customer_trx_all
WHERE customer_trx_id = p_refer2;
-- Select the document info from Ar_Cash_Receipt_History_All table
OPEN Receivables_History;
-- Select the document info from Ar_Misc_Cash_Distributions_All table
OPEN Receivables_Misc;