Hi all,
How can I append values to this collection?
What's happening is the results from the inner loop are the only values returned.
I need to combine and return values from l_header collection and l_detail collection
| FUNCTION monthly_ticket_out | |
RETURN monthly_ticket_out_type
PIPELINED
IS
TYPE header_record_array IS TABLE OF header_record_type
INDEX BY BINARY_INTEGER;
l_header header_record_array;
TYPE detail_record_array IS TABLE OF detail_record_type
INDEX BY BINARY_INTEGER;
l_detail detail_record_array;
--return records
TYPE out_rec IS TABLE OF VARCHAR2(2500)INDEX BY BINARY_INTEGER;
return_recs out_rec;
BEGIN
SELECT a.agencycode,
| | 'BATCH'||' '|| | |
| | | | rpad(a.agency,46)||' '|| | |
| | | | rpad(a.agencycode,5)||' '|| | |
| | rpad(a.street,25)||' '|| | |
| | rpad(a.city,15)||' '|| | |
| | rpad(a.statecode,2)||' '|| | |
| | rpad(a.zip,5)||' '|| | |
| | rpad(substr(a.contact,1,25),25)||' '|| | | |
| | rpad(lpad(count(t.agencycode),6,0),6)||' '|| | | |
| | rpad(lpad(0,9,0),9)||' '|| | |
| | rpad(' ',50)||'X' | |
BULK COLLECT INTO l_header
FROM ifob_pol_tkt t,
WHERE t.agencycode = a.agencycode
GROUP BY a.agencycode, a.agency, a.street, a.city, a.statecode, a.zip, a.contact
ORDER BY a.agencycode;
FOR header_rec IN l_header.FIRST..l_header.LAST LOOP
--assign header rec to output
return_recs(header_rec) := l_header(header_rec).header;
SELECT rpad(t.mraccountnum,7)||' '||
| | | | | rpad(t.rank,6)||' '|| |
| | | | | rpad(t.firstname,10)||' '|| |
| | | | | rpad(nvl(t.middleinitial,' '),1)||' '|| |
| | | | | rpad(nvl(t.employeenum,' '),9)||' '|| |
| | | | | rpad(t.gender,1)||' '|| |
| | | | | rpad(nvl(t.shield,' '),5)||' '|| |
| | | | | rpad(t.agencycode,5)||' '|| |
| | | | | rpad(nvl(t.agencyaddtl,' '),3)||' '|| |
| | | | | rpad(nvl(t.command,' '),4)||' '|| |
| | | | | rpad(nvl(t.precinct,' '),3)||' '|| |
| | | | | rpad(t.polcustline,1)||' '|| |
| | | | | rpad(t.origstation,4)||' '|| |
| | | | | rpad(t.homestation,4)||' '|| |
| | | | | rpad(t.deststation,4)||' '|| |
| | | | | rpad(t.transactiondate,8)||' '|| |
| | | | | rpad(nvl(t.polcusttype,' '),1)||' '|| |
| | | | | rpad(t.invoicedate,6)||' '|| |
| | | | | rpad(nvl(t.amount,' '),4)||' '|| |
| | | | | rpad(nvl(t.ticketnum2,' '),7)||' '|| |
| | | | | rpad(t.agencyfullname,43)||' '|| |
| | | | | rpad(t.seqcntlno,7)||' '|| |
| | | | | rpad(t.ticketnum,7)||' '|| |
| | | | | rpad(t.senddate,8)||' '|| |
| | | | | rpad(nvl(t.futurefiller,' '),27)||' '|| |
| | | | | rpad(t.endofrecord,1) |
| | BULK COLLECT INTO l_detail | |
| | FROM ifob_pol_tkt t |
| | WHERE t.agencycode = l_header(header_rec).agencycode; |
| | |
| | FOR detail_rec IN l_detail.FIRST .. l_detail.LAST LOOP |
| | | null; |
| | | --assign detail rec to output |
| | return_recs(detail_rec) := l_detail(detail_rec).detail; |
| | | | | |
| | END LOOP; |
END LOOP;
FOR i IN 1..return_recs.count LOOP
| PIPE ROW(monthly_ticket_out_obj(return_recs(i))); |
| | | | | | | | | | | | | | | | |
END LOOP;
RETURN;
END monthly_ticket_out;