AP Supplier Bank Info Query

 SELECT hou.name operating_unit,
  av.vendor_name                ,
  av.segment1 vendor_number     ,
  assa.vendor_site_code          ,
  eb.bank_name                 ,
  ebb.bank_branch_name         ,
  ebb.branch_number            ,
  eba.bank_account_num         ,
  eba.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 assa.org_id                 =89 ;

AP Supplier site Query in R12

 SELECT assa.vendor_site_id                                 ,
  iep.default_payment_method_code payment_method_lookup_code,
  hou.name operating_unit                                   ,
  av.vendor_name                                            ,
  av.segment1                                               ,
  av.vendor_id                                              ,
  assa.vendor_site_code                                     ,
  assa.address_line1                                        ,
  assa.county                                               ,
  assa.city                                                 ,
  assa.state                                                ,
  assa.country                                              ,
  assa.vendor_site_code_alt                                 ,
  assa.purchasing_site_flag                                 ,
  assa.rfq_only_site_flag                                   ,
  assa.pay_site_flag                                        ,
  assa.address_line2                                        ,
  assa.address_line3                                        ,
  assa.address_line4                                        ,
  assa.address_style                                        ,
  assa.zip                                                  ,
  assa.province                                             ,
  assa.area_code                                            ,
  assa.phone                                                ,
  assa.inactive_date                                        ,
  assa.fax                                                  ,
  assa.fax_area_code--,assa.payment_method_lookup_code
  ,
  assa.terms_date_basis                                ,
  assa.distribution_set_id                             ,
  gcck.concatenated_segments accts_pay_code_combination,
  cc.concatenated_segments prepay_code_combination     ,
  assa.pay_group_lookup_code                           ,
  assa.terms_id                                        ,
  apt.name term_name                                   ,
  assa.invoice_amount_limit                            ,
  assa.pay_date_basis_lookup_code                      ,
  assa.invoice_currency_code                           ,
  assa.payment_currency_code                           ,
  assa.hold_all_payments_flag                          ,
  assa.hold_future_payments_flag                       ,
  assa.hold_reason                                     ,
  assa.hold_unmatched_invoices_flag                    ,
  assa.allow_awt_flag                                  ,
  assa.awt_group_id                                    ,
  aw.tax_name                                          ,
  assa.bank_charge_bearer                              ,
  assa.country_of_origin_code                          ,
  assa.create_debit_memo_flag                          ,
  assa.offset_tax_flag                                 ,
  assa.supplier_notif_method                           ,
  assa.email_address                                   ,
  assa.remittance_email                                ,
  assa.primary_pay_site_flag
   FROM ap_suppliers av        ,
  ap_supplier_sites_all assa   ,
  hr_operating_units hou       ,
  gl_code_combinations_kfv gcck,
  gl_code_combinations_kfv cc  ,
  IBY_EXTERNAL_PAYEES_ALL iep  ,
  ap_terms_tl apt              ,
  AP_AWT_TAX_RATES_ALL AW
  WHERE av.vendor_id        =assa.vendor_id
AND assa.terms_id           =apt.term_id(+)
AND assa.org_id             = hou.organization_id
AND gcck.code_combination_id=assa.accts_pay_code_combination_id
AND cc.code_combination_id  =assa.prepay_code_combination_id
AND assa.vendor_site_id     =iep.supplier_site_id
AND ASSA.AWT_GROUP_ID       =AW.TAX_RATE_id(+)
  --and iep.default_payment_method_code is null
AND assa.org_id= 89;

OAF Profile Options(Basic Profile Options) OR How to Enable Personalization link for OAF Pages?

Set the value for following profiles

1.   FND: Personalization Region Link Enabled     to Yes 

(Minimal will also do but better to set Yes so that you get personalization links for all the regions)

2.Personalize Self-Service Defn                                 to Yes

3.Disable Self-Service Personal                                 to No

AP TO GL - PREPAY QUERY

select 
gps.period_name,
gjl.reference_2,
(select  distinct pha.segment1  from ap_invoice_lines_all ailla, po_headers_all pha where ailla.invoice_id=aia.invoice_id and ailla.po_header_id=pha.po_header_id) po_order_number,

