Purchase order Report value

Header

-----------------------

<?CP_ORG_NAME?>		Purchase Order Value				<?xdoxslt:sysdate('dd-MM-yyyy HH24:mi:ss')?> Page 1 of 1
Material and Services raised between <?CF_FROM_DATE?>  and  <?CF_TO_DATE?>
Selection for <?CF_PROJECT?> Project  ,for <?CF_TASK?> Task  , for <?CF_ACTION?> Activity Code

                                                                       

Purchase Order	Date Raised	No.of lines	Account Code and Name	Contract and Description	Value Ordered	Value Received	Value Invoiced	Value to Follow	Value to Invoice
HSpo	Date	L No.	Account -Sup	Type Desc	0.00	0.00	0.00	0.00	0.00E
Totals for all selected purchase orders:					0.00	0.00	0.00	0.00	0.00
Number of purchase orders included :PO's

If Data Found ***End of Report***End If Data Found

If No Data Found ***No Data Found*** End No Data Found

DataFound----<?if:count(CREATION_DATE)>0?> <?end if?>

NO Data Found--<?if:count(CREATION_DATE)=0?> <?end if?>

SQL Query

----------------------------------------------

select * from(SELECT pv.vendor_name,
--pla.purchase_basis,
   pha.segment1,
        'PO'||pha.segment1,
       pha.creation_date,
       pha.org_id,
       --pla.line_num,
             (select count(po_line_id) from po_lines_all a
      where a.po_header_id = pha.po_header_id and  a.org_id=fnd_profile.value('ORG_ID')) count,
       pha.type_lookup_code ,
       pv.segment1 segments1,
       pha.comments,
       pha.po_header_id,
      -- NVL (SUM (pla.unit_price * pla.quantity), 0.00) AS value_ordered,
       NVL (SUM (rt.po_unit_price * rt.quantity_billed), 0.00)
          AS value_received,
       NVL (SUM (pla.unit_price * pla.quantity)
            - SUM (rt.po_unit_price * rt.quantity_billed),
            0.00
       ) AS value_to_follow
FROM po_headers_all pha,
     po_lines_all pla,
     po_distributions_all pda,
     pa_projects_all ppa,
     pa_tasks pt,
     po_vendors pv,
     rcv_transactions rt
WHERE     pha.po_header_id = pla.po_header_id
--and pha.segment1='20100081'
      AND pla.po_line_id = pda.po_line_id(+)
      --AND pha.po_header_id = pda.po_header_id --raju 10 mar 2011
      and pha.vendor_id = pv.vendor_id
      AND pda.project_id = ppa.project_id(+)
      AND pda.task_id = pt.task_id(+)
      AND pv.vendor_id = nvl(:p_vendor_id,pv.vendor_id)
      AND TRUNC (pha.creation_date) BETWEEN TO_CHAR (:p_date_from, 'DD-MON-RR'
                                            )
                                        AND  TO_CHAR (:p_date_to, 'DD-MON-RR')
      AND pha.org_id = FND_PROFILE.VALUE('ORG_ID')--:p_org_id
      AND pha.po_header_id = rt.po_header_id(+)
      AND rt.transaction_type(+) = 'RECEIVE'
      AND pha.type_lookup_code = NVL (:p_document_type, pha.type_lookup_code)
--AND pla.purchase_basis =NVL(
  --          DECODE (:p_purchase_basis,
    --                'MATERIAL',
      --             'GOODS',
        --            'SERVICES',
          --          'SERVICES',
            --        'ALL',
              --      pla.purchase_basis
           -- ),pla.purchase_basis)
AND nvl(pda.expenditure_type,'NULL') =
           NVL (:p_expenditure_type, nvl(pda.expenditure_type,'NULL'))
      AND nvl(ppa.name ,'NULL')= NVL (:p_name, NVl(ppa.name,'NULL'))
      AND nvl(pt.task_name,'NULL') = NVL (:p_task_name, nvl(pt.task_name,'NULL'))
      and pla.contract_id is null
      and type_lookup_code <> 'RFQ'
and type_lookup_code <> 'QUOTATION'
and type_lookup_code <> 'PLANNED'
&LP_PURCHASE_BASIS
GROUP BY pv.vendor_name,
--pla.purchase_basis,
pha.segment1,
         'PO' || pha.segment1,
         pha.creation_date,
         pha.org_id,
         --pla.line_num,
         --COUNT (line_num),
         pv.vendor_name,
         pha.type_lookup_code,
         pha.comments,
         pha.po_header_id,
         pv.segment1
UNION ALL
select
pv.vendor_name,
--pla.purchase_basis,
pha.segment1,'PO' || pha.segment1,
       pha.creation_date,
       pha.org_id,
       --pla.line_num,
       COUNT (line_num),
       pha.type_lookup_code ,
       pv.segment1 segments1,
       pha.comments,
       pha.po_header_id,
      -- null value_ordered,
              NVL (SUM (rt.po_unit_price * rt.quantity_billed), 0.00)
          AS value_received,
          NVL (SUM (pla.unit_price * pla.quantity)
            - SUM (rt.po_unit_price * rt.quantity_billed),
            0.00
       ) AS value_to_follow
from po_headers_all pha
      ,po_lines_all pla
      ,po_distributions_all pda
      ,po_vendors pv
      ,pa_projects_all ppa,
     pa_tasks pt,
     rcv_transactions rt
     --rcv_transactions rt
 where 1=1 --and type_lookup_code='CONTRACT'
--and pha.segment1='20100666'
   and pha.po_header_id=pla.contract_id
   and pha.vendor_id=pv.vendor_id
   and pda.po_line_id=pla.po_line_id
   and pda.project_id=ppa.project_id(+)
   and pda.task_id=pt.task_id(+)
    AND pv.vendor_id = nvl(:p_vendor_id,pv.vendor_id)
    AND TRUNC (pha.creation_date) BETWEEN TO_CHAR (:p_date_from, 'DD-MON-RR'
                                            )
                                        AND  TO_CHAR (:p_date_to, 'DD-MON-RR')
      AND pha.org_id = FND_PROFILE.VALUE('ORG_ID')--:p_org_id
       AND pha.type_lookup_code = NVL (:p_document_type, pha.type_lookup_code)
--AND pla.purchase_basis =NVL(
  --          DECODE (:p_purchase_basis,
    --                'MATERIAL',
      --             'GOODS',
        --            'SERVICES',
          --          'SERVICES',
            --        'ALL',
              --      pla.purchase_basis
           -- ),pla.purchase_basis)
            AND nvl(pda.expenditure_type,'NULL') =
           NVL (:p_expenditure_type, nvl(pda.expenditure_type,'NULL'))
      AND nvl(ppa.name,'NULL') = NVL (:p_name,nvl(ppa.name,'NULL'))
      AND nvl(pt.task_name,'NULL')
       = NVL (:p_task_name,nvl( pt.task_name,'NULL'))
            AND pha.po_header_id = rt.po_header_id(+)
      AND rt.transaction_type(+) = 'RECEIVE'
--and pla.cancel_flag <>'Y'   --added by raju (sunil said)
and type_lookup_code <> 'RFQ'
and type_lookup_code <> 'QUOTATION'
and type_lookup_code <> 'PLANNED'
&LP_PURCHASE_BASIS
GROUP BY pv.vendor_name,
--pla.purchase_basis,
pha.segment1,'PO' || pha.segment1,
       pha.creation_date,
       pha.org_id,
      -- pla.line_num,
      -- COUNT (line_num),
       pha.type_lookup_code ,
       pv.segment1,
       pha.comments,
       pha.po_header_id)
order by creation_date

CF_ORG_NAME

-----------------------------------

function CF_ORG_NAME return Char is
v_org_name varchar2(150);
begin
    begin
  select organization_name
    into v_org_name
    from org_organization_definitions
   where organization_id = :p_org_id;  --fnd_profile.value('ORG_ID');
   exception
    when no_data_found then
     v_org_name:=null;
     end;
   return(v_org_name);
end;

CF_FROM_DATE

----------------------------------

function CF_FROM_DATEFormula return date is
begin
 return(to_char(:p_date_from,'DD-MON-RR'));
end;

CF_TO_DATE

----------------------------------

function CF_TO_DATEFormula return date i

begin
  return(to_char(:P_date_to,'DD-MON-RR'));
end;

function CF_1FORMULA0008 return Char is
begin
  :CP_REQUEST_ID:=:P_CONC_REQUEST_ID;
  BEGIN
  SELECT   name
    INTO   :CP_ORG_NAME
    FROM   hr_operating_units
   WHERE   organization_id=FND_PROFILE.VALUE ('ORG_ID');
   EXCEPTION
    WHEN OTHERS THEN
     NULL;
   END;
   return('a');
end;