Open PO's to display the information regarding the PO in oracle apps R12

select  
(l.quantity*l.unit_price) "Amount Ordered",
(select sum(aid.amount)
 from ap_invoices_all aia,
      ap_invoice_distributions_all aid
where aia.invoice_id = aid.invoice_id
and   aid.po_distribution_id = d.po_distribution_id)"Amount Billed",
(select sum(aip.amount )
from AP_INVOICE_PAYMENTS_all aip ,
     ap_invoice_distributions_all adi
where aip.invoice_id=adi.invoice_id
and   adi.po_distribution_id= d.po_distribution_id)"Amount Received",

gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 "Charge account",
ll.ship_to_location_id "Ship to Location",
l.quantity            "quantity",
ll.quantity          "Ordered Quantity",
ll.quantity_Billed     "Billed Quantity",
ll.quantity_received  "Received Quantity",
h.segment1,
d.po_distribution_id
from  
po.po_headers_all h,  
po.po_lines_all l,  
po.po_line_locations_all ll,  
po.po_distributions_all d,
gl_code_combinations   gcc
where h.po_header_id = l.po_header_id  
and  gcc.code_combination_id=d.code_combination_id
and ll.po_line_id = l.po_Line_id  
and ll.line_location_id = d.line_location_id
and h.segment1='1505'
and h.closed_date is null ;

utl file creation for inventory item in oracle apps R12

CREATE OR REPLACE procedure GE_INV_Out_BAL(Errbuf OUT varchar2,
        Retcode ouT varchar2,
f_id  in number,
t_id  in varchar2
--,dir_name varchar2
) as
cursor c1 is select
msi.segment1 item,
msi.inventory_item_id itemid,
msi.description itemdesc,
msi.primary_uom_code uom,
ood.organization_name name,
ood.organization_id id,
mc . segment1||','||mc.segment2 category
from
mtl_system_items_b msi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories mc
where
msi.organization_id = ood.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and mic.category_id = mc.category_id
and msi.purchasing_item_flag = 'Y'
--and msi.inventory_item_id=63
and ood.organization_name='Vision Operations'
and msi.organization_id between f_id and t_id;

x_id utl_file.file_type;

l_count number(5) default 0;

path  varchar2(1000);

--path V$PARAMETER.value%type;

begin
x_id:=utl_file.fopen('Select value into path from V$PARAMETER Where NAME like 'user_dump_dest'','invoutdata10.txt','W');
--select * from v$parameter where name like '%utl_file%'
for x1 in c1 loop
l_count:=l_count+1;
utl_file.put_line( x_id,'"'||
x1.item ||'"-"'||
x1.itemid ||'"-"'||
x1.itemdesc||'"-"'||
x1.uom ||'"-"'||
x1.name ||'"-"'||
x1.id ||'"-"'||
x1.category||'"' );
end loop;
utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transferred to the data file :'||l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name '||Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name '||Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date :'|| SYSDATE);
Exception
WHEN utl_file.invalid_operation THEN
fnd_file.put_line(fnd_File.log,'invalid operation');
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
fnd_file.put_line(fnd_File.log,'invalid path');
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
fnd_file.put_line(fnd_File.log,'invalid mode');
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
fnd_file.put_line(fnd_File.log,'invalid filehandle');
utl_file.fclose_all;
WHEN utl_file.read_error THEN
fnd_file.put_line(fnd_File.log,'read error');
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
fnd_file.put_line(fnd_File.log,'internal error');
utl_file.fclose_all;
WHEN OTHERS THEN
fnd_file.put_line(fnd_File.log,'other error');
utl_file.fclose_all;
End GE_INV_Out_BAL;

ACTIVE USERS EMPLOYEE AND THEIR RESPONSIBILITIES IN ORACLE APPS

SELECT fu.user_name user_name
      ,fu.description user_description
      ,fu.email_address user_email
      ,ppf.full_name employee_name
      ,hou.name business_group
      ,fr.responsibility_name resp_name
FROM apps.fnd_user fu
    ,apps.per_all_people_f ppf
    ,apps.hr_all_organization_units hou
    ,apps.fnd_user_resp_groups_all fur
    ,apps.fnd_responsibility_tl fr
WHERE ppf.person_id = fu.employee_id
AND hou.organization_id = ppf.business_group_id
AND fu.user_id = fur.user_id
AND NVL (fur.end_date, SYSDATE + 1) > SYSDATE
AND fur.responsibility_id = fr.responsibility_id
--AND fr.responsibility_name LIKE 'Shipping User'
AND fr.LANGUAGE = 'US'
ORDER BY fu.user_name
        ,fr.responsibility_name 

Open PO's To display the amount billed , ordered amount , etc in oracle apps R12

Open PO's To display the amount billed , ordered amount , etc in Shipment Level


select  
(l.quantity*l.unit_price) "Amount Ordered",
(select aia.invoice_amount
 from ap_invoices_all aia,
      ap_invoice_distributions_all aid
 where aia.invoice_id = aid.invoice_id
 and   aid.po_distribution_id = d.po_distribution_id) "Amount Billed",
gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 "Charge account",
ll.ship_to_location_id "Ship to Location",
l.quantity            "quantity",
d.quantity_ordered    "Ordered Quantity",
d.quantity_billed     "Billed Quantity",
d.quantity_delivered  "Received Quantity"
from  
po.po_headers_all h,  
po.po_lines_all l,  
po.po_line_locations_all ll,  
po.po_distributions_all d,
gl_code_combinations   gcc
where h.po_header_id = l.po_header_id  
and  gcc.code_combination_id=d.code_combination_id
and ll.po_line_id = l.po_Line_id  
and ll.line_location_id = d.line_location_id
and h.segment1='8533'
and h.closed_date is null ;