(select distinct rsh.receipt_num
from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
where rsh.shipment_header_id=rsl.shipment_header_id
and rsl.shipment_header_id=rt.shipment_header_id
and rsl.shipment_line_id=rt.shipment_line_id
and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null) ) "Receipt Number",
(select distinct rt.transaction_date
from rcv_shipment_headers rsh, rcv_shipment_lines rsl,rcv_transactions rt, ap_invoice_lines_all aila
where rsh.shipment_header_id=rsl.shipment_header_id
and rsl.shipment_header_id=rt.shipment_header_id
and rsl.shipment_line_id=rt.shipment_line_id
and rt.transaction_id=(select distinct rcv_transaction_id from ap_invoice_lines_all aila where aila.invoice_id=aia.invoice_id and rownum=1 and rcv_transaction_id is not null)) "Receipt Date",
        aia.invoice_num   "Invoice Number",
        aia.gl_date   "Invoice Date",
        aps.vendor_name "Vendor Name",
        aps.segment1 "Vendor Number",
        gjh.je_source "Source",
        gjh.ledger_id "set_of_books_id" ,
        cc.concatenated_segments Segment,
        cc.concatenated_segments segment2,
        aia.payment_currency_code currency_code,
--       xal.entered_dr,
--       xal.entered_cr,
--       xal.accounted_dr,
--       xal.accounted_cr,
      
       XDL.UNROUNDED_ENTERED_DR entered_dr,
       XDL.UNROUNDED_ENTERED_CR entered_cr,
       XDL.UNROUNDED_ACCOUNTED_DR accounted_dr,
       XDL.UNROUNDED_ACCOUNTED_CR accounted_cr,
      
      
       gir.je_header_id,
       gir.je_line_num,
       cc.code_combination_id,
       --gps.application_id,
       --
       aia.doc_sequence_value "Vocher Number"
          ,aia.gl_date "GL_Date"
          ,NULL "Staff Number"
          ,null "Project"
          ,null "Customer Num/Name"
          ,xal.description "Description"
FROM ap_invoices_all aia,
ap_invoice_lines_all aila,
ap_invoice_distributions_all aida,
ap_prepay_app_dists apad,
xla_distribution_links xdl,
xla_ae_lines xal,
xla_ae_headers xah,
gl_import_references gir,
gl_je_lines gjl,
gl_je_headers gjh, gl_code_combinations_kfv cc,
    gl_period_statuses gps,
    apps.ap_suppliers aps
where aia.invoice_id=aila.invoice_id
and aila.invoice_id=aida.invoice_id
and aila.line_number=aida.invoice_line_number
--and aia.invoice_id=88734
and aida.line_type_lookup_code='PREPAY'
and aida.invoice_distribution_id= apad.prepay_app_distribution_id
and apad.amount<>0
and apad.prepay_app_dist_id=xdl.source_distribution_id_num_1
and xdl.applied_to_source_id_num_1 in (aila.prepay_invoice_id,aila.invoice_id)
and xdl.ae_header_id=xal.ae_header_id
and xdl.ae_line_num=xal.ae_line_num
and xal.ae_header_id=xah.ae_header_id
and xal.gl_sl_link_id= gir.gl_sl_link_id
and xal.gl_sl_link_table= gir.gl_sl_link_table
    and cc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
    and cc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
    and aia.VENDOR_ID=aps.VENDOR_ID   
and gir.je_header_id=gjl.je_header_id
and gir.je_line_num=gjl.je_line_num
and gjl.je_header_id=gjh.je_header_id
AND gjh.ledger_id=gps.ledger_id
  AND  101=gps.application_id      
  and gps.set_of_books_id=gjh.ledger_id
     and gjh.je_source ='Payables'
  and gjh.Ledger_ID not in ( 1014,1015,1016 )
AND gjh.period_name       = gps.period_name
    --and cc.segment5 in (:P_MIN_FLEX,:P_MAX_FLEX)
    and ( nvl(xal.accounted_cr,0)<>0 or nvl(xal.accounted_dr,0)<>0)
   
    --and aia.org_id=:P_ORG
    and xah.je_category_name='Purchase Invoices'
  AND (    NVL (gps.effective_period_num, 0) >=:cf_period_from
        AND (NVL (gps.effective_period_num, 0) <=:cf_period_to)
             ) 
                AND (    NVL (cc.segment5, 0) >= :p_account_from
        AND (NVL (cc.segment5, 0) <= :p_account_to)
       )
          
     AND (    NVL (cc.segment1, 0) >=:P_COMPANY_FROM
        AND (NVL (cc.segment1, 0) <= :P_COMPANY_TO)
       ) 
       AND aia.payment_currency_code =decode(:p_currency_code,'INR','INR',
                                              'ALL',aia.payment_currency_code,
                                              'NON INR',decode(aia.payment_currency_code,'INR','###',aia.payment_currency_code))
                                              order by 6;

