[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT into cst_accrual_accounts(
operating_unit_id,
accrual_account_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
)
/* Grabs accrual accounts that have been part of a purchase order */
SELECT
t.org_id,
t.accrual_account_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate
FROM
(select distinct p_ou_id org_id, paat.accrual_account_id accrual_account_id
from po_accrual_accounts_temp_all paat
where paat.org_id = p_ou_id
and not exists (
select 1
from cst_accrual_accounts caa
where caa.accrual_account_id = paat.accrual_account_id
and caa.operating_unit_id = p_ou_id)
and exists ( select 1
from financials_system_params_all fsp,
gl_sets_of_books gsb,
gl_code_combinations gcc
where gsb.set_of_books_id = fsp.set_of_books_id
and fsp.org_id = p_ou_id
and gcc.code_combination_id = paat.accrual_account_id
and gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
)
union
/* Grabs the default Purchasing accrual account */
select distinct p_ou_id org_id, psp.accrued_code_combination_id accrual_account_id
from po_system_parameters_all psp
where psp.accrued_code_combination_id is not null
and psp.org_id = p_ou_id
and not exists (
select 1
from cst_accrual_accounts caa
where caa.accrual_account_id = psp.accrued_code_combination_id
and caa.operating_unit_id = p_ou_id)
and exists ( select 1
from financials_system_params_all fsp,
gl_sets_of_books gsb,
gl_code_combinations gcc
where gsb.set_of_books_id = fsp.set_of_books_id
and fsp.org_id = p_ou_id
and gcc.code_combination_id = psp.accrued_code_combination_id
and gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
)
union
/* Grabs the accrual account for each inventory organization*/
select distinct p_ou_id org_id, mp.ap_accrual_account accrual_account_id
from mtl_parameters mp
where mp.ap_accrual_account is not null
and exists (
select 1
from hr_organization_information hoi
where hoi.organization_id = mp.organization_id
and hoi.org_information_context = 'Accounting Information'
and hoi.org_information3 = to_char(p_ou_id))
and not exists (
select 1
from cst_accrual_accounts caa
where caa.accrual_account_id = mp.ap_accrual_account
and caa.operating_unit_id = p_ou_id)
and exists ( select 1
from financials_system_params_all fsp,
gl_sets_of_books gsb,
gl_code_combinations gcc
where gsb.set_of_books_id = fsp.set_of_books_id
and fsp.org_id = p_ou_id
and gcc.code_combination_id = mp.ap_accrual_account
and gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
)
) t ;
fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Table Select: ' || to_char(p_prog));
update cst_reconciliation_summary
set write_off_select_flag = 'Y'
where rowid = p_row_id;
update cst_misc_reconciliation
set write_off_select_flag = 'Y'
where rowid = p_row_id;
update cst_write_offs
set write_off_select_flag = 'Y'
where rowid = p_row_id;
update cst_reconciliation_summary
set write_off_select_flag = null
where rowid = p_row_id;
update cst_misc_reconciliation
set write_off_select_flag = null
where rowid = p_row_id;
update cst_write_offs
set write_off_select_flag = null
where rowid = p_row_id;
procedure update_all ( p_where in varchar2,
p_prog in number,
p_ou_id in number,
x_out out nocopy number,
x_tot out nocopy number,
x_err_num out nocopy number,
x_err_code out nocopy varchar2,
x_err_msg out nocopy varchar2) is
l_api_version constant number := 1.0;
l_api_name constant varchar2(30) := 'update_all';
'.begin', 'update_all << '
|| 'p_where := ' || p_where
|| 'p_prog := ' || to_char(p_prog)
|| 'p_ou := ' || to_char(p_ou_id));
fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Table Select: ' || to_char(p_prog));
select count(*), sum(po_balance + ap_balance + write_off_balance)
into x_tot, x_out
from cst_reconciliation_summary
where operating_unit_id = p_ou_id
and write_off_select_flag = 'Y';
select count(*), sum(amount)
into x_tot, x_out
from cst_misc_reconciliation
where operating_unit_id = p_ou_id
and write_off_select_flag = 'Y';
x_err_msg := 'CST_Accrual_Rec_PVT.update_all() ' || SQLERRM;
end update_all;
procedure insert_misc_data_all(
p_wo_date in date,
p_off_id in number,
p_rea_id in number,
p_comments in varchar2,
p_sob_id in number,
p_ou_id in number,
x_count out nocopy number,
x_err_num out nocopy number,
x_err_code out nocopy varchar2,
x_err_msg out nocopy varchar2) is
l_api_version constant number := 1.0;
l_api_name constant varchar2(30) := 'insert_misc_data_all';
/* Cursor to hold all select miscellaneous transactions*/
cursor c_wo(l_ou_id number) is
select po_accrual_write_offs_s.nextval l_wo_id,
accrual_account_id,
transaction_date,
amount,
entered_amount,
quantity,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
transaction_type_code,
invoice_distribution_id,
inventory_transaction_id,
po_distribution_id,
inventory_item_id,
vendor_id,
inventory_organization_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
ae_header_id,
ae_line_num
from cst_misc_reconciliation
where operating_unit_id = l_ou_id
and write_off_select_flag = 'Y';
'.begin', 'insert_misc_data_all << '
|| 'p_wo_date := ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS')
|| 'p_off_id := ' || to_char(p_off_id)
|| 'p_rea_id := ' || to_char(p_rea_id)
|| 'p_comments := ' || p_comments
|| 'p_sob_id := ' || to_char(p_sob_id)
|| 'p_ou_id := ' || to_char(p_ou_id));
/* Check whether any transactions have been selected for write-off */
select count(*)
into l_rows
from cst_misc_reconciliation
where operating_unit_id = p_ou_id
and write_off_select_flag = 'Y';
select org_information2
into l_le_id
from hr_organization_information
where organization_id = c_wo_rec.inventory_organization_id
and org_information_context = 'Accounting Information';
select apia.legal_entity_id
into l_le_id
from ap_invoices_all apia
,(SELECT invoice_id
,invoice_distribution_id
FROM ap_invoice_distributions_all
UNION
SELECT invoice_id
,invoice_distribution_id
FROM ap_self_assessed_tax_dist_all ) aida
where aida.invoice_distribution_id = c_wo_rec.invoice_distribution_id
and apia.invoice_id = aida.invoice_id;
/* Insert necessary information into SLA events temp table */
insert into xla_events_int_gt
(
application_id,
ledger_id,
legal_entity_id,
entity_code,
source_id_int_1,
event_class_code,
event_type_code,
event_date,
event_status_code,
--BUG#7226250
security_id_int_2,
transaction_date,
reference_date_1,
transaction_number
)
values
(
707,
p_sob_id,
l_le_id,
'WO_ACCOUNTING_EVENTS',
c_wo_rec.l_wo_id,
'ACCRUAL_WRITE_OFF',
'ACCRUAL_WRITE_OFF',
p_wo_date,
XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
p_ou_id,
p_wo_date,
INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(p_wo_date,p_ou_id),
to_char(c_wo_rec.l_wo_id)
);
Insert the selected miscellaneous transactions into
Costing's Write-Off tables
*/
insert all
into cst_write_offs
(
write_off_id,
transaction_date,
accrual_account_id,
offset_account_id,
write_off_amount,
entered_amount,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
transaction_type_code,
invoice_distribution_id,
inventory_transaction_id,
po_distribution_id,
reason_id,
comments,
inventory_item_id,
vendor_id,
legal_entity_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
)
values
(
c_wo_rec.l_wo_id,
p_wo_date,
c_wo_rec.accrual_account_id,
p_off_id,
(-1) * c_wo_rec.amount,
(-1) * c_wo_rec.entered_amount,
c_wo_rec.currency_code,
c_wo_rec.currency_conversion_type,
c_wo_rec.currency_conversion_rate,
c_wo_rec.currency_conversion_date,
'WRITE OFF',
c_wo_rec.invoice_distribution_id,
c_wo_rec.inventory_transaction_id,
c_wo_rec.po_distribution_id,
p_rea_id,
p_comments,
c_wo_rec.inventory_item_id,
c_wo_rec.vendor_id,
l_le_id,
p_ou_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate -- program_update_date
)
into cst_write_off_details
(
write_off_id,
transaction_date,
amount,
entered_amount,
quantity,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
transaction_type_code,
invoice_distribution_id,
inventory_transaction_id,
inventory_organization_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
ae_header_id,
ae_line_num
)
values
(
c_wo_rec.l_wo_id,
c_wo_rec.transaction_date,
c_wo_rec.amount,
c_wo_rec.entered_amount,
c_wo_rec.quantity,
c_wo_rec.currency_code,
c_wo_rec.currency_conversion_type,
c_wo_rec.currency_conversion_rate,
c_wo_rec.currency_conversion_date,
c_wo_rec.transaction_type_code,
c_wo_rec.invoice_distribution_id,
c_wo_rec.inventory_transaction_id,
c_wo_rec.inventory_organization_id,
p_ou_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate, --program_update_date,
c_wo_rec.ae_header_id,
c_wo_rec.ae_line_num
)
select c_wo_rec.l_wo_id,
c_wo_rec.accrual_account_id,
c_wo_rec.transaction_date,
c_wo_rec.amount,
c_wo_rec.entered_amount,
c_wo_rec.quantity,
c_wo_rec.currency_code,
c_wo_rec.currency_conversion_type,
c_wo_rec.currency_conversion_rate,
c_wo_rec.currency_conversion_date,
c_wo_rec.transaction_type_code,
c_wo_rec.invoice_distribution_id,
c_wo_rec.inventory_transaction_id,
c_wo_rec.po_distribution_id,
c_wo_rec.inventory_item_id,
c_wo_rec.vendor_id,
c_wo_rec.inventory_organization_id,
c_wo_rec.operating_unit_id,
c_wo_rec.ae_header_id,
c_wo_rec.ae_line_num
from cst_misc_reconciliation
where rownum = 1;
/* Delete written-off transactions from Costing's Miscellaneous table */
delete from cst_misc_reconciliation
where operating_unit_id = p_ou_id
and write_off_select_flag = 'Y';
inserted into SLA's event temp table
*/
xla_events_pub_pkg.create_bulk_events(p_source_application_id => 201,
p_application_id => 707,
p_ledger_id => p_sob_id,
p_entity_type_code => 'WO_ACCOUNTING_EVENTS');
x_err_msg := 'CST_Accrual_Rec_PVT.insert_misc_data_all() ' || SQLERRM;
end insert_misc_data_all;
procedure insert_appo_data_all(
p_wo_date in date,
p_rea_id in number,
p_comments in varchar2,
p_sob_id in number,
p_ou_id in number,
x_count out nocopy number,
x_err_num out nocopy number,
x_err_code out nocopy varchar2,
x_err_msg out nocopy varchar2) is
l_api_version constant number := 1.0;
l_api_name constant varchar2(30) := 'insert_appo_data_all';
/* Cusor to hold all the PO distributions selected in the AP and PO form*/
cursor c_wo(l_ou_id number) is
select po_accrual_write_offs_s.nextval l_wo_id,
(po_balance + ap_balance + write_off_balance) l_tot_bal,
po_distribution_id,
accrual_account_id,
destination_type_code,
inventory_item_id,
vendor_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
from cst_reconciliation_summary
where operating_unit_id = l_ou_id
and write_off_select_flag = 'Y';
'.begin', 'insert_appo_data_all << '
|| 'p_wo_date := ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS')
|| 'p_rea_id := ' || to_char(p_rea_id)
|| 'p_comments := ' || p_comments
|| 'p_sob_id := ' || to_char(p_sob_id)
|| 'p_ou_id := ' || to_char(p_ou_id));
/* Make sure user selected PO distributions to write-off */
select count(*)
into l_rows
from cst_reconciliation_summary
where operating_unit_id = p_ou_id
and write_off_select_flag = 'Y';
/* Insert necessary information into SLA events temp table */
insert into xla_events_int_gt
(
application_id,
ledger_id,
entity_code,
source_id_int_1,
event_class_code,
event_type_code,
event_date,
event_status_code,
--BUG#7226250
security_id_int_2,
transaction_date,
reference_date_1,
transaction_number
)
values
(
707,
p_sob_id,
'WO_ACCOUNTING_EVENTS',
c_wo_rec.l_wo_id,
'ACCRUAL_WRITE_OFF',
'ACCRUAL_WRITE_OFF',
p_wo_date,
XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
p_ou_id,
p_wo_date,
INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(p_wo_date,p_ou_id),
to_char(c_wo_rec.l_wo_id)
);
Insert the individual AP and/or PO transactions into
the write-off details table
*/
insert into cst_write_off_details
(
write_off_id,
transaction_date,
amount,
entered_amount,
quantity,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
transaction_type_code,
rcv_transaction_id,
invoice_distribution_id,
write_off_transaction_id,
inventory_organization_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
ae_header_id,
ae_line_num
)
select c_wo_rec.l_wo_id,
capr.transaction_date,
capr.amount,
capr.entered_amount,
capr.quantity,
capr.currency_code,
capr.currency_conversion_type,
capr.currency_conversion_rate,
capr.currency_conversion_date,
capr.transaction_type_code,
capr.rcv_transaction_id,
capr.invoice_distribution_id,
capr.write_off_id,
capr.inventory_organization_id,
capr.operating_unit_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate, --program_update_date,
capr.ae_header_id,
capr.ae_line_num
from cst_ap_po_reconciliation capr
where capr.po_distribution_id = c_wo_rec.po_distribution_id
and capr.accrual_account_id = c_wo_rec.accrual_account_id
and capr.operating_unit_id = c_wo_rec.operating_unit_id;
select sum(capr.entered_amount)
into l_ent_sum
from cst_ap_po_reconciliation capr
where capr.po_distribution_id = c_wo_rec.po_distribution_id
and capr.accrual_account_id = c_wo_rec.accrual_account_id
and capr.operating_unit_id = c_wo_rec.operating_unit_id;
/* the offset account is selected as follows.If the destination type code is Expense, get the charge account
else get the variance account from the po distribution */
select decode(pod.destination_type_code,'EXPENSE',pod.code_combination_id,
pod.variance_account_id
),
decode(poll.match_option, 'P', pod.variance_account_id,
decode(pod.destination_type_code,'EXPENSE', pod.code_combination_id,-1)),
poh.currency_code,
poh.rate_type,
--
--BUG#9191539: The exchange rate date for PO:PO_RATE_DATE - For Receipt:Write Off Date
--
DECODE(poll.match_option, 'P',NVL(pod.rate_date,TRUNC(pod.creation_date))
,NVL(p_wo_date,TRUNC(SYSDATE)))
into l_off_id,
l_erv_id,
l_wo_cc,
l_wo_ct,
l_wo_cd
from po_distributions_all pod,
po_line_locations_all poll,
po_headers_all poh
where pod.po_distribution_id = c_wo_rec.po_distribution_id
and pod.org_id = p_ou_id
and poh.po_header_id = pod.po_header_id
and poll.line_location_id = pod.line_location_id;
select decode(poll.match_option, 'P',NVL(pod.rate,1),
gl_currency_api.get_rate(poh.currency_code, gsb.currency_code,
trunc(p_wo_date),poh.rate_type)
)
into l_wo_cr
from po_distributions_all pod,
po_line_locations_all poll,
po_headers_all poh,
gl_sets_of_books gsb
where pod.po_distribution_id = c_wo_rec.po_distribution_id
and pod.org_id = p_ou_id
and poh.po_header_id = pod.po_header_id
and poll.line_location_id = pod.line_location_id
and gsb.set_of_books_id = pod.set_of_books_id ;
Select NVL(pod.rate,1),
NVL(pod.rate_date,TRUNC(pod.creation_date))
into l_wo_cr,
l_wo_cd
from po_distributions_all pod
where pod.po_distribution_id = c_wo_rec.po_distribution_id
and pod.org_id = p_ou_id ;
select rate_var_gain_ccid
into l_erv_id
from financials_system_params_all
where org_id = p_ou_id;
select rate_var_loss_ccid
into l_erv_id
from financials_system_params_all
where org_id = p_ou_id;
Insert the PO distribution information, as well as the extra values
recently calcuated into the write-off headers table.
*/
insert into cst_write_offs
(
write_off_id,
transaction_date,
accrual_account_id,
offset_account_id,
erv_account_id,
write_off_amount,
entered_amount,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
transaction_type_code,
po_distribution_id,
reason_id,
comments,
destination_type_code,
inventory_item_id,
vendor_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
)
values
(
c_wo_rec.l_wo_id,
p_wo_date,
c_wo_rec.accrual_account_id,
l_off_id,
l_erv_id,
(-1) * c_wo_rec.l_tot_bal,
(-1) * l_ent_sum,
l_wo_cc,
l_wo_ct,
l_wo_cr,
l_wo_cd,
'WRITE OFF',
c_wo_rec.po_distribution_id,
p_rea_id,
p_comments,
c_wo_rec.destination_type_code,
c_wo_rec.inventory_item_id,
c_wo_rec.vendor_id,
p_ou_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate --program_update_date
);
First delete the individual transactions from cst_ap_po_reconciliation
as to maintain referential integretiy.
*/
delete from cst_ap_po_reconciliation capr
where exists (
select 'X'
from cst_reconciliation_summary crs
where capr.operating_unit_id = crs.operating_unit_id
and capr.po_distribution_id = crs.po_distribution_id
and capr.accrual_account_id = crs.accrual_account_id
and crs.write_off_select_flag = 'Y');
Once all the individual transaction have been deleted, removed the
header information from cst_reconciliation_summary
*/
delete from cst_reconciliation_summary
where operating_unit_id = p_ou_id
and write_off_select_flag = 'Y';
inserted into SLA's event temp table
*/
xla_events_pub_pkg.create_bulk_events(p_source_application_id => 201,
p_application_id => 707,
p_ledger_id => p_sob_id,
p_entity_type_code => 'WO_ACCOUNTING_EVENTS');
x_err_msg := 'CST_Accrual_Rec_PVT.insert_appo_data_all() ' || SQLERRM;
end insert_appo_data_all;
select count(*)
into l_enabled
from cst_write_offs
where reversal_id = p_wo_id
and operating_unit_id = p_ou_id;
select count(*)
into l_enabled
from cst_write_off_details
where write_off_transaction_id = p_wo_id
and operating_unit_id = p_ou_id;
select po_accrual_write_offs_s.nextval l_wo_id,
write_off_id l_rev_id,
accrual_account_id,
offset_account_id,
erv_account_id,
write_off_amount amount,
entered_amount,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
po_distribution_id,
invoice_distribution_id,
inventory_transaction_id,
destination_type_code,
inventory_item_id,
vendor_id,
legal_entity_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
from cst_write_offs
where operating_unit_id = l_ou_id
and write_off_select_flag = 'Y';
/* Check that the user has selected distributions to reverse */
select count(*)
into l_rows
from cst_write_offs
where operating_unit_id = p_ou_id
and write_off_select_flag = 'Y';
/* Insert the necessary information into SLA's event temp table */
insert into xla_events_int_gt
(
application_id,
ledger_id,
legal_entity_id,
entity_code,
source_id_int_1,
event_class_code,
event_type_code,
event_date,
event_status_code,
--BUG#7226250
security_id_int_2,
transaction_date,
reference_date_1,
transaction_number
)
values
(
707,
p_sob_id,
c_wo_rec.legal_entity_id,
'WO_ACCOUNTING_EVENTS',
c_wo_rec.l_wo_id,
'ACCRUAL_WRITE_OFF',
'ACCRUAL_WRITE_OFF',
p_wo_date,
XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
p_ou_id,
p_wo_date,
INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(p_wo_date,p_ou_id),
to_char(c_wo_rec.l_wo_id)
);
/* Insert the reversal into the headers table */
insert into cst_write_offs
(
write_off_id,
transaction_date,
accrual_account_id,
offset_account_id,
erv_account_id,
write_off_amount,
entered_amount,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
transaction_type_code,
po_distribution_id,
invoice_distribution_id,
inventory_transaction_id,
reversal_id,
reason_id,
comments,
destination_type_code,
inventory_item_id,
vendor_id,
legal_entity_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
)
values
(
c_wo_rec.l_wo_id,
p_wo_date,
c_wo_rec.accrual_account_id,
c_wo_rec.offset_account_id,
c_wo_rec.erv_account_id,
(-1) * c_wo_rec.amount,
(-1) * c_wo_rec.entered_amount,
c_wo_rec.currency_code,
c_wo_rec.currency_conversion_type,
c_wo_rec.currency_conversion_rate,
c_wo_rec.currency_conversion_date,
'REVERSE WRITE OFF',
c_wo_rec.po_distribution_id,
c_wo_rec.invoice_distribution_id,
c_wo_rec.inventory_transaction_id,
c_wo_rec.l_rev_id,
p_rea_id,
p_comments,
c_wo_rec.destination_type_code,
c_wo_rec.inventory_item_id,
c_wo_rec.vendor_id,
c_wo_rec.legal_entity_id,
c_wo_rec.operating_unit_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate --program_update_date
);
Insert the details from the previous write-off but with the new write-off ID
into the write-off details table
*/
insert into cst_write_off_details
(
write_off_id,
transaction_date,
amount,
entered_amount,
quantity,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
transaction_type_code,
rcv_transaction_id,
invoice_distribution_id,
inventory_transaction_id,
write_off_transaction_id,
inventory_organization_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
ae_header_id,
ae_line_num
)
select c_wo_rec.l_wo_id,
cwod.transaction_date,
cwod.amount,
cwod.entered_amount,
cwod.quantity,
cwod.currency_code,
cwod.currency_conversion_type,
cwod.currency_conversion_rate,
cwod.currency_conversion_date,
cwod.transaction_type_code,
cwod.rcv_transaction_id,
cwod.invoice_distribution_id,
cwod.inventory_transaction_id,
cwod.write_off_transaction_id,
cwod.inventory_organization_id,
cwod.operating_unit_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate, --program_update_date,
cwod.ae_header_id,
cwod.ae_line_num
from cst_write_off_details cwod
where cwod.write_off_id = c_wo_rec.l_rev_id
and cwod.operating_unit_id = c_wo_rec.operating_unit_id;
/* Need to re-insert transations, either Miscellaneous or AP-PO*/
/* Doing Miscellaneous */
if((c_wo_rec.po_distribution_id is null) or
(c_wo_rec.inventory_transaction_id is not null and c_wo_rec.po_distribution_id is not null) or
(c_wo_rec.invoice_distribution_id is not null)) then
--{
l_stmt_num := 35;
insert into cst_misc_reconciliation
(
transaction_date,
amount,
entered_amount,
quantity,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
invoice_distribution_id,
inventory_transaction_id,
po_distribution_id,
accrual_account_id,
transaction_type_code,
inventory_item_id,
vendor_id,
inventory_organization_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
ae_header_id,
ae_line_num
)
select cwod.transaction_date,
cwod.amount,
cwod.entered_amount,
cwod.quantity,
cwod.currency_code,
cwod.currency_conversion_type,
cwod.currency_conversion_rate,
cwod.currency_conversion_date,
cwod.invoice_distribution_id,
cwod.inventory_transaction_id,
cwo.po_distribution_id,
cwo.accrual_account_id,
cwod.transaction_type_code,
cwo.inventory_item_id,
cwo.vendor_id,
cwod.inventory_organization_id,
cwod.operating_unit_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate, --program_update_date,
cwod.ae_header_id,
cwod.ae_line_num
from cst_write_off_details cwod,
cst_write_offs cwo
where cwo.write_off_id = c_wo_rec.l_wo_id
and cwo.operating_unit_id = c_wo_rec.operating_unit_id
and cwod.write_off_id = cwo.write_off_id
and cwod.operating_unit_id = cwo.operating_unit_id;
select count(*)
into l_po_proc
from cst_reconciliation_summary
where po_distribution_id = c_wo_rec.po_distribution_id
and accrual_account_id = c_wo_rec.accrual_account_id
and operating_unit_id = c_wo_rec.operating_unit_id;
/* No records in CRS so insert relevant records from CWOD and CWO */
if(l_po_proc = 0) then
--{
l_stmt_num := 50;
insert into cst_ap_po_reconciliation
(
transaction_date,
amount,
entered_amount,
quantity,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
po_distribution_id,
rcv_transaction_id,
invoice_distribution_id,
accrual_account_id,
transaction_type_code,
write_off_id,
inventory_organization_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date,
ae_header_id,
ae_line_num
)
select cwod.transaction_date,
cwod.amount,
cwod.entered_amount,
cwod.quantity,
cwod.currency_code,
cwod.currency_conversion_type,
cwod.currency_conversion_rate,
cwod.currency_conversion_date,
cwo.po_distribution_id,
cwod.rcv_transaction_id,
cwod.invoice_distribution_id,
cwo.accrual_account_id,
cwod.transaction_type_code,
cwod.write_off_transaction_id,
cwod.inventory_organization_id,
cwod.operating_unit_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate, --program_update_date,
cwod.ae_header_id,
cwod.ae_line_num
from cst_write_offs cwo,
cst_write_off_details cwod
where cwo.write_off_id = c_wo_rec.l_wo_id
and cwo.po_distribution_id = c_wo_rec.po_distribution_id
and cwo.accrual_account_id = c_wo_rec.accrual_account_id
and cwo.operating_unit_id = c_wo_rec.operating_unit_id
and cwod.write_off_id = cwo.write_off_id
and cwod.operating_unit_id = cwo.operating_unit_id;
/* Next insert the new write-off header and reversal header into CAPR */
insert into cst_ap_po_reconciliation
(
transaction_date,
amount,
entered_amount,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
po_distribution_id,
accrual_account_id,
transaction_type_code,
write_off_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
)
select cwo.transaction_date,
cwo.write_off_amount,
cwo.entered_amount,
cwo.currency_code,
cwo.currency_conversion_type,
cwo.currency_conversion_rate,
cwo.currency_conversion_date,
cwo.po_distribution_id,
cwo.accrual_account_id,
cwo.transaction_type_code,
cwo.write_off_id,
cwo.operating_unit_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate --program_update_date
from cst_write_offs cwo
where cwo.write_off_id in (c_wo_rec.l_wo_id, c_wo_rec.l_rev_id)
and cwo.po_distribution_id = c_wo_rec.po_distribution_id
and cwo.accrual_account_id = c_wo_rec.accrual_account_id
and cwo.operating_unit_id = c_wo_rec.operating_unit_id;
/* Insert the data into the summary table */
insert into cst_reconciliation_summary
(
po_distribution_id,
accrual_account_id,
po_balance,
ap_balance,
write_off_balance,
last_receipt_date,
last_invoice_dist_date,
last_write_off_date,
inventory_item_id,
vendor_id,
destination_type_code,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
)
select cwo.po_distribution_id,
cwo.accrual_account_id,
sum(decode(capr.write_off_id,NULL,
decode(capr.invoice_distribution_id,NULL,
capr.amount,0),0)),
sum(decode(capr.invoice_distribution_id,NULL,0,capr.amount)),
sum(decode(capr.write_off_id,NULL,0,capr.amount)),
max(decode(capr.write_off_id,NULL,
decode(capr.invoice_distribution_id,NULL,
capr.transaction_date,NULL),NULL)),
max(decode(capr.invoice_distribution_id,NULL,NULL,capr.transaction_date)),
max(decode(capr.write_off_id,NULL,NULL,capr.transaction_date)),
cwo.inventorY_item_id,
cwo.vendor_id,
cwo.destination_type_code,
cwo.operating_unit_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate --program_update_date
from cst_ap_po_reconciliation capr,
cst_write_offs cwo
where cwo.write_off_id = c_wo_rec.l_wo_id
and cwo.operating_unit_id = c_wo_rec.operating_unit_id
and capr.po_distribution_id = cwo.po_distribution_id
and capr.accrual_account_id = cwo.accrual_account_id
and capr.operating_unit_id = cwo.operating_unit_id
group by cwo.po_distribution_id,
cwo.accrual_account_id,
cwo.inventory_item_id,
cwo.vendor_id,
cwo.destination_type_code,
cwo.operating_unit_id,
cwo.last_update_date,
cwo.last_updated_by,
cwo.last_update_login,
cwo.creation_date,
cwo.created_by,
cwo.request_id,
cwo.program_application_id,
cwo.program_id,
cwo.program_update_date;
select (po_balance + ap_balance + write_off_balance)
into l_po_proc
from cst_reconciliation_summary
where po_distribution_id = c_wo_rec.po_distribution_id
and accrual_account_id = c_wo_rec.accrual_account_id
and operating_unit_id = c_wo_rec.operating_unit_id;
delete from cst_ap_po_reconciliation
where po_distribution_id = c_wo_rec.po_distribution_id
and accrual_account_id = c_wo_rec.accrual_account_id
and operating_unit_id = c_wo_rec.operating_unit_id;
delete from cst_reconciliation_summary
where po_distribution_id = c_wo_rec.po_distribution_id
and accrual_account_id = c_wo_rec.accrual_account_id
and operating_unit_id = c_wo_rec.operating_unit_id;
/* If it doesn't, update wo_balance in CRS and insert reversal only in CAPR */
else
--{
l_stmt_num := 85;
update cst_reconciliation_summary crs
set crs.write_off_balance = crs.write_off_balance + (-1 * c_wo_rec.amount)
where po_distribution_id = c_wo_rec.po_distribution_id
and accrual_account_id = c_wo_rec.accrual_account_id
and operating_unit_id = c_wo_rec.operating_unit_id;
insert into cst_ap_po_reconciliation
(
transaction_date,
amount,
entered_amount,
currency_code,
currency_conversion_type,
currency_conversion_rate,
currency_conversion_date,
po_distribution_id,
accrual_account_id,
transaction_type_code,
write_off_id,
operating_unit_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
)
select cwo.transaction_date,
cwo.write_off_amount,
cwo.entered_amount,
cwo.currency_code,
cwo.currency_conversion_type,
cwo.currency_conversion_rate,
cwo.currency_conversion_date,
cwo.po_distribution_id,
cwo.accrual_account_id,
cwo.transaction_type_code,
cwo.write_off_id,
cwo.operating_unit_id,
sysdate, --last_update_date,
FND_GLOBAL.USER_ID, --last_updated_by,
FND_GLOBAL.USER_ID, --last_update_login,
sysdate, --creation_date,
FND_GLOBAL.USER_ID, --created_by,
FND_GLOBAL.CONC_REQUEST_ID, --request_id,
FND_GLOBAL.PROG_APPL_ID, --program_application_id,
FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
sysdate --program_update_date
from cst_write_offs cwo
where cwo.write_off_id = c_wo_rec.l_wo_id
and cwo.po_distribution_id = c_wo_rec.po_distribution_id
and cwo.accrual_account_id = c_wo_rec.accrual_account_id
and cwo.operating_unit_id = c_wo_rec.operating_unit_id;
inserted into SLA's event temp table
*/
xla_events_pub_pkg.create_bulk_events(p_source_application_id => 201,
p_application_id => 707,
p_ledger_id => p_sob_id,
p_entity_type_code => 'WO_ACCOUNTING_EVENTS');
/* need to reset the write_off_select_flag back CWO back to NULL */
Update cst_write_offs
set write_off_select_flag = NULL
where operating_unit_id = p_ou_id
and write_off_select_flag = 'Y';