[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* INSERT INTO bcpsa_xla_temp
* VALUES(bcpsa_xla_temp_S.NEXTVAL,p_debug);
* -- Insert into FV_REF ......values (event_id,line_num.,application_id .......) -- columns for PO
* -- END IF
* -- END IF----- End PO
* --
* --
* -- IF Application_id = 501 THEN -- Start AP
* --
* -- IF AP Transaction object is NOT NULL THEN
* -- -- process logic and populate the FV reference object for AP events...
* -- Insert into FV_REF ......values (event_id,line_num,application_id........) -- columns for AP
* -- END IF
* -- END IF----- End AP
* -- ...
* -- ...
* -- .. Costing etc
* --
* --
* -- END IF --- check for accounting mode
* --
* -- RETURN TRUE
* --
* -- ------------------End FV Extract Logic---------------
* --
* /*============================================================================*/
PROCEDURE extract
(
p_application_id IN NUMBER,
p_accounting_mode IN VARCHAR2
)
IS
l_debug_info VARCHAR2(240);
SELECT glseg.ar_gl_natural_segment_value inv_natseg_value
FROM ar_gl_segments_ref_v glseg , ar_cust_trx_lines_l_v trxobj
WHERE trxobj.trx_line_dist_ccid = glseg.ar_gl_code_combination_id
AND trxobj.event_id = p_event_id
AND trxobj.line_number = p_line_number;
SELECT bill_customer_class_code
FROM ar_bill_to_customers_s_v trxcc
WHERE trxcc.event_id = p_event_id;
SELECT to_number(glseg.ar_gl_natural_segment_value) rct_natseg_value
FROM ar_gl_segments_ref_v glseg , ar_distributions_l_v trxobj
WHERE trxobj.dist_code_combination_id = glseg.ar_gl_code_combination_id
AND trxobj.event_id = p_event_id
AND trxobj.line_number = p_line_number;
SELECT dist_source_type
FROM ar_distributions_l_v ardist
WHERE ardist.event_id = p_event_id
AND ardist.line_number = p_line_number;
IF (p_event_info.event_type_code IN ('INV_CREATE', 'INV_UPDATE')) THEN
/* Get the Customer Class Information */
OPEN cur_ar_inv_trx_custclass(p_event_info.event_id);
ELSIF (p_event_info.event_type_code IN ('RECP_CREATE', 'RECP_UPDATE', 'RECP_REVERSE')) THEN
BEGIN
-- Get the Natural Account Segment
OPEN cur_ar_rct_natseg_value(p_fv_extract_rec.event_id,
p_fv_extract_rec.line_number);
ELSIF (p_event_info.event_type_code IN ('MISC_RECP_CREATE', 'MISC_RECP_UPDATE', 'MISC_RECP_REVERSE')) THEN
/* Check the Distribution Type */
OPEN cur_ar_rct_dist_type (p_fv_extract_rec.event_id,
p_fv_extract_rec.line_number);
SELECT ussgl_account --, template_id
FROM Fv_Facts_Ussgl_Accounts
WHERE anticipated_unanticipated = 'Y';
SELECT template_id
FROM FV_PYA_FISCALYEAR_SEGMENT
WHERE set_of_books_id = p_ledger_id;
SELECT currency_code
FROM gl_ledgers
WHERE ledger_id = p_Ledger_id;
SELECT period_year, period_num
FROM gl_period_statuses
WHERE ledger_id = c_ledger_id
AND application_id = 101
AND period_name = c_period_name;
l_fund_select VARCHAR2(2000);
--Build the Select statement for getting the fund values and ccids
l_fund_select := 'SELECT code_combination_id ' ||
' FROM Gl_Code_Combinations ' ||
' WHERE chart_of_accounts_id = :p_coaid AND '||
'segment'||p_balancing_segment || ' = :p_fund_value AND ' ||
'template_id = :p_template_id AND '||
'Summary_flag = ''Y''' ;
trace(C_STATE_LEVEL, l_procedure_name, 'l_fund_select='||l_fund_select);
DBMS_SQL.PARSE(l_fund_cur_id, l_fund_select, DBMS_SQL.Native);
/*SELECT SUM((begin_balance_dr - begin_balance_cr) +
(period_net_dr - period_net_cr))
INTO l_amount
FROM Gl_Balances
WHERE ledger_id = p_Ledger_id
--AND currency_code = vp_currency_code
AND code_combination_id = l_ccid
AND period_name = p_period_name;
SELECT code_combination_id
INTO l_ccid
FROM gl_code_combinations
WHERE chart_of_accounts_id = p_coaid
AND template_id = l_template_id
AND summary_flag = 'Y';*/
SELECT SUM((begin_balance_dr - begin_balance_cr) +
(period_net_dr - period_net_cr))
INTO l_amount
FROM Gl_Balances
WHERE ledger_id = p_Ledger_id
AND currency_code = l_currency_code
AND code_combination_id = l_ccid
AND period_name = p_period_name;
SELECT SUM(NVL(accounted_dr,0) - NVL(accounted_cr,0))
INTO l_amount
FROM Gl_bc_packets gbc,
gl_account_hierarchies gah
WHERE gbc.ledger_id = p_Ledger_id
AND gah.ledger_id = p_Ledger_id
AND gah.template_id = l_template_id
AND gah.summary_code_combination_id = l_ccid
AND gbc.currency_code = l_currency_code
AND gbc.code_combination_id = gah.detail_code_combination_id
AND gbc.period_year = l_period_year
AND gbc.period_num <= l_period_num
AND gbc.status_code = 'A';
SELECT accounted_amt, event_type_code,code_combination_id FROM PO_BC_DISTRIBUTIONS
WHERE distribution_id = p_dist_id AND ae_event_id =
(SELECT max(ae_event_id) FROM PO_BC_DISTRIBUTIONS pbd
WHERE distribution_id = p_dist_id
AND main_or_backing_code = 'M'
AND ae_event_id <> p_event_id
AND distribution_type <> 'REQUISITION'
AND EXISTS (select 1
from xla_ae_headers xah
where application_id = 201
and xah.event_id = pbd.ae_event_id
and xah.accounting_entry_status_code = 'F'));
SELECT pbd.distribution_id,pbd.code_combination_id,pbd.accounted_amt
FROM PO_EXTRACT_DETAIL_V ped, PO_BC_DISTRIBUTIONS pbd
WHERE ped.event_id = pbd.ae_event_id --p_event_id AND
AND ped.po_distribution_id = pbd.distribution_id
AND pbd.header_id = p_header_id
AND pbd.main_or_backing_code = 'M';
SELECT fund_category, fund_expire_date
FROM FV_FUND_PARAMETERS
WHERE FUND_VALUE=P_fund_value;
SELECT fund_category, fund_expire_date
FROM FV_FUND_PARAMETERS
WHERE FUND_VALUE = p_fund_value
AND set_of_books_id = p_ledger_id ;
SELECT ts.time_frame
FROM fv_treasury_symbols ts, fv_fund_parameters fp
WHERE ts.treasury_symbol_id = fp.treasury_symbol_id
AND fp.fund_value = p_fund_value
AND ts.set_of_books_id = p_ledger_id;
SELECT application_column_name, fyr_segment_id
FROM fv_pya_fiscalyear_segment
WHERE set_of_books_id = p_ledger_id;
SELECT period_year
FROM fv_pya_fiscalyear_map
WHERE set_of_books_id = p_ledger_id
AND fyr_segment_id = p_segment_id
AND fyr_segment_value = p_segment_value;
SELECT period_year, period_name
FROM gl_period_statuses
WHERE ledger_id = p_ledger_id
AND application_id = p_application_id
AND (trunc(p_gl_date) BETWEEN start_date AND end_date)
and ADJUSTMENT_PERIOD_FLAG='N';
EXECUTE IMMEDIATE 'SELECT ' || l_bfy_segment.application_column_name ||
' FROM gl_code_combinations WHERE code_combination_id = :x_ccid' ||
' AND chart_of_accounts_id = :x_coaid '
INTO l_bfy_segment_value USING p_ccid, p_coa_id;
SELECT period_year, period_name
FROM Gl_Period_Statuses
WHERE ledger_id = cp_ledger_id
AND cp_gl_date BETWEEN START_DATE AND end_date ;
SELECT po_header_id INTO l_header_id
FROM po_extract_header_v
WHERE event_id = p_event_id;
SELECT chart_of_accounts_id coaid, gl.Ledger_id ledger_id
FROM xla_events_gt xgt ,gl_ledgers gl
WHERE gl.ledger_id = xgt.ledger_id
AND xgt.application_id = p_application_id
AND xgt.event_id = p_event_id;
SELECT *
FROM po_req_extract_detail_v
where req_distribution_type = 'REQUISITION' ;
SELECT *
FROM po_extract_detail_v
where po_distribution_type <> 'REQUISITION';
SELECT gl_date
FROM po_req_extract_header_v
WHERE event_id = p_event_id;
SELECT gl_date
FROM po_extract_header_v
WHERE event_id = p_event_id;
SELECT accounted_amt,event_type_code,code_combination_id FROM PO_BC_DISTRIBUTIONS
WHERE distribution_id = p_dist_id AND ae_event_id =
(SELECT max(ae_event_id) FROM PO_BC_DISTRIBUTIONS pbd
WHERE pbd.distribution_id = p_dist_id
AND pbd.ae_event_id <> p_event_id
AND pbd.distribution_type <> 'REQUISITION'
AND pbd.main_or_backing_code = 'M'
AND EXISTS (select 1
from xla_ae_headers xah
where application_id = 201
and xah.event_id = pbd.ae_event_id
and xah.accounting_entry_status_code = 'F') );
SELECT event_type_code
FROM po_bc_distributions
WHERE ae_event_id = p_event_id;
l_debug_info := 'Begin of inserting extra line - for adjustment entry';
l_debug_info := 'End of inserting extra line - for adjustment entry';
INSERT INTO FV_EXTRACT_DETAIL_GT VALUES l_fv_extract_detail(l_index);
l_debug_info := 'No of rows inserted into FV_EXTRACT_DETAIL_GT: '|| SQL%ROWCOUNT;
SELECT chart_of_accounts_id coaid, gl.Ledger_id ledger_id
FROM xla_events_gt xgt ,gl_ledgers gl
WHERE gl.ledger_id = xgt.ledger_id
AND xgt.application_id = p_application_id
AND xgt.event_id = p_event_id;
SELECT *
FROM CST_XLA_RCV_REF_V r_ref ,
cst_xla_rcv_headers_v r
WHERE r.event_id=p_event_id
AND r.RCV_ACCOUNTING_EVENT_ID = r_ref.ref_rcv_accounting_event_id;
SELECT *
FROM cst_xla_rcv_lines_v;
SELECT *
FROM po_dists_ref_v pod
WHERE pod.po_header_id = p_po_header_id
AND pod.po_distribution_id = p_po_distribution_id;
SELECT event_type_code
FROM xla_events_gt
WHERE event_id = p_event_id;
SELECT period_year, period_name
FROM Gl_Period_Statuses
WHERE ledger_id = p_ledger_id
AND p_gl_date BETWEEN START_DATE AND end_date ;
SELECT sum(quantity) INTO l_returned_quantity_net FROM RCV_TRANSACTIONS
WHERE po_header_id = l_rcv_extract_header_rec.po_header_id
AND po_distribution_id = l_rcv_extract_header_rec.po_distribution_id;
INSERT INTO FV_EXTRACT_DETAIL_GT VALUES l_fv_extract_detail(l_index);
SELECT chart_of_accounts_id coaid, gl.Ledger_id ledger_id
FROM xla_events_gt xgt ,gl_ledgers gl
WHERE gl.ledger_id = xgt.ledger_id
AND xgt.application_id = p_application_id
AND xgt.event_id = p_event_id;
SELECT apinvdt.*
FROM AP_EXTRACT_INVOICE_DTLS_BC_V apinvdt,
XLA_EVENTS_GT xlagt
where apinvdt.event_id = xlagt.event_id;
SELECT apinvhd.*
FROM AP_INVOICE_EXTRACT_HEADER_V apinvhd,
XLA_EVENTS_GT xlagt
where apinvhd.event_id = xlagt.event_id;
SELECT appaydd.*
FROM AP_PAYMENT_EXTRACT_DETAILS_V appaydd,
xla_events_gt xlagt
where appaydd.event_id = xlagt.event_id;
SELECT appayhd.*
FROM AP_PAYMENT_EXTRACT_HEADER_V appayhd,
xla_events_gt xlagt
where appayhd.event_id = xlagt.event_id;
SELECT *
FROM po_distributions_all pod
WHERE pod.po_distribution_id = p_po_distribution_id;
SELECT event_type_code
FROM xla_events_gt
WHERE event_id = p_event_id;
SELECT period_year, period_name
FROM Gl_Period_Statuses
WHERE ledger_id = p_ledger_id
AND p_gl_date BETWEEN START_DATE AND end_date ;
FOR xla_rec in (select * from XLA_EVENTS_GT) loop
trace (C_STATE_LEVEL, l_procedure_name, 'line_number='||xla_rec.line_number);
INSERT INTO FV_EXTRACT_DETAIL_GT VALUES l_fv_extract_detail(l_index);
l_debug_info := 'Number of Rows inserted into FV_EXTRACT_DETAIL_GT: '|| SQL%ROWCOUNT;
SELECT event_type_code
FROM xla_events_gt
WHERE application_id = p_application_id
GROUP BY event_type_code;
SELECT * FROM xla_events_gt;
SELECT fund_category
FROM fv_fund_parameters
WHERE set_of_books_id = p_set_of_books_id
AND fund_value = p_fund_value;
SELECT *
FROM ar_cust_trx_lines_l_v;
SELECT led.ledger_id ledger_id, led.ldg_chart_of_accounts_id coa_id
FROM ar_ledger_h_v led
WHERE led.event_id = p_event_id;
SELECT glseg.ar_gl_balacing_segment_value inv_fund_value
FROM ar_gl_segments_ref_v glseg , ar_transactions_s_v trxobj
WHERE trxobj.trx_receivable_ccid = glseg.ar_gl_code_combination_id
AND trxobj.event_id = p_event_id;
SELECT glseg.ar_gl_balacing_segment_value inv_fund_value
FROM ar_gl_segments_ref_v glseg , ar_cust_trx_lines_l_v trxobj
WHERE trxobj.trx_line_dist_ccid = glseg.ar_gl_code_combination_id
AND trxobj.event_id = p_event_id
AND trxobj.line_number = p_line_number;
SELECT glseg.ar_gl_natural_segment_value invhead_natseg_value
FROM ar_gl_segments_ref_v glseg , ar_transactions_s_v trxobj
WHERE trxobj.trx_receivable_ccid = glseg.ar_gl_code_combination_id
AND trxobj.event_id = p_event_id;
SELECT dist.event_id, dist.line_number, dist.dist_code_combination_id,
trxobj.trx_line_dist_ccid
FROM ar_distributions_l_v dist, ar_cust_trx_lines_l_v trxobj
WHERE dist.dist_source_type = p_dist_source_type
AND trxobj.event_id (+) = dist.event_id
AND trxobj.line_number (+) = dist.line_number;
SELECT distinct trxobj.trx_line_dist_ccid
FROM ar_distributions_l_v dist,
ar_cust_trx_lines_l_v trxobj
WHERE dist.dist_source_type = 'REC'
AND trxobj.event_id = dist.event_id
AND trxobj.line_number = dist.line_number;
SELECT to_number(glseg.ar_gl_natural_segment_value) natseg_value
FROM ar_gl_segments_ref_v glseg
WHERE glseg.ar_gl_code_combination_id = p_ccid;
SELECT *
FROM ar_distributions_l_v;
SELECT glseg.ar_gl_balacing_segment_value inv_rev_fund_value
FROM ar_gl_segments_ref_v glseg , ar_cust_trx_lines_l_v trxobj
WHERE trxobj.trx_line_dist_ccid = glseg.ar_gl_code_combination_id
AND trxobj.event_id = p_event_id
AND trxobj.line_number = p_line_number;
SELECT glseg.ar_gl_balacing_segment_value rct_fund_value
FROM ar_gl_segments_ref_v glseg , ar_distributions_l_v trxobj
WHERE trxobj.dist_code_combination_id = glseg.ar_gl_code_combination_id
AND trxobj.event_id = p_event_id
AND trxobj.line_number = p_line_number;
IF (l_ar_event_type IN ('INV_CREATE', 'INV_UPDATE')) THEN
-- ================================== FND_LOG ==================================
l_debug_info := 'Inside Invoice Event type';
ELSIF (l_ar_event_type IN ('RECP_CREATE', 'RECP_UPDATE', 'RECP_REVERSE')) THEN
-- ================================== FND_LOG ==================================
l_debug_info := 'Inside Cash Receipt Event type';
So directly jump to the condition that insert the records to fv_extract_detail_gt table */
l_overall_acct_valid := TRUE;
GOTO insert_row;
ELSIF (l_ar_event_type IN ('MISC_RECP_CREATE', 'MISC_RECP_UPDATE', 'MISC_RECP_REVERSE')) THEN
-- ================================== FND_LOG ==================================
l_debug_info := 'Inside Miscellaneous Receipt Event type';
<>
IF l_fv_extract_detail.COUNT<> 0 THEN
/* Set Overall Account Valid Flag */
IF (l_overall_acct_valid = FALSE) THEN
FOR l_index IN l_fv_extract_detail.first..l_fv_extract_detail.last
LOOP
l_fv_extract_detail(l_index).account_valid_flag := 'N';
/* Insert data into FV_EXTRACT_DETAILS_GT table */
FORALL l_index IN l_fv_extract_detail.first..l_fv_extract_detail.last
INSERT INTO fv_extract_detail_gt VALUES l_fv_extract_detail(l_index);
l_debug_info := 'No of rows inserted into FV_EXTRACT_DETAIL_GT: '|| SQL%ROWCOUNT;