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
 
 
 

2 comments:

murali sethi said...

Hi Rohit,

How GST and TDS value can be calculated through payables open interface import?

Could you please help me on this?

Thanks!!!

murali sethi said...

Hi Rohit,

How GST and TDS value can be calculated through payables open interface import?

Could you please help me on this?

Thanks!!!

Post a Comment