[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_program_update_date DATE := sysdate;
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER:= fnd_global.user_id;
l_last_update_login NUMBER:= fnd_global.login_id;
SELECT agreement_currency_code,
nvl(start_date, to_date('01/01/1952','DD/MM/YYYY')),
nvl(expiration_date, sysdate)
INTO l_funding_currency_code,
l_agreement_start_date,
l_agreement_exp_date
FROM pa_agreements_all
WHERE agreement_id =p_agreement_id;
tmp_denominator_tab.delete;
tmp_numerator_tab.delete;
tmp_denominator_tab.delete;
tmp_numerator_tab.delete;
tmp_denominator_tab.delete;
tmp_numerator_tab.delete;
tmp_denominator_tab.delete;
tmp_numerator_tab.delete;
tmp_denominator_tab.delete;
tmp_numerator_tab.delete;
-- Update the events table
-- Log Messages for Converted Amounts
IF g1_debug_mode = 'Y' THEN
PA_MCB_INVOICE_PKG.log_message('Event_Convert_amount_bulk: ' || 'Project Id ' || p_project_id);
UPDATE pa_events
SET bill_amount = decode(SIGN(bill_trans_bill_amount),SIGN(tmp_invproc_bill_amount(i)),
tmp_invproc_bill_amount(i),(-1) * tmp_invproc_bill_amount(i)),
invproc_currency_code =decode(invproc_currency_code,NULL,
tmp_invproc_currency_code(i),invproc_currency_code),
/*bug-2483358*/
project_bill_amount = decode(SIGN(bill_trans_bill_amount),SIGN(tmp_project_bill_amount(i)),
tmp_project_bill_amount(i),(-1) * tmp_project_bill_amount(i)),
project_inv_exchange_rate =tmp_project_exchange_rate(i),
-- project_inv_rate_date =tmp_project_rate_date(i), --Modified for Bug3087929
project_inv_rate_date =decode(p_project_rate_type(i), 'User', null, tmp_project_rate_date(i)),
projfunc_bill_amount = decode(SIGN(bill_trans_bill_amount),SIGN(tmp_projfunc_bill_amount(i)),
tmp_projfunc_bill_amount(i),(-1) * tmp_projfunc_bill_amount(i)),
projfunc_inv_exchange_rate =tmp_projfunc_exchange_rate(i),
-- projfunc_inv_rate_date =tmp_projfunc_rate_date(i), --Modified for Bug3087929
projfunc_inv_rate_date =decode(p_projfunc_rate_type(i), 'User', null, tmp_projfunc_rate_date(i)),
inv_gen_rejection_code = tmp_status_tab(i),
request_id = p_request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date,
last_update_date = l_last_update_date,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE project_id = p_project_id
AND NVL(task_id,0) = NVL(p_task_id(i),0)
AND event_num = p_event_num(i);
PA_MCB_INVOICE_PKG.log_message('No of Rows Updated ' || sql%rowcount);
/* SELECT FUNDING_CURRENCY_CODE
INTO tmp_funding_currency_code
FROM pa_summary_project_fundings
WHERE agreement_id = p_agreement_id
AND NVL(task_id,0) = NVL(p_task_id,0)
AND project_id = p_project_id
AND rownum=1
GROUP BY funding_currency_code
HAVING sum(total_baselined_amount) <>0;*/
select funding_currency_code
into tmp_funding_currency_code
from (
select funding_currency_code
from pa_summary_project_fundings
where project_id = p_project_id
and agreement_id = p_agreement_id
and nvl(task_id, 0) = nvl(p_task_id, 0)
group by funding_currency_code
having sum(total_baselined_amount) <> 0)
where rownum=1;
/*SELECT FUNDING_CURRENCY_CODE
INTO tmp_funding_currency_code
FROM pa_summary_project_fundings
WHERE agreement_id = p_agreement_id
AND project_id = p_project_id
AND rownum=1
GROUP BY funding_currency_code
HAVING sum(total_baselined_amount) <>0;*/
select funding_currency_code
into tmp_funding_currency_code
from(
select funding_currency_code
from pa_summary_project_fundings
where project_id = p_project_id
and agreement_id = p_agreement_id
group by funding_currency_code
having sum(total_baselined_amount) <> 0
)
where rownum = 1;
SELECT evt.bill_trans_currency_code,
/* decode(etyp.event_type_classification,
'INVOICE REDUCTION' ,-evt.bill_trans_bill_amount,
evt.bill_trans_bill_amount), Commented for bug 3108623 */
evt.bill_trans_bill_amount, /*Added for 3108623 */
evt.project_bill_amount,
evt.projfunc_bill_amount,
evt.bill_amount,
evt.funding_rate_type,
evt.funding_rate_date,
evt.funding_exchange_rate ,
evt.project_currency_code,
evt.projfunc_currency_code,
evt.invproc_currency_code,
evt.project_inv_exchange_rate,
evt.project_inv_rate_date,
evt.project_rate_type,
evt.projfunc_inv_exchange_rate,
evt.projfunc_inv_rate_date,
evt.projfunc_rate_type,
pr.invproc_currency_type
INTO tmp_bill_trans_currency_code,
tmp_bill_trans_event_amount,
tmp_project_event_amount,
tmp_projfunc_event_amount,
tmp_invproc_event_amount,
tmp_funding_rate_type,
tmp_funding_rate_date,
tmp_funding_exchange_rate,
tmp_project_currency_code,
tmp_projfunc_currency_code,
tmp_invproc_currency_code,
tmp_project_inv_exch_rate,
tmp_project_inv_rate_date,
tmp_project_rate_type,
tmp_projfunc_inv_exch_rate,
tmp_projfunc_inv_rate_date,
tmp_projfunc_rate_type,
tmp_invproc_currency_type
FROM pa_events evt, pa_projects_all pr,
pa_event_types etyp
WHERE evt.project_id = p_project_id
AND NVL(evt.task_id,0) = NVL(p_task_id,0)
AND evt.event_num = p_event_num
AND evt.project_id = pr.project_id
AND evt.event_type = etyp.event_type;
G_LAST_UPDATE_LOGIN := fnd_global.login_id;
G_LAST_UPDATED_BY := fnd_global.user_id;
SELECT lu.meaning
FROM pa_lookups lu
WHERE lu.lookup_type = 'INVOICE DISTRIBUTION WARNING'
AND lu.lookup_code = reject_code;
UPDATE pa_draft_invoices_all
SET projfunc_invtrans_rate_type = NULL
,projfunc_invtrans_rate_date = NULL
,projfunc_invtrans_ex_rate = NULL
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
UPDATE pa_draft_invoices_all
SET projfunc_invtrans_rate_type = 'User'
,projfunc_invtrans_rate_date = l_invoice_date
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
SELECT NVL(sum(dii.bill_trans_bill_amount),0)
INTO tmp_bill_trans_amount_tab(1)
FROM pa_draft_invoice_items dii
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = p_draft_invoice_num
AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
AND dii.invoice_line_type <> 'RETENTION';
UPDATE pa_draft_invoices_all
SET generation_error_flag = 'Y',
TRANSFER_REJECTION_REASON = l_reject_reason_meaning
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
UPDATE pa_draft_invoices_all
SET projfunc_invtrans_rate_type = p_pfc_rate_type
,projfunc_invtrans_rate_date = DECODE(p_pfc_ex_rate_date_code,'PA_INVOICE_DATE',
tmp_projfunc_rate_date_tab(1),p_pfc_rate_date)
,projfunc_invtrans_ex_rate = DECODE(p_pfc_ex_rate_date_code,'PA_INVOICE_DATE',
tmp_rate_tab(1), p_pfc_ex_rate )
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
PA_MCB_INVOICE_PKG.log_message('cal_conversion_Attr: ' || ' update invtrans_rate details as User ');
SELECT sum(NVL(dii.projfunc_bill_amount,0))
INTO l_sum_projfunc_bill_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
AND dii.invoice_line_type <> 'RETENTION'
AND dii.draft_invoice_num = p_draft_invoice_num;
SELECT sum(NVL(dii.bill_trans_bill_amount,0))
INTO l_sum_inv_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
AND dii.invoice_line_type <> 'RETENTION'
AND dii.draft_invoice_num = p_draft_invoice_num;
SELECT sum(NVL(dii.bill_trans_bill_amount,0))/sum(NVL(dii.projfunc_bill_amount,0))
INTO l_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = p_draft_invoice_num
AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
AND dii.invoice_line_type <> 'RETENTION'
having sum(nvl(dii.projfunc_bill_amount,0)) <> 0;
SELECT NVL(dii.bill_trans_bill_amount,0)/NVL(dii.projfunc_bill_amount,0)
INTO l_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = p_draft_invoice_num
AND nvl(dii.projfunc_bill_amount,0) <> 0
AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
AND dii.invoice_line_type <> 'RETENTION'
AND rownum=1;
SELECT NVL(sum(dii.bill_trans_bill_amount),0) / nvl(sum(dii.projfunc_bill_amount),0)
INTO l_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = p_draft_invoice_num
AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
AND dii.invoice_line_type <> 'RETENTION';
UPDATE pa_draft_invoices_all
SET projfunc_invtrans_rate_type = 'User'
,projfunc_invtrans_rate_date = NVL(l_invoice_date,p_bill_thru_date)
,projfunc_invtrans_ex_rate = l_rate
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
PA_MCB_INVOICE_PKG.log_message('cal_conversion_Attr: ' || 'Before the Select statement ');
SELECT NVL(sum(dii.bill_trans_bill_amount),0)
INTO tmp_bill_trans_amount_tab(1)
FROM pa_draft_invoice_items dii
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = p_draft_invoice_num
AND dii.invoice_line_type in ('STANDARD','INVOICE REDUCTION')
AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
AND dii.invoice_line_type <> 'RETENTION';
UPDATE pa_draft_invoices_all
SET generation_error_flag = 'Y',
TRANSFER_REJECTION_REASON = l_reject_reason_meaning
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
UPDATE pa_draft_invoices_all
SET projfunc_invtrans_rate_type = p_inv_rate_type
,projfunc_invtrans_rate_date = NVL(p_inv_rate_date,NVL(l_invoice_date,p_bill_thru_date))
,projfunc_invtrans_ex_rate = NVL(p_inv_ex_rate,tmp_rate_tab(1))
WHERE project_id = p_project_id
AND draft_invoice_num = p_draft_invoice_num;
SELECT di.agreement_id,
di.draft_invoice_num,
di.retention_percentage,
dii.line_num,
dii.bill_trans_currency_code,
dii.invoice_line_type,
NVL(dii.event_num,0) event_num,
NVL(evt.revenue_amount,0) revenue_amount
FROM pa_draft_invoices_all di,
pa_draft_invoice_items dii,
pa_events evt
WHERE di.project_id = p_project_id
AND di.project_id = dii.project_id
AND dii.request_id = p_request_id
AND di.request_id = p_request_id
AND di.draft_invoice_num = dii.draft_invoice_num
AND NVL(di.canceled_flag,'N') <> 'Y'
AND NVL(di.cancel_credit_memo_flag,'N') <>'Y'
AND dii.draft_inv_line_num_credited IS NULL
AND dii.event_num = evt.event_num(+)
AND dii.project_id = evt.project_id(+)
AND NVL(dii.event_task_id,-99) = NVL(evt.task_id(+),-99)
AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
AND dii.invoice_line_type <> 'RETENTION'
ORDER BY di.draft_invoice_num,dii.bill_trans_currency_code,dii.line_num;
SELECT di.draft_invoice_num,
di.draft_invoice_num_credited,
dii.line_num
FROM pa_draft_invoices_all di, pa_draft_invoice_items dii
WHERE di.project_id = p_project_id
AND di.request_id = p_request_id
AND dii.project_id= di.project_id
AND dii.draft_invoice_num = di.draft_invoice_num
AND di.draft_invoice_num_credited IS NOT NULL
AND nvl(di.write_off_flag,'N') <> 'Y'
AND nvl(di.cancel_credit_memo_flag,'N') <> 'Y'
ORDER BY di.drafT_invoice_num;
SELECT projfunc_attr_for_ar_flag,
projfunc_currency_code,
projfunc_bil_exchange_rate,
projfunc_bil_rate_date_code,
projfunc_bil_rate_type,
projfunc_bil_rate_date,
project_currency_code,
invproc_currency_type
FROM pa_projects_all
WHERE project_id = P_Project_Id;
TmpInvLines.delete;
TmpInvHeaders.delete;
SELECT funding_currency_code
INTO l_funding_currency_code
FROM pa_summary_project_fundings
WHERE project_id = p_project_id
AND rownum=1
AND NVL(total_baselined_amount,0) > 0;
select nvl(da.INV_EXCHANGE_RATE, 1), da.INV_CURRENCY_CODE
into l_head_inv_exch_rate, l_head_inv_curr_code
from pa_draft_invoices_all da
where project_id = p_project_id
and draft_invoice_num = inv_num_cached;
/* Bug 4735682: Following update clause altered to include the exchange rate *
/* Commented and rewritten for bug 4735682
UPDATE pa_draft_invoice_items dii
SET dii.inv_amount =
(SELECT sum(nvl(rdl.bill_trans_bill_amount, 0)) FROM pa_cust_rev_dist_lines_all rdl
WHERE rdl.project_id = dii.project_id
AND rdl.draft_invoice_num = dii.draft_invoice_num
AND rdl.draft_invoice_item_line_num = dii.line_num)
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = cm_rec.draft_invoice_num
AND dii.line_num = cm_rec.line_num
AND dii.invoice_line_type = 'STANDARD';
/* Select query for bug 4735682 brought out to comply with 8i.. bug 4995695 */
SELECT sum(nvl(rdl.bill_trans_bill_amount, 0)) * l_head_inv_exch_rate
INTO l_calc_inv_amount
FROM pa_cust_rev_dist_lines_all rdl
WHERE rdl.project_id = p_project_id
AND rdl.draft_invoice_num = inv_num_cached
AND rdl.draft_invoice_item_line_num = cm_rec.line_num;
UPDATE pa_draft_invoice_items dii
SET dii.inv_amount =
PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(l_calc_inv_amount, l_head_inv_curr_code)
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = cm_rec.draft_invoice_num
AND dii.line_num = cm_rec.line_num
AND dii.invoice_line_type = 'STANDARD';
UPDATE pa_draft_invoice_items dii
SET inv_amount = bill_trans_bill_amount
WHERE dii.project_id = p_project_id
AND dii.draft_invoice_num = cm_rec.draft_invoice_num
AND dii.line_num = cm_rec.line_num
AND dii.invoice_line_type = 'RETENTION';
SELECT NVL(MAX(draft_invoice_num),0) INTO Last_invoice_num
FROM pa_draft_invoices_all
WHERE project_id = p_project_id;
UPDATE pa_draft_invoice_items
SET line_num = TmpInvLines(k).current_line_num+1000000
WHERE project_id = p_project_id
AND request_id = p_request_id
AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
AND line_num = TmpInvLines(k).current_line_num;
--Update the RDLS
UPDATE pa_cust_rev_dist_lines
SET draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000
WHERE project_id = p_project_id
AND request_id = p_request_id
AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num;
-- Update only if the event is revenue event
UPDATE pa_cust_event_rdl_all
SET draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000
WHERE project_id = p_project_id
AND request_id = p_request_id
AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num;
-- Update the draft invoice items
-- IF (TmpInvLines(k).current_draft_invoice_num <>
-- TmpInvLInes(k).new_draft_invoice_num ) OR
-- ( TmpInvLines(k).current_line_num <>
-- TmpInvLines(k).new_line_num) THEN
--PA_MCB_INVOICE_PKG.log_message('Update Invoice Lines ');
UPDATE pa_draft_invoice_items
SET draft_invoice_num = TmpInvLines(k).new_draft_invoice_num,
line_num = TmpInvLines(k).new_line_num,
inv_amount= bill_trans_bill_amount
WHERE project_id = p_project_id
AND request_id = p_request_id
AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
AND line_num = TmpInvLines(k).current_line_num+1000000;
UPDATE pa_cust_rev_dist_lines
SET draft_invoice_num = TmpInvLines(k).new_draft_invoice_num,
draft_invoice_item_line_num = TmpInvLines(k).new_line_num
WHERE project_id = p_project_id
AND request_id = p_request_id
AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000;
-- Update only if the event is revenue event
UPDATE pa_cust_event_rdl_all
SET draft_invoice_num = TmpInvLines(k).new_draft_invoice_num,
draft_invoice_item_line_num = TmpInvLines(k).new_line_num
WHERE project_id = p_project_id
AND request_id = p_request_id
AND draft_invoice_num = TmpInvLines(k).current_draft_invoice_num
AND draft_invoice_item_line_num = TmpInvLines(k).current_line_num+1000000;
update pa_draft_invoice_items
set inv_amount = 0
where project_id = P_Project_Id
and invoice_line_type = 'NET ZERO ADJUSTMENT'
and request_id = p_request_id;
-- Insert the New Invoice Headers
-- All the values will be the same exception draft_invoice_num
-- , inv_currency_code
l_projfunc_invtrans_rate :=0;
SELECT NVL(sum(dii.inv_amount),0),
NVL(sum(dii.projfunc_bill_amount),0),
NVL(sum(dii.project_bill_amount),0),
NVL(sum(dii.bill_trans_bill_amount),0),
NVL(sum(dii.funding_bill_amount),0),
NVL(MAX(dii.line_num),0) +1
INTO l_inv_amount,
l_pfc_amount,
l_pc_amount,
l_btc_amount,
l_fc_amount,
l_ret_line_num
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
AND dii.invoice_line_type <> 'NET ZERO ADJUSTMENT'
AND dii.invoice_line_type <> 'RETENTION';
SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
AND nvl(dii.projfunc_bill_amount,0) <> 0
AND rownum=1;
SELECT sum(NVL(dii.projfunc_bill_amount,0))
INTO l_sum_projfunc_bill_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num;
SELECT sum(NVL(dii.inv_amount,0))
INTO l_sum_inv_amount
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num;
SELECT sum(NVL(dii.inv_amount,0))/sum(NVL(dii.projfunc_bill_amount,0))
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
having sum(nvl(dii.projfunc_bill_amount,0)) <> 0;
SELECT NVL(dii.inv_amount,0)/NVL(dii.projfunc_bill_amount,0)
INTO l_projfunc_invtrans_rate
FROM pa_draft_invoice_items dii
WHERE dii.project_id = P_Project_Id
AND dii.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
AND nvl(dii.projfunc_bill_amount,0) <> 0
AND rownum=1;
-- Insert if the action flag is I
IF TmpInvHeaders(k).action_flag ='I' THEN
IF g1_debug_mode = 'Y' THEN
PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Insert New Invoices ');
INSERT INTO pa_draft_invoices_all
(project_id,
draft_invoice_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
transfer_status_code,
generation_error_flag,
agreement_id,
pa_date,
request_id,
program_application_id,
program_id,
program_update_date,
customer_bill_split,
bill_through_date,
invoice_comment,
approved_date,
approved_by_person_id,
released_date,
released_by_person_id,
invoice_date,
ra_invoice_number,
transferred_date,
transfer_rejection_reason,
unearned_revenue_cr,
unbilled_receivable_dr,
gl_date,
system_reference,
draft_invoice_num_credited ,
canceled_flag,
cancel_credit_memo_flag ,
write_off_flag,
converted_flag,
extracted_date,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
retention_percentage,
invoice_set_id,
org_id,
inv_currency_code,
inv_rate_type,
inv_rate_date,
inv_exchange_rate,
bill_to_address_id,
ship_to_address_id ,
prc_generated_flag,
receivable_code_combination_id,
rounding_code_combination_id,
unbilled_code_combination_id,
unearned_code_combination_id,
woff_code_combination_id,
acctd_curr_code,
acctd_rate_type,
acctd_rate_date,
acctd_exchg_rate,
language,
cc_invoice_group_code ,
cc_project_id,
ib_ap_transfer_status_code,
ib_ap_transfer_error_code ,
invproc_currency_code,
projfunc_invtrans_rate_type,
projfunc_invtrans_rate_date ,
projfunc_invtrans_ex_rate,
customer_id,
bill_to_customer_id,
ship_to_customer_id,
bill_to_contact_id,
ship_to_contact_id)
SELECT project_id,
TmpInvHeaders(k).new_draft_invoice_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
transfer_status_code,
generation_error_flag,
agreement_id,
pa_date,
request_id,
program_application_id,
program_id,
program_update_date,
customer_bill_split,
bill_through_date,
invoice_comment,
approved_date,
approved_by_person_id,
released_date,
released_by_person_id,
invoice_date,
ra_invoice_number,
transferred_date,
transfer_rejection_reason,
unearned_revenue_cr,
unbilled_receivable_dr,
gl_date,
system_reference,
draft_invoice_num_credited ,
canceled_flag,
cancel_credit_memo_flag ,
write_off_flag,
converted_flag,
extracted_date,
last_update_login,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
retention_percentage,
invoice_set_id,
org_id,
TmpInvHeaders(k).inv_currency_code,
NULL, --'User'
NULL, --sysdate
NULL, --1
bill_to_address_id,
ship_to_address_id ,
prc_generated_flag,
receivable_code_combination_id,
rounding_code_combination_id,
unbilled_code_combination_id,
unearned_code_combination_id,
woff_code_combination_id,
acctd_curr_code,
acctd_rate_type,
acctd_rate_date,
acctd_exchg_rate,
language,
cc_invoice_group_code ,
cc_project_id,
ib_ap_transfer_status_code,
ib_ap_transfer_error_code ,
invproc_currency_code,
'User',
Sysdate ,
l_projfunc_invtrans_rate,
customer_id,
bill_to_customer_id,
ship_to_customer_id,
bill_to_contact_id,
ship_to_contact_id
FROM pa_draft_invoices_all
WHERE project_id = p_project_id
AND draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num;
PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Insert Ret Line for New Invoice ');
-- Insert new retention line
INSERT INTO pa_draft_invoice_items(
project_id,
draft_invoice_num,
line_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
amount,
text,
invoice_line_type,
request_id,
program_application_id,
program_id,
program_update_date,
unearned_revenue_cr,
unbilled_receivable_dr,
task_id,
event_task_id,
event_num,
ship_to_address_id,
taxable_flag,
draft_inv_line_num_credited,
last_update_login,
inv_amount,
output_tax_classification_code,
output_tax_exempt_flag,
output_tax_exempt_reason_code,
output_tax_exempt_number,
acct_amount,
rounding_amount,
unbilled_rounding_amount_dr,
unearned_rounding_amount_cr,
translated_text,
cc_rev_code_combination_id,
cc_project_id,
cc_tax_task_id,
project_currency_code,
project_bill_amount,
projfunc_currency_code,
projfunc_bill_amount,
funding_currency_code,
funding_bill_amount,
invproc_currency_code,
bill_trans_currency_code,
bill_trans_bill_amount)
SELECT
project_id,
TmpInvHeaders(k).new_draft_invoice_num,
l_ret_line_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
NVL(l_btc_amount,0) *
( NVL(TmpInvHeaders(k).retention_percentage,0)/100),
text,
invoice_line_type,
request_id,
program_application_id,
program_id,
program_update_date,
unearned_revenue_cr,
unbilled_receivable_dr,
task_id,
event_task_id,
event_num,
ship_to_address_id,
taxable_flag,
draft_inv_line_num_credited,
last_update_login,
NVL(l_btc_amount,0) *
( NVL(TmpInvHeaders(k).retention_percentage,0)/100),
output_tax_classification_code,
output_tax_exempt_flag,
output_tax_exempt_reason_code,
output_tax_exempt_number,
acct_amount,
rounding_amount,
unbilled_rounding_amount_dr,
unearned_rounding_amount_cr,
translated_text,
cc_rev_code_combination_id,
cc_project_id,
cc_tax_task_id,
project_currency_code,
NVL(l_pc_amount,0) *
(NVL(TmpInvHeaders(k).retention_percentage,0)/100),
projfunc_currency_code,
NVL(l_pfc_amount,0) *
(NVL(TmpInvHeaders(k).retention_percentage,0)/100),
funding_currency_code,
NVL(l_fc_amount,0) *
(NVL(TmpInvHeaders(k).retention_percentage,0)/100),
invproc_currency_code,
TmpInvHeaders(k).inv_currency_code,
NVL(l_btc_amount,0) *
(NVL(TmpInvHeaders(k).retention_percentage,0)/100)
FROM pa_draft_invoice_items
WHERE project_id = p_project_id
AND draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num
AND invoice_Line_type ='RETENTION';
---- Existing Invoice, update BTC currency code and rates
IF g1_debug_mode = 'Y' THEN
PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Update Existing Invoice ');
UPDATE pa_draft_invoices_all
set inv_currency_code = TmpInvHeaders(k).inv_currency_code,
inv_rate_type = NULL, --'User',
inv_rate_date = NULL, --sysdate,
inv_exchange_rate = NULL, --1,
projfunc_invtrans_rate_type = 'User',
/* projfunc_invtrans_rate_date = sysdate, commented for bug 5141073 */
projfunc_invtrans_rate_date = invoice_date, /* Added for bug 5141073 */
projfunc_invtrans_ex_rate = NVL(l_projfunc_invtrans_rate,0)
WHERE project_id = P_Project_Id
AND draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num;
PA_MCB_INVOICE_PKG.log_message('Inv_by_Bill_Trans_Currency: ' || ' Update Existing Invoice Retention Line ');
UPDATE pa_draft_invoice_items
SET bill_trans_currency_code = TmpInvHeaders(k).inv_currency_code,
projfunc_bill_amount =
NVL(l_pfc_amount,0) *
(NVL(TmpInvHeaders(k).retention_percentage,0)/100),
project_bill_amount =
NVL(l_pc_amount,0) *
(NVL(TmpInvHeaders(k).retention_percentage,0)/100),
funding_bill_amount =
NVL(l_fc_amount,0) *
(NVL(TmpInvHeaders(k).retention_percentage,0)/100),
bill_trans_bill_amount =
NVL(l_btc_amount,0) *
(NVL(TmpInvHeaders(k).retention_percentage,0)/100),
amount =
NVL(l_btc_amount,0) *
NVL(TmpInvHeaders(k).retention_percentage,0),
inv_amount =
NVL(l_btc_amount,0) *
(NVL(TmpInvHeaders(k).retention_percentage,0)/100)
WHERE draft_invoice_num = TmpInvHeaders(k).current_draft_invoice_num
AND project_id = p_project_id
AND invoice_line_type = 'RETENTION';
SELECT agr.customer_id,
i.bill_through_date
INTO l_customer_id,
l_bill_thru_date
FROM pa_draft_invoices_all i,
pa_agreements_all agr
WHERE i.project_id = p_project_id
AND i.request_id = p_request_id
AND i.draft_invoice_num = TmpInvHeaders(k).new_draft_invoice_num
AND NVL(i.generation_error_flag,'N')= 'N'
AND i.agreement_id = agr.agreement_id;
SELECT NVL(ppc.inv_rate_date,NVL(l_invoice_date,l_bill_thru_date)),
ppc.inv_rate_type,
ppc.inv_exchange_rate
INTO l_inv_rate_date,
l_inv_rate_type,
l_inv_rate
FROM pa_project_customers ppc
WHERE ppc.project_id = P_Project_Id
AND ppc.customer_id = l_customer_id;