Friday, 5 January 2018

Oracle Payables AP Invoice Interface


In this post , I am sharing the complete PLSQL code to upload AP invoices from Staging table to Oracle Interface Tables.




DECLARE
 
 
v_inv_type ap_invoices_interface.INVOICE_TYPE_lookup_code%TYPE;

v_invoice_date ap_invoices_interface.INVOICE_DATE%TYPE;

V_vendor_id ap_invoices_interface.vendor_id%TYPE;

v_vendor_site_id ap_invoices_interface.vendor_site_id%TYPE;

v_invoice_num ap_invoices_interface.INVOICE_NUM %TYPE;

v_invoice_curr ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE;

v_invoice_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;

v_payment_cross_rate ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE;

v_cross_payment_curr ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE;

v_pay_rate_date ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE;

v_payment_rate_type ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE;

v_description ap_invoices_interface.DESCRIPTION%TYPE;

v_rate_type ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE;

v_exchange_date ap_invoices_interface.EXCHANGE_DATE%TYPE;

v_exchange_rate ap_invoices_interface.EXCHANGE_RATE%TYPE;

v_terms_date ap_invoices_interface.TERMS_DATE%TYPE;

v_terms ap_invoices_interface.TERMS_ID%TYPE;

v_payment_method ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE;

v_discountable_amt ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE;

v_invoice_recieved_date ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE;

v_recieved_goods_date ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE;

v_pay_code_combination_id ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE;

v_inv_count number;

V_CODE_COMBINATION_ID NUMBER;

v_invoice_id NUMBER;

v_invoice_line_id NUMBER;

v_line_no NUMBER;

v_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;

v_accounting_date DATE;

v_line_type VARCHAR2(400);

V_LOOKUP_CODE VARCHAR2(400);

V_CURRENCY_CODE VARCHAR2(400);

V_INVOICE_NUMBER VARCHAR2(400);

V_concatenated_segment VARCHAR2(4000);

V_ERROR_MESSAGE VARCHAR2(4000);

V_ERROR_FLAG VARCHAR2(40);

cursor c_invoice IS

SELECT INVOICE_NUMBER,INVOICE_DATE INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY

FROM XX_INVOICES_STG

WHERE NVL(STATUS,'NEW') NOT IN ('PROCESSED')

GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY;

cursor c_inv_detls(c_inv_number varchar2,c_supplier_number varchar2) IS

SELECT INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY,LINE_NUMBER,LINE_AMOUNT,GL_ACCOUNT,ATTRIBUTE1,ATTRIBUTE2

FROM XX_INVOICES_STG

where INVOICE_NUMBER=c_inv_number

and SUPPLIER_NUMBER=c_supplier_number;






 
begin
 
 

 
for f_invoice in c_invoice loop

V_ERROR_MESSAGE:=NULL;

V_ERROR_FLAG:=NULL;

v_inv_type :=NULL;

v_invoice_date:=NULL;

v_invoice_num:=NULL;

v_invoice_curr:=NULL;

v_invoice_amount:=NULL;

v_description:=NULL;




BEGIN
 
 
SELECT INVOICE_NUMBER into V_INVOICE_NUMBER

FROM XX_INVOICES_STG

WHERE INVOICE_NUMBER=f_invoice.invoice_number

GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY;




EXCEPTION
 
 
WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Header Information is not Identical';

V_Error_flag:='Y';




END;
 
 



 

 
 
BEGIN
 
 
select LOOKUP_CODE INTO V_LOOKUP_CODE from fnd_lookup_values

where LOOKUP_CODE='STANDARD'

AND LOOKUP_TYPE='INVOICE TYPE';

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type';

V_Error_flag:='Y';




END;
 
 



 

 
 
begin
 
 
select VENDOR_ID into v_vendor_id from ap_suppliers

where segment1=f_invoice.SUPPLIER_NUMBER

and VENDOR_NAME=f_invoice.SUPPLIER_NAME;

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Selection';

V_Error_flag:='Y';




END;
 
 




