[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(sob.sla_ledger_cash_basis_flag, 'N')
INTO cash_acct_flag
FROM gl_sets_of_books sob
WHERE sob.set_of_books_id = p_set_of_books_id;
SELECT ly.meaning,
ln.meaning,
l1.displayed_field,
l2.displayed_field,
l3.displayed_field
INTO nls_yes,
nls_no,
nls_all,
nls_void,
nls_na
FROM fnd_lookups ly,
fnd_lookups ln,
ap_lookup_codes l1,
ap_lookup_codes l2,
ap_lookup_codes l3
WHERE ly.lookup_type = 'YES_NO'
AND ly.lookup_code = 'Y'
AND ln.lookup_type = 'YES_NO'
AND ln.lookup_code = 'N'
AND l1.lookup_type = 'NLS REPORT PARAMETER'
AND l1.lookup_code = 'ALL'
AND l2.lookup_type = 'NLS TRANSLATION'
AND l2.lookup_code = 'VOID'
AND l3.lookup_type = 'NLS REPORT PARAMETER'
AND l3.lookup_code = 'NA';
THEN SELECT start_date, end_date
INTO p_start_date, p_end_date
FROM gl_period_statuses
WHERE period_name = p_period_name
AND application_id = 201
AND set_of_books_id = p_set_of_books_id;
if (update_acctg_dates() <> TRUE) then
RAISE init_failure;
SELECT name,
chart_of_accounts_id
INTO l_name,
l_chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_sob_id;
SELECT COUNT(*)
INTO cup_counter
FROM ap_invoice_payments_all
WHERE accounting_date BETWEEN p_start_date AND p_end_date
AND posted_flag IN ('N','S') --Bug3476167
AND org_id = co_org_id
AND rownum = 1;
SELECT COUNT(*)
INTO cui_counter
FROM ap_invoice_distributions_all
WHERE accounting_date BETWEEN p_start_date AND p_end_date
AND accrual_posted_flag = 'N'
AND p_acct_method = 'A'
AND org_id = CO_org_id
AND rownum = 1;
SELECT COUNT(*)
INTO cfp_counter
FROM ap_checks_all
WHERE future_pay_due_date IS NOT NULL
AND status_lookup_code = 'ISSUED'
AND future_pay_due_date BETWEEN p_start_date AND p_end_date
AND org_id = co_org_id
AND rownum = 1;
SELECT COUNT(*)
INTO cpb_counter
FROM ap_inv_selection_criteria_all
WHERE check_date BETWEEN p_start_date AND p_end_date
AND status NOT IN ('CONFIRMED', 'CANCELED', 'QUICKCHECK')
AND org_id = co_org_id
AND rownum = 1;
SELECT COUNT(*)
INTO cut_counter
FROM xla_ae_headers xah, xla_transaction_entities xte
WHERE xah.accounting_date BETWEEN p_start_date AND p_end_date
AND xah.gl_transfer_status_code = 'N'
AND xah.entity_id = xte.entity_id
AND xte.security_id_int_1 = co_org_id
AND rownum = 1;
SELECT multi_org_flag
INTO multi_org_installation
FROM fnd_product_groups
WHERE product_group_id = 1;
c_select_le := 'le.name ';
c_select_ou := 'ou.name ';
c_select_le := '''Legal Entity''';
c_select_ou := '''Operating Unit''';
FUNCTION UPDATE_PO_CLOSE_DATE RETURN BOOLEAN IS
CURSOR PO_LIST IS
SELECT DISTINCT PLL.LINE_LOCATION_ID,
PLL.CLOSED_DATE
FROM PO_LINE_LOCATIONS_ALL PLL,
PO_DISTRIBUTIONS_ALL PD,
AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
AND PLL.CLOSED_DATE IS NOT NULL
AND PD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID
AND AID.POSTED_FLAG = 'N'
AND NVL(AID.ORG_ID,-99) = NVL(PD.ORG_ID,-99)
AND NVL(AID.ORG_ID,-99) IN
(SELECT NVL(ASP.ORG_ID,-99)
FROM HR_ORGANIZATION_INFORMATION OI,
HR_ALL_ORGANIZATION_UNITS_TL LE,
HR_ALL_ORGANIZATION_UNITS_TL OU,
AP_SYSTEM_PARAMETERS_ALL ASP,
GL_SETS_OF_BOOKS SOB
WHERE nvl(SOB.SLA_LEDGER_CASH_BASIS_FLAG, 'N') <> 'Y'
AND SOB.SET_OF_BOOKS_ID = ASP.SET_OF_BOOKS_ID
AND ASP.ORG_ID = OI.ORGANIZATION_ID
AND OU.ORGANIZATION_ID = OI.ORGANIZATION_ID
AND OI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
AND DECODE(LTRIM(OI.ORG_INFORMATION3,'0123456789'), NULL ,
TO_NUMBER(OI.ORG_INFORMATION3), NULL ) = P_SET_OF_BOOKS_ID
AND DECODE(LTRIM(OI.ORG_INFORMATION2,'0123456789'), NULL ,
TO_NUMBER(OI.ORG_INFORMATION2), NULL )
= LE.ORGANIZATION_ID
AND OU.ORGANIZATION_ID = OI.ORGANIZATION_ID
AND OU.LANGUAGE = USERENV('LANG')
AND LE.LANGUAGE = USERENV('LANG')
)
AND ( (P_PERIOD_NAME IS NULL AND AID.ACCOUNTING_DATE BETWEEN
P_FROM_ACCTG_DATE and P_TO_ACCTG_DATE)
OR (P_PERIOD_NAME IS NOT NULL AND AID.PERIOD_NAME = P_PERIOD_NAME ))
AND AID.PO_DISTRIBUTION_ID IS NOT NULL
GROUP BY PLL.LINE_LOCATION_ID, PLL.CLOSED_DATE, AID.PO_DISTRIBUTION_ID
HAVING SUM(AID.AMOUNT) > 0;
update po_line_locations_all
set closed_date = c_sweep_to_date
where line_location_id = l_line_loc_id;
select distinct POH.po_header_id,
POH.closed_date
into l_header_id,
l_header_close_date
from po_headers_all POH,
po_line_locations_all PLL
where POH.po_header_id = PLL.po_header_id
and PLL.line_location_id = l_line_loc_id;
update po_headers
set closed_date = c_sweep_to_date
where po_header_id = l_header_id;
FUNCTION UPDATE_ACCTG_DATES RETURN BOOLEAN IS
CURSOR DIST_ORGS IS
SELECT aid.invoice_id, aid.invoice_distribution_id
FROM ap_invoice_distributions_all aid
WHERE aid.accrual_posted_flag = 'N'
AND ((p_period_name is null and aid.accounting_date between p_from_acctg_date
and p_to_acctg_date)
OR (p_period_name is not null and aid.period_name = p_period_name))
AND nvl(aid.org_id, -99) IN
(select nvl(asp.org_id, -99)
from hr_organization_information oi,
hr_all_organization_units_tl le,
hr_all_organization_units_tl ou,
ap_system_parameters_all asp
where asp.accounting_method_option = 'Accrual'
and asp.org_id = oi.organization_id
and ou.organization_id = oi.organization_id
and oi.org_information_context =
'Operating Unit Information'
and DECODE(LTRIM(oi.org_information3,'0123456789'), NULL
, TO_NUMBER(oi.org_information3) , NULL ) =
p_set_of_books_id
and DECODE(LTRIM(oi.org_information2,'0123456789'), NULL
, TO_NUMBER(oi.org_information2), NULL) =
le.organization_id
and ou.organization_id = oi.organization_id
and ou.language = USERENV('LANG')
and le.language = USERENV('LANG'));
SELECT aid.invoice_id, aid.invoice_distribution_id
FROM ap_invoice_distributions_all aid,
ap_system_parameters_all asp
WHERE aid.accrual_posted_flag = 'N'
AND asp.accounting_method_option = 'Accrual'
AND ((p_period_name is null and aid.accounting_date between p_from_acctg_date
and p_to_acctg_date)
OR (p_period_name is not null and aid.period_name = p_period_name));
/*SRW.MESSAGE(0, 'UPDATE_ACCTG_DATES');*/null;
if (update_po_close_date() <> TRUE) then
return(FALSE);
select count(*)
into v_no_orgs
from ap_system_parameters_all;
UPDATE ap_invoice_distributions_all aid
SET accounting_date = c_sweep_to_date,
period_name = p_to_period,
last_update_date = sysdate,
last_updated_by = 5
WHERE aid.invoice_distribution_id = l_invoice_distribution_id;
UPDATE ap_invoice_payments_all aip
SET accounting_date = c_sweep_to_date,
period_name = p_to_period,
last_update_date = sysdate,
last_updated_by = 5
WHERE posted_flag IN ('N','S') AND ((p_period_name is null and accounting_date between p_from_acctg_date
and p_to_acctg_date)
OR (p_period_name is not null and period_name = p_period_name))
AND nvl(aip.org_id, -99) IN
(select nvl(asp.org_id, -99)
from hr_organization_information oi,
hr_all_organization_units_tl le,
hr_all_organization_units_tl ou,
ap_system_parameters_all asp
where
asp.org_id = oi.organization_id
and ou.organization_id = oi.organization_id
and oi.org_information_context =
'Operating Unit Information'
and DECODE(LTRIM(oi.org_information3,'0123456789'), NULL
, TO_NUMBER(oi.org_information3) , NULL ) =
p_set_of_books_id
and DECODE(LTRIM(oi.org_information2,'0123456789'), NULL
, TO_NUMBER(oi.org_information2), NULL) =
le.organization_id
and ou.organization_id = oi.organization_id
and ou.language = USERENV('LANG')
and le.language = USERENV('LANG'));
UPDATE ap_payment_history_all aph
SET accounting_date = c_sweep_to_date,
last_update_date = sysdate,
last_updated_by = 5
WHERE nvl(aph.posted_flag, 'N') IN ('N','S')
AND ((p_period_name is null and accounting_date between p_from_acctg_date
and p_to_acctg_date)
OR
(p_period_name is not null and accounting_date between c_from_acctg_date
and c_to_acctg_date))
AND nvl(aph.org_id, -99) IN
(select nvl(asp.org_id, -99)
from hr_organization_information oi,
hr_all_organization_units_tl le,
hr_all_organization_units_tl ou,
ap_system_parameters_all asp
where
asp.org_id = oi.organization_id
and ou.organization_id = oi.organization_id
and oi.org_information_context =
'Operating Unit Information'
and DECODE(LTRIM(oi.org_information3,'0123456789'), NULL
, TO_NUMBER(oi.org_information3) , NULL ) =
p_set_of_books_id
and DECODE(LTRIM(oi.org_information2,'0123456789'), NULL
, TO_NUMBER(oi.org_information2), NULL) =
le.organization_id
and ou.organization_id = oi.organization_id
and ou.language = USERENV('LANG')
and le.language = USERENV('LANG'));
p_calling_sequence => 'APXTRSWP.rdf (update_acctg_dates() )'
);
UPDATE ap_invoice_distributions_all aid
SET accounting_date = c_sweep_to_date,
period_name = p_to_period,
last_update_date = sysdate,
last_updated_by = 5
WHERE aid.invoice_distribution_id = l_invoice_distribution_id;
UPDATE ap_invoice_payments_all aip
SET accounting_date = c_sweep_to_date,
period_name = p_to_period,
last_update_date = sysdate,
last_updated_by = 5
WHERE posted_flag IN ('N','S') AND ((p_period_name is null and accounting_date between p_from_acctg_date
and p_to_acctg_date)
OR (p_period_name is not null and period_name = p_period_name));
UPDATE ap_payment_history_all aph
SET accounting_date = c_sweep_to_date,
last_update_date = sysdate,
last_updated_by = 5
WHERE nvl(aph.posted_flag, 'N') IN ('N','S') AND ((p_period_name is null and accounting_date between p_from_acctg_date
and p_to_acctg_date)
OR
(p_period_name is not null and accounting_date between c_from_acctg_date
and c_to_acctg_date));
p_calling_sequence => 'APXTRSWP.rdf (update_acctg_dates() )'
);
SELECT start_date
INTO l_to_acctg_date
FROM gl_period_statuses
WHERE period_name = p_to_period
AND application_id = 200
AND set_of_books_id = p_set_of_books_id
AND nvl(adjustment_period_flag, 'N') = 'N';
SELECT start_date,
end_date
INTO l_start_date,
l_end_date
FROM gl_period_statuses
WHERE period_name = p_period_name
AND application_id = 200
AND set_of_books_id = p_set_of_books_id
AND nvl(adjustment_period_flag, 'N') = 'N';
SELECT nvl(sla_ledger_cash_basis_flag, 'N')
INTO l_cash_basis_flag
FROM gl_sets_of_books
WHERE set_of_books_id = p_set_of_books_id;
ELSE SELECT COUNT(*)
INTO cui_counter
FROM ap_invoice_distributions_all
WHERE accounting_date BETWEEN p_start_date AND p_end_date
AND accrual_posted_flag = 'N'
AND p_acct_method = 'A'
AND org_id = CO_org_id
AND rownum = 1;
SELECT COUNT(*)
INTO cup_counter
FROM ap_invoice_payments_all
WHERE accounting_date BETWEEN p_start_date AND p_end_date
AND posted_flag IN ('N','S') AND org_id = co_org_id
AND rownum = 1;
SELECT COUNT(*)
INTO cut_counter
FROM xla_ae_headers xah, xla_transaction_entities xte
WHERE xah.accounting_date BETWEEN p_start_date AND p_end_date
AND xah.gl_transfer_status_code = 'N'
AND xah.entity_id = xte.entity_id
AND xte.security_id_int_1 = co_org_id
AND rownum = 1;
SELECT COUNT(*)
INTO cfp_counter
FROM ap_checks_all
WHERE future_pay_due_date IS NOT NULL
AND status_lookup_code = 'ISSUED'
AND future_pay_due_date BETWEEN p_start_date AND p_end_date
AND org_id = co_org_id
AND rownum = 1;
SELECT COUNT(*)
INTO cpb_counter
FROM ap_inv_selection_criteria_all
WHERE check_date BETWEEN p_start_date AND p_end_date
AND status NOT IN ('CONFIRMED', 'CANCELED', 'QUICKCHECK')
AND org_id = co_org_id
AND rownum = 1;
Function C_SELECT_LE_p return varchar2 is
Begin
return C_SELECT_LE;
Function C_SELECT_OU_p return varchar2 is
Begin
return C_SELECT_OU;
SELECT COUNT(*)
INTO cui_counter
FROM ap_invoice_distributions_all
WHERE accounting_date BETWEEN p_start_date AND p_end_date
AND accrual_posted_flag = 'N'
AND p_acct_method = 'A'
AND set_of_books_id = p_set_of_books_id
AND rownum = 1;
SELECT COUNT(*)
INTO cup_counter
FROM ap_invoice_payments_all
WHERE accounting_date BETWEEN p_start_date AND p_end_date
AND posted_flag IN ('N','S') --Bug 3476167
AND set_of_books_id = p_set_of_books_id
AND rownum = 1;
SELECT COUNT(*)
INTO cut_counter
FROM xla_ae_headers --Bug 3739324
WHERE accounting_date BETWEEN p_start_date AND p_end_date
AND gl_transfer_status_code = 'N'
AND ledger_id = p_set_of_books_id
AND rownum = 1;
SELECT COUNT(*)
INTO cfp_counter
FROM ap_checks_all AC, hr_operating_units HOU
WHERE AC.org_id = HOU.organization_id
AND AC.future_pay_due_date IS NOT NULL
AND AC.status_lookup_code = 'ISSUED'
AND AC.future_pay_due_date BETWEEN p_start_date AND p_end_date
AND HOU.set_of_books_id = to_char(p_set_of_books_id) --Bug 2986690
AND rownum = 1;
SELECT COUNT(*)
INTO cpb_counter
FROM ap_inv_selection_criteria_all AIS, hr_operating_units HOU
WHERE AIS.org_id = HOU.organization_id
AND AIS.check_date BETWEEN p_start_date AND p_end_date
AND status NOT IN ('CONFIRMED', 'CANCELED', 'QUICKCHECK')
AND HOU.set_of_books_id = to_char(p_set_of_books_id) --Bug 2986690
AND rownum = 1;