CREATE OR REPLACE PACKAGE APPS.AP_INVOICE_UPLOAD IS
PROCEDURE ap_invoice_call ( errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_gl_date IN VARCHAR2
, p_match_option IN VARCHAR2
) ;
--
------------------------------------------------------------
-- Main Procedure of AP Invoice upload
------------------------------------------------------------
PROCEDURE AP_INVOICE_MAIN ( errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_gl_date IN VARCHAR2
, p_match_option IN VARCHAR2
) ;
--
--
END AP_INVOICE_UPLOAD ;
################################################################################
CREATE OR REPLACE PACKAGE BODY APPS.AP_INVOICE_UPLOAD
IS
------------------------------------------------------------------------------
pv_user_id VARCHAR2 (30) := NVL (fnd_profile.VALUE ('USER_ID'), '1');
pv_login_id VARCHAR2 (30) := NVL (fnd_profile.VALUE ('LOGIN_ID'), '1');
pv_conc_req_id NUMBER := fnd_global.conc_request_id;
pv_org_id VARCHAR2 (10) := NVL (fnd_profile.VALUE ('ORG_ID'), '1');
pv_responsibility_id VARCHAR2 (30) := NVL (fnd_profile.VALUE ('RESP_ID'), '1');
pv_respappl_id VARCHAR2 (30) := NVL (fnd_profile.VALUE ('RESP_APPL_ID'), '1');
------------------------------------------------------------------------------
-- Function returns whether a given period is open or not
------------------------------------------------------------------------------
FUNCTION mas_gl_period_sts ( p_appl_short_name VARCHAR2
, p_date DATE
)
RETURN VARCHAR2 IS
--
lv_period_status gl_period_statuses.closing_status%TYPE;
--
BEGIN
SELECT closing_status
INTO lv_period_status
FROM gl_period_statuses a
, fnd_application b
WHERE TRUNC (p_date) BETWEEN start_date AND end_date
AND a.application_id = b.application_id
AND b.application_short_name = p_appl_short_name --'SQLAP'
AND a.set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
IF NVL (lv_period_status, 'X') = 'O' THEN
RETURN lv_period_status; --TRUE;
ELSE
RETURN NULL ;
END IF;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.LOG , 'ERROR! Period not found for date '|| p_date|| SQLERRM);
RETURN NULL ;
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG , 'ERROR! Period not found for date '|| p_date|| SQLERRM);
RETURN NULL ;
END mas_gl_period_sts ;
--
------------------------------------------------------------------------------
-- Output Report Procedure
------------------------------------------------------------------------------
--
--
------------------------------------------------------------
-- Main Procedure of AP Invoice upload Call
------------------------------------------------------------
PROCEDURE ap_invoice_call (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_gl_date IN VARCHAR2,
p_match_option IN VARCHAR2
)
IS
--
lv_prog_name VARCHAR2 (30) := 'APINVLOAD';
lv_prog_name1 VARCHAR2 (30) := 'APINVRECLOAD';
lv_app_short_name VARCHAR2 (30) := 'TEST';
custom_exception EXCEPTION;
lv_phase VARCHAR2 (100);
lv_status VARCHAR2 (100);
lv_dev_phase VARCHAR2 (100);
lv_dev_status VARCHAR2 (100);
lv_message VARCHAR2 (200);
lv_req_done BOOLEAN;
lv_req_done1 BOOLEAN;
lv_request_id NUMBER;
lv_request_id1 NUMBER;
lv_record_loaded NUMBER; --
BEGIN
--
DBMS_OUTPUT.put_line ('Inside ap_invoice_call');
fnd_file.put_line (fnd_file.LOG, 'Inside ap_invoice_call');
-- Submit Payables MasTec (AT) AP Invoice Record Loader
-----------------------------------------------------
fnd_file.put_line (fnd_file.LOG,
' AP Invoice Data Upload'
);
lv_request_id1 :=
fnd_request.submit_request
(lv_app_short_name,
lv_prog_name1,
' AP Invoice Data Upload'
,''
,FALSE
);
--
IF lv_request_id1 = 0
THEN
--
DBMS_OUTPUT.put_line ('Request not successfull');
fnd_file.put_line (fnd_file.LOG, 'Request not successfull');
RAISE custom_exception;
--
ELSE
COMMIT;
DBMS_OUTPUT.put_line ( 'Request sucessful and the id is '
|| TO_CHAR (lv_request_id1)
);
fnd_file.put_line (fnd_file.LOG,
'Request sucessful and the id is '
|| TO_CHAR (lv_request_id1)
);
--
lv_req_done1 :=
fnd_concurrent.wait_for_request
(lv_request_id1,
3,
(1000 * 60),
lv_phase,
lv_status,
lv_dev_phase,
lv_dev_status,
lv_message
);
END IF;
SELECT COUNT (invoice_num)
INTO lv_record_loaded
FROM AP_INV_EXT_TBL
WHERE process_flag = 'N';
DBMS_OUTPUT.put_line ( 'Total number of records loaded :- '
|| lv_record_loaded
);
fnd_file.put_line (fnd_file.LOG,
'Total number of records loaded :- '
|| lv_record_loaded
);
--
IF NVL (lv_record_loaded, 0) != 0
THEN
-----------------------------------------------------
-- Submit Payables MasTec ONS AP Invoice Bulk Upload
-----------------------------------------------------
DBMS_OUTPUT.put_line (' AP Invoice Upload Program.');
fnd_file.put_line (fnd_file.LOG,
' AP Invoice Upload Program.'
);
--
fnd_file.put_line (fnd_file.LOG, 'Date' || p_gl_date);
--- lv_gl_date := TO_DATE (p_gl_date, 'MM/DD/RRRR');
lv_request_id :=
fnd_request.submit_request
(lv_app_short_name,
lv_prog_name,
' AP Invoice Upload Program',
'',
FALSE,
p_gl_date,
p_match_option
);
--
IF lv_request_id = 0
THEN -- IF#2
--
DBMS_OUTPUT.put_line ('Request not successfull');
fnd_file.put_line (fnd_file.LOG, 'Request not successfull');
RAISE custom_exception;
--
ELSE
COMMIT;
DBMS_OUTPUT.put_line ( 'Request sucessful and the id is '
|| TO_CHAR (lv_request_id)
);
fnd_file.put_line (fnd_file.LOG,
'Request sucessful and the id is '
|| TO_CHAR (lv_request_id)
);
--
lv_req_done :=
fnd_concurrent.wait_for_request
(lv_request_id,
3,
(1000 * 60),
lv_phase,
lv_status,
lv_dev_phase,
lv_dev_status,
lv_message
);
--
END IF;
--
--
END IF;
--
----------------------------------------------
----------------------------------------------
DBMS_OUTPUT.put_line ( 'Request id for AP Invoice Upload Program :-'
|| lv_request_id
);
fnd_file.put_line (fnd_file.LOG,
'Request id for AP Invoice Upload Program :-'
|| lv_request_id
);
DBMS_OUTPUT.put_line ('End ap_invoice_call');
fnd_file.put_line (fnd_file.LOG, 'End ap_invoice_call');
--
EXCEPTION
WHEN custom_exception
THEN
DBMS_OUTPUT.put_line
('ERROR! Custom error In ap_invoice_call of AP Invoice upload Call : '
);
fnd_file.put_line
(fnd_file.LOG,
'ERROR! Custom error In ap_invoice_call of AP Invoice upload Call : '
);
WHEN OTHERS
THEN
IF SQLCODE = -29913
THEN
--
retcode := SQLCODE;
DBMS_OUTPUT.put_line ('Data File Not found, ' || retcode);
fnd_file.put_line (fnd_file.LOG,
'Data File Not found, ' || retcode
);
--
ELSE
--
DBMS_OUTPUT.put_line
( 'ERROR! In ap_invoice_call of AP Invoice upload Call : '
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
'ERROR! In ap_invoice_call of AP Invoice upload Call : '
|| SQLERRM
);
retcode := 1; -- Warning
--
END IF;
END ap_invoice_call;
--
------------------------------------------------------------
-- Main Procedure of AP Invoice upload
------------------------------------------------------------
PROCEDURE ap_invoice_main (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_gl_date IN VARCHAR2,
p_match_option IN VARCHAR2
)
IS
CURSOR C_PAYMENT IS
SELECT * FROM AP_INV_EXT_TBL WHERE PROCESS_FLAG = 'N';
CURSOR c_dup_inv
IS
SELECT DISTINCT INVOICE_NUM,
COUNT (DISTINCT PAYMENT_TERM) V_PAYTERM
FROM AP_INV_EXT_TBL
WHERE PROCESS_FLAG = 'N'
GROUP BY INVOICE_NUM;
CURSOR c_source
IS
SELECT DISTINCT SOURCE
FROM AP_INV_EXT_TBL
WHERE PROCESS_FLAG = 'N';
CURSOR c_invoice (p_source VARCHAR2)
IS
SELECT DISTINCT TRIM (INVOICE_NUM) INVOICE_NUM, TRIM (VENDOR_NUM) VENDOR_NUM,
TRIM (VENDOR_SITE_CODE) VENDOR_SITE_CODE,
TRIM (INVOICE_AMOUNT) INVOICE_AMOUNT, TRIM (SOURCE) SOURCE,
TRUNC (INVOICE_DATE) INVOICE_DATE,
TRIM (INVOICE_DESCRIPTION) INVOICE_DESCRIPTION,
TRIM (PAYMENT_TERM) PAYMENT_TERM
FROM AP_INV_EXT_TBL
WHERE PROCESS_FLAG = 'N' AND SOURCE = P_SOURCE
ORDER BY PAYMENT_TERM;
CURSOR C_LINE (
P_INVOICE_NUM VARCHAR2,
P_VENDOR_NUM VARCHAR2,
P_VENDOR_SITE_CODE VARCHAR2
)
IS
SELECT TRIM (LINE_NUMBER) LINE_NUMBER, TRIM (LINE_AMOUNT) LINE_AMOUNT,
TRIM (DIST_CODE_CONCATENATED) DIST_CODE_CONCATENATED,
TRIM (LINE_DESCRIPTION) LINE_DESCRIPTION, TRIM (TAX_CODE) TAX_CODE,
TRUNC (ACCOUNTING_DATE) ACCOUNTING_DATE,
TRIM (ACTIVITY_NUMBER) ACTIVITY_NUMBER, TRIM (TECH_NUMBER) TECH_NUMBER,
TRIM (LOCATION) LOCATION, TRIM (COMPLETION_DATE) COMPLETION_DATE,
PO_NUMBER, PO_LINE_NUMBER, QUANT_INV, UNIT_PRICE,
TRIM (SUBSTR (DTV_ACCOUNT_NUMBER, 1, 30)) DTV_ACCOUNT_NUMBER, ROWID
FROM AP_INV_EXT_TBL
WHERE TRIM (INVOICE_NUM) = P_INVOICE_NUM
AND TRIM (VENDOR_NUM) = P_VENDOR_NUM
AND TRIM (VENDOR_SITE_CODE) = P_VENDOR_SITE_CODE
AND PROCESS_FLAG = 'N';
lv_price_correction_flag VARCHAR2 (2);
lv_receipt_number VARCHAR2 (40);
lv_receipt_num VARCHAR2 (40);
lv_invoice_line_id NUMBER;
lv_quant_inv NUMBER;
lv_unit_price NUMBER;
lv_invoice_line_num NUMBER := 0;
lv_amount NUMBER := 0;
lv_po_header_id NUMBER := 0;
lv_po_line_id NUMBER := 0;
lv_line_location_id NUMBER := 0;
lv_po_distribution_id NUMBER := 0;
lv_inventory_item_id NUMBER := 0;
lv_po_header_id_1 NUMBER := 0;
lv_po_line_id_1 NUMBER := 0;
lv_line_location_id_1 NUMBER := 0;
lv_po_distribution_id_1 NUMBER := 0;
lv_inventory_item_id_1 NUMBER := 0;
lv_dist_code_concatenated VARCHAR2 (42);
lv_match_option VARCHAR2 (5);
-- Added
lv_project_num VARCHAR2(42);
ln_project_id NUMBER := 0;
lv_po_num VARCHAR2(42);
lv_po_number VARCHAR2(42);
lv_task_name VARCHAR2(42);
lv_task_id NUMBER := 0;
ln_task_id NUMBER := 0;
lv_expenditure_type VARCHAR2(60);
lv_pa_add_flag VARCHAR2(42) :='N';
lv_po_ship_num NUMBER := 0;
lv_po_dist_num NUMBER := 0;
lv_expenditure_date DATE;
lv_pro_acc_cont VARCHAR2(60);
lv_expenditure_org_id NUMBER := 0;
lv_final_match_flag VARCHAR2(42) :='N';
lv_code_combi_id NUMBER := 0;
-- Added
lv_quantity NUMBER := 0;
lv_invoice_type VARCHAR2 (20);
lv_group VARCHAR2 (40);
lv_gl_period_status VARCHAR2 (20);
lv_request_id NUMBER;
lv_invoice_source VARCHAR2 (40);
lv_prog_name VARCHAR2 (30) := 'APXIIMPT';
lv_app_short_name VARCHAR2 (30) := 'SQLAP';
lv_phase VARCHAR2 (100);
lv_status VARCHAR2 (100);
lv_dev_phase VARCHAR2 (100);
lv_dev_status VARCHAR2 (100);
lv_message VARCHAR2 (200);
lv_rec_count NUMBER := 0;
custom_exception EXCEPTION;
lv_req_done BOOLEAN;
lv_gl_date DATE;
lv_invoice_processed NUMBER;
lv_invoice_rejected NUMBER;
lv_record_cnt NUMBER := 0;
lv_account_dt_status VARCHAR2 (20);
custom_line_exception EXCEPTION;
lv_fail_valid_cnt NUMBER := 0;
lv_term_id NUMBER;
v_payment varchar2(100);
v_org_id number;
--
BEGIN
fnd_client_info.set_org_context(pv_org_id);
fnd_global.apps_initialize ( user_id => pv_user_id
,resp_id => pv_responsibility_id
,resp_appl_id => pv_respappl_id);
fnd_global.set_nls_context('AMERICAN');
mo_global.set_policy_context('S',pv_org_id);
fnd_file.put_line (fnd_file.LOG, 'org' || pv_org_id);
fnd_file.put_line (fnd_file.LOG, 'AP Invoice upload');
lv_group := 'AUTO_LOAD' || pv_conc_req_id;
fnd_file.put_line (fnd_file.LOG, 'lv_group :- ' || lv_group);
fnd_file.put_line (fnd_file.LOG, 'p_gl_date :- ' || p_gl_date);
lv_gl_date := TO_DATE (p_gl_date, 'MM/DD/RRRR');
lv_gl_period_status := mas_gl_period_sts ('SQLGL', lv_gl_date);
--
IF NVL (lv_gl_period_status, 'x') != 'O'
THEN
fnd_file.put_line
(fnd_file.LOG,
'Period is not open for GL Date you have passed :- '
|| lv_gl_date
);
RAISE custom_exception;
END IF;
for rec_payment in c_payment loop
IF rec_payment.ORG_ID IS NULL THEN
update AP_INV_EXT_TBL
set org_id= pv_org_id
where invoice_num = rec_payment.invoice_num;
END IF;
commit;
end loop;
------------------------------------------------------
-- Delete the old rejected records AP Interface table
------------------------------------------------------
--
DELETE FROM ap_invoice_lines_interface apl
WHERE apl.invoice_id IN (
SELECT ap.invoice_id
FROM AP_INV_EXT_TBL ma, ap_invoices_interface ap
WHERE ma.process_flag = 'N'
AND ap.invoice_num = ma.invoice_num);
--
fnd_file.put_line
(fnd_file.LOG,
'Records deleted from ap_invoice_lines_interface table :- '
|| SQL%ROWCOUNT
);
--
DELETE FROM ap_invoices_interface
WHERE invoice_num IN (SELECT invoice_num
FROM AP_INV_EXT_TBL
WHERE process_flag = 'N');
--
fnd_file.put_line
(fnd_file.LOG,
'Records deleted from ap_invoices_interface table :- '
|| SQL%ROWCOUNT
);
BEGIN
FOR rec_dup_inv IN c_dup_inv
LOOP
IF rec_dup_inv.v_payterm >= 2
THEN
fnd_file.put_line (fnd_file.LOG,
'Same Invoice Number :'
|| rec_dup_inv.invoice_num
|| ' Consist more than 1 Payment Term'
);
UPDATE AP_INV_EXT_TBL
SET process_flag = 'E'
WHERE invoice_num = rec_dup_inv.invoice_num;
END IF;
END LOOP;
FOR rec_source IN c_source
LOOP
SELECT rec_source.SOURCE || '-'
|| TO_CHAR (SYSDATE, 'ddmmyyyyhh24')
INTO lv_group
FROM DUAL;
fnd_file.put_line (fnd_file.LOG, 'DATA PROCESS');
FOR rec_invoice IN c_invoice (rec_source.SOURCE)
LOOP
IF rec_invoice.invoice_amount > 0
THEN
lv_invoice_type := 'STANDARD';
ELSE
lv_invoice_type := 'CREDIT';
END IF;
BEGIN
SELECT term_id
INTO lv_term_id
FROM ap_terms_tl
WHERE UPPER (TRIM (NAME)) =
UPPER (TRIM (rec_invoice.payment_term));
EXCEPTION
WHEN OTHERS
THEN
lv_term_id := NULL;
END;
--
fnd_file.put_line
(fnd_file.LOG,
'Inserting records to ap_invoices_interface for non po invoice num :- '
|| rec_invoice.invoice_num
);
--
INSERT INTO ap_invoices_interface
(invoice_id,
invoice_num,
invoice_date,
vendor_num,
vendor_site_code,
invoice_amount,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date, created_by,
SOURCE, GROUP_ID,
invoice_type_lookup_code,
terms_id
)
VALUES (ap_invoices_interface_s.NEXTVAL -- invoice_id
,rec_invoice.invoice_num -- invoice_num
,rec_invoice.invoice_date -- invoice_date
,rec_invoice.vendor_num -- vendor_num
,rec_invoice.vendor_site_code -- vendor_site_code
,rec_invoice.invoice_amount -- invoice_amount
,rec_invoice.invoice_description -- description
,SYSDATE -- last_update_date
,pv_user_id -- last_updated_by
,pv_login_id -- last_update_login
,SYSDATE -- creation_date
,pv_user_id -- created_by
,rec_invoice.SOURCE -- source
,lv_group -- group_id
,lv_invoice_type -- invoice_type_lookup_code
,lv_term_id
);
------------------------------------------------
-- Cursor Open for AP Invoice header records
------------------------------------------------
FOR rec_line IN
c_line (p_invoice_num => rec_invoice.invoice_num,
p_vendor_num => rec_invoice.vendor_num,
p_vendor_site_code => rec_invoice.vendor_site_code
)
LOOP -- LOOP #2
--
BEGIN
lv_account_dt_status :=
mas_gl_period_sts
('SQLGL',
rec_line.accounting_date
);
--
IF NVL (lv_account_dt_status, 'x') != 'O'
THEN
-- Period is not open for accounting date
fnd_file.put_line
(fnd_file.LOG,
'Period is not open for Accounting Date you have passed :- '
|| rec_line.accounting_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
--
END IF;
--
--
IF (rec_line.accounting_date < rec_invoice.invoice_date
)
THEN
-- Accounting date can not be less than the Invoice date
fnd_file.put_line
(fnd_file.LOG,
'Accounting date can not be less than the Invoice date, for Accounting Date :- '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
--
END IF;
--
--
IF rec_line.tax_code IS NULL
THEN
lv_amount := 0;
lv_po_header_id := NULL;
lv_po_line_id := NULL;
lv_line_location_id := NULL;
lv_po_distribution_id := NULL;
lv_inventory_item_id := NULL;
lv_po_header_id_1 := NULL;
lv_po_line_id_1 := NULL;
lv_line_location_id_1 := NULL;
lv_po_distribution_id_1 := NULL;
lv_inventory_item_id_1 := NULL;
lv_match_option := NULL;
lv_dist_code_concatenated := NULL;
IF rec_line.po_number IS NOT NULL
THEN
lv_invoice_line_num := lv_invoice_line_num + 1;
-- Added by skb on 02-Dec-2009
lv_price_correction_flag := NULL;
lv_receipt_number := NULL;
lv_quant_inv := NULL;
lv_unit_price := NULL;
lv_quantity := NULL;
fnd_file.put_line (fnd_file.LOG,
'p_match_option :- '
|| p_match_option
);
fnd_file.put_line (fnd_file.LOG,
'lv_receipt_number :- '
|| lv_receipt_number
);
--
--
IF p_match_option = 'P'
THEN
BEGIN
-- New project related information
SELECT DISTINCT c.po_header_id,
b.po_line_id,
c.segment1,
b.line_location_id,
b.po_distribution_id,
b.distribution_num,
a.unit_price,
(a.quantity - b.quantity_billed),
NULL,
a.item_id,
g.SEGMENT1 "Project Number",
h.TASK_NAME,
h.TASK_ID,
b.EXPENDITURE_TYPE,
b.EXPENDITURE_ITEM_DATE,
b.PROJECT_ACCOUNTING_CONTEXT,
b.EXPENDITURE_ORGANIZATION_ID,
j.SHIPMENT_NUM
INTO lv_po_header_id,
lv_po_line_id,
lv_po_num,
lv_line_location_id,
lv_po_distribution_id,
lv_po_dist_num,
lv_unit_price,
lv_quantity,
lv_receipt_num,
lv_inventory_item_id,
lv_project_num,
lv_task_name,
lv_task_id,
lv_expenditure_type,
lv_expenditure_date,
lv_pro_acc_cont,
lv_expenditure_org_id,
lv_po_ship_num
FROM po_lines_all a,
po_distributions_all b,
po_headers_all c,
pa_projects_all g,
pa_tasks h,
pa_project_statuses i,
po_line_locations_all j
WHERE 1 = 1
AND c.segment1 = rec_line.po_number
AND a.line_num = NVL (rec_line.po_line_number, a.line_num)
AND c.po_header_id = a.po_header_id
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND b.PROJECT_ID = g.PROJECT_ID
AND b.TASK_ID = h.TASK_ID
AND g.project_status_code = i.project_status_code
AND j.po_line_id = a.po_line_id
AND j.line_location_id = b.line_location_id
AND (a.quantity - b.quantity_billed) > 0
AND NVL (a.cancel_flag, 'N') <> 'Y'
and i.project_system_status_code = ('APPROVED');
/* SELECT c.po_header_id, b.po_line_id,
b.line_location_id,
b.po_distribution_id,
a.unit_price,
(a.quantity - b.quantity_billed
),
NULL, a.item_id
INTO lv_po_header_id, lv_po_line_id,
lv_line_location_id,
lv_po_distribution_id,
lv_unit_price,
lv_quantity,
lv_receipt_num, lv_inventory_item_id
FROM po_lines_all a,
po_distributions_ap_v b,
po_headers_all c
WHERE c.segment1 = rec_line.po_number
AND c.po_header_id = a.po_header_id
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND NVL (a.cancel_flag, 'N') <> 'Y'
AND a.line_num =
NVL (rec_line.po_line_number,
a.line_num
)
AND (a.quantity - b.quantity_billed) > 0; */
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'PO Data not found for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while getting PO Data for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
END;
-- Added
UPDATE XXMTZ_ONS_AP_INV_EXT_TBL SET
Project_number = lv_project_num,
Task_name = lv_task_name,
expenditure_type = lv_expenditure_type
WHERE po_number = rec_line.po_number
AND po_line_number = NVL (rec_line.po_line_number, po_line_number);
-- Added
ELSIF p_match_option = 'R'
THEN
BEGIN
SELECT c.po_header_id, b.po_line_id,
b.line_location_id,
b.po_distribution_id,
a.unit_price,
(f.primary_quantity
- b.quantity_billed
) quantity,
e.receipt_num,
a.item_id inventory_item_id
INTO lv_po_header_id, lv_po_line_id,
lv_line_location_id,
lv_po_distribution_id,
lv_unit_price,
lv_quantity,
lv_receipt_num,
lv_inventory_item_id
FROM po_lines_all a,
po_distributions_ap_v b,
po_headers_all c,
rcv_shipment_headers e,
rcv_transactions f
WHERE c.segment1 = rec_line.po_number
AND c.po_header_id = a.po_header_id
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND NVL (a.cancel_flag, 'N') <> 'Y'
AND a.line_num =
NVL (rec_line.po_line_number,
a.line_num
)
AND e.shipment_header_id =
f.shipment_header_id
AND f.transaction_type = 'RECEIVE'
AND b.po_distribution_id =
f.po_distribution_id
AND (a.quantity - b.quantity_billed) > 0;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'PO Data not found for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while getting PO Data for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
END;
END IF;
--
-- Populate the invoiced quantity if different from po quantity
--
IF ( rec_line.quant_inv <> lv_quantity
AND rec_line.quant_inv IS NOT NULL
)
THEN
--
lv_quant_inv := rec_line.quant_inv;
--
ELSE
--
lv_quant_inv := lv_quantity;
--
END IF;
--
fnd_file.put_line (fnd_file.LOG,
'lv_quant_inv :- '
|| lv_quant_inv
);
--
-- Populate the invoiced unit_price if different from po unit_price
--
IF ( rec_line.unit_price <> lv_unit_price
AND rec_line.unit_price IS NOT NULL
)
THEN
--
lv_unit_price := rec_line.unit_price;
lv_price_correction_flag := 'Y';
--
ELSE
--
lv_unit_price := lv_unit_price;
lv_price_correction_flag := NULL;
--
END IF;
--
fnd_file.put_line (fnd_file.LOG,
'lv_unit_price :- '
|| lv_unit_price
);
fnd_file.put_line
(fnd_file.LOG,
'lv_price_correction_flag :- '
|| lv_price_correction_flag
);
lv_amount := lv_unit_price * lv_quant_inv;
lv_po_header_id_1 := lv_po_header_id;
lv_po_line_id_1 := lv_po_line_id;
lv_line_location_id_1 := lv_line_location_id;
lv_po_distribution_id_1 := lv_po_distribution_id;
lv_inventory_item_id_1 := lv_inventory_item_id;
lv_dist_code_concatenated := NULL;
lv_match_option := p_match_option;
lv_receipt_number := lv_receipt_num;
ELSIF rec_line.po_number IS NULL
THEN
lv_invoice_line_num := lv_invoice_line_num + 1;
lv_price_correction_flag := NULL;
lv_receipt_number := NULL;
lv_quant_inv := NULL;
lv_unit_price := NULL;
lv_quantity := NULL;
fnd_file.put_line (fnd_file.LOG,
'p_match_option :- '
|| p_match_option
);
fnd_file.put_line (fnd_file.LOG,
'lv_receipt_number :- '
|| lv_receipt_number
);
--
--
IF p_match_option = 'NON-PO'
THEN
BEGIN
lv_amount := rec_line.line_amount;
lv_po_header_id_1 := NULL;
lv_po_line_id_1 := NULL;
lv_line_location_id_1 := NULL;
lv_po_distribution_id_1 := NULL;
lv_inventory_item_id_1 := NULL;
lv_dist_code_concatenated :=
rec_line.dist_code_concatenated;
lv_match_option := NULL;
lv_receipt_number := NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'PO Data not found for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while getting PO Data for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
END;
ELSE
lv_amount := rec_line.line_amount;
lv_po_header_id_1 := NULL;
lv_po_line_id_1 := NULL;
lv_line_location_id_1 := NULL;
lv_po_distribution_id_1 := NULL;
lv_inventory_item_id_1 := NULL;
lv_dist_code_concatenated :=
rec_line.dist_code_concatenated;
lv_match_option := NULL;
lv_receipt_number := NULL;
END IF;
END IF;
/************************************************************************************************************************/
-------------------------------------------------------
-- Insert into Invoice line interface for line records
-------------------------------------------------------
--
--
fnd_file.put_line
(fnd_file.LOG,
'Inserting invoice lines table for po_distribution_id :- '
|| lv_po_distribution_id_1
);
fnd_file.put_line (fnd_file.LOG,
'lv_invoice_line_num :- '
|| lv_invoice_line_num
);
fnd_file.put_line (fnd_file.LOG,
'lv_quant_inv :- '
|| lv_quant_inv
);
fnd_file.put_line (fnd_file.LOG,
'lv_unit_price :- '
|| lv_unit_price
);
fnd_file.put_line (fnd_file.LOG,
'lv_amount :- ' || lv_amount
);
fnd_file.put_line (fnd_file.LOG,
'lv_po_header_id_1 :- '
|| lv_po_header_id_1
);
fnd_file.put_line (fnd_file.LOG,
'lv_po_line_id_1 :- '
|| lv_po_line_id_1
);
fnd_file.put_line
(fnd_file.LOG,
'lv_line_location_id_1 :- '
|| lv_line_location_id_1
);
fnd_file.put_line (fnd_file.LOG,
'lv_po_distribution_id_1 :- '
|| lv_po_distribution_id_1
);
fnd_file.put_line
(fnd_file.LOG,
'lv_inventory_item_id_1 :- '
|| lv_inventory_item_id_1
);
fnd_file.put_line
(fnd_file.LOG,
'lv_dist_code_concatenated :- '
|| lv_dist_code_concatenated
);
fnd_file.put_line (fnd_file.LOG,
'lv_match_option :- '
|| lv_match_option
);
--
INSERT INTO ap_invoice_lines_interface
(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
dist_code_concatenated,
description,
receipt_number,
quantity_invoiced,
unit_price,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
inventory_item_id,
match_option,
attribute8,
attribute6,
attribute7,
attribute9,
attribute3
)
VALUES (ap_invoices_interface_s.CURRVAL
,ap_invoice_lines_interface_s.NEXTVAL
,rec_line.line_number
,'ITEM'
,lv_amount
,rec_line.accounting_date
,pv_user_id
,SYSDATE
,pv_login_id
,pv_user_id
,SYSDATE
,lv_dist_code_concatenated
,rec_line.line_description
,lv_receipt_number
,lv_quant_inv
,lv_unit_price
,lv_po_header_id
,lv_po_line_id
,lv_line_location_id
,lv_po_distribution_id
,lv_inventory_item_id
,lv_match_option
,rec_line.activity_number
,rec_line.tech_number, rec_line.LOCATION
,rec_line.completion_date
,rec_line.dtv_account_number
);
fnd_file.put_line
(fnd_file.LOG,
'Updating custom table for po_distribution_id :- '
|| lv_po_distribution_id_1
);
--
ELSE
-------------------------------------------------------
-- Insert into Invoice line interface for line records
-------------------------------------------------------
--
--
fnd_file.put_line
(fnd_file.LOG,
'Inserting tax records to ap_invoice_lines_interface for non po line_number :- '
|| rec_line.line_number
);
--
INSERT INTO ap_invoice_lines_interface
(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
dist_code_concatenated,
description,
tax_code,
attribute8,
attribute6, attribute7,
attribute9,
attribute3
)
VALUES (ap_invoices_interface_s.CURRVAL
-- invoice_id
,
ap_invoice_lines_interface_s.NEXTVAL
-- invoice_line_id
,
rec_line.line_number -- line_number
,
'TAX' -- line_type_lookup_code
,
rec_line.line_amount -- amount
,
rec_line.accounting_date
-- SYSDATE -- accounting_date ,
pv_user_id -- last_updated_by
,
SYSDATE -- last_update_date
,
pv_login_id -- last_update_login
,
pv_user_id -- created_by
,
SYSDATE -- creation_date
,
rec_line.dist_code_concatenated
-- dist_code_concatenated
,
rec_line.line_description -- description
,
rec_line.tax_code -- tax_code
,
rec_line.activity_number,
rec_line.tech_number, rec_line.LOCATION,
rec_line.completion_date,
rec_line.dtv_account_number
);
--
END IF;
--
--
fnd_file.put_line (fnd_file.LOG,
'Updating stagging table ' || rec_line.ROWID
);
--
UPDATE AP_INV_EXT_TBL a
SET invoice_line_id =
ap_invoice_lines_interface_s.CURRVAL,
last_update_date = SYSDATE,
last_updated_by = pv_user_id,
process_flag = 'P',
conc_request_id = pv_conc_req_id,
po_header_id = lv_po_header_id_1,
po_line_id = lv_po_line_id_1,
po_line_location_id = lv_line_location_id_1,
po_distribution_id = lv_po_distribution_id_1,
inventory_item_id = lv_inventory_item_id_1,
org_id = pv_org_id
WHERE a.ROWID = rec_line.ROWID;
--
--
EXCEPTION
WHEN custom_line_exception
THEN
--
lv_fail_valid_cnt := lv_fail_valid_cnt + 1;
--
fnd_file.put_line (fnd_file.LOG,
'Custom error on Accounting Date '
);
UPDATE AP_INV_EXT_TBL a
SET last_update_date = SYSDATE,
last_updated_by = pv_user_id,
process_flag = 'E',
conc_request_id = pv_conc_req_id,
org_id = pv_org_id
WHERE a.ROWID = rec_line.ROWID;
--
END;
--
IF lv_rec_count >= 1000
THEN
--
COMMIT;
lv_rec_count := 0;
--
ELSE
lv_rec_count := lv_rec_count + 1;
END IF;
--
END LOOP;
/* fnd_file.put_line
(fnd_file.LOG,
'Invoice Source :- '
|| rec_invoice.SOURCE
);
SELECT LOOKUP_CODE into lv_invoice_source
FROM ap_lookup_codes
WHERE lookup_type = 'SOURCE'
and displayed_field = rec_invoice.SOURCE;
fnd_file.put_line
(fnd_file.LOG,
'Invoice Source Lookup Code :- '
|| lv_invoice_source
);
*/
lv_invoice_source := rec_invoice.SOURCE;
END LOOP;
SELECT COUNT (*)
INTO lv_record_cnt
FROM ap_invoices_interface api
WHERE api.GROUP_ID = lv_group;
fnd_file.put_line
(fnd_file.LOG,
'number of records uploaded to interface :- '
|| lv_record_cnt
);
IF NVL (lv_record_cnt, 0) > 0
THEN
--
fnd_file.put_line (fnd_file.LOG,
'lv_gl_date :- '
|| TO_CHAR (lv_gl_date,
'YYYY/MM/DD HH24:MI:SS'
)
);
-------------------------------------------------
-- Submit Payables Open Interface Import request
-------------------------------------------------
lv_request_id :=
fnd_request.submit_request
(lv_app_short_name,
lv_prog_name,
'ONS Bulk AP Invoice',
'' , -- start_time
FALSE, -- subsequent ,
pv_org_id,
lv_invoice_source, -- Source
lv_group, -- Group
lv_group, -- Batch Name
'', -- Hold Name
'', -- Hold Reason
TO_CHAR (lv_gl_date,'YYYY/MM/DD HH24:MI:SS'), -- GL Date
'Y', -- Purge
'N', -- Trace switch
'N', -- Debug switch
'N', -- Summarize Report
'1000' , -- Commit Batch Size
pv_user_id , -- User Id
pv_login_id -- Login Id
);
lv_invoice_source := NULL;
IF lv_request_id = 0
THEN
fnd_file.put_line (fnd_file.LOG, 'Request not successfull');
RAISE custom_exception;
ELSE
COMMIT;
fnd_file.put_line (fnd_file.LOG,
'Request successfull and the id is '
|| TO_CHAR (lv_request_id)
);
lv_req_done :=
fnd_concurrent.wait_for_request
(lv_request_id,
3 -- seconds to wait before next check
,
(1000 * 60
) -- Wait for max of 1000 minutes
,
lv_phase,
lv_status,
lv_dev_phase,
lv_dev_status,
lv_message
);
END IF;
--
END IF; -- Statement added by skb on 20-Jul-2009
END LOOP; -- END LOOP #1
--end if;
--end loop;
END;
--
COMMIT;
--
fnd_file.put_line (fnd_file.LOG,
'Calling PO Matched AP Invoice upload procedure'
);
--
-------------------------------------------------------
-- count total number of records uploaded to interface
-------------------------------------------------------
--
----------------------------------------------
-- Following code
----------------------------------------------
fnd_file.put_line (fnd_file.LOG,
'Request id for AP Invoice Bulk Upload :-'
|| lv_request_id
);
-------------------------------
-- Invoices record processed
-------------------------------
SELECT COUNT (aid.invoice_id)
INTO lv_invoice_processed
FROM ap_invoices_all aia, ap_invoice_distributions_all aid
WHERE aia.invoice_id = aid.invoice_id
AND invoice_num IN (SELECT DISTINCT TRIM (invoice_num)
FROM AP_INV_EXT_TBL
WHERE conc_request_id = pv_conc_req_id);
--
fnd_file.put_line (fnd_file.LOG,
'lv_invoice_processed :- ' || lv_invoice_processed
);
-------------------------------
-- Invoices record rejected
-------------------------------
SELECT COUNT (*)
INTO lv_invoice_rejected
FROM ap_invoices_interface api, ap_invoice_lines_interface apil
WHERE api.GROUP_ID = lv_group
AND api.status = 'REJECTED'
AND api.invoice_id = apil.invoice_id;
--
fnd_file.put_line (fnd_file.LOG,
'lv_invoice_rejected :- ' || lv_invoice_rejected
);
fnd_file.put_line (fnd_file.output,
'Total Number of records processed :- '
|| lv_invoice_processed
);
fnd_file.put_line (fnd_file.output,
'Total Number of records rejected :- '
|| lv_invoice_rejected
);
fnd_file.put_line (fnd_file.output,
'Total Number of records failed validation :- '
|| lv_fail_valid_cnt
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line
(fnd_file.output,
'For rejected records Please check Payables Open Interface Import process'
);
--
-----------------------------------------------------------
-- If no record uploaded to interface or atleast one record
-- is rejected then complete the process with warning
-----------------------------------------------------------
/*
IF ( lv_record_cnt = 0
OR lv_invoice_processed = 0
OR lv_invoice_rejected > 0
)
THEN
--
retcode := 1; -- Warning, because no record processed
--
END IF;
*/
--
-- Deleting seven days old records, as there is no need to store old records in this process.
--
fnd_file.put_line (fnd_file.LOG, 'Deleting seven days old records');
--
DELETE FROM AP_INV_EXT_TBL
WHERE creation_date <= SYSDATE - 7;
--
COMMIT;
--
EXCEPTION
WHEN custom_exception
THEN
fnd_file.put_line
(fnd_file.LOG,
'ERROR! Custom error In ap_invoice_main of AP Invoice upload : '
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'ERROR! In ap_invoice_main of AP Invoice upload : '
|| SQLERRM
);
retcode := 1; -- Warning
END ap_invoice_main;
--
END AP_INVOICE_UPLOAD;
/
PROCEDURE ap_invoice_call ( errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_gl_date IN VARCHAR2
, p_match_option IN VARCHAR2
) ;
--
------------------------------------------------------------
-- Main Procedure of AP Invoice upload
------------------------------------------------------------
PROCEDURE AP_INVOICE_MAIN ( errbuf OUT VARCHAR2
, retcode OUT NUMBER
, p_gl_date IN VARCHAR2
, p_match_option IN VARCHAR2
) ;
--
--
END AP_INVOICE_UPLOAD ;
################################################################################
CREATE OR REPLACE PACKAGE BODY APPS.AP_INVOICE_UPLOAD
IS
------------------------------------------------------------------------------
pv_user_id VARCHAR2 (30) := NVL (fnd_profile.VALUE ('USER_ID'), '1');
pv_login_id VARCHAR2 (30) := NVL (fnd_profile.VALUE ('LOGIN_ID'), '1');
pv_conc_req_id NUMBER := fnd_global.conc_request_id;
pv_org_id VARCHAR2 (10) := NVL (fnd_profile.VALUE ('ORG_ID'), '1');
pv_responsibility_id VARCHAR2 (30) := NVL (fnd_profile.VALUE ('RESP_ID'), '1');
pv_respappl_id VARCHAR2 (30) := NVL (fnd_profile.VALUE ('RESP_APPL_ID'), '1');
------------------------------------------------------------------------------
-- Function returns whether a given period is open or not
------------------------------------------------------------------------------
FUNCTION mas_gl_period_sts ( p_appl_short_name VARCHAR2
, p_date DATE
)
RETURN VARCHAR2 IS
--
lv_period_status gl_period_statuses.closing_status%TYPE;
--
BEGIN
SELECT closing_status
INTO lv_period_status
FROM gl_period_statuses a
, fnd_application b
WHERE TRUNC (p_date) BETWEEN start_date AND end_date
AND a.application_id = b.application_id
AND b.application_short_name = p_appl_short_name --'SQLAP'
AND a.set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');
IF NVL (lv_period_status, 'X') = 'O' THEN
RETURN lv_period_status; --TRUE;
ELSE
RETURN NULL ;
END IF;
--
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_file.put_line (fnd_file.LOG , 'ERROR! Period not found for date '|| p_date|| SQLERRM);
RETURN NULL ;
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG , 'ERROR! Period not found for date '|| p_date|| SQLERRM);
RETURN NULL ;
END mas_gl_period_sts ;
--
------------------------------------------------------------------------------
-- Output Report Procedure
------------------------------------------------------------------------------
--
--
------------------------------------------------------------
-- Main Procedure of AP Invoice upload Call
------------------------------------------------------------
PROCEDURE ap_invoice_call (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_gl_date IN VARCHAR2,
p_match_option IN VARCHAR2
)
IS
--
lv_prog_name VARCHAR2 (30) := 'APINVLOAD';
lv_prog_name1 VARCHAR2 (30) := 'APINVRECLOAD';
lv_app_short_name VARCHAR2 (30) := 'TEST';
custom_exception EXCEPTION;
lv_phase VARCHAR2 (100);
lv_status VARCHAR2 (100);
lv_dev_phase VARCHAR2 (100);
lv_dev_status VARCHAR2 (100);
lv_message VARCHAR2 (200);
lv_req_done BOOLEAN;
lv_req_done1 BOOLEAN;
lv_request_id NUMBER;
lv_request_id1 NUMBER;
lv_record_loaded NUMBER; --
BEGIN
--
DBMS_OUTPUT.put_line ('Inside ap_invoice_call');
fnd_file.put_line (fnd_file.LOG, 'Inside ap_invoice_call');
-- Submit Payables MasTec (AT) AP Invoice Record Loader
-----------------------------------------------------
fnd_file.put_line (fnd_file.LOG,
' AP Invoice Data Upload'
);
lv_request_id1 :=
fnd_request.submit_request
(lv_app_short_name,
lv_prog_name1,
' AP Invoice Data Upload'
,''
,FALSE
);
--
IF lv_request_id1 = 0
THEN
--
DBMS_OUTPUT.put_line ('Request not successfull');
fnd_file.put_line (fnd_file.LOG, 'Request not successfull');
RAISE custom_exception;
--
ELSE
COMMIT;
DBMS_OUTPUT.put_line ( 'Request sucessful and the id is '
|| TO_CHAR (lv_request_id1)
);
fnd_file.put_line (fnd_file.LOG,
'Request sucessful and the id is '
|| TO_CHAR (lv_request_id1)
);
--
lv_req_done1 :=
fnd_concurrent.wait_for_request
(lv_request_id1,
3,
(1000 * 60),
lv_phase,
lv_status,
lv_dev_phase,
lv_dev_status,
lv_message
);
END IF;
SELECT COUNT (invoice_num)
INTO lv_record_loaded
FROM AP_INV_EXT_TBL
WHERE process_flag = 'N';
DBMS_OUTPUT.put_line ( 'Total number of records loaded :- '
|| lv_record_loaded
);
fnd_file.put_line (fnd_file.LOG,
'Total number of records loaded :- '
|| lv_record_loaded
);
--
IF NVL (lv_record_loaded, 0) != 0
THEN
-----------------------------------------------------
-- Submit Payables MasTec ONS AP Invoice Bulk Upload
-----------------------------------------------------
DBMS_OUTPUT.put_line (' AP Invoice Upload Program.');
fnd_file.put_line (fnd_file.LOG,
' AP Invoice Upload Program.'
);
--
fnd_file.put_line (fnd_file.LOG, 'Date' || p_gl_date);
--- lv_gl_date := TO_DATE (p_gl_date, 'MM/DD/RRRR');
lv_request_id :=
fnd_request.submit_request
(lv_app_short_name,
lv_prog_name,
' AP Invoice Upload Program',
'',
FALSE,
p_gl_date,
p_match_option
);
--
IF lv_request_id = 0
THEN -- IF#2
--
DBMS_OUTPUT.put_line ('Request not successfull');
fnd_file.put_line (fnd_file.LOG, 'Request not successfull');
RAISE custom_exception;
--
ELSE
COMMIT;
DBMS_OUTPUT.put_line ( 'Request sucessful and the id is '
|| TO_CHAR (lv_request_id)
);
fnd_file.put_line (fnd_file.LOG,
'Request sucessful and the id is '
|| TO_CHAR (lv_request_id)
);
--
lv_req_done :=
fnd_concurrent.wait_for_request
(lv_request_id,
3,
(1000 * 60),
lv_phase,
lv_status,
lv_dev_phase,
lv_dev_status,
lv_message
);
--
END IF;
--
--
END IF;
--
----------------------------------------------
----------------------------------------------
DBMS_OUTPUT.put_line ( 'Request id for AP Invoice Upload Program :-'
|| lv_request_id
);
fnd_file.put_line (fnd_file.LOG,
'Request id for AP Invoice Upload Program :-'
|| lv_request_id
);
DBMS_OUTPUT.put_line ('End ap_invoice_call');
fnd_file.put_line (fnd_file.LOG, 'End ap_invoice_call');
--
EXCEPTION
WHEN custom_exception
THEN
DBMS_OUTPUT.put_line
('ERROR! Custom error In ap_invoice_call of AP Invoice upload Call : '
);
fnd_file.put_line
(fnd_file.LOG,
'ERROR! Custom error In ap_invoice_call of AP Invoice upload Call : '
);
WHEN OTHERS
THEN
IF SQLCODE = -29913
THEN
--
retcode := SQLCODE;
DBMS_OUTPUT.put_line ('Data File Not found, ' || retcode);
fnd_file.put_line (fnd_file.LOG,
'Data File Not found, ' || retcode
);
--
ELSE
--
DBMS_OUTPUT.put_line
( 'ERROR! In ap_invoice_call of AP Invoice upload Call : '
|| SQLERRM
);
fnd_file.put_line
(fnd_file.LOG,
'ERROR! In ap_invoice_call of AP Invoice upload Call : '
|| SQLERRM
);
retcode := 1; -- Warning
--
END IF;
END ap_invoice_call;
--
------------------------------------------------------------
-- Main Procedure of AP Invoice upload
------------------------------------------------------------
PROCEDURE ap_invoice_main (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_gl_date IN VARCHAR2,
p_match_option IN VARCHAR2
)
IS
CURSOR C_PAYMENT IS
SELECT * FROM AP_INV_EXT_TBL WHERE PROCESS_FLAG = 'N';
CURSOR c_dup_inv
IS
SELECT DISTINCT INVOICE_NUM,
COUNT (DISTINCT PAYMENT_TERM) V_PAYTERM
FROM AP_INV_EXT_TBL
WHERE PROCESS_FLAG = 'N'
GROUP BY INVOICE_NUM;
CURSOR c_source
IS
SELECT DISTINCT SOURCE
FROM AP_INV_EXT_TBL
WHERE PROCESS_FLAG = 'N';
CURSOR c_invoice (p_source VARCHAR2)
IS
SELECT DISTINCT TRIM (INVOICE_NUM) INVOICE_NUM, TRIM (VENDOR_NUM) VENDOR_NUM,
TRIM (VENDOR_SITE_CODE) VENDOR_SITE_CODE,
TRIM (INVOICE_AMOUNT) INVOICE_AMOUNT, TRIM (SOURCE) SOURCE,
TRUNC (INVOICE_DATE) INVOICE_DATE,
TRIM (INVOICE_DESCRIPTION) INVOICE_DESCRIPTION,
TRIM (PAYMENT_TERM) PAYMENT_TERM
FROM AP_INV_EXT_TBL
WHERE PROCESS_FLAG = 'N' AND SOURCE = P_SOURCE
ORDER BY PAYMENT_TERM;
CURSOR C_LINE (
P_INVOICE_NUM VARCHAR2,
P_VENDOR_NUM VARCHAR2,
P_VENDOR_SITE_CODE VARCHAR2
)
IS
SELECT TRIM (LINE_NUMBER) LINE_NUMBER, TRIM (LINE_AMOUNT) LINE_AMOUNT,
TRIM (DIST_CODE_CONCATENATED) DIST_CODE_CONCATENATED,
TRIM (LINE_DESCRIPTION) LINE_DESCRIPTION, TRIM (TAX_CODE) TAX_CODE,
TRUNC (ACCOUNTING_DATE) ACCOUNTING_DATE,
TRIM (ACTIVITY_NUMBER) ACTIVITY_NUMBER, TRIM (TECH_NUMBER) TECH_NUMBER,
TRIM (LOCATION) LOCATION, TRIM (COMPLETION_DATE) COMPLETION_DATE,
PO_NUMBER, PO_LINE_NUMBER, QUANT_INV, UNIT_PRICE,
TRIM (SUBSTR (DTV_ACCOUNT_NUMBER, 1, 30)) DTV_ACCOUNT_NUMBER, ROWID
FROM AP_INV_EXT_TBL
WHERE TRIM (INVOICE_NUM) = P_INVOICE_NUM
AND TRIM (VENDOR_NUM) = P_VENDOR_NUM
AND TRIM (VENDOR_SITE_CODE) = P_VENDOR_SITE_CODE
AND PROCESS_FLAG = 'N';
lv_price_correction_flag VARCHAR2 (2);
lv_receipt_number VARCHAR2 (40);
lv_receipt_num VARCHAR2 (40);
lv_invoice_line_id NUMBER;
lv_quant_inv NUMBER;
lv_unit_price NUMBER;
lv_invoice_line_num NUMBER := 0;
lv_amount NUMBER := 0;
lv_po_header_id NUMBER := 0;
lv_po_line_id NUMBER := 0;
lv_line_location_id NUMBER := 0;
lv_po_distribution_id NUMBER := 0;
lv_inventory_item_id NUMBER := 0;
lv_po_header_id_1 NUMBER := 0;
lv_po_line_id_1 NUMBER := 0;
lv_line_location_id_1 NUMBER := 0;
lv_po_distribution_id_1 NUMBER := 0;
lv_inventory_item_id_1 NUMBER := 0;
lv_dist_code_concatenated VARCHAR2 (42);
lv_match_option VARCHAR2 (5);
-- Added
lv_project_num VARCHAR2(42);
ln_project_id NUMBER := 0;
lv_po_num VARCHAR2(42);
lv_po_number VARCHAR2(42);
lv_task_name VARCHAR2(42);
lv_task_id NUMBER := 0;
ln_task_id NUMBER := 0;
lv_expenditure_type VARCHAR2(60);
lv_pa_add_flag VARCHAR2(42) :='N';
lv_po_ship_num NUMBER := 0;
lv_po_dist_num NUMBER := 0;
lv_expenditure_date DATE;
lv_pro_acc_cont VARCHAR2(60);
lv_expenditure_org_id NUMBER := 0;
lv_final_match_flag VARCHAR2(42) :='N';
lv_code_combi_id NUMBER := 0;
-- Added
lv_quantity NUMBER := 0;
lv_invoice_type VARCHAR2 (20);
lv_group VARCHAR2 (40);
lv_gl_period_status VARCHAR2 (20);
lv_request_id NUMBER;
lv_invoice_source VARCHAR2 (40);
lv_prog_name VARCHAR2 (30) := 'APXIIMPT';
lv_app_short_name VARCHAR2 (30) := 'SQLAP';
lv_phase VARCHAR2 (100);
lv_status VARCHAR2 (100);
lv_dev_phase VARCHAR2 (100);
lv_dev_status VARCHAR2 (100);
lv_message VARCHAR2 (200);
lv_rec_count NUMBER := 0;
custom_exception EXCEPTION;
lv_req_done BOOLEAN;
lv_gl_date DATE;
lv_invoice_processed NUMBER;
lv_invoice_rejected NUMBER;
lv_record_cnt NUMBER := 0;
lv_account_dt_status VARCHAR2 (20);
custom_line_exception EXCEPTION;
lv_fail_valid_cnt NUMBER := 0;
lv_term_id NUMBER;
v_payment varchar2(100);
v_org_id number;
--
BEGIN
fnd_client_info.set_org_context(pv_org_id);
fnd_global.apps_initialize ( user_id => pv_user_id
,resp_id => pv_responsibility_id
,resp_appl_id => pv_respappl_id);
fnd_global.set_nls_context('AMERICAN');
mo_global.set_policy_context('S',pv_org_id);
fnd_file.put_line (fnd_file.LOG, 'org' || pv_org_id);
fnd_file.put_line (fnd_file.LOG, 'AP Invoice upload');
lv_group := 'AUTO_LOAD' || pv_conc_req_id;
fnd_file.put_line (fnd_file.LOG, 'lv_group :- ' || lv_group);
fnd_file.put_line (fnd_file.LOG, 'p_gl_date :- ' || p_gl_date);
lv_gl_date := TO_DATE (p_gl_date, 'MM/DD/RRRR');
lv_gl_period_status := mas_gl_period_sts ('SQLGL', lv_gl_date);
--
IF NVL (lv_gl_period_status, 'x') != 'O'
THEN
fnd_file.put_line
(fnd_file.LOG,
'Period is not open for GL Date you have passed :- '
|| lv_gl_date
);
RAISE custom_exception;
END IF;
for rec_payment in c_payment loop
IF rec_payment.ORG_ID IS NULL THEN
update AP_INV_EXT_TBL
set org_id= pv_org_id
where invoice_num = rec_payment.invoice_num;
END IF;
commit;
end loop;
------------------------------------------------------
-- Delete the old rejected records AP Interface table
------------------------------------------------------
--
DELETE FROM ap_invoice_lines_interface apl
WHERE apl.invoice_id IN (
SELECT ap.invoice_id
FROM AP_INV_EXT_TBL ma, ap_invoices_interface ap
WHERE ma.process_flag = 'N'
AND ap.invoice_num = ma.invoice_num);
--
fnd_file.put_line
(fnd_file.LOG,
'Records deleted from ap_invoice_lines_interface table :- '
|| SQL%ROWCOUNT
);
--
DELETE FROM ap_invoices_interface
WHERE invoice_num IN (SELECT invoice_num
FROM AP_INV_EXT_TBL
WHERE process_flag = 'N');
--
fnd_file.put_line
(fnd_file.LOG,
'Records deleted from ap_invoices_interface table :- '
|| SQL%ROWCOUNT
);
BEGIN
FOR rec_dup_inv IN c_dup_inv
LOOP
IF rec_dup_inv.v_payterm >= 2
THEN
fnd_file.put_line (fnd_file.LOG,
'Same Invoice Number :'
|| rec_dup_inv.invoice_num
|| ' Consist more than 1 Payment Term'
);
UPDATE AP_INV_EXT_TBL
SET process_flag = 'E'
WHERE invoice_num = rec_dup_inv.invoice_num;
END IF;
END LOOP;
FOR rec_source IN c_source
LOOP
SELECT rec_source.SOURCE || '-'
|| TO_CHAR (SYSDATE, 'ddmmyyyyhh24')
INTO lv_group
FROM DUAL;
fnd_file.put_line (fnd_file.LOG, 'DATA PROCESS');
FOR rec_invoice IN c_invoice (rec_source.SOURCE)
LOOP
IF rec_invoice.invoice_amount > 0
THEN
lv_invoice_type := 'STANDARD';
ELSE
lv_invoice_type := 'CREDIT';
END IF;
BEGIN
SELECT term_id
INTO lv_term_id
FROM ap_terms_tl
WHERE UPPER (TRIM (NAME)) =
UPPER (TRIM (rec_invoice.payment_term));
EXCEPTION
WHEN OTHERS
THEN
lv_term_id := NULL;
END;
--
fnd_file.put_line
(fnd_file.LOG,
'Inserting records to ap_invoices_interface for non po invoice num :- '
|| rec_invoice.invoice_num
);
--
INSERT INTO ap_invoices_interface
(invoice_id,
invoice_num,
invoice_date,
vendor_num,
vendor_site_code,
invoice_amount,
description,
last_update_date,
last_updated_by,
last_update_login,
creation_date, created_by,
SOURCE, GROUP_ID,
invoice_type_lookup_code,
terms_id
)
VALUES (ap_invoices_interface_s.NEXTVAL -- invoice_id
,rec_invoice.invoice_num -- invoice_num
,rec_invoice.invoice_date -- invoice_date
,rec_invoice.vendor_num -- vendor_num
,rec_invoice.vendor_site_code -- vendor_site_code
,rec_invoice.invoice_amount -- invoice_amount
,rec_invoice.invoice_description -- description
,SYSDATE -- last_update_date
,pv_user_id -- last_updated_by
,pv_login_id -- last_update_login
,SYSDATE -- creation_date
,pv_user_id -- created_by
,rec_invoice.SOURCE -- source
,lv_group -- group_id
,lv_invoice_type -- invoice_type_lookup_code
,lv_term_id
);
------------------------------------------------
-- Cursor Open for AP Invoice header records
------------------------------------------------
FOR rec_line IN
c_line (p_invoice_num => rec_invoice.invoice_num,
p_vendor_num => rec_invoice.vendor_num,
p_vendor_site_code => rec_invoice.vendor_site_code
)
LOOP -- LOOP #2
--
BEGIN
lv_account_dt_status :=
mas_gl_period_sts
('SQLGL',
rec_line.accounting_date
);
--
IF NVL (lv_account_dt_status, 'x') != 'O'
THEN
-- Period is not open for accounting date
fnd_file.put_line
(fnd_file.LOG,
'Period is not open for Accounting Date you have passed :- '
|| rec_line.accounting_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
--
END IF;
--
--
IF (rec_line.accounting_date < rec_invoice.invoice_date
)
THEN
-- Accounting date can not be less than the Invoice date
fnd_file.put_line
(fnd_file.LOG,
'Accounting date can not be less than the Invoice date, for Accounting Date :- '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
--
END IF;
--
--
IF rec_line.tax_code IS NULL
THEN
lv_amount := 0;
lv_po_header_id := NULL;
lv_po_line_id := NULL;
lv_line_location_id := NULL;
lv_po_distribution_id := NULL;
lv_inventory_item_id := NULL;
lv_po_header_id_1 := NULL;
lv_po_line_id_1 := NULL;
lv_line_location_id_1 := NULL;
lv_po_distribution_id_1 := NULL;
lv_inventory_item_id_1 := NULL;
lv_match_option := NULL;
lv_dist_code_concatenated := NULL;
IF rec_line.po_number IS NOT NULL
THEN
lv_invoice_line_num := lv_invoice_line_num + 1;
-- Added by skb on 02-Dec-2009
lv_price_correction_flag := NULL;
lv_receipt_number := NULL;
lv_quant_inv := NULL;
lv_unit_price := NULL;
lv_quantity := NULL;
fnd_file.put_line (fnd_file.LOG,
'p_match_option :- '
|| p_match_option
);
fnd_file.put_line (fnd_file.LOG,
'lv_receipt_number :- '
|| lv_receipt_number
);
--
--
IF p_match_option = 'P'
THEN
BEGIN
-- New project related information
SELECT DISTINCT c.po_header_id,
b.po_line_id,
c.segment1,
b.line_location_id,
b.po_distribution_id,
b.distribution_num,
a.unit_price,
(a.quantity - b.quantity_billed),
NULL,
a.item_id,
g.SEGMENT1 "Project Number",
h.TASK_NAME,
h.TASK_ID,
b.EXPENDITURE_TYPE,
b.EXPENDITURE_ITEM_DATE,
b.PROJECT_ACCOUNTING_CONTEXT,
b.EXPENDITURE_ORGANIZATION_ID,
j.SHIPMENT_NUM
INTO lv_po_header_id,
lv_po_line_id,
lv_po_num,
lv_line_location_id,
lv_po_distribution_id,
lv_po_dist_num,
lv_unit_price,
lv_quantity,
lv_receipt_num,
lv_inventory_item_id,
lv_project_num,
lv_task_name,
lv_task_id,
lv_expenditure_type,
lv_expenditure_date,
lv_pro_acc_cont,
lv_expenditure_org_id,
lv_po_ship_num
FROM po_lines_all a,
po_distributions_all b,
po_headers_all c,
pa_projects_all g,
pa_tasks h,
pa_project_statuses i,
po_line_locations_all j
WHERE 1 = 1
AND c.segment1 = rec_line.po_number
AND a.line_num = NVL (rec_line.po_line_number, a.line_num)
AND c.po_header_id = a.po_header_id
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND b.PROJECT_ID = g.PROJECT_ID
AND b.TASK_ID = h.TASK_ID
AND g.project_status_code = i.project_status_code
AND j.po_line_id = a.po_line_id
AND j.line_location_id = b.line_location_id
AND (a.quantity - b.quantity_billed) > 0
AND NVL (a.cancel_flag, 'N') <> 'Y'
and i.project_system_status_code = ('APPROVED');
/* SELECT c.po_header_id, b.po_line_id,
b.line_location_id,
b.po_distribution_id,
a.unit_price,
(a.quantity - b.quantity_billed
),
NULL, a.item_id
INTO lv_po_header_id, lv_po_line_id,
lv_line_location_id,
lv_po_distribution_id,
lv_unit_price,
lv_quantity,
lv_receipt_num, lv_inventory_item_id
FROM po_lines_all a,
po_distributions_ap_v b,
po_headers_all c
WHERE c.segment1 = rec_line.po_number
AND c.po_header_id = a.po_header_id
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND NVL (a.cancel_flag, 'N') <> 'Y'
AND a.line_num =
NVL (rec_line.po_line_number,
a.line_num
)
AND (a.quantity - b.quantity_billed) > 0; */
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'PO Data not found for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while getting PO Data for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
END;
-- Added
UPDATE XXMTZ_ONS_AP_INV_EXT_TBL SET
Project_number = lv_project_num,
Task_name = lv_task_name,
expenditure_type = lv_expenditure_type
WHERE po_number = rec_line.po_number
AND po_line_number = NVL (rec_line.po_line_number, po_line_number);
-- Added
ELSIF p_match_option = 'R'
THEN
BEGIN
SELECT c.po_header_id, b.po_line_id,
b.line_location_id,
b.po_distribution_id,
a.unit_price,
(f.primary_quantity
- b.quantity_billed
) quantity,
e.receipt_num,
a.item_id inventory_item_id
INTO lv_po_header_id, lv_po_line_id,
lv_line_location_id,
lv_po_distribution_id,
lv_unit_price,
lv_quantity,
lv_receipt_num,
lv_inventory_item_id
FROM po_lines_all a,
po_distributions_ap_v b,
po_headers_all c,
rcv_shipment_headers e,
rcv_transactions f
WHERE c.segment1 = rec_line.po_number
AND c.po_header_id = a.po_header_id
AND a.po_header_id = b.po_header_id
AND a.po_line_id = b.po_line_id
AND NVL (a.cancel_flag, 'N') <> 'Y'
AND a.line_num =
NVL (rec_line.po_line_number,
a.line_num
)
AND e.shipment_header_id =
f.shipment_header_id
AND f.transaction_type = 'RECEIVE'
AND b.po_distribution_id =
f.po_distribution_id
AND (a.quantity - b.quantity_billed) > 0;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'PO Data not found for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while getting PO Data for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
END;
END IF;
--
-- Populate the invoiced quantity if different from po quantity
--
IF ( rec_line.quant_inv <> lv_quantity
AND rec_line.quant_inv IS NOT NULL
)
THEN
--
lv_quant_inv := rec_line.quant_inv;
--
ELSE
--
lv_quant_inv := lv_quantity;
--
END IF;
--
fnd_file.put_line (fnd_file.LOG,
'lv_quant_inv :- '
|| lv_quant_inv
);
--
-- Populate the invoiced unit_price if different from po unit_price
--
IF ( rec_line.unit_price <> lv_unit_price
AND rec_line.unit_price IS NOT NULL
)
THEN
--
lv_unit_price := rec_line.unit_price;
lv_price_correction_flag := 'Y';
--
ELSE
--
lv_unit_price := lv_unit_price;
lv_price_correction_flag := NULL;
--
END IF;
--
fnd_file.put_line (fnd_file.LOG,
'lv_unit_price :- '
|| lv_unit_price
);
fnd_file.put_line
(fnd_file.LOG,
'lv_price_correction_flag :- '
|| lv_price_correction_flag
);
lv_amount := lv_unit_price * lv_quant_inv;
lv_po_header_id_1 := lv_po_header_id;
lv_po_line_id_1 := lv_po_line_id;
lv_line_location_id_1 := lv_line_location_id;
lv_po_distribution_id_1 := lv_po_distribution_id;
lv_inventory_item_id_1 := lv_inventory_item_id;
lv_dist_code_concatenated := NULL;
lv_match_option := p_match_option;
lv_receipt_number := lv_receipt_num;
ELSIF rec_line.po_number IS NULL
THEN
lv_invoice_line_num := lv_invoice_line_num + 1;
lv_price_correction_flag := NULL;
lv_receipt_number := NULL;
lv_quant_inv := NULL;
lv_unit_price := NULL;
lv_quantity := NULL;
fnd_file.put_line (fnd_file.LOG,
'p_match_option :- '
|| p_match_option
);
fnd_file.put_line (fnd_file.LOG,
'lv_receipt_number :- '
|| lv_receipt_number
);
--
--
IF p_match_option = 'NON-PO'
THEN
BEGIN
lv_amount := rec_line.line_amount;
lv_po_header_id_1 := NULL;
lv_po_line_id_1 := NULL;
lv_line_location_id_1 := NULL;
lv_po_distribution_id_1 := NULL;
lv_inventory_item_id_1 := NULL;
lv_dist_code_concatenated :=
rec_line.dist_code_concatenated;
lv_match_option := NULL;
lv_receipt_number := NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
fnd_file.put_line
(fnd_file.LOG,
'PO Data not found for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error while getting PO Data for '
|| rec_line.accounting_date
|| 'Invoice Date :- '
|| rec_invoice.invoice_date
|| 'Invoice_num :- '
|| rec_invoice.invoice_num
|| 'Line num :- '
|| rec_line.line_number
);
--
RAISE custom_line_exception;
END;
ELSE
lv_amount := rec_line.line_amount;
lv_po_header_id_1 := NULL;
lv_po_line_id_1 := NULL;
lv_line_location_id_1 := NULL;
lv_po_distribution_id_1 := NULL;
lv_inventory_item_id_1 := NULL;
lv_dist_code_concatenated :=
rec_line.dist_code_concatenated;
lv_match_option := NULL;
lv_receipt_number := NULL;
END IF;
END IF;
/************************************************************************************************************************/
-------------------------------------------------------
-- Insert into Invoice line interface for line records
-------------------------------------------------------
--
--
fnd_file.put_line
(fnd_file.LOG,
'Inserting invoice lines table for po_distribution_id :- '
|| lv_po_distribution_id_1
);
fnd_file.put_line (fnd_file.LOG,
'lv_invoice_line_num :- '
|| lv_invoice_line_num
);
fnd_file.put_line (fnd_file.LOG,
'lv_quant_inv :- '
|| lv_quant_inv
);
fnd_file.put_line (fnd_file.LOG,
'lv_unit_price :- '
|| lv_unit_price
);
fnd_file.put_line (fnd_file.LOG,
'lv_amount :- ' || lv_amount
);
fnd_file.put_line (fnd_file.LOG,
'lv_po_header_id_1 :- '
|| lv_po_header_id_1
);
fnd_file.put_line (fnd_file.LOG,
'lv_po_line_id_1 :- '
|| lv_po_line_id_1
);
fnd_file.put_line
(fnd_file.LOG,
'lv_line_location_id_1 :- '
|| lv_line_location_id_1
);
fnd_file.put_line (fnd_file.LOG,
'lv_po_distribution_id_1 :- '
|| lv_po_distribution_id_1
);
fnd_file.put_line
(fnd_file.LOG,
'lv_inventory_item_id_1 :- '
|| lv_inventory_item_id_1
);
fnd_file.put_line
(fnd_file.LOG,
'lv_dist_code_concatenated :- '
|| lv_dist_code_concatenated
);
fnd_file.put_line (fnd_file.LOG,
'lv_match_option :- '
|| lv_match_option
);
--
INSERT INTO ap_invoice_lines_interface
(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
dist_code_concatenated,
description,
receipt_number,
quantity_invoiced,
unit_price,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
inventory_item_id,
match_option,
attribute8,
attribute6,
attribute7,
attribute9,
attribute3
)
VALUES (ap_invoices_interface_s.CURRVAL
,ap_invoice_lines_interface_s.NEXTVAL
,rec_line.line_number
,'ITEM'
,lv_amount
,rec_line.accounting_date
,pv_user_id
,SYSDATE
,pv_login_id
,pv_user_id
,SYSDATE
,lv_dist_code_concatenated
,rec_line.line_description
,lv_receipt_number
,lv_quant_inv
,lv_unit_price
,lv_po_header_id
,lv_po_line_id
,lv_line_location_id
,lv_po_distribution_id
,lv_inventory_item_id
,lv_match_option
,rec_line.activity_number
,rec_line.tech_number, rec_line.LOCATION
,rec_line.completion_date
,rec_line.dtv_account_number
);
fnd_file.put_line
(fnd_file.LOG,
'Updating custom table for po_distribution_id :- '
|| lv_po_distribution_id_1
);
--
ELSE
-------------------------------------------------------
-- Insert into Invoice line interface for line records
-------------------------------------------------------
--
--
fnd_file.put_line
(fnd_file.LOG,
'Inserting tax records to ap_invoice_lines_interface for non po line_number :- '
|| rec_line.line_number
);
--
INSERT INTO ap_invoice_lines_interface
(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
accounting_date,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
dist_code_concatenated,
description,
tax_code,
attribute8,
attribute6, attribute7,
attribute9,
attribute3
)
VALUES (ap_invoices_interface_s.CURRVAL
-- invoice_id
,
ap_invoice_lines_interface_s.NEXTVAL
-- invoice_line_id
,
rec_line.line_number -- line_number
,
'TAX' -- line_type_lookup_code
,
rec_line.line_amount -- amount
,
rec_line.accounting_date
-- SYSDATE -- accounting_date ,
pv_user_id -- last_updated_by
,
SYSDATE -- last_update_date
,
pv_login_id -- last_update_login
,
pv_user_id -- created_by
,
SYSDATE -- creation_date
,
rec_line.dist_code_concatenated
-- dist_code_concatenated
,
rec_line.line_description -- description
,
rec_line.tax_code -- tax_code
,
rec_line.activity_number,
rec_line.tech_number, rec_line.LOCATION,
rec_line.completion_date,
rec_line.dtv_account_number
);
--
END IF;
--
--
fnd_file.put_line (fnd_file.LOG,
'Updating stagging table ' || rec_line.ROWID
);
--
UPDATE AP_INV_EXT_TBL a
SET invoice_line_id =
ap_invoice_lines_interface_s.CURRVAL,
last_update_date = SYSDATE,
last_updated_by = pv_user_id,
process_flag = 'P',
conc_request_id = pv_conc_req_id,
po_header_id = lv_po_header_id_1,
po_line_id = lv_po_line_id_1,
po_line_location_id = lv_line_location_id_1,
po_distribution_id = lv_po_distribution_id_1,
inventory_item_id = lv_inventory_item_id_1,
org_id = pv_org_id
WHERE a.ROWID = rec_line.ROWID;
--
--
EXCEPTION
WHEN custom_line_exception
THEN
--
lv_fail_valid_cnt := lv_fail_valid_cnt + 1;
--
fnd_file.put_line (fnd_file.LOG,
'Custom error on Accounting Date '
);
UPDATE AP_INV_EXT_TBL a
SET last_update_date = SYSDATE,
last_updated_by = pv_user_id,
process_flag = 'E',
conc_request_id = pv_conc_req_id,
org_id = pv_org_id
WHERE a.ROWID = rec_line.ROWID;
--
END;
--
IF lv_rec_count >= 1000
THEN
--
COMMIT;
lv_rec_count := 0;
--
ELSE
lv_rec_count := lv_rec_count + 1;
END IF;
--
END LOOP;
/* fnd_file.put_line
(fnd_file.LOG,
'Invoice Source :- '
|| rec_invoice.SOURCE
);
SELECT LOOKUP_CODE into lv_invoice_source
FROM ap_lookup_codes
WHERE lookup_type = 'SOURCE'
and displayed_field = rec_invoice.SOURCE;
fnd_file.put_line
(fnd_file.LOG,
'Invoice Source Lookup Code :- '
|| lv_invoice_source
);
*/
lv_invoice_source := rec_invoice.SOURCE;
END LOOP;
SELECT COUNT (*)
INTO lv_record_cnt
FROM ap_invoices_interface api
WHERE api.GROUP_ID = lv_group;
fnd_file.put_line
(fnd_file.LOG,
'number of records uploaded to interface :- '
|| lv_record_cnt
);
IF NVL (lv_record_cnt, 0) > 0
THEN
--
fnd_file.put_line (fnd_file.LOG,
'lv_gl_date :- '
|| TO_CHAR (lv_gl_date,
'YYYY/MM/DD HH24:MI:SS'
)
);
-------------------------------------------------
-- Submit Payables Open Interface Import request
-------------------------------------------------
lv_request_id :=
fnd_request.submit_request
(lv_app_short_name,
lv_prog_name,
'ONS Bulk AP Invoice',
'' , -- start_time
FALSE, -- subsequent ,
pv_org_id,
lv_invoice_source, -- Source
lv_group, -- Group
lv_group, -- Batch Name
'', -- Hold Name
'', -- Hold Reason
TO_CHAR (lv_gl_date,'YYYY/MM/DD HH24:MI:SS'), -- GL Date
'Y', -- Purge
'N', -- Trace switch
'N', -- Debug switch
'N', -- Summarize Report
'1000' , -- Commit Batch Size
pv_user_id , -- User Id
pv_login_id -- Login Id
);
lv_invoice_source := NULL;
IF lv_request_id = 0
THEN
fnd_file.put_line (fnd_file.LOG, 'Request not successfull');
RAISE custom_exception;
ELSE
COMMIT;
fnd_file.put_line (fnd_file.LOG,
'Request successfull and the id is '
|| TO_CHAR (lv_request_id)
);
lv_req_done :=
fnd_concurrent.wait_for_request
(lv_request_id,
3 -- seconds to wait before next check
,
(1000 * 60
) -- Wait for max of 1000 minutes
,
lv_phase,
lv_status,
lv_dev_phase,
lv_dev_status,
lv_message
);
END IF;
--
END IF; -- Statement added by skb on 20-Jul-2009
END LOOP; -- END LOOP #1
--end if;
--end loop;
END;
--
COMMIT;
--
fnd_file.put_line (fnd_file.LOG,
'Calling PO Matched AP Invoice upload procedure'
);
--
-------------------------------------------------------
-- count total number of records uploaded to interface
-------------------------------------------------------
--
----------------------------------------------
-- Following code
----------------------------------------------
fnd_file.put_line (fnd_file.LOG,
'Request id for AP Invoice Bulk Upload :-'
|| lv_request_id
);
-------------------------------
-- Invoices record processed
-------------------------------
SELECT COUNT (aid.invoice_id)
INTO lv_invoice_processed
FROM ap_invoices_all aia, ap_invoice_distributions_all aid
WHERE aia.invoice_id = aid.invoice_id
AND invoice_num IN (SELECT DISTINCT TRIM (invoice_num)
FROM AP_INV_EXT_TBL
WHERE conc_request_id = pv_conc_req_id);
--
fnd_file.put_line (fnd_file.LOG,
'lv_invoice_processed :- ' || lv_invoice_processed
);
-------------------------------
-- Invoices record rejected
-------------------------------
SELECT COUNT (*)
INTO lv_invoice_rejected
FROM ap_invoices_interface api, ap_invoice_lines_interface apil
WHERE api.GROUP_ID = lv_group
AND api.status = 'REJECTED'
AND api.invoice_id = apil.invoice_id;
--
fnd_file.put_line (fnd_file.LOG,
'lv_invoice_rejected :- ' || lv_invoice_rejected
);
fnd_file.put_line (fnd_file.output,
'Total Number of records processed :- '
|| lv_invoice_processed
);
fnd_file.put_line (fnd_file.output,
'Total Number of records rejected :- '
|| lv_invoice_rejected
);
fnd_file.put_line (fnd_file.output,
'Total Number of records failed validation :- '
|| lv_fail_valid_cnt
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line
(fnd_file.output,
'For rejected records Please check Payables Open Interface Import process'
);
--
-----------------------------------------------------------
-- If no record uploaded to interface or atleast one record
-- is rejected then complete the process with warning
-----------------------------------------------------------
/*
IF ( lv_record_cnt = 0
OR lv_invoice_processed = 0
OR lv_invoice_rejected > 0
)
THEN
--
retcode := 1; -- Warning, because no record processed
--
END IF;
*/
--
-- Deleting seven days old records, as there is no need to store old records in this process.
--
fnd_file.put_line (fnd_file.LOG, 'Deleting seven days old records');
--
DELETE FROM AP_INV_EXT_TBL
WHERE creation_date <= SYSDATE - 7;
--
COMMIT;
--
EXCEPTION
WHEN custom_exception
THEN
fnd_file.put_line
(fnd_file.LOG,
'ERROR! Custom error In ap_invoice_main of AP Invoice upload : '
);
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'ERROR! In ap_invoice_main of AP Invoice upload : '
|| SQLERRM
);
retcode := 1; -- Warning
END ap_invoice_main;
--
END AP_INVOICE_UPLOAD;
/
No comments:
Post a Comment