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;

Supplier With Bank Details and Payment Details

SQL  Query
-----------------------------------
select asp.party_id,
       asp.vendor_id,
       ass.pay_site_flag,
       ass.vendor_site_id,
       hrl_ship.location_code,
       hp_supp.party_id,
       asp.VENDOR_NAME Supplier_name,
       asp.VENDOR_TYPE_LOOKUP_CODE type,
       asp.ONE_TIME_FLAG,
       asp.individual_1099,
       DECODE (
            UPPER (asp.vendor_type_lookup_code),
            'EMPLOYEE',
            papf.national_identifier,
            DECODE (asp.organization_type_lookup_code,
                    'INDIVIDUAL', asp.individual_1099,
                    'FOREIGN INDIVIDUAL', asp.individual_1099,
                    hp_supp.jgzz_fiscal_code)
         ) Taxpayer_ID,
         asp.VAT_REGISTRATION_NUM ,
         hp_supp.DUNS_Number_c  ,
         asp.start_date_active,
         asp.end_date_active,
         asp.hold_flag,
         asp.last_update_date,
         ass.address_line1,
         ass.address_line2,
         ass.address_line3 ,
         ass.city,
         ass.state,
         ass.zip
         , ass.country,
         ass.purchasing_site_flag ,
         ass.RFQ_ONLY_SITE_FLAG ,
         ass.PAY_SITE_FLAG
         , ass.Vendor_Site_Code
         , ass.ATTRIBUTE1 Supplier_Qualification_Status
         , ass.ATTRIBUTE15  Invoice_Approver --, ass.SHIP_TO_LOCATION_CODE
         , hrl_ship.location_code ship_to_location_code
         , hrl_bill.location_code bill_to_location_code
         , ass.inactive_date
         , ass.last_update_date site_last_update_date
         , ass.terms_id
         , att.name terms_name
         , ass.invoice_currency_code
         , ass.payment_currency_code
         , ass.hold_all_payments_flag
         , ass.hold_future_payments_flag
         , ass.vat_registration_num
         , hou.name OPERATING_UNIT_NAME
         , ass.email_address
         , ass.primary_pay_site_flag
         , ass.DUNS_NUMBER
         , ass.PAY_GROUP_LOOKUP_CODE
         , person.person_first_name first_name
         , person.person_last_name  last_name
         , pty_rel.email_address
         , pty_rel.primary_phone_number
         , apsc.creation_date contact_creation_date
         , apsc.last_update_date contact_last_update_date
          --hrl_ship.*
-- person.person_first_name ,
-- person.person_last_name ,pty_rel.primary_phone_number ,
-- pty_rel.email_address
-- , apsc.email_address , ass.*
,ass.duns_number site_duns_number
from ap_suppliers asp
   , ap_supplier_sites_all ass
   , ap_supplier_contacts apsc
   , hz_parties person
   , hz_parties pty_rel
   , hz_parties hp_supp
   , per_all_people_f papf
   , hr_locations hrl_ship
   , hr_locations hrl_bill  
   , ap_terms_tl att
   , hr_operating_units hou
   --, AND pv.party_id = hp.party_id
where 1 = 1
--and asp.vendor_id = 46006 -- 5001 -- 
and ass.vendor_id = asp.vendor_id
and asp.party_id = hp_supp.party_id
AND apsc.org_party_site_id(+) = ass.party_site_id
--and ass.org_id = 222 -- 123
AND apsc.per_party_id = person.party_id(+)
AND apsc.rel_party_id = pty_rel.party_id(+)
AND asp.employee_id = papf.person_id(+)
and hrl_ship.location_id(+) = ass.ship_to_location_id
and hrl_bill.location_id(+) = ass.bill_to_location_id
and att.term_id(+) = ass.terms_id
and ass.org_id = hou.organization_id
and TRUNC(asp.creation_date) between :P_FROM_CREATION_DATE AND :P_TO_CREATION_DATE
and TRUNC(asp.last_update_date) between NVL(:P_FROM_UPDATE_DATE,TRUNC(asp.last_update_date)) AND NVL(:P_TO_UPDATE_DATE,TRUNC(asp.last_update_date))
and asp.vendor_name = nvl(:p_supplier_name,asp.vendor_name)

Formula Column1(CF_BANK_DATA)
-----------------------------
function CF_BANK_DATAFormula return Number is
begin
 
 
     SELECT   eb.bank_name                 ,
              eb.bank_number ,
                  ebb.bank_branch_name         ,
              ebb.branch_number            ,
              eba.bank_account_num         ,
              eba.bank_account_name
       INTO :CP_BANK_NAME,
            :CP_BANK_NUMBER,
            :CP_BANK_BRANCH_NAME,
            :CP_BRANCH_NUMBER,
            :CP_BANK_ACCOUNT_NUM  ,
            :CP_bank_account_name
   FROM ap.ap_suppliers av         ,
        ap.ap_supplier_sites_all assa     ,
        apps.iby_ext_bank_accounts eba  ,
        apps.iby_account_owners ao      ,
        apps.iby_ext_banks_v eb         ,
        apps.iby_ext_bank_branches_v ebb,
            hr_operating_units hou
  WHERE av.vendor_id           = assa.vendor_id
AND ao.account_owner_party_id = av.party_id
AND eba.ext_bank_account_id   = ao.ext_bank_account_id
AND eb.bank_party_id          = ebb.bank_party_id
AND eba.branch_id             = ebb.branch_party_id
AND eba.bank_id               = eb.bank_party_id
AND assa.org_id                 =hou.organization_id
AND av.vendor_id               = :vendor_id
AND assa.vendor_site_id    = :vendor_site_id;

    RETURN(NULL);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(100,'No bank records for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

Formula Column2(CF_PAYMENT_METHOD_CODE)
--------------------------------
function CF_PAYMENT_METHOD_CODEFormula return Char is
lv_payment_method_code   VARCHAR2(30);
begin
 
 
  SELECT ieppm.payment_method_code
   INTO lv_payment_method_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id;
 

    RETURN(lv_payment_method_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(101,'No payment_method_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

formula Column3(CF_FORMAT_CODE)
-----------------------
function CF_FORMAT_CODEFormula return Char is
lv_format_code   VARCHAR2(30);
begin
 
 
  SELECT ifv.format_code
   INTO lv_format_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA,
        IBY_FORMATS_VL IFV
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id
    AND ifv.format_code(+) = iepa.payment_format_code;
 

    RETURN(lv_format_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(102,'No format_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;