begin
 
 
select VENDOR_ID into v_vendor_site_id from ap_supplier_SITES_ALL

where VENDOR_ID=v_vendor_id

and VENDOR_SITE_CODE=f_invoice.SUPPLIER_SITE;

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Vendor Site is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Site Selection';

V_Error_flag:='Y';




END;
 
 




begin
 
 
select CURRENCY_CODE into V_CURRENCY_CODE from fnd_currencies

where CURRENCY_CODE=f_invoice.INVOICE_CURRENCY;

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Invoice Currency is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Currency Selection';

V_Error_flag:='Y';




END;
 
 



 

 
 
for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop




begin
 
 
select COUNT(*) into V_concatenated_segment from gl_code_combinations_kfv

where trim(concatenated_segments)=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'-'||f_inv_detls.GL_ACCOUNT||'-'||'Error: GL Code is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: GL Code Selection';

V_Error_flag:='Y';




END;
 
 
end loop;



 
 
--v_status:= cur_invoice.status;
 
 
 





-- SELECT COUNT(*)

-- INTO v_inv_count

-- FROM AP_INVOICES_ALL

-- WHERE  INVOICE_NUM = V_INVOICE_NUM

-- AND VENDOR_ID     = v_vendor_id;

--

--IF  v_count > 0  THEN

--   FND_FILE.PUT_LINE(FND_FILE.LOG,' Invocie already Exists - '||v_invoice_num);

--end if;
 
 
 





--inserting validated values to interface table(headers)='N'
 
 
 

if NVL(V_Error_flag,'N')='N' THEN

SELECT ap_invoices_interface_s.nextval INTO v_invoice_id FROM dual;

v_inv_type := f_invoice.INVOICE_TYPE;

v_invoice_date:=to_DATE(f_invoice.INVOICE_DATE,'MM/DD/YYYY');

v_invoice_num:= f_invoice.invoice_number;

v_invoice_curr:= f_invoice.INVOICE_CURRENCY;

v_invoice_amount:= f_invoice.invoice_amount;

v_description:= f_invoice.description;

INSERT INTO ap_invoices_interface (

invoice_id,

invoice_num,

invoice_type_lookup_code,

invoice_date,

vendor_id,

vendor_site_id,

invoice_amount,

invoice_currency_code,

description,

status,

source,

creation_date,

created_by,

last_update_date,



last_updated_by
 
)

VALUES

(
 
 
v_invoice_id,

v_invoice_num,

v_inv_type,

v_invoice_date,

v_vendor_id,

v_vendor_site_id,

nvl(v_invoice_amount,0),

v_invoice_curr,

v_description,

'NEW',

'TEST SOURCE',

sysdate,

FND_GLOBAL.user_id,

sysdate,

FND_GLOBAL.user_id




);
 
 

for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop

select ap_invoice_lines_interface_s.nextval into v_invoice_line_id from dual;

v_line_no:=f_inv_detls.line_NUMBER;

v_amount:=f_inv_detls.line_amount;

v_accounting_date:=sysdate;

v_line_type:='Item';--(Item, Fright,Tax,Miscellaneous)

select CODE_COMBINATION_ID into V_CODE_COMBINATION_ID from gl_code_combinations_kfv

where concatenated_segments=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));



 
 
-- BEGIN

--        SELECT 'Y'

--        INTO   v_found

--        FROM   gl_period_statuses

--        WHERE  application_id  = 200--ASSUMTION

--        AND    set_of_books_id = 3--ASSUMTION

--        AND    SYSDATE between start_date AND end_date

--        AND    closing_status IN ('O', 'F')

-- AND    NVL(adjustment_period_flag, 'N') = 'N'

--        AND    ROWNUM = 1;

-- EXCEPTION

--  WHEN NO_DATA_FOUND THEN

--             FND_FILE.PUT_LINE(FND_FILE.log,'Accounting Date Is Not In Open');

--    END;

/*------start to Validate Line Level for po_number matching/validating----------*/
 
 
 
-- get the info of header (if invoice is related to po_number)





/*------end to Validate Line Level for po_number matching/validating----------*/
 
 
 
