Help!
I have a stored procedure that I can't get to work. The purpose of this
procedure is to take one file that has detail records and insert a distinct
header into one table, assign an unique_id, then insert into another table
all the detail lines associated with that header record and add a line
counter along with the unique_id from the header. I can get the header
inserted fine but its the detail that is not processing. Attached is the
code and now when I try and execute the code its giving me an error on my
two cursors. Thank you in advance for any help.
PROCEDURE temp_insert_interface
IS
l_invoice_id NUMBER(10);
l_invoice_line_id NUMBER;
CURSOR c_dri_apc_paid_history
IS
SELECT DISTINCT invoice_code,
invoice_date,
vendor_code,
invoice_amount,
record_type,
separate_handling,
separate_check,
type_code,
check_number ||
' ' ||
TO_CHAR (check_date, 'MM/DD/YY') attribute2,
batch_code attribute3,
po_number attribute4,
coder_initials attribute5,
ips_number attribute6
FROM dricus.dri_apc_paid_history;
CURSOR c_ap_invoice_interface
IS
SELECT
distribution_amount,
corp_code,
gl_account,
location_code,
desc_Code,
slid_code,
slac_code,
invoice_code,
dricus.dri_apc_paid_history.invoice_Date,
vendor_code,
dricus.dri_apc_paid_history.invoice_amount,
record_type,
separate_handling,
separate_check,
check_number,
check_date,
batch_code,
dricus.dri_apc_paid_history.po_number,
dricus.dri_apc_paid_history.coder_initials,
ips_number
from ap.ap_invoices_interface,
dricus.dri_apc_paid_history
WHERE
dricus.dri_apc_paid_history.invoice_code =
ap.ap_invoices_interface.invoice_num
AND dricus.dri_apc_paid_history.invoice_date =
ap.ap_invoices_interface.invoice_date
AND dricus.dri_apc_paid_history.vendor_code =
ap.ap_invoices_interface.vendor_site_code
AND dricus.dri_apc_paid_history.invoice_amount =
ap.ap_invoices_interface.invoice_amount
AND dricus.dri_apc_paid_history.record_type =
ap.ap_invoices_interface.source
AND dricus.dri_apc_paid_history.separate_handling
=ap.ap_invoices_interface.pay_group_lookup_code
AND dricus.dri_apc_paid_history.separate_check =
ap.ap_invoices_interface.exclusive_payment_flag
AND dricus.dri_apc_paid_history.type_code =
ap.ap_invoices_interface.invoice_type_lookup_code
AND dricus.dri_apc_paid_history.check_number ||' ' ||TO_CHAR
(dricus.dri_apc_paid_history.check_date, 'MM/DD/YY')
= ap.ap_invoices_interface.attribute2
AND dricus.dri_apc_paid_history.batch_code =
ap.ap_invoices_interface.attribute3
AND dricus.dri_apc_paid_history.po_number =
ap.ap_invoices_interface.attribute4
AND dricus.dri_apc_paid_history.coder_initials =
ap.ap_invoices_interface.attribute5
AND dricus.dri_apc_paid_history.ips_number =
ap.ap_invoices_interface.attribute6;
BEGIN
FOR cc_outer IN c_dri_apc_paid_history
LOOP
SELECT seq_dricus.nextval
INTO l_invoice_id
FROM dual;
INSERT INTO ap.ap_invoices_interface (
invoice_id,
invoice_num,
invoice_date,
vendor_site_code,
invoice_amount,
source,
pay_group_lookup_code,
exclusive_payment_flag,
invoice_type_lookup_code,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6
)
VALUES (
l_invoice_id,
cc_outer.invoice_code,
cc_outer.invoice_date,
cc_outer.vendor_code,
cc_outer.invoice_amount,
cc_outer.record_type,
cc_outer.separate_handling,
cc_outer.separate_check,
cc_outer.type_code,
cc_outer.attribute2,
cc_outer.attribute3,
cc_outer.attribute4,
cc_outer.attribute5,
cc_outer.attribute6
);
commit;
l_invoice_line_id := 1;
FOR cc_inner IN c_ap_invoice_interface
LOOP
INSERT INTO ap.ap_invoice_lines_interface (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
dist_code_concatenated,
attribute1
)
VALUES (
l_invoice_id,
l_invoice_line_id,
l_invoice_line_id,
'ITEM',
cc_inner.distribution_amount,
cc_inner.corp_code||'.'||cc_inner.gl_account||'.'||cc_inner.location_code||'.000.00000',
cc_inner.desc_code||' '||cc_inner.slid_code||' '||cc_inner.slac_code
);
l_invoice_line_id := l_invoice_line_id + 1;
commit;
END LOOP;
Close c_dri_apc_paid_history;
END LOOP;
Close c_ap_invoice_interface;
--EXCEPTION
-- WHEN exception_name THEN
-- statements ;
END; -- Procedure INSERT_INTERFACE
