Sunday 29 July 2018

AP invoice interface sample code in oracle apps r12

AP invoice interface sample code in oracle apps r12

AP invoice Interface uses to import External AP invoices in Oracle Payables. To Create AP invoices from External System we first need to Insert the External Invoice Data in the AP invoice Interface Tables. We need to prepare the External Invoice information's in the format of AP invoice Interface and then insert these records in the AP interface Tables. In this post , I am sharing the AP invoice Interface sample code in oracle r12 to insert the invoice information from external system to the AP invoice Interface tables. You can refer this AP invoice interface sample code to insert invoices from External System.
 

 
 
Steps of AP invoice Interface.
 
1. Prepare the External System data in the form of AP invoice Interface.
2.Insert record in the AP invoice Interface Tables.
3.Run the Payables Open interface Import program to import invoices from AP interface to AP base tables.
 
These are the two tables of AP Invoice Interface. These tables uses to import AP invoices from External System to the Oracle application.
 
AP invoice interface sample code in oracle apps r12
 
For example , This is the Invoice Test Data which we got from the External System and how i will insert this Data in these Two Interface tables As Below.
 
AP invoice interface sample code in oracle apps r12
 
 
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;

/

0 comments:

Post a Comment

Contact us for any Collaboration, Project Support & On Job Support Work

Name

Email *

Message *