At this article i am giving an example of creating invoice with AP_INVOICES_INTERFACE. You only need to be careful about your purchase order’s match option must be ‘R’ (match option at po_line_locations_all at R12).
This script inserts into ap invoice interfaces so that you can execute your Open Interfaces Import to create an invoice. You can also call this concurrent from this code sample.
procedure insert_into_interface (errbuf out varchar2,
retcode out varchar2,
p_allowance_id in number,
p_invoice_num in varchar2,
p_group_id in varchar2)
is
cursor c_allowance_lines(cp_allowance_id number)
is
select xeaa.vendor_id,
xeaa.vendor_site_id,
plla.line_location_id,
plla.match_option,
pha.segment1,
pha.po_header_id,
pla.po_line_id,
pda.po_distribution_id,
plla.org_id,
rsl.item_id,
plla.shipment_num,
apss.vendor_site_code,
apss.payment_method_lookup_code,
aps.accts_pay_code_combination_id,
rsh.receipt_num,
rsl.line_num,
xeal.*
from xxxt_allowances_all xeaa,
xxxt_allowance_lines_all xeal,
ap_suppliers aps,
ap_supplier_sites_all apss,
rcv_transactions rt,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
po_line_locations_all plla,
po_lines_all pla,
po_headers_all pha,
po_distributions_all pda
where xeal.allowance_id = xeaa.allowance_id
and pla.po_line_id = plla.po_line_id
and pha.po_header_id = pla.po_header_id
and pda.line_location_id = rt.po_line_location_id
and aps.vendor_id = xeaa.vendor_id
and apss.vendor_id = xeaa.vendor_id
and apss.vendor_site_id = xeaa.vendor_site_id
and xeaa.allowance_id = cp_allowance_id
and rt.transaction_id = xeal.rcv_transaction_id
and rsl.shipment_line_id = rt.shipment_line_id
and rsh.shipment_header_id = rsl.shipment_header_id
and plla.line_location_id = rt.po_line_location_id;
l_line_number number;
l_line_id number;
l_total_invoice_amount number;
l_invoice_id number;
l_invoice_count number;
l_allowance_status xxxt_allowances_all.status%type;
l_invoice_num ap_invoices_all.invoice_num%type;
begin
select count(1)
into l_invoice_count
from ap_invoices_all
where invoice_num = p_invoice_num;
begin
select status
into l_allowance_status
from xxxt_allowances_all
where allowance_id = p_allowance_id;
if(l_allowance_status <> ‘ONAYLANDI’) then
fnd_file.put_line (fnd_file.OUTPUT, ‘ ALLOWANCE_ID: ‘ || p_allowance_id || ‘ hakedişinin statüsü ONAYLANDI değildir.’);
–return;
end if;
exception
when no_data_found then
fnd_file.put_line (fnd_file.OUTPUT, ‘ ALLOWANCE_ID: ‘ || p_allowance_id || ‘ böyle bir hakediş bulunmamaktadır.’);
return;
end;
if(l_invoice_count > 0) then
fnd_file.put_line (fnd_file.OUTPUT, ‘ INVOICE_NUM: ‘ || p_invoice_num || ‘ fatura numarası kullanımdadır. Lütfen kullanılmayan bir fatura numarası giriniz.’);
return;
end if;
select ap_invoices_s.nextval
into l_invoice_id
from dual;
fnd_file.put_line (fnd_file.OUTPUT, ‘INVOICE_ID: ‘ || l_invoice_id ||’ INVOICE_NUM: ‘ || p_invoice_num || ‘ ALLOWANCE_ID:’ || p_allowance_id || ‘ Faturası girilecek.’);
–inserting into ap_invoices_interface
insert into ap_invoices_interface
(
invoice_id,
invoice_num,
invoice_type_lookup_code,
invoice_date,
exchange_date,
exchange_rate_type,
vendor_id,
vendor_site_id,
payment_method_lookup_code,
invoice_amount,
invoice_currency_code,
terms_name,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
source,
group_id,
accts_pay_code_combination_id,
org_id,
attribute15
)
–values
(
select l_invoice_id,
p_invoice_num,
‘STANDARD’,
sysdate,
null,–exchange_date
null,
xeaa.vendor_id,
xeaa.vendor_site_id,
nvl(apss.payment_method_lookup_code,’EFT’),
(select nvl(sum(round(price_override * quantity_override,2)),0)
from xxxt_allowance_lines_all
where allowance_id = xeaa.allowance_id),
‘TRY’,
’30 GÜN’,
sysdate,
fnd_profile.value(‘USER_ID’),
fnd_profile.value(‘LOGIN_ID’),
sysdate,
fnd_profile.value(‘USER_ID’),
‘HAKEDIS’,
p_group_id,
aps.accts_pay_code_combination_id,
fnd_profile.value(‘ORG_ID’),
apss.vendor_site_code
from xxxt_allowances_all xeaa,
ap_suppliers aps,
ap_supplier_sites_all apss
where aps.vendor_id = xeaa.vendor_id
and apss.vendor_id = xeaa.vendor_id
and apss.vendor_site_id = xeaa.vendor_site_id
and xeaa.allowance_id = p_allowance_id);
–looping at allowance lines
for r_allowance_lines in c_allowance_lines(p_allowance_id)
loop
begin
select ap_invoice_lines_interface_s.nextval
into l_line_id
from dual;
select nvl (max (line_number),0) + 1
into l_line_number
from ap_invoice_lines_interface
where invoice_id = l_invoice_id;
fnd_file.put_line (fnd_file.OUTPUT, ”);
fnd_file.put_line (fnd_file.OUTPUT, ‘——————————————————————————————————————————————’);
fnd_file.put_line (fnd_file.OUTPUT, ”);
fnd_file.put_line (fnd_file.OUTPUT, ‘INVOICE_LINE_ID: ‘ || l_line_id ||’ LINE_NUMBER: ‘ || l_line_number || ‘ AMOUNT: ‘ || r_allowance_lines.price_override);
fnd_file.put_line (fnd_file.OUTPUT, ‘RECEIPT_NUM: ‘ || r_allowance_lines.receipt_num ||’ RCV_TRANSACTION_ID: ‘ || r_allowance_lines.rcv_transaction_id || ‘ QUANTITY: ‘ || r_allowance_lines.quantity_override || ‘ ORG_ID: ‘ || r_allowance_lines.org_id);
–inserting into ap_invoice_lines_interface
insert into ap_invoice_lines_interface
(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
match_option,
amount,
inventory_item_id,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
rcv_transaction_id,
quantity_invoiced,
po_distribution_id,
org_id
)
values
(
l_invoice_id,–p_invoice_id,
l_line_id,
l_line_number,
‘ITEM’,
‘R’,–match option
round(r_allowance_lines.price_override * r_allowance_lines.quantity_override,2),–amount,
r_allowance_lines.item_id,
sysdate,
fnd_profile.value(‘USER_ID’),
fnd_profile.value(‘LOGIN_ID’),
sysdate,
fnd_profile.value(‘USER_ID’),
r_allowance_lines.rcv_transaction_id,
r_allowance_lines.quantity_override,
r_allowance_lines.po_distribution_id,
r_allowance_lines.org_id
);
commit;
exception
when no_data_found
then
xxxt_error_package.raise_error(sqlerrm);
end;
end loop;
end insert_into_interface;
