[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pa_draft_invoices_all.*
from pa_draft_invoices_all
where request_id = pn_request_id ;
select pa_draft_invoices_all.*
from pa_draft_invoices_all
where project_id = pn_project_id and
draft_invoice_num = pn_draft_invoice_num ;
select pdii.*
from pa_draft_invoice_items pdii
where pdii.project_id = r_new.project_id and
pdii.draft_invoice_num = r_new.draft_invoice_num ;
jai_pa_tax_pkg.insert_line_info(
r_new ,
ln_tax_category_id ,
pv_action ,
ln_draft_invoice_id ,
ln_draft_invoice_line_id ,
pv_process_message ,
pv_process_flag
);
select ppa.distribution_rule
from pa_projects_all ppa
where ppa.project_id = pn_project_id and
distribution_rule is not null ;
select jpsp.distribution_rule , jpsc.context ,jpsp.preference
from jai_pa_setup_contexts jpsc , jai_pa_setup_preferences jpsp
where jpsc.context_id = jpsp.context_id and
jpsp.distribution_rule = cv_distribution_rule
order by jpsp.preference asc ;
select pe.event_type
from pa_events pe
where pe.project_id = pn_project_id and
( pe.task_id = ln_event_task_id or pe.task_id is null ) and
pe.event_num = ln_event_num ;
select event_type_id
from pa_event_types
where event_type = ln_event_type ;
select setup_value1 tax_category
from jai_pa_setup_values
where context = jai_constants.setup_event_type and -- this will search only for events not for other context types
attribute1 = ln_event_type_id ;
select setup_value1 tax_category
from jai_pa_setup_values
where context = jai_constants.setup_project and
attribute1 = pn_project_id ;
select setup_value1
from jai_pa_setup_values
where attribute1 = cn_customer_id and
attribute2 = cn_address_id and
context = jai_constants.setup_customer_site ;
select setup_value1
from jai_pa_setup_values
where attribute1 = cn_customer_id and
context = jai_constants.setup_customer_site and
attribute2 is null ;
select count(distinct expenditure_type)
from pa_expenditure_items_all peia
where peia.expenditure_item_id in
( select expenditure_item_id
from pa_cust_rev_dist_lines_all pdida
where pdida.project_id = pn_project_id and
pdida.draft_invoice_num = pn_draft_invoice_num and
pdida.draft_invoice_item_line_num = pn_line_num
);
select distinct expenditure_type
from pa_expenditure_items_all peia
where peia.expenditure_item_id in
( select expenditure_item_id
from pa_cust_rev_dist_lines_all pdida
where pdida.project_id = pn_project_id and
pdida.draft_invoice_num = pn_draft_invoice_num and
pdida.draft_invoice_item_line_num = pn_line_num
);
select jpsv.setup_value1
from jai_pa_setup_values jpsv,
pa_expenditure_types pet
where jpsv.context = jai_constants.setup_expenditure_type and
jpsv.attribute1 = pet.expenditure_type_id and
pet.expenditure_type =cv_expenditure_type ;
/*-------------------------------BEGIN LOCAL METHOD INSERT_LINE_INFO -----------------------------*/
procedure insert_line_info (
r_new in pa_draft_invoice_items%rowtype,
pn_tax_category_id in JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
pv_action in varchar2,
pn_draft_invoice_id out nocopy jai_pa_draft_invoice_lines.draft_invoice_id%type ,
pn_draft_invoice_line_id out nocopy jai_pa_draft_invoice_lines.draft_invoice_line_id%type ,
pv_process_message out nocopy varchar2,
pv_process_flag out nocopy varchar2
) is
ln_draft_invoice_id jai_pa_draft_invoice_lines.draft_invoice_id%type ;
select draft_invoice_id
from jai_pa_draft_invoices
where project_id = r_new.project_id and
draft_invoice_num = r_new.draft_invoice_num ;
select jai_pa_draft_invoices_s.nextval
from dual ;
select jai_pa_draft_invoice_lines_s.nextval
from dual ;
select organization_id , location_id , draft_invoice_id
from jai_pa_draft_invoices
where project_id = r_new.project_id and
draft_invoice_num = pkg_global_type.ln_draft_invoice_num_credited;
select service_type_code ,draft_invoice_id , draft_invoice_line_id ,line_amt , tax_category_id
from jai_pa_draft_invoice_lines
where project_id =r_new.project_id and
draft_invoice_num =pkg_global_type.ln_draft_invoice_num_credited and
line_num =r_new.draft_inv_line_num_credited ;
insert into jai_pa_draft_invoices (
draft_invoice_id ,
project_id ,
draft_invoice_num ,
organization_id ,
location_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
parent_draft_invoice_id
)
values (
ln_draft_invoice_id ,
r_new.project_id ,
r_new.draft_invoice_num ,
ln_organization_id ,
ln_location_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
ln_parent_draft_invoice_id
) ;
insert into jai_pa_draft_invoice_lines(
draft_invoice_line_id ,
draft_invoice_id ,
project_id ,
draft_invoice_num ,
line_num ,
line_amt ,
line_tax_amt ,
tax_category_id ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login ,
service_type_code ,
parent_draft_invoice_id ,
parent_draft_invoice_line_id
)
values (
ln_draft_invoice_line_id ,
ln_draft_invoice_id ,
r_new.project_id ,
r_new.draft_invoice_num ,
r_new.line_num ,
r_new.amount ,
null ,
ln_tax_category_id ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id ,
lv_service_type_code ,
pkg_global_type.ln_draft_invoice_id ,
pkg_global_type.ln_draft_invoice_line_id
);
pv_process_message := substr('insert_line_info='|| sqlerrm,1,1999);
end insert_line_info;
select tax_category_id
from jai_cmn_document_taxes
where source_doc_id = pn_draft_invoice_id and
source_doc_line_id = pn_draft_invoice_line_id and
source_doc_type = jai_constants.pa_draft_invoice ;
select pdia.inv_currency_code , pdia.inv_exchange_rate
from pa_draft_invoices_all pdia, jai_pa_draft_invoice_lines jpdil
where pdia.project_id = jpdil.project_id and
pdia.draft_invoice_num = jpdil.draft_invoice_num and
jpdil.draft_invoice_line_id = pn_draft_invoice_line_id and
draft_invoice_id = pn_draft_invoice_id ;
select sum(tax_amt)
from jai_cmn_document_taxes
where source_doc_id = pn_draft_invoice_id and
source_doc_line_id = pn_draft_invoice_line_id and
source_doc_type = jai_constants.pa_draft_invoice ;
insert into jai_cmn_document_taxes
(
doc_tax_id ,
tax_line_no ,
tax_id ,
tax_type ,
currency_code ,
tax_rate ,
qty_rate ,
uom ,
tax_amt ,
func_tax_amt ,
modvat_flag ,
tax_category_id ,
source_doc_type ,
source_doc_id ,
source_doc_line_id ,
source_table_name ,
tax_modified_by ,
adhoc_flag ,
precedence_1 ,
precedence_2 ,
precedence_3 ,
precedence_4 ,
precedence_5 ,
precedence_6 ,
precedence_7 ,
precedence_8 ,
precedence_9 ,
precedence_10 ,
creation_date ,
created_by ,
last_update_date ,
last_updated_by ,
last_update_login
)
select jai_cmn_document_taxes_s.nextval ,
j1.tax_line_no ,
j1.tax_id ,
j1.tax_type ,
j1.currency_code ,
j1.tax_rate ,
j1.qty_rate ,
j1.uom ,
round(((pn_line_amount * ((j1.tax_amt *100)/pkg_global_type.ln_line_amt ) )/100),nvl(j2.rounding_factor,0)),
round(((pn_line_amount * ((j1.func_tax_amt *100)/pkg_global_type.ln_line_amt ) )/100),nvl(j2.rounding_factor,0)) ,
j1.modvat_flag , j1.tax_category_id ,
j1.source_doc_type , pn_draft_invoice_id ,
pn_draft_invoice_line_id ,
j1.source_table_name ,
j1.tax_modified_by ,
j1.adhoc_flag ,
j1.precedence_1 ,
j1.precedence_2 ,
j1.precedence_3 ,
j1.precedence_4 ,
j1.precedence_5 ,
j1.precedence_6 ,
j1.precedence_7 ,
j1.precedence_8 ,
j1.precedence_9 ,
j1.precedence_10 ,
sysdate ,
fnd_global.user_id ,
sysdate ,
fnd_global.user_id ,
fnd_global.login_id
from jai_cmn_document_taxes j1 , JAI_CMN_TAXES_ALL j2
where j1.source_doc_id = pkg_global_type.ln_draft_invoice_id and
j1.source_doc_line_id = pkg_global_type.ln_draft_invoice_line_id and
j1.source_doc_type = jai_constants.pa_draft_invoice and
j1.tax_id = j2.tax_id ;
update jai_pa_draft_invoice_lines
set line_tax_amt = ln_tax_amount
where draft_invoice_line_id = pn_draft_invoice_line_id and
draft_invoice_id = pn_draft_invoice_id ;
delete from jai_cmn_document_taxes
where source_doc_id = pn_draft_invoice_id and
source_doc_line_id = pn_draft_invoice_line_id and
source_doc_type = jai_constants.pa_draft_invoice ;
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
P_SOURCE_TRX_TYPE => JAI_CONSTANTS.PA_DRAFT_INVOICE,
P_SOURCE_TABLE_NAME => 'JAI_PA_DRAFT_INVOICE_LINES',
P_ACTION => JAI_CONSTANTS.DEFAULT_TAXES
) ;
update jai_pa_draft_invoice_lines
set line_tax_amt = pn_tax_amount
where draft_invoice_line_id = pn_draft_invoice_line_id and
draft_invoice_id = pn_draft_invoice_id ;
delete from jai_cmn_document_taxes
where ( source_doc_id , source_doc_line_id ) in
( select draft_invoice_id , draft_invoice_line_id
from jai_pa_draft_invoice_lines
where project_id = pn_project_id and
draft_invoice_num = pn_draft_invoice_num
) ;
delete from jai_pa_draft_invoice_lines
where project_id = pn_project_id and
draft_invoice_num = pn_draft_invoice_num ;
delete from jai_pa_draft_invoices
where project_id =pn_project_id and
draft_invoice_num = pn_draft_invoice_num ;
select
inv_currency_code,
inv_exchange_rate,
customer_id,
bill_to_customer_id,
ship_to_customer_id,
bill_to_address_id,
ship_to_address_id,
draft_invoice_num_credited,
write_off_flag
from pa_draft_invoices_all
where project_id = pn_project_id and
draft_invoice_num = pn_draft_invoice_num ;
select draft_invoice_id , draft_invoice_line_id
from jai_pa_draft_invoice_lines
where project_id = r_new.project_id and
draft_invoice_num = r_new.draft_invoice_num and
line_num = r_new.line_num ;
P_LAST_UPDATE_DATE => SYSDATE,
P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
P_SOURCE_TRX_TYPE => JAI_CONSTANTS.PA_DRAFT_INVOICE,
P_SOURCE_TABLE_NAME => 'JAI_PA_DRAFT_INVOICE_LINES',
P_ACTION => JAI_CONSTANTS.RECALCULATE_TAXES
) ;
update jai_pa_draft_invoice_lines
set line_tax_amt = ln_tax_amount ,
line_amt = r_new.inv_amount
where draft_invoice_line_id = r_get_line_detail.draft_invoice_line_id and
draft_invoice_id = r_get_line_detail.draft_invoice_id ;