Cursors with Parameters

declare
CURSOR C1 is
SELECT DISTINCT
       msi.inventory_item_id        item_id,
       msi.segment1                 item_number,
       mir.attribute7               issue,
       substr(mir.attribute10,1,10) UPC,
       substr(mir.attribute2,1,1)   nsc,
       substr(mir.attribute7,3,2)   iss_year,
       substr(mir.attribute2,3,2)   add_on,
       substr(msi.attribute6,1,5)   Bipad,
       substr(msi.attribute3,1,5)   parent_bipad,
       substr(msi.attribute7,1,2)   frequency,
       substr(msi.attribute5,1,25)  title,
       substr(mir.attribute8,1,10)  cover_date,
       to_char(mir.effectivity_date, 'YYYYMMDD') onsale,
       to_char(to_date(mir.attribute11), 'YYYYMMDD') offsale,
       substr(msi.attribute14,1,10) edit_category,
       substr(mir.attribute6,1,4) pub_code,
       substr(p.vendor_name,1,20) pub_desc
       ,p.attribute13 attribute13
FROM   po.po_vendors@PRODAPPS.WORLD  p, --@PRODAPPS_QUERY.WORLD
       mtl_item_revisions@PRODAPPS.WORLD  mir,
       mtl_system_items@PRODAPPS.WORLD  msi
        --, hdg.MISSING_BARCOV hdg
WHERE  p.vendor_id = mir.attribute6
AND    mir.effectivity_date          between to_date('01-JAN-'||(to_char(sysdate,'YYYY')-1))
                                          and to_date('31-DEC-'||(to_char(sysdate,'YYYY')+1))
AND    mir.attribute1                 = 'N'
AND    mir.organization_id            = msi.organization_id
AND    mir.inventory_item_id          = msi.inventory_item_id
AND    msi.attribute6                 not in ('99999','88888')
AND    msi.inventory_item_status_code = 'HDG ACTIVE'
AND    msi.item_type                  = 'HDG_MG'
-- and    msi.inventory_item_id          = 44
AND    msi.organization_id            = 103
--and  msi.inventory_item_id =  hdg.INVENTORY_ITEM_ID
--and mir.attribute7 =hdg.issue_code
AND    p.attribute13                  ='1000'; -- 1000 -> CMG ,2000_. CTC , 3000 -> Genera

CURSOR C2(p_item IN NUMBER,
          p_upc IN VARCHAR2 ) IS
select qll.operand list_price,
       qlh.name,
       qpa.pricing_attr_value_from UPC1 ,
       qpa.product_attr_value item1,
       substr(qlh.currency_code,1,1) country_code
from   qp_list_headers@PRODAPPS.WORLD  qlh,
       qp_list_lines@PRODAPPS.WORLD  qll,
       qp_pricing_attributes@PRODAPPS.WORLD  qpa
where  (qll.end_date_active is null or qll.end_date_active >= trunc(sysdate))
AND    (qll.start_date_active is null or qll.start_date_active < trunc(sysdate))
and    qll.list_line_type_code       = 'PLL'
and    qll.pricing_phase_id          = qpa.pricing_phase_id
and    qll.qualification_ind         = qpa.qualification_ind
and    qll.modifier_level_code       = 'LINE'
and    qlh.currency_code              in ('CAD','USD') -- use this condition only when loading for COMAG profitcenter 1000
and   qlh.active_flag = 'Y'
and   (qlh.end_date_active is null or qlh.end_date_active >= trunc(sysdate))
AND   (qlh.start_date_active is null or qlh.start_date_active < trunc(sysdate))
and    qlh.name in ('M-HDG-STANDARD-US','M-HDG STANDARD - CN') --use this condition only when loading for COMAG profitcenter 1000 and run again for the 100 profitsenter for condition like '40%'--
--and    qlh.name like '%40%'
and    qll.list_header_id            = qlh.list_header_id
and    qll.list_header_id            = qpa.list_header_id
and    qll.list_line_id              = qpa.list_line_id
and    qpa.pricing_attr_value_from   = p_upc
and    qpa.pricing_attribute         = 'PRICING_ATTRIBUTE1'
and    qpa.pricing_attribute_context = 'Upgrade Context'
and    qpa.product_uom_code          = 'EA'
and    qpa.product_attr_value        = p_item
and    qpa.product_attribute         = 'PRICING_ATTRIBUTE1'
and    qpa.product_attribute_context = 'ITEM';
V_count number(20):=0;
begin
for i in C1 loop
for j in C2 (i.item_id,i.upc) loop
insert into XXX_tests (Item_id, UPC) values(j.item1,j.UPC1);
V_count:=V_count+1;
end loop;
end loop;
Dbms_output.put_line(V_count);
end;



Select count(*) from XXX_tests