R12 AR Customer Query

SELECT    hl.orig_system_reference
         ,hl.country
         ,hl.address1
         ,hl.address2
         ,hl.address3
         ,hl.address4
         ,hl.city
         ,hl.postal_code
         ,hl.state
         ,hl.province
         ,hl.county
         ,hl.content_source_type
         ,hl.actual_content_source actual_content_source
         ,hps.party_site_number
         ,hps.identifying_address_flag
         ,hps.status
         ,hps.party_site_name
         ,hps.created_by_module
         ,hps.actual_content_source site_actual_content_source
         ,hcasa.orig_system_reference site_orig_system_reference
         ,hcasa.status         acct_site_status
         ,hp.party_number
         ,hca.account_number
         ,hou.NAME operting_unit_name
         ,bill_to_flag
   FROM  ar.hz_locations        hl
        ,ar.hz_party_sites      hps
        ,ar.hz_cust_acct_sites_all  hcasa
        ,ar.hz_parties     hp
        ,ar.hz_cust_accounts   hca
        ,hr_operating_units hou
   WHERE hcasa.party_site_id     = hps.party_site_id
   AND   hps.location_id         = hl.location_id
   AND   hp.party_id             = hca.party_id
   AND   hp.party_id             = hps.party_id
   AND   hca.cust_account_id     = hcasa.cust_account_id
   AND   hcasa.org_id            = hou.organization_id
   AND   hp.party_type           ='ORGANIZATION'
   AND   hps.status              = 'A'
   AND   hcasa.status            = 'A'
   AND   hp.status               = 'A'
   AND   hca.status              = 'A'

R12 AR Customer Query

SELECT      hp.party_number
           ,hp.party_name
           ,hp.party_type
           ,hp.orig_system_reference
           --,hp.category_code
           --,hp.jgzz_fiscal_code         taxpayer_id
           ,hp.status
           ,hp.tax_name
           ,hp.person_first_name
           ,hp.person_middle_name
           ,hp.person_last_name
           ,hp.person_title
           ,hp.country
           ,hp.address1
           ,hp.address2
           ,hp.address3
           ,hp.address4
           ,hp.city
           ,hp.postal_code
           ,hp.state
           ,hp.province
           ,hca.status            account_status
           ,hca.created_by_module        
           ,hp.county
           ,hca.account_number        
           ,hca.customer_type
           ,hca.account_established_date
           ,hca.account_activation_date
           ,hca.account_name
           ,hca.tax_code
           --,hca.attribute2                                -- Added on 24-07-09
           ,hca.attribute3
           ,hcpc.profile_class_id
           ,hcpc.status rec_status
           ,hp.sic_code_type category_code      
           ,hca.customer_class_code
           ,hca.orig_system_reference      acct_orig_sys_ref
           ,hp.tax_reference            tax_reg_number        
           ,hcpc.NAME profile_class
           ,hcp.dunning_letters
           ,hcp.override_terms
           ,hcp.credit_balance_statements                 -- Added on 24-07-09
           ,hcpc.statements                               -- Added on 24-07-09
           ,rt.NAME payment_term
           ,arsc.NAME statement_cycle                     -- Added on 24-07-09
           ,arc.NAME  collector_name                      -- Added on 24-07-09
     FROM  ar.hz_parties              hp
          ,ar.hz_cust_accounts        hca
          ,ar.hz_customer_profiles    hcp
          ,ar.ra_terms_tl              rt
          ,ar.hz_cust_profile_classes hcpc
          ,ar.ar_statement_cycles   arsc
          ,ar.ar_collectors   arc
          --,ap_terms_tl att
     WHERE   hp.party_id                = hca.party_id
       AND   hcp.party_id        = hp.party_id
       AND   hca.cust_account_id    = hcp.cust_account_id
       AND   hcp.site_use_id              IS NULL
       AND   hca.payment_term_id        = rt.term_id(+)
       AND   hcpc.profile_class_id      = hcp.profile_class_id
       AND   arsc.statement_cycle_id(+) = hcpc.statement_cycle_id
       AND   arc.collector_id           = hcpc.collector_id
       AND   hp.status                  = 'A'
       AND   hca.status                 = 'A'
       AND   hcp.status                 = 'A'
       AND   hcpc.status                = 'A'
       AND   hp.party_type              = 'ORGANIZATION'
       --AND   hp.party_name='BARCLAYS BANK PLC'
       --and att.term_id(+)=rt.term_id
       --and rownum<11
       order by 2

