[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE po_distributions_all pod
SET pod.accrued_flag = 'Y'
WHERE pod.po_distribution_id = g_accrued_dist_id_tbl(l_ctr);
g_accrued_dist_id_tbl.DELETE;
SELECT shipment_id,
distribution_id,
quantity_received,
quantity_billed,
accrual_quantity,
encum_quantity,
unit_price,
accrual_amount,
encum_amount,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date
FROM cst_per_end_accruals_temp;
'Insert_AccrualSubLedger <<');
SELECT poll.ship_to_organization_id,
poh.segment1
INTO l_inv_org_id,
l_po_number
FROM po_line_locations_all poll,
po_headers_all poh
WHERE poll.line_location_id = l_accounting_events_rec.shipment_id
AND poh.po_header_id = poll.po_header_id;
SELECT rcv_accounting_event_s.nextval,
raet.event_type_id
INTO g_rae_event_id_tbl(l_ctr),
g_rae_event_type_id_tbl(l_ctr)
FROM rcv_accounting_event_types raet
WHERE raet.event_type_name = 'PERIOD_END_ACCRUAL';
SELECT rcv_accounting_event_s.nextval,
raet.event_type_id
INTO g_rae_event_id_tbl(l_ctr),
g_rae_event_type_id_tbl(l_ctr)
FROM rcv_accounting_event_types raet
WHERE raet.event_type_name = 'ENCUMBRANCE_REVERSAL';
INSERT into RCV_ACCOUNTING_EVENTS (
accounting_event_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_udpate_date,
rcv_transaction_id,
event_type_id,
event_source,
event_source_id,
set_of_books_id,
org_id,
organization_id,
debit_account_id,
credit_account_id,
transaction_date,
source_doc_quantity,
transaction_quantity,
primary_quantity,
source_doc_unit_of_measure,
transaction_unit_of_measure,
primary_unit_of_measure,
po_header_id,
po_release_id,
po_line_id,
po_line_location_id,
po_distribution_id,
inventory_item_id,
unit_price,
intercompany_pricing_option,
transaction_amount,
quantity_received,
quantity_invoiced,
amount_received,
amount_invoiced,
nr_tax,
rec_tax,
nr_tax_amount,
rec_tax_amount,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
accounted_flag,
cross_ou_flag
)
SELECT
g_rae_event_id_tbl(l_ctr),
SYSDATE,
l_user_id,
l_login_id,
sysdate,
l_user_id,
l_conc_request_id,
l_prog_appl_id,
l_conc_program_id,
sysdate,
0,
g_rae_event_type_id_tbl(l_ctr),
'PERIOD_END_ACCRUAL',
g_rae_distribution_id_tbl(l_ctr),
p_sys_setup_rec.set_of_books_id,
l_org_id,
poll.ship_to_organization_id,
pod.code_combination_id,
pod.accrual_account_id,
p_sys_setup_rec.transaction_date,
DECODE (poll.matching_basis,
'QUANTITY', g_rae_txn_qty_tbl(l_ctr) ),
DECODE (poll.matching_basis,
'QUANTITY', g_rae_txn_qty_tbl(l_ctr) ),
DECODE (poll.matching_basis,
'QUANTITY', g_rae_txn_qty_tbl(l_ctr) *
inv_convert.inv_um_convert(
NVL(pol.item_id, 0),
10,
NULL,
NULL,
NULL,
poll.unit_meas_lookup_code,
NVL(msi.primary_unit_of_measure, puom.unit_of_measure))
),
poll.unit_meas_lookup_code,
poll.unit_meas_lookup_code,
NVL(msi.primary_unit_of_measure, puom.unit_of_measure),
poh.po_header_id,
poll.po_release_id,
pol.po_line_id,
poll.line_location_id,
pod.po_distribution_id,
pol.item_id,
g_rae_unit_pice_tbl(l_ctr),
1,
DECODE (poll.matching_basis,
'AMOUNT', g_rae_txn_amount_tbl(l_ctr)),
DECODE (poll.matching_basis,
'QUANTITY', g_rae_qty_received_tbl(l_ctr)),
DECODE (poll.matching_basis,
'QUANTITY', g_rae_qty_invoiced_tbl(l_ctr)),
DECODE (poll.matching_basis,
'AMOUNT', g_rae_qty_received_tbl(l_ctr)),
DECODE (poll.matching_basis,
'AMOUNT', g_rae_qty_invoiced_tbl(l_ctr)),
DECODE (poll.matching_basis,
'QUANTITY', pod.recoverable_tax / pod.quantity_ordered),
DECODE (poll.matching_basis,
'QUANTITY', pod.nonrecoverable_tax / pod.quantity_ordered),
DECODE (poll.matching_basis,
'AMOUNT', pod.recoverable_tax * g_rae_txn_qty_tbl(l_ctr)
/ pod.amount_ordered),
DECODE (poll.matching_basis,
'AMOUNT', pod.nonrecoverable_tax * g_rae_txn_qty_tbl(l_ctr)
/ pod.amount_ordered),
g_rae_currency_code_tbl(l_ctr),
g_rae_cur_conv_type_tbl(l_ctr),
g_rae_cur_conv_rate_tbl(l_ctr),
g_rae_cur_conv_date_tbl(l_ctr),
'N',
DECODE( poh.org_id,
cod.operating_unit, 'N',
'Y')
FROM
po_headers_all poh,
po_lines_all pol,
po_line_locations_all poll,
po_distributions_all pod,
cst_organization_definitions cod,
mtl_system_items msi,
mtl_units_of_measure tuom,
mtl_units_of_measure puom
WHERE
pod.po_distribution_id = g_rae_distribution_id_tbl(l_ctr)
AND poh.po_header_id = pol.po_header_id
AND pol.po_line_id = poll.po_line_id
AND poll.line_location_id = pod.line_location_id
AND cod.organization_id = poll.ship_to_organization_id
AND msi.inventory_item_id (+) = pol.item_id
AND (msi.organization_id IS NULL
OR
(msi.organization_id = poll.ship_to_organization_id AND msi.organization_id IS NOT NULL))
AND tuom.unit_of_measure(+) = decode(poll.matching_basis, 'QUANTITY', poll.unit_meas_lookup_code, NULL)
AND puom.uom_class(+) = tuom.uom_class
AND puom.base_uom_flag(+) = 'Y';
g_rae_distribution_id_tbl.DELETE;
g_rae_qty_received_tbl.DELETE;
g_rae_qty_invoiced_tbl.DELETE;
g_rae_unit_pice_tbl.DELETE;
g_rae_txn_qty_tbl.DELETE;
g_rae_txn_amount_tbl.DELETE;
g_rae_currency_code_tbl.DELETE;
g_rae_cur_conv_type_tbl.DELETE;
g_rae_cur_conv_rate_tbl.DELETE;
g_rae_cur_conv_date_tbl.DELETE;
'Insert_AccrualSubLedger >>'
);
SELECT cpea.shipment_id shipment_id,
cpea.distribution_id distribution_id,
cpea.accrual_quantity accrual_quantity,
cpea.encum_quantity encum_quantity,
cpea.accrual_amount entered_dr,
cpea.accrual_amount entered_cr,
cpea.encum_amount entered_encum_dr,
cpea.encum_amount entered_encum_cr,
poh.segment1 po_number,
NVL(fnc1.minimum_accountable_unit,0) min_func_acct_unit,
fnc1.precision func_currency_precision,
NVL(fnc2.minimum_accountable_unit,0) min_acct_unit,
fnc2.precision currency_precision,
poh.po_header_id po_header_id,
cpea.currency_code currency_code,
cpea.currency_conversion_rate currency_rate,
NVL(pod.rate,1) encum_currency_rate,
pod.rate_date encum_currency_conv_date,
glct.user_conversion_type user_curr_conv_type,
cpea.currency_conversion_date currency_conv_date,
pod.recoverable_tax * cpea.accrual_quantity
/ DECODE(poll.matching_basis,
'AMOUNT', pod.amount_ordered,
pod.quantity_ordered ) entered_rec_tax,
pod.nonrecoverable_tax * cpea.accrual_quantity
/ DECODE(poll.matching_basis,
'AMOUNT', pod.amount_ordered,
pod.quantity_ordered ) entered_nr_tax,
pod.code_combination_id act_debit_ccid,
pod.accrual_account_id act_credit_ccid,
pod.budget_account_id enc_credit_ccid
FROM cst_per_end_accruals_temp cpea,
po_headers_all poh,
po_line_locations_all poll,
po_distributions_all pod,
fnd_currencies fnc1,
fnd_currencies fnc2,
gl_daily_conversion_types glct
WHERE pod.po_distribution_id = cpea.distribution_id
AND pod.po_header_id = poh.po_header_id
AND pod.line_location_id = poll.line_location_id
AND fnc1.currency_code = l_func_currency_code
AND fnc2.currency_code = cpea.currency_code
AND cpea.currency_conversion_type = glct.conversion_type(+)
;
Insert_Account (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_accrual_info_rec => l_accrual_info_rec,
p_sys_setup_rec => p_sys_setup_rec
);
l_msg_data := 'Failed inserting data in Accrual table';
Insert_Account (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_accrual_info_rec => l_accrual_info_rec,
p_sys_setup_rec => p_sys_setup_rec
);
l_msg_data := 'Failed inserting data in Accrual table';
Insert_Account (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_accrual_info_rec => l_accrual_info_rec,
p_sys_setup_rec => p_sys_setup_rec
);
l_msg_data := 'Failed inserting data in Accrual table';
Insert_AccrualSubLedger (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_sys_setup_rec => p_sys_setup_rec
);
l_msg_data := 'Failed inserting data in RCV_RECEIVING_SUB_LEDGER';
DELETE FROM XLA_EVENTS_INT_GT;
INSERT INTO XLA_EVENTS_INT_GT
( application_id,
ledger_id,
entity_code,
source_id_int_1,
source_id_int_2,
source_id_int_3,
event_class_code,
event_type_code,
event_date,
event_status_code,
security_id_int_1,
security_id_int_2,
transaction_date,
reference_date_1,
transaction_number,
budgetary_control_flag
)
VALUES (
707,
p_sys_setup_rec.set_of_books_id,
'RCV_ACCOUNTING_EVENTS',
0,
decode(g_rae_event_type_id_tbl(l_ctr),
13,g_rae_pnt_event_id_tbl(l_ctr),
g_rae_event_id_tbl(l_ctr)),
g_rae_inv_org_id_tbl(l_ctr),
'PERIOD_END_ACCRUAL',
'PERIOD_END_ACCRUAL',
p_sys_setup_rec.transaction_date,
XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
g_rae_inv_org_id_tbl(l_ctr),
p_sys_setup_rec.org_id,
p_sys_setup_rec.transaction_date,
l_reference_date_1,
g_rae_po_number_tbl(l_ctr),
NULL
);
g_rae_event_id_tbl.DELETE;
g_rae_event_type_id_tbl.DELETE;
g_rae_inv_org_id_tbl.DELETE;
g_rae_po_number_tbl.DELETE;
g_accrual_index_tbl.DELETE;
g_encum_index_tbl.DELETE;
g_rae_pnt_event_id_tbl.DELETE;
PROCEDURE Insert_Account
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_accrual_info_rec IN CST_ACCRUAL_INFO_REC_TYPE,
p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
)
IS
l_api_name CONSTANT VARCHAR2(30) :='Insert_Account';
SAVEPOINT Insert_Account_PVT;
'Insert_Account <<');
Insert_AccrualSubLedger (
p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_sys_setup_rec => p_sys_setup_rec
);
l_msg_data := 'Failed inserting data in RCV_RECEIVING_SUB_LEDGER';
'Insert_Account >>'
);
ROLLBACK TO Insert_Account_PVT;
ROLLBACK TO Insert_Account_PVT;
END Insert_Account;
PROCEDURE Insert_AccrualSubLedger
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_validation_level IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
)
IS
l_api_name CONSTANT VARCHAR2(30) :='Insert_AccrualSubLedger';
SAVEPOINT Insert_AccrualSubLedger_PVT;
'Insert_AccrualSubLedger <<');
INSERT INTO rcv_receiving_sub_ledger (
rcv_sub_ledger_id,
rcv_transaction_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
actual_flag,
currency_code,
je_source_name,
je_category_name,
set_of_books_id,
accounting_date,
code_combination_id,
entered_dr,
entered_cr,
accounted_dr,
accounted_cr,
currency_conversion_date,
user_currency_conversion_type,
currency_conversion_rate,
transaction_date,
period_name,
chart_of_accounts_id,
functional_currency_code,
reference1,
reference2,
reference3,
reference4,
reference9,
source_doc_quantity,
entered_rec_tax,
entered_nr_tax,
accounted_rec_tax,
accounted_nr_tax,
accrual_method_flag,
accounting_event_id,
accounting_line_type
)
VALUES (
DECODE( g_actual_flag_tbl(l_ctr),'E',-1,1) * rcv_receiving_sub_ledger_s.nextval,
0,
SYSDATE,
l_user_id,
SYSDATE,
l_user_id,
l_login_id,
g_actual_flag_tbl(l_ctr),
g_currency_code_tbl(l_ctr),
p_sys_setup_rec.user_je_source_name,
p_sys_setup_rec.user_je_category_name,
p_sys_setup_rec.set_of_books_id,
p_sys_setup_rec.accrual_effect_date,
g_code_combination_id_tbl(l_ctr),
g_entered_dr_tbl(l_ctr),
g_entered_cr_tbl(l_ctr),
g_accounted_dr_tbl(l_ctr),
g_accounted_cr_tbl(l_ctr),
g_curr_conversion_date_tbl(l_ctr),
g_user_curr_conversion_tbl(l_ctr),
g_curr_conversion_rate_tbl(l_ctr),
p_sys_setup_rec.transaction_date,
p_sys_setup_rec.period_name,
p_sys_setup_rec.chart_of_accounts_id,
p_sys_setup_rec.functional_currency_code,
'PO',
TO_CHAR(g_po_header_id_tbl(l_ctr)),
TO_CHAR(g_distribution_id_tbl(l_ctr)),
g_po_number_tbl(l_ctr),
g_pnt_rcv_acc_event_id_tbl(l_ctr),
g_source_doc_quantity_tbl(l_ctr),
g_entered_rec_tax_tbl(l_ctr),
g_entered_nr_tax_tbl(l_ctr),
g_accounted_rec_tax_tbl(l_ctr),
g_accounted_nr_tax_tbl(l_ctr),
g_accrual_method_flag_tbl(l_ctr),
g_rcv_acc_event_id_tbl(l_ctr),
g_accounting_line_type_tbl(l_ctr)
);
g_rcv_acc_event_id_tbl.DELETE;
g_actual_flag_tbl.DELETE;
g_currency_code_tbl.DELETE;
g_code_combination_id_tbl.DELETE;
g_entered_dr_tbl.DELETE;
g_entered_cr_tbl.DELETE;
g_accounted_dr_tbl.DELETE;
g_accounted_cr_tbl.DELETE;
g_curr_conversion_date_tbl.DELETE;
g_user_curr_conversion_tbl.DELETE;
g_curr_conversion_rate_tbl.DELETE;
g_po_header_id_tbl.DELETE;
g_distribution_id_tbl.DELETE;
g_po_number_tbl.DELETE;
g_source_doc_quantity_tbl.DELETE;
g_entered_rec_tax_tbl.DELETE;
g_entered_nr_tax_tbl.DELETE;
g_accounted_rec_tax_tbl.DELETE;
g_accounted_nr_tax_tbl.DELETE;
g_accrual_method_flag_tbl.DELETE;
g_accounting_line_type_tbl.DELETE;
g_pnt_rcv_acc_event_id_tbl.DELETE;
'Insert_AccrualSubLedger >>'
);
ROLLBACK TO Insert_AccrualSubLedger_PVT;
ROLLBACK TO Insert_AccrualSubLedger_PVT;
END Insert_AccrualSubLedger;
SELECT NVL(fsp.set_of_books_id, 0),
NVL(sob.chart_of_accounts_id, 0),
sob.currency_code,
NVL(fsp.purch_encumbrance_flag, 'N'),
DECODE( SIGN(acr.start_date - SYSDATE),
1, acr.start_date,
DECODE( SIGN(SYSDATE - acr.end_date),
1, acr.end_date,
SYSDATE)),
acr.end_date,
fsp.org_id
INTO x_sys_setup_rec.set_of_books_id,
x_sys_setup_rec.chart_of_accounts_id,
x_sys_setup_rec.functional_currency_code,
x_sys_setup_rec.purch_encumbrance_flag,
x_sys_setup_rec.accrual_effect_date,
x_sys_setup_rec.accrual_cutoff_date,
x_sys_setup_rec.org_id
FROM gl_period_statuses acr,
financials_system_parameters fsp,
gl_sets_of_books sob
WHERE acr.application_id = l_application_id
AND acr.set_of_books_id = fsp.set_of_books_id
AND acr.period_name = p_period_name
AND fsp.set_of_books_id = sob.set_of_books_id
AND acr.adjustment_period_flag = 'N';
SELECT TO_NUMBER(org_information2)
INTO l_legal_entity
FROM hr_organization_information
WHERE organization_id = MO_GLOBAL.GET_CURRENT_ORG_ID
AND org_information_context = 'Operating Unit Information';
SELECT user_je_category_name
INTO x_sys_setup_rec.user_je_category_name
FROM gl_je_categories
WHERE je_category_name = 'Accrual';
SELECT user_je_source_name
INTO x_sys_setup_rec.user_je_source_name
FROM gl_je_sources
WHERE je_source_name = 'Purchasing';