[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_inserted_flag Varchar2(1);
select org_id from pa_projects_all
where project_id = c_project_id;
inserted_flag => l_inserted_flag,
exemption_type => l_exemption_type,
tax_exemption_id => l_exemption_id);
select hz_c.party_id, hz_cs.party_site_id, pc.bill_to_address_id
into l_bill_to_party_id, l_bill_to_party_site_id, l_bill_to_address_id
from hz_cust_accounts hz_c, hz_cust_acct_sites hz_cs, pa_project_customers pc
where pc.project_id = p_project_id
and pc.customer_id = p_customer_id
and hz_cs.cust_acct_site_id = pc.bill_to_address_id
and hz_c.cust_account_id = p_customer_id;
select hz_c.party_id, hz_cs.party_site_id, pc.ship_to_address_id
into l_ship_to_party_id, l_ship_to_party_site_id, l_ship_to_address_id
from hz_cust_accounts hz_c, hz_cust_acct_sites hz_cs, pa_project_customers pc
where pc.project_id = p_project_id
and pc.customer_id = p_customer_id
and hz_cs.cust_acct_site_id = pc.ship_to_address_id
and hz_c.cust_account_id = p_customer_id;
l_program_update_date DATE := sysdate;
select b.expenditure_item_id, b.line_num
from PA_CUST_REV_DIST_LINES_ALL a,
PA_CUST_REV_DIST_LINES_ALL b
where a.expenditure_item_id = p_exp_item_id
and a.line_num = p_line
and a.line_num_reversed is not null
and a.expenditure_item_id = b.expenditure_item_id
and a.line_num_reversed = b.line_num ;
select adjusted_expenditure_item_id
from pa_expenditure_items_all e1
where e1.expenditure_item_id = p_Exp_item_id; */
select b.expenditure_item_id, max(b.line_num ) line_num
from pa_expenditure_items_all e1,
pa_expenditure_items_all e2,
PA_CUST_REV_DIST_LINES_ALL b
where e1.expenditure_item_id = p_exp_item_id
and e1.adjusted_expenditure_item_id is not null
and e1.adjusted_expenditure_item_id = e2.expenditure_item_id
and e2.expenditure_item_id = b.expenditure_item_id
group by b.expenditure_item_id;
SELECT project_inv_rate_type,
project_inv_rate_date,
project_inv_exchange_rate,
-project_bill_amount,
projfunc_inv_rate_type,
projfunc_inv_rate_date,
projfunc_inv_exchange_rate,
-projfunc_bill_amount,
invproc_rate_type,
invproc_rate_date,
invproc_exchange_rate,
-bill_amount,
funding_inv_rate_type,
funding_inv_rate_date,
funding_inv_exchange_rate,
-funding_bill_amount,
project_currency_code,
projfunc_currency_code,
invproc_currency_code,
funding_currency_code,
output_tax_classification_code,
output_tax_exempt_flag,
output_tax_exempt_reason_code,
output_tax_exempt_number
FROM PA_CUST_REV_DIST_LINES_ALL
WHERE EXPENDITURE_ITEM_ID = p_exp_item_id
AND LINE_NUM = p_line;
SELECT agreement_currency_code,
nvl(start_date,to_date('01/01/1952', 'DD/MM/YYYY')),
nvl(expiration_date,TO_DATE(P_invoice_date,'YYYY/MM/DD'))
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;
PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || 'Update RDL and EI');
Update PA_CUST_REV_DIST_LINES_ALL
Set
-- invoice_eligible_flag = tmp_invoice_eligible_flag, for bug 2649243, 2645634
-- output_vat_tax_id = l_Output_vat_tax_id, --commented by hsiu
output_tax_classification_code = l_output_tax_code,
output_tax_exempt_flag = l_Output_tax_exempt_flag,
output_tax_exempt_reason_code = l_Output_exempt_reason_code,
output_tax_exempt_number = l_Output_tax_exempt_number,
project_inv_rate_type =
DECODE(tmp_status_tab(loop_index),'N',
tmp_project_rate_type(loop_index), NULL),
project_inv_rate_date =
DECODE(tmp_status_tab(loop_index),'N',
--tmp_project_rate_date(loop_index), NULL), --Modified for Bug 3137196
decode(tmp_project_rate_type(loop_index), 'User', null, tmp_project_rate_date(loop_index)), NULL),
project_inv_exchange_rate =
DECODE(tmp_status_tab(loop_index),'N',
tmp_project_exchange_rate(loop_index), NULL),
project_bill_amount =
DECODE(tmp_status_tab(loop_index),'N',
tmp_project_bill_amount(loop_index), NULL),
projfunc_inv_rate_type =
DECODE(tmp_status_tab(loop_index),'N',
tmp_projfunc_rate_type(loop_index), NULL),
projfunc_inv_rate_date =
DECODE(tmp_status_tab(loop_index),'N',
--tmp_projfunc_rate_date(loop_index), NULL), --Modified for Bug 3137196
decode(tmp_projfunc_rate_type(loop_index), 'User', null, tmp_projfunc_rate_date(loop_index)), NULL),
projfunc_inv_exchange_rate =
DECODE(tmp_status_tab(loop_index),'N',
tmp_projfunc_exchange_rate(loop_index), NULL),
projfunc_bill_amount =
DECODE(tmp_status_tab(loop_index),'N',
tmp_projfunc_bill_amount(loop_index), NULL),
invproc_rate_type =
DECODE(tmp_status_tab(loop_index),'N',
tmp_invproc_rate_type(loop_index), NULL),
invproc_rate_date =
DECODE(tmp_status_tab(loop_index),'N',
--tmp_invproc_rate_date(loop_index), NULL), --Modified for Bug 3137196
decode(tmp_invproc_rate_type(loop_index), 'User', null, tmp_invproc_rate_date(loop_index)), NULL),
invproc_exchange_rate =
DECODE(tmp_status_tab(loop_index),'N',
tmp_invproc_exchange_rate(loop_index), NULL),
bill_amount =
DECODE(tmp_status_tab(loop_index),'N',
tmp_invproc_bill_amount(loop_index), NULL),
funding_inv_rate_type =
DECODE(tmp_status_tab(loop_index),'N',
tmp_funding_rate_type(loop_index), NULL),
funding_inv_rate_date =
DECODE(tmp_status_tab(loop_index),'N',
--tmp_funding_rate_date(loop_index), NULL), --Modified for Bug 3137196
decode(tmp_funding_rate_type(loop_index), 'User', null, tmp_funding_rate_date(loop_index)), NULL),
funding_inv_exchange_rate =
DECODE(tmp_status_tab(loop_index),'N',
tmp_funding_exchange_rate(loop_index), NULL),
project_currency_code =
DECODE(tmp_status_tab(loop_index),'N',
tmp_project_currency_code(loop_index), NULL),
projfunc_currency_code =
DECODE(tmp_status_tab(loop_index),'N',
tmp_projfunc_currency_code(loop_index), NULL),
invproc_currency_code =
DECODE(tmp_status_tab(loop_index),'N',
tmp_invproc_currency_code(loop_index), NULL),
funding_currency_code =
DECODE(tmp_status_tab(loop_index),'N',
tmp_funding_currency_code(loop_index), NULL),
funding_bill_amount =
DECODE(tmp_status_tab(loop_index),'N',
tmp_funding_bill_amount(loop_index), NULL),
inv_gen_rejection_code = tmp_status_tab(loop_index),
request_id = P_Request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
Where expenditure_item_id = P_Expenditure_item_id(loop_index)
and line_num = P_Line_num(loop_index);
PA_MCB_INVOICE_PKG.log_message('GET_DEFAULT_TAX_INFO: ' || ' updateing ei ');
Update PA_EXPENDITURE_ITEMS_ALL
Set
invproc_rate_type =
DECODE(tmp_status_tab(loop_index),'N',
tmp_invproc_rate_type(loop_index), NULL),
invproc_rate_date =
DECODE(tmp_status_tab(loop_index),'N',
--tmp_invproc_rate_date(loop_index), NULL), --Modified for Bug 3137196
decode(tmp_invproc_rate_type(loop_index), 'User', null, tmp_invproc_rate_date(loop_index)), NULL),
invproc_exchange_rate =
DECODE(tmp_status_tab(loop_index),'N',
tmp_invproc_exchange_rate(loop_index), NULL),
bill_amount =
DECODE(tmp_status_tab(loop_index),'N',NVL(bill_amount,0) +
tmp_invproc_bill_amount(loop_index), NULL), --for bug#2251021,
invproc_currency_code =
DECODE(tmp_status_tab(loop_index),'N',
tmp_invproc_currency_code(loop_index), NULL),
projfunc_inv_rate_type =
DECODE(tmp_status_tab(loop_index),'N',
tmp_projfunc_rate_type(loop_index), NULL),
projfunc_inv_rate_date =
DECODE(tmp_status_tab(loop_index),'N',
--tmp_projfunc_rate_date(loop_index), NULL), --Modified for Bug 3137196
decode(tmp_projfunc_rate_type(loop_index), 'User', null, tmp_projfunc_rate_date(loop_index)), NULL),
projfunc_inv_exchange_rate =
DECODE(tmp_status_tab(loop_index),'N',
tmp_projfunc_exchange_rate(loop_index), NULL),
projfunc_bill_amount =
DECODE(tmp_status_tab(loop_index),'N',NVL(projfunc_bill_amount,0) +
tmp_projfunc_bill_amount(loop_index), NULL), --bug2251021
projfunc_currency_code =
DECODE(tmp_status_tab(loop_index),'N',
tmp_projfunc_currency_code(loop_index), NULL),
project_inv_rate_type =
DECODE(tmp_status_tab(loop_index),'N',
tmp_project_rate_type(loop_index), NULL),
project_inv_rate_date =
DECODE(tmp_status_tab(loop_index),'N',
--tmp_project_rate_date(loop_index), NULL), --Modified for Bug 3137196
decode(tmp_project_rate_type(loop_index), 'User', null, tmp_project_rate_date(loop_index)), NULL),
project_inv_exchange_rate =
DECODE(tmp_status_tab(loop_index),'N',
tmp_project_exchange_rate(loop_index), NULL),
project_bill_amount =
DECODE(tmp_status_tab(loop_index),'N',NVL(project_bill_amount,0) +
tmp_project_bill_amount(loop_index), NULL), --bug2251021
inv_gen_rejection_code = tmp_status_tab(loop_index),
request_id = P_Request_id,
program_id = l_program_id,
program_application_id = l_program_application_id,
program_update_date = l_program_update_date
Where expenditure_item_id = P_Expenditure_item_id(loop_index);
Select count(*)
Into l_count
From AR_SYSTEM_PARAMETERS;
Select sum(TRX_LINE.EXTENDED_AMOUNT)
Into l_tax_amout
From RA_Customer_Trx_Lines TRX_LINE
Where TRX_LINE.Customer_Trx_Id = P_Trx_Id
And TRX_LINE.Line_Type = 'TAX'
And TRX_LINE.LINK_TO_CUST_TRX_LINE_ID Is Not Null;
select rtrim(tax_classification_code)
into l_tax_code
from zx_id_tcc_mapping_all
where tax_rate_code_id = p_tax_id
and org_id = p_org_id
and tax_class = 'OUTPUT'; -- added for bug 5061887
SELECT business_group_id,
proj_org_structure_version_id,
invoice_batch_source_id
INTO l_business_grp_id,
l_org_struct_ver_id,
l_batch_source_id
FROM pa_implementations;
SELECT PROJ.Carrying_Out_Organization_ID
INTO l_carry_out_org_id
FROM pa_projects proj
WHERE project_id = p_project_id;
SELECT language_code
INTO l_basic_language_code
FROM fnd_languages
WHERE installed_flag = 'B';
/*Added exception handling block for the below select into query for bug 9322678*/
BEGIN
/* added for bug 9246335 */
SELECT invoice_date
INTO l_invoice_date
FROM pa_draft_invoices
WHERE project_id = p_project_id
and draft_invoice_num = p_draft_invoice_num;
SELECT fnd_lk.meaning into l_meaning
FROM zx_output_classifications_v fnd_lk
WHERE fnd_lk.lookup_code = x_output_tax_class_code
AND fnd_lk.org_id = x_org_id
AND x_inv_date BETWEEN fnd_lk.start_date_active AND NVL(fnd_lk.end_date_active,x_inv_date);
SELECT fnd_lk.meaning into l_meaning
FROM zx_output_classifications_v fnd_lk
WHERE fnd_lk.lookup_code = x_output_tax_class_code
AND fnd_lk.org_id = -99
AND x_inv_date BETWEEN fnd_lk.start_date_active AND NVL(fnd_lk.end_date_active,x_inv_date);