R12 OPEN PO DISTRIBUTION

SELECT
ph.segment1 po_num,
hou.name Operating_unit,
pl.line_num,
pda.distribution_num,
--pla.quantity quantity_ordered,
pda.quantity_ordered,
pla.quantity_shipped quantity_shipped,
pla.quantity_cancelled quantity_cancelled,
pla.quantity_billed quantity_billed,
--pla.quantity
--pda.expenditure_item_date,
--pda.expenditure_type,
--ppa.name PROJECT_NAME,
--ppa.project_type,
ph.currency_code,
cast(pla.quantity_received as decimal (15,6)) quantity_received,
--pla.quantity_received,
--pl.quantity Line_quantity,
--pl.unit_price*pl.quantity Amt,
(select CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.code_combination_id ) charge_accounts ,
(select CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where pda.dest_charge_account_id =pda.accrual_account_id ) dest_charge_accounts ,
trunc(ph.creation_date),
povs.PAY_GROUP_LOOKUP_CODE,
povs.PAYMENT_METHOD_LOOKUP_CODE,
vendor_name,
vendor_site_code,
--povs.attribute1,
pla.days_early_receipt_allowed,
        pla.days_late_receipt_allowed,
        pla.receipt_days_exception_code,
        pla.last_accept_date,
        pla.qty_rcv_tolerance,
        pla.qty_rcv_exception_code ,
        --pla.receiving_routing_id,
        pla.enforce_ship_to_location_code,
        rrh.routing_name,
        pda.destination_type_code,
        --pda.deliver_to_person_id,
        --papf.full_name,
        (papf.last_name||','||papf.first_name) requester,
        --pda.deliver_to,
        hl1.location_code deliver_to,
        pda.destination_subinventory,
        --pda.quantity_ordered,
        pda.req_header_reference_num,
        pda.req_line_reference_num,
        pda.rate_date,
        (select CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.accrual_account_id ) accrual_accounts ,
        (select concatenated_segments from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id=pda.budget_account_id) po_budget_account,
        (select concatenated_segments from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id= pda.variance_account_id) po_variance_account,
        (select concatenated_segments from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id= pda.dest_variance_account_id) po_dest_variance_account,
        ppa.segment1 project_number,
ppa.name "Project_name",
pt.task_number ,
pt.task_name,
pda.award_id,--gms_awards_all to get award_number
pda.expenditure_type,
(select name from apps.hr_all_organization_units where organization_id=pda.expenditure_organization_id) EXPENDITURE_ORGANIZATION,
pda.expenditure_item_date

