[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select a.vendor_site_id vendor_site_id ,
a.ap_inv_exp_type expenditure_type,
a.ap_inv_exp_organization_id expenditure_organization_id,
b.vendor_id vendor_id
from pa_cc_org_relationships a,
po_vendor_sites_all b
where a.prvdr_org_id= p_prvdr_org_id
and a.recvr_org_id= p_recvr_org_id
and a.vendor_site_id =b.vendor_site_id;
select sum(extended_amount) amount
from ra_customer_trx_lines_all
where customer_trx_id = p_customer_trx_id;
select ppc.receiver_task_id task_id,
pt.project_id project_id
from pa_project_customers ppc,
pa_tasks pt
where pt.task_id=ppc.receiver_task_id
and ppc.customer_id=p_project_customer_id
and ppc.project_id=p_project_id;
select count(*) lines_counter from pa_draft_invoice_items
where project_id=p_project_id
and draft_invoice_num=p_draft_invoice_number
and invoice_line_type <> 'NET ZERO ADJUSTMENT'/* added as fix for Bug 1580854 */
and amount <> 0; /* Added for Bug 9711235 */
SELECT pdii.line_num line_number,
pdii.inv_amount amount,
nvl(pdii.translated_text, pdii.text) description,
pdii.output_tax_classification_code tax_code,
--aptax.name tax_code,
--pdii.output_vat_tax_id tax_id,
pdii.cc_project_id project_id,
pdii.cc_tax_task_id task_id,
pdii.inv_amount pa_quantity,
arinv.line_number pa_cc_ar_invoice_line_num,
arinv.customer_trx_line_id cust_trx_line_id -- added for bug 5045406
FROM pa_draft_invoice_items pdii,
ra_customer_trx_lines_all arinv
-- ap_tax_codes_all aptax,
-- ar_vat_tax_all artax
where arinv.interface_line_attribute6= pdii.line_num
and arinv.customer_trx_id = p_customer_trx_id
and pdii.project_id=p_project_id
and pdii.draft_invoice_num=p_draft_invoice_number
and pdii.output_tax_classification_code IS NOT NULL
and pdii.invoice_line_type <> 'NET ZERO ADJUSTMENT' /* added as fix for Bug 2397907 */
and pdii.amount <> 0 /* Added for Bug 9711235 */
-- and pdii.output_vat_tax_id = artax.vat_tax_id
-- and artax.tax_code= aptax.name
-- and pdii.output_vat_tax_id is not null
-- and aptax.org_id= p_recvr_org_id
UNION
SELECT pdii.line_num line_number,
pdii.inv_amount amount,
nvl(pdii.translated_text, pdii.text) description,
null tax_code,
-- pdii.output_vat_tax_id tax_id,
pdii.cc_project_id project_id,
pdii.cc_tax_task_id task_id,
pdii.inv_amount pa_quantity,
arinv.line_number pa_cc_ar_invoice_line_num,
arinv.customer_trx_line_id cust_trx_line_id -- added for bug 5045406
FROM pa_draft_invoice_items pdii,
ra_customer_trx_lines_all arinv
where arinv.interface_line_attribute6= pdii.line_num
and pdii.project_id=p_project_id
and pdii.draft_invoice_num=p_draft_invoice_number
and arinv.customer_trx_id =p_customer_trx_id
and pdii.invoice_line_type <> 'NET ZERO ADJUSTMENT' /* added as fix for Bug 2397907 */
and pdii.amount <> 0 /* Added for Bug 9711235 */
and pdii.output_Tax_classificatioN_code IS NULL;
select ap_invoices_interface_s.nextval into v_invoice_id from sys.dual;
select decode(draft_invoice_num_credited, NULL, 'INVOICE', 'CREDIT_MEMO')
into v_invoice_type
from pa_draft_invoices_all
where project_id = p_project_id(I)
and draft_invoice_num = p_draft_invoice_number(I);
select 'x'
into dummy_x
from dual
where EXISTS
( select 'x' from
pa_expend_typ_sys_links
where system_linkage_function = 'VI'
and expenditure_type = l_expenditure_type);
select 'x'
into dummy_x
from dual
where EXISTS
( select 'x' from
pa_all_organizations
where org_id = p_recvr_org_id(I)
and organization_id = l_expenditure_organization_id
and PA_ORG_USE_TYPE = 'EXPENDITURES');
pa_debug.G_err_stage := 'Insert into AP_invoices_interface table';
pa_debug.G_err_stage := 'Insert into AP_invoice_lines_interface table';
Insert into ap_invoices_interface (
invoice_id,
invoice_num,
invoice_date,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
description,
source,
group_id,
workflow_flag,
calc_tax_during_import_flag, -- added for bug 5045406
org_id,
created_by ,
last_update_login ,
last_updated_by,
creation_date ,
last_update_date,
invoice_received_date) /* Added for bug 3658825*/
values (p_invoice_id,
p_invoice_number,
p_invoice_date,
p_vendor_id,
p_vendor_site_id,
p_invoice_amount,
p_invoice_currency_code,
p_description,
decode(p_internal_billing_type,'PA_IC_INVOICES','PA_IC_INVOICES','PA_IP_INVOICES'),
p_group_id,
p_workflow_flag,
'Y', -- added for bug 5045406
p_org_id,
G_created_by,
G_last_update_login,
G_last_updated_by ,
G_creation_date ,
G_last_update_date,
sysdate); /* Added for bug 3658825*/
SELECT APPLICATION_ID, ENTITY_CODE,
EVENT_CLASS_CODE, TRX_ID, TRX_LEVEL_TYPE
into l_application_id, l_entity_code, l_event_class_code, l_trx_id, l_trx_level_type
FROM ZX_LINES_DET_FACTORS
WHERE trx_id = p_customer_trx_id
AND application_id = 222
AND entity_Code = 'TRANSACTIONS'
AND event_class_code = p_invoice_type
AND rownum = 1;
INSERT INTO ap_invoice_lines_interface(
invoice_id,
line_number,
line_type_lookup_code,
amount,
description,
amount_includes_tax_flag,
prorate_across_flag,
tax_classification_code,/*Changed for bug 4882123 */
final_match_flag,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
project_id,
task_id,
expenditure_type,
expenditure_item_date,
expenditure_organization_id,
project_accounting_context,
pa_addition_flag,
pa_quantity,
org_id,
pa_cc_ar_invoice_id,
pa_cc_ar_invoice_line_num,
TAX_CODE_OVERRIDE_FLAG,
SOURCE_APPLICATION_ID,
SOURCE_ENTITY_CODE,
SOURCE_EVENT_CLASS_CODE,
SOURCE_TRX_ID,
SOURCE_TRX_LEVEL_TYPE,
SOURCE_LINE_ID -- added for bug 5045406
)
VALUES( p_invoice_id,
p_invoice_line_number(i),
'ITEM',
p_inv_amount(i),
p_description(i),
'N',
'N',
p_tax_code(i),
'N',
G_last_updated_by,
G_last_update_date,
G_last_update_login,
G_created_by,
G_creation_date,
decode(p_internal_billing_type, 'PA_IC_INVOICES', p_project_id(i), p_receiver_project_id),
decode(p_internal_billing_type,'PA_IC_INVOICES',p_task_id(i), p_receiver_task_id),
p_expenditure_type(i),
(select least(NVL(completion_date,p_invoice_date),p_invoice_date) from pa_tasks pt where pt.task_id =decode(p_internal_billing_type,'PA_IC_INVOICES',p_task_id(i), p_receiver_task_id)), /* Modified this for bug 7234925*/
p_expenditure_organization_id(i),
'Yes',
decode(p_internal_billing_type,'PA_IC_INVOICES','T','N'),/*Bug# 2042840*/
p_pa_quantity(i),
p_recvr_org_id,
p_customer_trx_id,
p_pa_cc_ar_inv_line_num(i),
'Y',
l_application_id, -- added for etax changes
l_entity_code, -- added for etax changes
'INTERCOMPANY_TRX', -- l_event_class_code, -- added for etax changes
l_trx_id, -- added for etax changes
'LINE' , -- l_trx_level_type -- added for etax changes
p_cust_trx_line_id(i) -- added for bug 5045406
);