[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT num_scheme_id,
prefix,
suffix,
next_seq_val
FROM igi_exp_num_schemes
WHERE numbering_type = pi_number_type
AND numbering_class = pi_number_class
AND du_tu_type_id = pi_du_tu_type_id
AND fiscal_year = pi_fiscal_year
FOR UPDATE OF next_seq_val; --bug3589744 sdixit
UPDATE igi_exp_num_schemes_all
SET next_seq_val = (next_seq_val + 1)
WHERE num_scheme_id = l_num_scheme_id;
SELECT period_year
FROM gl_periods gp
,gl_sets_of_books gsob
WHERE gp.period_set_name = gsob.period_set_name
AND gp.period_type = gsob.accounted_period_type
-- AND TRUNC(TO_DATE(pi_gl_date, 'DD-MON-RRRR'))
AND TRUNC(pi_gl_date)
BETWEEN TRUNC(gp.start_date)
AND TRUNC(gp.end_date)
AND gsob.set_of_books_id = p_sob_id;
SELECT trx.previous_customer_trx_id
, trx.complete_flag
, ctt.accounting_affect_flag
, ctt.creation_sign
, ctt.allow_overapplication_flag
, ctt.natural_application_only_flag
FROM ra_customer_trx_all trx
, ra_cust_trx_types_all ctt
WHERE trx.customer_trx_id = p_trx_id
AND trx.cust_trx_type_id = ctt.cust_trx_type_id
AND trx.org_id = ctt.org_id;
SELECT ctt.accounting_affect_flag
FROM ra_customer_trx_all trx
, ra_cust_trx_types_all ctt
WHERE trx.customer_trx_id = p_trx_id
AND trx.cust_trx_type_id = ctt.cust_trx_type_id
AND trx.org_id = ctt.org_id;
SELECT status
INTO v_status
FROM fnd_product_installations
WHERE application_id = 300;
UPDATE ra_customer_trx
SET complete_flag = 'Y'
WHERE customer_trx_id = p_customer_trx;
l_debug_info := 'UPDATE ra_customer_trx complete';
p_update_gl_date OUT NOCOPY VARCHAR2,
p_du_id IN VARCHAR2) -- shsaxena Bug 2777575
Is
CURSOR c_get_encum_flag
IS
SELECT purch_encumbrance_flag
FROM financials_system_parameters;
SELECT gps.period_year,
gsob.latest_encumbrance_year
FROM gl_period_statuses gps,
gl_sets_of_books gsob
WHERE gps.application_id = pv_app_id
AND gps.set_of_books_id = pv_sob_id
AND gps.set_of_books_id = gsob.set_of_books_id
AND trunc(pv_gl_date) BETWEEN trunc(gps.start_date) AND trunc(gps.end_date) --Bug5705031
AND gps.closing_status IN ('O', 'F')
AND NVL(gps.adjustment_period_flag, 'N') = 'N';
SELECT gps.period_year,
gsob.latest_encumbrance_year,
gps.end_date
FROM gl_period_statuses gps,
gl_sets_of_books gsob
WHERE gps.application_id = pv_app_id
AND gps.set_of_books_id = pv_sob_id
AND gps.set_of_books_id = gsob.set_of_books_id
AND gsob.latest_opened_period_name = gps.period_name
AND gps.period_year = pv_period_year; -- shsaxena Bug 2777575.
SELECT gps.period_year
FROM gl_period_statuses gps,
gl_sets_of_books gsob
WHERE gps.application_id = pv_app_id
AND gps.set_of_books_id = pv_sob_id
AND gps.set_of_books_id = gsob.set_of_books_id
AND trunc(pv_gl_date) BETWEEN trunc(gps.start_date) AND trunc(gps.end_date);
SELECT gps.end_date
FROM gl_period_statuses gps
WHERE gps.application_id = pv_app_id
AND gps.set_of_books_id = pv_sob_id
AND gps.period_year = pv_period_year
AND gps.period_num =
(SELECT max(gps1.period_num) from gl_period_statuses gps1
WHERE gps1.application_id = pv_app_id
AND gps1.set_of_books_id = pv_sob_id
AND gps1.period_year = pv_period_year);
SELECT du_fiscal_year from igi_exp_dus_v
WHERE du_id = p_du_id;
p_update_gl_date := 'Y';
p_update_gl_date:= 'N'; -- update gl_date for AP doc's set to No
l_update_gl_date VARCHAR2(1);
SELECT a.invoice_id,
a.source,
a.cancelled_date,
a.gl_date -- shsaxena bug2777575.
FROM igi_exp_ap_trans i,
ap_invoices_all a
WHERE i.invoice_id = a.invoice_id
AND i.du_id = pv_du_id;
SELECT rct.customer_trx_id
, rct.trx_number
, rctt.name
, arl.meaning
FROM ra_customer_trx_all rct
, igi_exp_ar_trans i
, ar_lookups arl
, ra_cust_trx_types_all rctt
WHERE i.du_id = pv_du_id
AND rct.customer_trx_id = i.customer_trx_id
AND rctt.cust_trx_type_id = rct.cust_trx_type_id
AND rctt.org_id = rct.org_id
AND arl.lookup_code = rct.status_trx
AND arl.lookup_type ='INVOICE_TRX_STATUS' ;
SELECT COUNT(1)
INTO l_dummy
FROM igi_exp_dus
WHERE du_id = p_du_id;
l_update_gl_date,
p_du_id); -- shsaxena Bug 2777575.
l_debug_info := 'l_update_gl_date = ';
SELECT COUNT(1)
INTO l_ar_trans
FROM igi_exp_ar_trans_all
WHERE du_id = p_du_id;
IF l_update_gl_date = 'Y' THEN
/* shsaxena bug.2777575 START */
UPDATE ap_invoice_distributions apd
SET apd.accounting_date = TRUNC(p_gl_date),
apd.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
apd.last_update_date = SYSDATE,
apd.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
WHERE apd.invoice_id = r_ap_invoices.invoice_id
AND apd.posted_flag = 'N';
UPDATE ap_invoice_lines apl
SET apl.gl_date = TRUNC(p_gl_date),
apl.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
apl.last_update_date = SYSDATE,
apl.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
WHERE apl.invoice_id = r_ap_invoices.invoice_id
AND EXISTS
(SELECT 'x' FROM ap_invoice_distributions aid
WHERE aid.invoice_id = apl.invoice_id
AND aid.posted_flag = 'N');
UPDATE ap_invoices api
SET api.gl_date = TRUNC(p_gl_date),
api.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
api.last_update_date = SYSDATE,
api.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
WHERE api.invoice_id = r_ap_invoices.invoice_id
AND EXISTS
(SELECT 'x' FROM ap_invoice_distributions aid
WHERE aid.invoice_id = api.invoice_id
AND aid.posted_flag = 'N');
END IF; --l_update_gl_date = 'Y'
SELECT COUNT(1)
INTO l_ap_trans
FROM igi_exp_ap_trans
WHERE du_id = p_du_id;
UPDATE ra_cust_trx_line_gl_dist rgd
SET rgd.gl_date = TRUNC(p_gl_date),
rgd.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
rgd.last_update_date = SYSDATE,
rgd.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
WHERE rgd.customer_trx_id = r_ar_trx.customer_trx_id
AND rgd.gl_posted_date IS NULL;
UPDATE ra_customer_trx rct
SET rct.trx_date = TRUNC(p_gl_date),
rct.last_update_login = NVL(fnd_profile.value('LOGIN_ID'),-1),
rct.last_update_date = SYSDATE,
rct.last_updated_by = NVL(fnd_profile.value('USER_ID'),-1)
WHERE rct.customer_trx_id = r_ar_trx.customer_trx_id
AND EXISTS
(SELECT 'x' FROM ra_cust_trx_line_gl_dist rgd
WHERE rgd.customer_trx_id = rct.customer_trx_id
AND rgd.gl_posted_date IS NULL);
' has not been completed and the gl date has not been updated';