from apps.po_headers_all ph,
apps.po_lines_all pl,
apps.po_line_locations_all pla,
apps.hr_operating_units hou,
apps.po_line_types plt,
apps.pa_projects_all ppa,
apps.po_distributions_all pda,
apps.pa_tasks pt,
apps.gl_code_combinations_kfv gcc,
hr_locations hl,
hr_locations hl1,
apps.po_vendors pov,
apps.po_vendor_sites_all povs,
apps.rcv_routing_headers rrh,
apps.per_all_people_f papf
where 1=1 --ph.segment1=111120004
and ph.closed_code='OPEN'
and TRIM(ph.closed_code) not in( 'Finally Closed','Closed')
--and ph.segment1='TU672620'
and ph.po_header_id=pl.po_header_id
and pl.po_line_id=pla.po_line_id
and pl.line_type_id=plt.line_type_id
and ph.po_header_id=pda.po_header_id
and pda.line_location_id=pla.line_location_id
and pda.project_id=ppa.project_id(+)
and pda.task_id=pt.task_id(+)
--and (pda.project_id is null or pda.task_id is null)
and gcc.code_combination_id =pda.code_combination_id
and ph.AUTHORIZATION_STATUS ='APPROVED'
and ph.AUTHORIZATION_STATUS !='Rejected'
--AND trunc(ph.creation_date) >= to_date(:p_date) -- MM/DD/YYYY
--and ph.APPROVED_FLAG ='Y'
and hl.location_id=ph.ship_to_location_id
and hou.organization_id=pda.org_id
and ph.vendor_id = pov.vendor_id
and ph.vendor_site_id = povs.vendor_site_id
and pla.receiving_routing_id=rrh.routing_header_id
and papf.person_id=pda.deliver_to_person_id
and pda.deliver_to_location_id=hl1.location_id
order by ph.segment1

R12 OPEN PO HEADER

select distinct pha.segment1 PONUMBER,
        hou.name ORGANIZATION_CODE,
       PHA.type_lookup_code POTYPE,
       trunc(pha.creation_date) CDATE,
       pv.vendor_name supplier,
       pv.segment1 supplier_number,
       pvs.vendor_site_code suppliersite,
       --(pvc.first_name|| pvc.last_name) Contact,
       hl1.location_code Shipto,
       hl2.location_code BILLTO,
       pha.currency_code CURRENCY,
       --papf.full_name BUYER,
       (papf.last_name||','||papf.first_name) BUYER,
       pha.authorization_status postatus,
       --sum(pla.quantity*pla.unit_price) Linelevelprice,
       pha.comments COMMENTS,
       atl.name TERMS,
       --plla.need_by_date,
       plla.promised_date,
       pha.approved_date,
       pha.closed_code
from apps.po_headers_all pha,
apps.po_vendors pv,
apps.po_vendor_sites_all pvs,
--apps.po_vendor_contacts pvc,
hr_locations hl1,
hr_locations hl2,
apps.per_all_people_f papf,
apps.po_lines_all pla,
hr_operating_units hou,
apps.ap_terms_tl atl,
apps.po_line_locations_all plla
--po_distributions_all pda
where  pha.vendor_id=pv.vendor_id
and pha.vendor_site_id=pvs.vendor_site_id
and pha.ship_to_location_id= hl1.location_id
and pha.bill_to_location_id= hl2.location_id
and pha.agent_id=papf.person_id
and pha.po_header_id=pla.po_header_id
and pha.org_id= hou.organization_id
and pha.terms_id=atl.term_id(+)
and plla.po_header_id=pha.po_header_id
and pla.po_line_id=plla.po_line_id
and pha.org_id=plla.org_id
--and pla.po_line_id=pda.po_line_id
and pha.closed_code='OPEN'
and pha.AUTHORIZATION_STATUS ='APPROVED'
and pha.AUTHORIZATION_STATUS !='Rejected'
--and pha.segment1='111010101'
group by  pha.segment1,
        hou.name,
       PHA.type_lookup_code ,
       trunc(pha.creation_date) ,
       pv.vendor_name ,
       pv.segment1 ,
       pvs.vendor_site_code ,
       --(pvc.first_name|| pvc.last_name) Contact,
       hl1.location_code ,
       hl2.location_code ,
       pha.currency_code ,
       (papf.last_name||','||papf.first_name),
       pha.authorization_status ,
       --sum(pla.quantity*pla.unit_price) Linelevelprice,
       pha.comments ,
       atl.name,
       plla.need_by_date,
       plla.promised_date,
       pha.approved_date,
       pha.closed_code
     
       ORDER BY 1

OAF Hello Page

OAF Screen Shots.