Purchase Requisition to Receipt & Purchase Requisition to Invoice

select * from (select prha.created_by,hou.name operating_unit,pha.org_id,prha.requisition_header_id
,prha.segment1 pr_number
,prha.creation_date pr_creation_date
,to_char(prha.creation_date,'MON-RRRR') pr_month
, prha.authorization_status pr_status
, prla.line_num pr_line_num
,plt.line_type pr_line_type
,msib.segment1 pr_item_name
,mct.description pr_item_category
,prla.item_description pr_description
,prla.unit_meas_lookup_code pr_uom
,prla.quantity pr_quantity
,prla.unit_price pr_unit_price
,(prla.quantity * prla.unit_price) pr_amount
,'INR' pr_currency_code
,prla.rate pr_rate
,PAPF2.FULL_NAME pr_requester
,HRL1.LOCATION_CODE pr_location_code
,gcck.segment1 pr_company
,gcck.segment2 pr_location
,gcck.segment3 pr_vertical
,gcck.segment4 pr_costcenter
,gcck.segment5 pr_account
,gcck.segment6 pr_inter_company
,gcck.segment7 pr_practice
,gcck.segment8 pr_future_2
,ppa.segment1 pr_project
,PRLA.NOTE_TO_AGENT pr_agent_note
,PRLA.NOTE_TO_RECEIVER pr_receiver_note
,prha.approved_date pr_approved_date
,pha.segment1 po_number
,pha.creation_date po_date
,to_char(pha.creation_date,'MON-RRRR') po_month
,papf.full_name po_buyer
,pla.line_num po_line_num
,pla.item_description po_description
,plla.need_by_date  po_needby_date
,pla.unit_meas_lookup_code po_uom
,pla.quantity po_quantity
,pla.unit_price po_price
,(pla.quantity*pla.unit_price) po_amount
,pha.currency_code po_currency
,pda.rate po_rate
,av.vendor_name
,assa.vendor_site_code
,(assa.address_line1||' '||assa.address_line2) address
,pha.authorization_status
,rsh.receipt_num  grir_number
,rt.transaction_date grir_date
,to_char(transaction_date,'MON-RRRR') grir_month--,aida.invoice_num,aida.invoice_date,aida.invoice_currency_code
--,aia.invoice_num,aia.invoice_date,aila.amount,aia.payment_currency_code
,null invoice_num,null invoice_date,null invoice_amount,null currency_code
from po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_req_distributions_all prda,
po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
po.rcv_shipment_lines rsl,
po.rcv_shipment_headers rsh,
apps.rcv_transactions rt--,po_action_history_v pahv
--,ap_invoice_distributions_all aida--,
--ap_invoice_lines_all aila,
--ap_invoices_all aia
--,invoice_view aida
, hr_operating_units hou
,PO_LINE_TYPES plt,
mtl_categories_tl mct,
mtl_system_items_b msib,
per_all_people_f  PAPF2
,HR_LOCATIONS_ALL_TL HRL1,
gl_code_combinations_kfv gcck,
pa_projects_all ppa,
per_all_people_f papf,
apps.po_line_locations_all plla,
ap_suppliers av,
ap_supplier_sites_all assa, fnd_user fu
where prha.requisition_header_id= prla.requisition_header_id
and prla.requisition_line_id=prda.requisition_line_id
--and prha.segment1='111230054'--'111230084'--
and prda.distribution_id=pda.req_distribution_id
and pda.po_line_id=pla.po_line_id
and pla.po_header_id=pha.po_header_id
and pda.po_distribution_id=rsl.po_distribution_id(+)
AND rsl.shipment_header_id = rsh.shipment_header_id(+)
and rt.shipment_line_id(+)=rsl.shipment_line_id
and rt.transaction_id is null
and prha.org_id=hou.organization_id
and prla.line_type_id=plt.line_type_id
and prla.category_id=mct.category_id
and prla.item_id=msib.inventory_item_id(+)
AND PAPF2.PERSON_ID         = prla.TO_PERSON_ID
AND HRL1.LOCATION_ID(+) = PRLA.DELIVER_TO_LOCATION_ID
AND HRL1.LANGUAGE(+) = USERENV('LANG')
and gcck.code_combination_id=prda.code_combination_id
and prda.project_id=ppa.project_id(+)
and pha.agent_id=papf.person_id
and pha.po_header_id=plla.po_header_id
  and pla.po_line_id=plla.po_line_id
  and pha.vendor_id=av.vendor_id
  and av.vendor_id=assa.vendor_id
  and pha.org_id=assa.org_id
  and fu.user_id=prha.created_by
union all
select prha.created_by,hou.name operating_unit,pha.org_id,prha.requisition_header_id
,prha.segment1 pr_number
,prha.creation_date pr_creation_date
,to_char(prha.creation_date,'MON-RRRR') pr_month
, prha.authorization_status pr_status
, prla.line_num pr_line_num
,plt.line_type pr_line_type
,msib.segment1 pr_item_name
,mct.description pr_item_category
,prla.item_description pr_description
,prla.unit_meas_lookup_code pr_uom
,prla.quantity pr_quantity
,prla.unit_price pr_unit_price
,(prla.quantity * prla.unit_price) pr_amount
,'INR' pr_currency_code
,prla.rate pr_rate
,PAPF2.FULL_NAME pr_requester
,HRL1.LOCATION_CODE pr_location
,gcck.segment1 pr_company
,gcck.segment2 pr_location_code
,gcck.segment3 pr_vertical
,gcck.segment4 pr_costcenter
,gcck.segment5 pr_account
,gcck.segment6 pr_inter_company
,gcck.segment7 pr_practice
,gcck.segment8 pr_future_2
,ppa.segment1 pr_project
,PRLA.NOTE_TO_AGENT pr_agent_note
,PRLA.NOTE_TO_RECEIVER pr_receiver_note
,prha.approved_date pr_approved_date
,pha.segment1 po_number
,pha.creation_date po_date
,to_char(pha.creation_date,'MON-RRRR') po_month
,papf.full_name po_buyer
,pla.line_num po_line_num
,pla.item_description po_description
,plla.need_by_date  po_needby_date
,pla.unit_meas_lookup_code po_uom
,pla.quantity po_quantity
,pla.unit_price po_price
,(pla.quantity*pla.unit_price) po_amount
,pha.currency_code po_currency
,pda.rate po_rate
,av.vendor_name
,assa.vendor_site_code
,(assa.address_line1||' '||assa.address_line2) address
,pha.authorization_status
,rsh.receipt_num  grir_number
,rt.transaction_date grir_date
,to_char(transaction_date,'MON-RRRR') grir_month
,aia.invoice_num,aia.invoice_date,aila.amount,aia.payment_currency_code currency_code
from po_requisition_headers_all prha,
po_requisition_lines_all prla,
po_req_distributions_all prda,
po_headers_all pha,
po_lines_all pla,
po_distributions_all pda,
po.rcv_shipment_lines rsl,
po.rcv_shipment_headers rsh,
apps.rcv_transactions rt--,po_action_history_v pahv
,ap_invoice_distributions_all aida,
ap_invoice_lines_all aila,
ap_invoices_all aia
, hr_operating_units hou
,PO_LINE_TYPES plt,
mtl_categories_tl mct,
mtl_system_items_b msib,
per_all_people_f  PAPF2
,HR_LOCATIONS_ALL_TL HRL1,
gl_code_combinations_kfv gcck,
pa_projects_all ppa,
per_all_people_f papf,
apps.po_line_locations_all plla,
ap_suppliers av,
ap_supplier_sites_all assa, fnd_user fu
where prha.requisition_header_id= prla.requisition_header_id
and prla.requisition_line_id=prda.requisition_line_id
--and prha.segment1='111230054'--'111230084'--
and prda.distribution_id=pda.req_distribution_id
and pda.po_line_id=pla.po_line_id
and pla.po_header_id=pha.po_header_id
AND rsl.shipment_header_id = rsh.shipment_header_id
AND pla.po_line_id = rsl.po_line_id
and pda.po_distribution_id=rsl.po_distribution_id
and pha.po_header_id=rsl.po_header_id
and rt.shipment_header_id = rsh.shipment_header_id
and rt.shipment_line_id=rsl.shipment_line_id
and transaction_type='RECEIVE'
--and rt.po_distribution_id=aida.po_distribution_id
and rt.po_distribution_id=aila.po_distribution_id(+)
--and rt.po_line_id=aila.po_line_id
--and rt.po_header_id=aila.po_header_id
and aia.invoice_id=aila.invoice_id
and aila.invoice_id=aida.invoice_id
and aila.line_number=aida.invoice_line_number
and rt.transaction_id= aida.rcv_transaction_id(+)
and prha.org_id=hou.organization_id
and prla.line_type_id=plt.line_type_id
and prla.category_id=mct.category_id
and prla.item_id=msib.inventory_item_id(+)
AND PAPF2.PERSON_ID         = prla.TO_PERSON_ID
AND HRL1.LOCATION_ID(+) = PRLA.DELIVER_TO_LOCATION_ID
AND HRL1.LANGUAGE(+) = USERENV('LANG')
and gcck.code_combination_id=prda.code_combination_id
and prda.project_id=ppa.project_id(+)
and pha.agent_id=papf.person_id
and pha.po_header_id=plla.po_header_id
  and pla.po_line_id=plla.po_line_id
  and pha.vendor_id=av.vendor_id
  and av.vendor_id=assa.vendor_id
  and fu.user_id=prha.created_by
  and pha.org_id=assa.org_id) aa
  where 1=1--pr_number in ('111230083','111230084','111230054')
  and aa.pr_number between NVL(:p_requisition_number_low,aa.pr_number)  and  NVL(:p_requisition_number_high,aa.pr_number) 
  and TO_DATE(aa.pr_creation_date,'DD-MON-RRRR') BETWEEN NVL(TO_DATE(:p_req_date_low,'DD-MON-RRRR'),TO_DATE(aa.pr_creation_date,'DD-MON-RRRR'))
  and NVL(TO_DATE(:p_req_date_high,'DD-MON-RRRR'),TO_DATE(aa.pr_creation_date,'DD-MON-RRRR'))-- NVL(:p_req_date_low,a.pr_creation_date)