/*start to inserting invoice line*/

INSERT INTO ap_invoice_lines_interface (

invoice_id,

invoice_line_id,

line_number,

line_type_lookup_code,

amount,

accounting_date,

dist_code_combination_id,

creation_date,

created_by,

last_update_date,



last_updated_by
 
)

VALUES(v_invoice_id,

v_invoice_line_id,

v_line_no,

v_line_type,

v_amount,

v_accounting_date,

V_CODE_COMBINATION_ID,

sysdate,

FND_GLOBAL.user_id,

sysdate,

FND_GLOBAL.user_id

);

end loop;

UPDATE APPS.XX_INVOICES_STG

SET STATUS='PROCESSED',

ERROR_MESSAGE=V_ERROR_MESSAGE

WHERE invoice_number=f_invoice.invoice_number

AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;

ELSE

UPDATE APPS.XX_INVOICES_STG

SET STATUS='ERROR',

ERROR_MESSAGE=V_ERROR_MESSAGE

WHERE invoice_number=f_invoice.invoice_number

AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;

END IF;

COMMIT;

END LOOP;




END;

/
 
 
DECLARE
 
 
v_inv_type ap_invoices_interface.INVOICE_TYPE_lookup_code%TYPE;

v_invoice_date ap_invoices_interface.INVOICE_DATE%TYPE;

V_vendor_id ap_invoices_interface.vendor_id%TYPE;

v_vendor_site_id ap_invoices_interface.vendor_site_id%TYPE;

v_invoice_num ap_invoices_interface.INVOICE_NUM %TYPE;

v_invoice_curr ap_invoices_interface.INVOICE_CURRENCY_CODE%TYPE;

v_invoice_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;

v_payment_cross_rate ap_invoices_interface.PAYMENT_CROSS_RATE%TYPE;

v_cross_payment_curr ap_invoices_interface.PAYMENT_CURRENCY_CODE%TYPE;

v_pay_rate_date ap_invoices_interface.PAYMENT_CROSS_RATE_DATE%TYPE;

v_payment_rate_type ap_invoices_interface.PAYMENT_CROSS_RATE_TYPE%TYPE;

v_description ap_invoices_interface.DESCRIPTION%TYPE;

v_rate_type ap_invoices_interface.EXCHANGE_RATE_TYPE%TYPE;

v_exchange_date ap_invoices_interface.EXCHANGE_DATE%TYPE;

v_exchange_rate ap_invoices_interface.EXCHANGE_RATE%TYPE;

v_terms_date ap_invoices_interface.TERMS_DATE%TYPE;

v_terms ap_invoices_interface.TERMS_ID%TYPE;

v_payment_method ap_invoices_interface.PAYMENT_METHOD_LOOKUP_CODE%TYPE;

v_discountable_amt ap_invoices_interface.AMOUNT_APPLICABLE_TO_DISCOUNT%TYPE;

v_invoice_recieved_date ap_invoices_interface.INVOICE_RECEIVED_DATE%TYPE;

v_recieved_goods_date ap_invoices_interface.GOODS_RECEIVED_DATE%TYPE;

v_pay_code_combination_id ap_invoices_interface.ACCTS_PAY_CODE_COMBINATION_ID%TYPE;

v_inv_count number;

V_CODE_COMBINATION_ID NUMBER;

v_invoice_id NUMBER;

v_invoice_line_id NUMBER;

v_line_no NUMBER;

v_amount ap_invoices_interface.INVOICE_AMOUNT%TYPE;

v_accounting_date DATE;

v_line_type VARCHAR2(400);

V_LOOKUP_CODE VARCHAR2(400);

V_CURRENCY_CODE VARCHAR2(400);

V_INVOICE_NUMBER VARCHAR2(400);

V_concatenated_segment VARCHAR2(4000);

V_ERROR_MESSAGE VARCHAR2(4000);

V_ERROR_FLAG VARCHAR2(40);

cursor c_invoice IS

SELECT INVOICE_NUMBER,INVOICE_DATE INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY

