Dear Friends
I have created one procedure in that i m passing Customer id as in put parameter based on that i m fetching all orders placed by that order.
I have given out parameter as pl/sql base apps table.
Problem is that when i m going to execute it from pl/sql block.
it is taking all lines only for one order..
Can any one suggest me how to resolve this problem...
----------------------------------------------------
i have to pass the ouput of this procedure to the BPEL Process.
--------------------------------------------------------
create or replace procedure XXX_customer_order(cust_no IN varchar2,
OUT_ORDER_HEAD out OE_ORDER_PUB.Header_Tbl_Type ,
OUT_ORDER_LINE out OE_ORDER_PUB.Line_Tbl_Type
) is
xx_header_id number;
xx_org_id number;
I NUMBER :=1;
j NUMBER :=1;
XXX_ORDER_HEAD OE_ORDER_PUB.Header_Tbl_Type ;
XXX_ORDER_LINE OE_ORDER_PUB.Line_Tbl_Type ;
Cursor ORDER_HEADER is
SELECT distinct
ooh.header_id header_id ,
ooh.org_id org_id,
ooh.order_number order_number,
ooh.ordered_date order_date,
ooh.flow_status_code header_flow_status
FROM oe_order_headers_all ooh,
oe_transaction_types_tl ott,
org_organization_definitions ood
WHERE ooh.order_type_id = ott.transaction_type_id
AND ott.LANGUAGE = (SELECT fl.language_code
FROM fnd_languages fl
WHERE fl.installed_flag = 'B')
AND ooh.ship_from_org_id = ood.organization_id
and ood.organization_id=ooh.org_id
and ooh.org_id=204
AND ooh.sold_to_org_id in(SELECT DISTINCT D.CUST_ACCOUNT_ID--sold_to_org_id--A.PARTY_NAME
FROM hz_parties A,
hz_party_sites B,
HZ_CUST_ACCT_SITES_ALL C,
HZ_CUST_ACCOUNTS D,
HZ_CUST_SITE_USES_ALL E
WHERE A.PARTY_ID=B.PARTY_ID
and b.PARTY_ID=D.PARTY_ID
AND B.PARTY_SITE_ID=C.PARTY_SITE_ID
AND A.STATUS='A'
AND C.CUST_ACCOUNT_ID=d.CUST_ACCOUNT_ID
AND E.CUST_ACCT_SITE_ID=C.CUST_ACCT_SITE_ID
AND D.CUST_ACCOUNT_ID=cust_no
AND E.SITE_USE_CODE='BILL_TO');
-----------------------------------------------------------------------------------
cursor order_line is
select distinct
nvl(oel.ordered_quantity,0) order_quantity,
nvl(oel.shipped_quantity,0) invoice_quantity,
a.segment1 item_name,
nvl(oel.unit_selling_price,0) unit_price,
oel.promise_date promise_date
from oe_order_lines_all oel,
mtl_system_items_b a
where oel.header_id = xx_header_id
and a.inventory_item_id = oel.inventory_item_id
and a.organization_id = oel.org_id
and oel.org_id = 204--xx_org_id
group by
nvl(oel.ordered_quantity,0) ,
nvl(oel.shipped_quantity,0) ,
a.segment1 ,
nvl(oel.unit_selling_price,0) ,
oel.promise_date ;
begin
FND_CLIENT_INFO.SET_ORG_CONTEXT(204);
for head_rec in ORDER_HEADER loop
xx_header_id :=head_rec.header_id;
xx_org_id :=head_rec.org_id;
XXX_ORDER_HEAD(I).order_number :=head_rec.order_number;
XXX_ORDER_HEAD(I).flow_status_code :=head_rec.header_flow_status;
XXX_ORDER_HEAD(I).ordered_date :=head_rec.order_date;
/*dbms_output.put_line('HEADER') ;
dbms_output.put_line(XXX_ORDER_HEAD(I).order_number
||','||XXX_ORDER_HEAD(I).flow_status_code
||','||XXX_ORDER_HEAD(I).ordered_date);*/
for LINE_rec in ORDER_LINE loop
XXX_ORDER_LINE(j).ordered_item :=line_rec.item_name;
XXX_ORDER_LINE(j).ordered_quantity :=line_rec.order_quantity;
XXX_ORDER_LINE(j).shipping_quantity :=line_rec.invoice_quantity;
XXX_ORDER_LINE(j).promise_date :=line_rec.promise_date;
/* dbms_output.put_line('LINES');
dbms_output.put_line(XXX_ORDER_LINE(j).ordered_item
||','||XXX_ORDER_LINE(j).ordered_quantity
||','||XXX_ORDER_LINE(j).shipping_quantity
||','||XXX_ORDER_LINE(j).promise_date);*/
J:=J+1;
end loop;
i:=i+1;
end loop;
out_ORDER_HEAD:=XXX_ORDER_HEAD;
out_ORDER_LINE:=XXX_ORDER_LINE;
End;
------------------------------------------------
CREATE OR REPLACE procedure x_test as
out_head OE_ORDER_PUB.Header_Tbl_Type;
out_line OE_ORDER_PUB.Line_Tbl_Type;
ip_cust number:=1290;
BEGIN
dhl_customer_order(ip_cust,out_head,out_LINE);
for i in out_head.first..out_head.last loop
dbms_output.put_line(OUT_head(i).order_number
||','||OUT_head(i).flow_status_code
||','||OUT_head(i).ordered_date);
for j in out_line.first..out_line.last loop
dbms_output.put_line(out_line(j).ordered_item
||','||out_line(j).ordered_quantity
||','||out_line(j).shipping_quantity
||','||out_line(j).promise_date);
end loop;
end loop;
END;
------------------------------------------------------
sql>exec x_test
-------------
output as followas...
56698,ENTERED,20-MAR-07
CM00033,100,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00056,1,0,
CM38080,1,0,
CM38080,1,0,
CM38080,1,0,
CM38080,1,0,
CM38080,1,0,
CM38080,1,0,
CM38080,1,0,
CM38080,1,0,
CM38080,1,0,
CM38080,1,0,
CM38080,25,0,
CM38080,3555,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00033,1,0,
CM00033,10,0,
CM00033,10,0,
CM00033,10,0,
CM00033,10,0,
CM00033,10,0,
CM00033,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,
CM44316,10,0,28-MAR-07
CM44316,10,0,28-MAR-07
CM44316,10,0,29-MAR-07
CM44316,1,0,29-MAR-07
CM44316,1,0,29-MAR-07
CM44316,1,0,29-MAR-07
CM44316,1,0,29-MAR-07
CM44316,1,0,29-MAR-07
CM44316,1,0,29-MAR-07
CM44316,1,0,29-MAR-07
CM44316,1,0,29-MAR-07
FS90000,1,0,29-MAR-07
Thanks and Regards
--------------------
Shekhar Lonkar
Oracle Apps tech.
|
] 
Blog
Search
Help
Members
Register
Login
Home