and aa.created_by = :p_created_by
and aa.org_id=:P_ORG
  order by pr_number,pr_line_num;

PO DISTRIBUTIONS QUERY 11i/R12

SELECT
ph.segment1 po_num,
hou.name Operating_unit,
pl.line_num,
pda.distribution_num,
pda.quantity_ordered,
pla.quantity_shipped quantity_shipped,
pla.quantity_cancelled quantity_cancelled,
pla.quantity_billed quantity_billed,
ph.currency_code,
cast(pla.quantity_received as decimal (15,6)),
(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,
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 requester,
        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.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 gcc.code_combination_id =pda.code_combination_id
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

PO LINES QUERY 11i/R12

select pha.segment1 PO_Number
       ,pla.line_num "Line Number"
       ,plla.shipment_num "Shipment Number"
       ,plt.line_type "Line Type"
       ,hou.name Operating_Unit_Code
      ,msib.segment1 "ITEM NAME"
       ,pla.item_revision "ITEM REVISION"
       ,mic.description  "ITEM CATEGORY"
       ,pla.item_description "ITEM DESCRIPTION"
       ,pla.unit_meas_lookup_code UOM
       ,pla.quantity
       ,pla.unit_price
       ,pha.currency_code
       ,plla.promised_date
       ,plla.need_by_date
       ,pla.LIST_PRICE_PER_UNIT
       ,plla.note_to_receiver
       ,pla.note_to_vendor
       ,(pla.unit_price*pla.quantity) Amt
       ,pda.expenditure_type
       ,
      
(select name from apps.hr_all_organization_units where organization_id=plla.ship_to_organization_id) ship_to_org_id,
(select location_code from apps.hr_locations where location_id=plla.ship_to_location_id) Ship_to_location,
(select name from apps.hr_all_organization_units where organization_id=pda.expenditure_organization_id) EXPENDITURE_ORGANIZATION,
(select gcc.CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.code_combination_id ) charge_accounts ,
(select gcc.CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.accrual_account_id ) accrual_accounts ,
PHA.closed_code,
pha.authorization_status
        from apps.po_lines_all pla,
        apps.po_headers_all pha,
        apps.po_line_types plt,
        apps.mtl_system_items_b msib,
        apps.mtl_categories_tl mic,
        hr_operating_units hou,
        apps.po_distributions_all pda,
        apps.po_line_locations_all plla
where  pha.po_header_id=pla.po_header_id
and pha.po_header_id=pda.po_header_id
and pla.po_line_id=pda.po_line_id
  and plt.line_type_id=pla.line_type_id(+)
  and pla.item_id=msib.inventory_item_id(+)
  and pla.category_id= mic.category_id
  and pha.org_id=hou.organization_id
  and pla.org_id=hou.organization_id
  and pha.po_header_id=plla.po_header_id
  and pla.po_line_id=plla.po_line_id
  order by pha.segment1