AP Invoice API

their is a custom API for create AP invoice Using Purchase order number



CREATE OR REPLACE procedure APPS.ap_invoice_pk (p_po_number in number, p_org_id in number)
as
 v_invoice_num varchar2(100);
 v_amount  number;
 v_vendor_id number;
 v_vendor_site_id number;
 v_org_id  number;
 v_line_num number;
 v_quantity  number;
 l_standard_request_id number;

 -- cursor for enter data in ap_invoice_lines_interface table
 cursor inv_line is

 select  rownum line_num,quantity*unit_price quantity  from PO_LINES_ALL pla,PO_HEADERS_ALL pha where segment1=p_po_number
 and pla.PO_HEADER_ID=pha.po_header_id;



begin

--- this query for create new invoice number as in sequence 'ERS-12345-0001' you can chose your sequence type

select 'ERS-'||(select distinct receipt_num from RCV_shipment_headerS rsh,RCV_shipment_lines rsl where rsh.shipment_header_id=rsl.shipment_header_id
and rsl.po_header_id=(select po_header_id from po_headers_all where segment1=p_po_number))||'-'||(select to_number(substr(invoice_num,instr(invoice_num,'-',-2)+1,length(invoice_num))+1) invoice_number
from AP_INVOICES_INTERFACE where invoice_num like 'ERS-%' and invoice_id=(select max(invoice_id) from AP_INVOICES_INTERFACE where invoice_num like 'ERS-%'))
into v_invoice_num from dual;
 
-- query for select data on the basis of PO number for insert in ap_invoices_interface table

select vendor_id, vendor_site_id into v_vendor_id, v_vendor_site_id  from PO_HEADERS_ALL where segment1=p_po_number;

select sum(quantity*unit_price) into v_amount from PO_LINES_ALL pla,PO_HEADERS_ALL pha where segment1=p_po_number   and pla.PO_HEADER_ID=pha.po_header_id;

dbms_output.put_line(v_invoice_num||'  '||v_vendor_id||'  '|| v_vendor_site_id||'  '||v_amount||'  '|| v_quantity);

insert into AP_INVOICES_INTERFACE (
            invoice_id,
            INVOICE_TYPE_LOOKUP_CODE,
            invoice_num,
            vendor_id,
            vendor_site_id,
            invoice_amount,
            INVOICE_CURRENCY_CODE,
            invoice_date,
            DESCRIPTION,
            PAY_GROUP_LOOKUP_CODE,
            source,
            org_id
                )
values (
           ap_invoices_interface_s.NEXTVAL,
           'STANDARD',
            v_invoice_num,
            v_vendor_id,
            v_vendor_site_id,
            v_amount,
            'USD',
            sysdate,
            'AP Invoice',
            null,
            'Manual Invoice Entry',
            p_org_id
);

commit;

dbms_output.put_line('insert in table AP_INVOICES_INTERFACE completed');

for i in inv_line loop
insert into AP_INVOICE_LINES_INTERFACE (
            invoice_id,
            invoice_line_id,
            line_number,
            line_type_lookup_code,
            amount,
            org_id
            )
values     (
            ap_invoices_interface_s.CURRVAL,
            ap_invoice_lines_interface_s.NEXTVAL,
            i.line_num,
            'ITEM',
            i.quantity,
            p_org_id
);

commit;

dbms_output.put_line('insert in table AP_INVOICE_LINES_INTERFACE completed');

 end loop;

mo_global.set_policy_context ('S', p_org_id);
l_standard_request_id :=fnd_request.submit_request (application                  => 'SQLAP'
                                  ,program                      => 'APXIIMPT'
                                  ,description                  => NULL
                                  ,start_time                   => NULL
                                  ,sub_request                  => FALSE
                                  ,argument1                    => p_org_id
                                  ,argument2                    => 'Manual Invoice Entry'
                                  ,argument3                    => NULL                                                        
                                  ,argument4                    => NULL                                                        
                                  ,argument5                    => NULL                
                                  ,argument6                    => NULL                                                          
                                  ,argument7                    => NULL
                                  ,argument8                    => 'Y'
                                 );
                               
 commit;  

                 

end;
/

you will check AP in ap_invoice_all table 

thank you.....