FROM XX_INVOICES_STG

WHERE NVL(STATUS,'NEW') NOT IN ('PROCESSED')

GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY;

cursor c_inv_detls(c_inv_number varchar2,c_supplier_number varchar2) IS

SELECT INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY,LINE_NUMBER,LINE_AMOUNT,GL_ACCOUNT,ATTRIBUTE1,ATTRIBUTE2

FROM XX_INVOICES_STG

where INVOICE_NUMBER=c_inv_number

and SUPPLIER_NUMBER=c_supplier_number;






 
begin
 
 

 
for f_invoice in c_invoice loop

V_ERROR_MESSAGE:=NULL;

V_ERROR_FLAG:=NULL;

v_inv_type :=NULL;

v_invoice_date:=NULL;

v_invoice_num:=NULL;

v_invoice_curr:=NULL;

v_invoice_amount:=NULL;

v_description:=NULL;




BEGIN
 
 
SELECT INVOICE_NUMBER into V_INVOICE_NUMBER

FROM XX_INVOICES_STG

WHERE INVOICE_NUMBER=f_invoice.invoice_number

GROUP BY INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,DESCRIPTION,INVOICE_AMOUNT,SUPPLIER_NUMBER,SUPPLIER_NAME

,SUPPLIER_SITE,INVOICE_CURRENCY;




EXCEPTION
 
 
WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Header Information is not Identical';

V_Error_flag:='Y';




END;
 
 



 

 
 
BEGIN
 
 
select LOOKUP_CODE INTO V_LOOKUP_CODE from fnd_lookup_values

where LOOKUP_CODE='STANDARD'

AND LOOKUP_TYPE='INVOICE TYPE';

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Invoice Type';

V_Error_flag:='Y';




END;
 
 



 

 
 
begin
 
 
select VENDOR_ID into v_vendor_id from ap_suppliers

where segment1=f_invoice.SUPPLIER_NUMBER

and VENDOR_NAME=f_invoice.SUPPLIER_NAME;

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Selection';

V_Error_flag:='Y';




END;
 
 




begin
 
 
select VENDOR_ID into v_vendor_site_id from ap_supplier_SITES_ALL

where VENDOR_ID=v_vendor_id

and VENDOR_SITE_CODE=f_invoice.SUPPLIER_SITE;

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Vendor Site is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Vendor Site Selection';

V_Error_flag:='Y';




END;
 
 




begin
 
 
select CURRENCY_CODE into V_CURRENCY_CODE from fnd_currencies

where CURRENCY_CODE=f_invoice.INVOICE_CURRENCY;

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'---'||'Error: Invoice Currency is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: Currency Selection';

V_Error_flag:='Y';




END;
 
 



 

 
 
for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop




begin
 
 
select COUNT(*) into V_concatenated_segment from gl_code_combinations_kfv

where trim(concatenated_segments)=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));

EXCEPTION WHEN NO_DATA_FOUND THEN

V_ERROR_MESSAGE:= V_ERROR_MESSAGE||'-'||f_inv_detls.GL_ACCOUNT||'-'||'Error: GL Code is Not Valid';

V_Error_flag:='Y';

WHEN OTHERS THEN

V_ERROR_MESSAGE:=V_ERROR_MESSAGE||'---'||'Error: GL Code Selection';

V_Error_flag:='Y';




END;
 
 
end loop;



 
 
--v_status:= cur_invoice.status;
 
 
 





-- SELECT COUNT(*)

-- INTO v_inv_count

-- FROM AP_INVOICES_ALL

-- WHERE  INVOICE_NUM = V_INVOICE_NUM

-- AND VENDOR_ID     = v_vendor_id;

--

--IF  v_count > 0  THEN

--   FND_FILE.PUT_LINE(FND_FILE.LOG,' Invocie already Exists - '||v_invoice_num);

--end if;
 
 
 





--inserting validated values to interface table(headers)='N'
 
 
 

if NVL(V_Error_flag,'N')='N' THEN

SELECT ap_invoices_interface_s.nextval INTO v_invoice_id FROM dual;

v_inv_type := f_invoice.INVOICE_TYPE;

v_invoice_date:=to_DATE(f_invoice.INVOICE_DATE,'MM/DD/YYYY');

v_invoice_num:= f_invoice.invoice_number;

v_invoice_curr:= f_invoice.INVOICE_CURRENCY;

v_invoice_amount:= f_invoice.invoice_amount;

v_description:= f_invoice.description;

INSERT INTO ap_invoices_interface (

invoice_id,

invoice_num,

invoice_type_lookup_code,

invoice_date,

vendor_id,

vendor_site_id,

invoice_amount,

invoice_currency_code,

description,

status,

source,

creation_date,

created_by,

last_update_date,



last_updated_by
 
)

VALUES

(
 
 
v_invoice_id,

v_invoice_num,

v_inv_type,

v_invoice_date,

v_vendor_id,

v_vendor_site_id,

nvl(v_invoice_amount,0),

v_invoice_curr,

v_description,

'NEW',

'TEST SOURCE',

sysdate,

FND_GLOBAL.user_id,

sysdate,

FND_GLOBAL.user_id




);
 
 

for f_inv_detls in c_inv_detls(f_invoice.invoice_number,f_invoice.supplier_number) loop

select ap_invoice_lines_interface_s.nextval into v_invoice_line_id from dual;

v_line_no:=f_inv_detls.line_NUMBER;

v_amount:=f_inv_detls.line_amount;

v_accounting_date:=sysdate;

v_line_type:='Item';--(Item, Fright,Tax,Miscellaneous)

select CODE_COMBINATION_ID into V_CODE_COMBINATION_ID from gl_code_combinations_kfv

where concatenated_segments=TRIM(REPLACE(f_inv_detls.GL_ACCOUNT,CHR(13),''));



 
 
-- BEGIN

--        SELECT 'Y'

--        INTO   v_found

--        FROM   gl_period_statuses

--        WHERE  application_id  = 200--ASSUMTION

--        AND    set_of_books_id = 3--ASSUMTION

--        AND    SYSDATE between start_date AND end_date

--        AND    closing_status IN ('O', 'F')

-- AND    NVL(adjustment_period_flag, 'N') = 'N'

--        AND    ROWNUM = 1;

-- EXCEPTION

--  WHEN NO_DATA_FOUND THEN

--             FND_FILE.PUT_LINE(FND_FILE.log,'Accounting Date Is Not In Open');

--    END;

/*------start to Validate Line Level for po_number matching/validating----------*/
 
 
 
-- get the info of header (if invoice is related to po_number)





/*------end to Validate Line Level for po_number matching/validating----------*/
 
 
 
/*start to inserting invoice line*/

INSERT INTO ap_invoice_lines_interface (

invoice_id,

invoice_line_id,

line_number,

line_type_lookup_code,

amount,

accounting_date,

dist_code_combination_id,

creation_date,

created_by,

last_update_date,



last_updated_by
 
)

VALUES(v_invoice_id,

v_invoice_line_id,

v_line_no,

v_line_type,

v_amount,

v_accounting_date,

V_CODE_COMBINATION_ID,

sysdate,

FND_GLOBAL.user_id,

sysdate,

FND_GLOBAL.user_id

);

end loop;

UPDATE APPS.XX_INVOICES_STG

SET STATUS='PROCESSED',

ERROR_MESSAGE=V_ERROR_MESSAGE

WHERE invoice_number=f_invoice.invoice_number

AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;

ELSE

UPDATE APPS.XX_INVOICES_STG

SET STATUS='ERROR',

ERROR_MESSAGE=V_ERROR_MESSAGE

WHERE invoice_number=f_invoice.invoice_number

AND SUPPLIER_NUMBER=f_invoice.SUPPLIER_NUMBER;

END IF;

COMMIT;

END LOOP;




END;

/


After Inserting Invoices in Oracle Interface you have need to run 'Payables Open Interface' to import Invoices in Oracle Invoice Base Tabes
 
 
 

0 comments:

Post a Comment