R12 Oracle Apps: September 2012

Wednesday, September 26, 2012

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;

Tuesday, September 11, 2012

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

PO HEADER QUERY 11i/R12

select 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,
       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.closed_code
from apps.po_headers_all pha,
apps.ap_suppliers 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'
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.full_name ,
       pha.authorization_status ,
       --sum(pla.quantity*pla.unit_price) Linelevelprice,
       pha.comments ,
       atl.name,
       plla.need_by_date,
       plla.promised_date,
       pha.closed_code

SLA or XLA(AP-GL,RECEIPT-GL,AR-GL)(SubledgerAccounting)

SELECT DISTINCT PS.PERIOD_NAME,
GJL.REFERENCE_2,
TO_CHAR (NULL) PO_ORDER_NUMBER,
TO_CHAR (NULL) "RECEIPT NUMBER",
TO_DATE (NULL) "RECEIPT DATE",
TO_CHAR (NULL) "INVOICE NUMBER",
TO_DATE (NULL) "INVOICE DATE",
TO_CHAR (NULL) "VENDOR NAME",
TO_CHAR (NULL) "VENDOR NUMBER",
JE_SOURCE "SOURCE",
GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',GSCV.CHART_OF_ACCOUNTS_ID, GSCV.CODE_COMBINATION_ID) SEGMENT2,
GJH.CURRENCY_CODE,
GJL.ENTERED_DR ,
GJL.ENTERED_CR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_DR )) ACCOUNTED_DR,
TO_NUMBER(DECODE ( LR.RELATIONSHIP_TYPE_CODE, 'BALANCE', NULL, GJL.ACCOUNTED_CR )) ACCOUNTED_CR,
GJH.JE_HEADER_ID,
GJL.JE_LINE_NUM,
GJL.CODE_COMBINATION_ID ,
GJH.DOC_SEQUENCE_VALUE "VOCHER NUMBER",
GJH.DEFAULT_EFFECTIVE_DATE GL_DATE,
GJL.ATTRIBUTE1 "STAFF NUMBER",
GJL.ATTRIBUTE2 "PROJECT",
GJL.ATTRIBUTE3 "CUSTOMER NUM/NAME" ,
GJL.DESCRIPTION DESCRIPTION
FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH, GL_SUMMARY_COMBINATIONS_V GSCV, GL_LEDGERS,GL_PERIOD_STATUSES PS,GL_JE_BATCHES B,
GL_LEDGER_RELATIONSHIPS LR
WHERE GSCV.CODE_COMBINATION_ID =GJL.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJL.PERIOD_NAME       = PS.PERIOD_NAME
AND PS.LEDGER_ID            = GJL.LEDGER_ID
AND B.JE_BATCH_ID           = GJH.JE_BATCH_ID
AND GJH.LEDGER_ID             = LR.SOURCE_LEDGER_ID
AND LR.SOURCE_LEDGER_ID = LR.TARGET_LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND GJH.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES','COST MANAGEMENT','CONSOLIDATION','RECEIVABLES')
AND GJH.LEDGER_ID = GL_LEDGERS.LEDGER_ID
--- AND GL_LEDGERS.SUSPENSE_ALLOWED_FLAG = 'P'
AND (    NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND (    NVL (GSCV.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (GSCV.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND (    NVL (GSCV.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (GSCV.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',GJH.CURRENCY_CODE,
'NON INR',DECODE(GJH.CURRENCY_CODE,'INR','###',GJH.CURRENCY_CODE))
UNION ALL


SELECT DISTINCT B.PERIOD_NAME, D.REFERENCE_2, AAA.PO_ORDER_NUMBER, AAA.RCV_RECEIPT_NUM "RECEIPT NUMBER",
AAA.TRX_DATE "RECEIPT DATE", AAA.TRX_NUMBER_DISPLAYED "INVOICE NUMBER",
AAA.ACCOUNTING_DATE "INVOICE DATE", AAA.THIRD_PARTY_NAME "VENDOR NAME",
AAA.THIRD_PARTY_NUMBER "VENDOR NUMBER", B.JE_SOURCE "SOURCE", B.LEDGER_ID "SET_OF_BOOKS_ID" ,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID) SEGMENT,
FND_FLEX_EXT.GET_SEGS('SQLGL','GL#',CCC.CHART_OF_ACCOUNTS_ID, CCC.CODE_COMBINATION_ID) SEGMENT2,
AAA.CURRENCY_CODE, AAA.ENTERED_DR, AAA.ENTERED_CR, AAA.ACCOUNTED_DR,
AAA.ACCOUNTED_CR, AAA.JE_HEADER_ID, AAA.JE_LINE_NUM, AAA.CODE_COMBINATION_ID,
AAA.DOC_SEQUENCE_VALUE "VOCHER NUMBER", AAA.GL_DATE "GL_DATE",
AAA.ATTRIBUTE1 "STAFF NUMBER", AAA.ATTRIBUTE2 "PROJECT",
AAA.ATTRIBUTE3 "CUSTOMER NUM/NAME", AAA.DESCRIPTION "DESCRIPTION"
FROM (SELECT DISTINCT POH.SEGMENT1 PO_ORDER_NUMBER, RSH.RECEIPT_NUM RCV_RECEIPT_NUM,
               RCT.TRANSACTION_DATE TRX_DATE, API.INVOICE_NUM TRX_NUMBER_DISPLAYED,
               RRS.ACCOUNTING_DATE ACCOUNTING_DATE,
               POV.VENDOR_NAME THIRD_PARTY_NAME,
               POV.SEGMENT1 THIRD_PARTY_NUMBER, RRS.CURRENCY_CODE,
               RRS.ENTERED_DR, RRS.ENTERED_CR, RRS.ACCOUNTED_DR,
               RRS.ACCOUNTED_CR, R.JE_HEADER_ID, R.JE_LINE_NUM,
               RRS.CODE_COMBINATION_ID, 201 APPLICATION_ID,
               TO_DATE (NULL) GL_DATE, TO_NUMBER (NULL) DOC_SEQUENCE_VALUE,
               TO_CHAR (NULL) DESCRIPTION, TO_CHAR (NULL) ATTRIBUTE1,
               TO_CHAR (NULL) ATTRIBUTE2, TO_CHAR (NULL) ATTRIBUTE3
               --POH.ORG_ID
          FROM   PO_HEADERS_ALL POH, PO_LINES_ALL PL, PO_DISTRIBUTIONS_ALL POD
, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, RCV_TRANSACTIONS RCT, RCV_RECEIVING_SUB_LEDGER RRS
, AP_INVOICE_DISTRIBUTIONS_ALL AIDA,AP_INVOICES_ALL API , XLA_AE_HEADERS XAH , XLA_AE_LINES XAL
,GL_IMPORT_REFERENCES R
,AP_SUPPLIERS POV
     WHERE 1=1
AND POH.PO_HEADER_ID=PL.PO_HEADER_ID
AND POH.PO_HEADER_ID=POD.PO_HEADER_ID
AND POH.PO_HEADER_ID= RSL.PO_HEADER_ID(+)
AND RSL.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID(+)
AND RSH.SHIPMENT_HEADER_ID=RCT.SHIPMENT_HEADER_ID(+)
AND POD.PO_DISTRIBUTION_ID= AIDA.PO_DISTRIBUTION_ID(+)
AND API.INVOICE_ID(+)=AIDA.INVOICE_ID
AND POD.PO_DISTRIBUTION_ID = RRS.REFERENCE3
AND RCT.TRANSACTION_ID = RRS.RCV_TRANSACTION_ID
AND AIDA.ACCOUNTING_EVENT_ID=XAH.EVENT_ID(+)
AND XAH.AE_HEADER_ID=XAL.AE_HEADER_ID(+)
AND XAL.GL_SL_LINK_ID= R.GL_SL_LINK_ID(+)
AND R.REFERENCE_8=XAL.AE_LINE_NUM
AND POH.VENDOR_ID=POV.VENDOR_ID
AND AIDA.LINE_TYPE_LOOKUP_CODE(+)='ACCRUAL'
AND RRS.ACCOUNTING_LINE_TYPE='ACCRUAL'
AND XAL.ACCOUNTING_CLASS_CODE(+)='LIABILITY'
AND POD.DESTINATION_TYPE_CODE='EXPENSE'
           AND EXISTS (SELECT 1
                 FROM AP_INVOICE_DISTRIBUTIONS_ALL AID,AP_INVOICES_ALL AI
        WHERE AID.PO_DISTRIBUTION_ID=POD.PO_DISTRIBUTION_ID
        AND AI.INVOICE_ID = AID.INVOICE_ID
                       AND AI.VENDOR_ID = POV.VENDOR_ID) )           AAA,
       GL_JE_HEADERS B, GL_JE_LINES D,GL_JE_BATCHES E, GL_PERIOD_STATUSES PS,
       GL_SUMMARY_COMBINATIONS_V         CCC
WHERE B.JE_HEADER_ID = AAA.JE_HEADER_ID
AND D.JE_LINE_NUM = AAA.JE_LINE_NUM
AND B.JE_HEADER_ID=D.JE_HEADER_ID
AND D.CODE_COMBINATION_ID = CCC.CODE_COMBINATION_ID
AND AAA.CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
AND PS.LEDGER_ID            = D.LEDGER_ID
AND B.ACTUAL_FLAG = 'A'
AND B.STATUS || '' = 'P'
AND B.CURRENCY_CODE != 'STAT'
AND JE_SOURCE NOT IN ('PAYABLES', 'PURCHASING','CONSOLIDATION')
--AND     AAA.ORG_ID=:P_ORG
AND (    NVL (PS.EFFECTIVE_PERIOD_NUM, 0) >= :CF_PERIOD_FROM
AND (NVL (PS.EFFECTIVE_PERIOD_NUM, 0) <= :CF_PERIOD_TO)
)
AND (    NVL (CCC.SEGMENT5, 0) >= :P_ACCOUNT_FROM
AND (NVL (CCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
)
AND (    NVL (CCC.SEGMENT1, 0) >=:P_COMPANY_FROM
AND (NVL (CCC.SEGMENT1, 0) <= :P_COMPANY_TO)
)
AND B.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND B.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
'ALL',B.CURRENCY_CODE,
'NON INR',DECODE(B.CURRENCY_CODE,'INR','###',B.CURRENCY_CODE))
UNION ALL
--PO-RECEIPT-GL
SELECT GPS.PERIOD_NAME,
        L.REFERENCE_2,
        PHA.SEGMENT1 PO_ORDER_NUMBER,
        RSH.RECEIPT_NUM "RECEIPT NUMBER",
        RT.TRANSACTION_DATE "RECEIPT DATE",
        NULL   "INVOICE NUMBER",
        NULL   "INVOICE DATE",
        PV.VENDOR_NAME "VENDOR NAME",
        PV.SEGMENT1 "VENDOR NUMBER",
        H.JE_SOURCE "SOURCE",
        H.LEDGER_ID "SET_OF_BOOKS_ID" ,
        GCC.CONCATENATED_SEGMENTS SEGMENT,
        GCC.CONCATENATED_SEGMENTS SEGMENT2,
        PHA.CURRENCY_CODE,
--       PLA.LINE_NUM,
--       PLLA.SHIPMENT_NUM,
--       PLA.ITEM_DESCRIPTION,
--       PLA.UNIT_PRICE,
--       PLLA.QUANTITY,
--       PLLA.QUANTITY_RECEIVED,
--       PLLA.QUANTITY_BILLED,
--       RT.TRANSACTION_TYPE,
       --RT.TRANSACTION_DATE,
       XAL.ENTERED_DR,
       XAL.ENTERED_CR,
       XAL.ACCOUNTED_DR,
       XAL.ACCOUNTED_CR,
       GIR.JE_HEADER_ID,
       GIR.JE_LINE_NUM,
       RRSL.CODE_COMBINATION_ID,
       --GPS.APPLICATION_ID,
       --
       TO_NUMBER (NULL) "VOCHER NUMBER",
TO_DATE (NULL) GL_DATE,
                TO_CHAR (NULL) "STAFF NUMBER",
               TO_CHAR (NULL) "PROJECT",
               TO_CHAR (NULL) "CUSTOMER NUM/NAME" ,
               --PHA.ORG_ID,
--               TO_CHAR (NULL) DESCRIPTION
               RSL.ITEM_DESCRIPTION "DESCRIPTION"
FROM GL.GL_JE_HEADERS            H,
       GL.GL_JE_LINES                L,
       GL_CODE_COMBINATIONS_KFV     GCC,
       GL.GL_IMPORT_REFERENCES     GIR,
       APPS.XLA_AE_LINES             XAL,
       APPS.XLA_DISTRIBUTION_LINKS   XDL,
       PO.RCV_RECEIVING_SUB_LEDGER RRSL,
       PO.RCV_TRANSACTIONS RT,
       PO.PO_HEADERS_ALL PHA,
       PO.PO_LINES_ALL PLA,
       PO.PO_LINE_LOCATIONS_ALL PLLA,
       APPS.PO_VENDORS PV,
       RCV_SHIPMENT_HEADERS RSH,
       RCV_SHIPMENT_LINES RSL,
       GL_PERIOD_STATUSES GPS
WHERE 1=1
AND H.JE_HEADER_ID = L.JE_HEADER_ID
AND L.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND (    NVL (GCC.SEGMENT5, 0) >=:P_ACCOUNT_FROM
AND (NVL (GCC.SEGMENT5, 0) <= :P_ACCOUNT_TO)
       )

     AND (    NVL (GCC.SEGMENT1, 0) >=:P_COMPANY_FROM
        AND (NVL (GCC.SEGMENT1, 0) <= :P_COMPANY_TO)
       )
   AND L.JE_HEADER_ID = GIR.JE_HEADER_ID
   AND L.JE_LINE_NUM = GIR.JE_LINE_NUM
   AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
   AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
   AND XAL.AE_LINE_NUM = XDL.AE_LINE_NUM
   AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = RRSL.RCV_SUB_LEDGER_ID
   AND RRSL.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
   AND RT.PO_HEADER_ID = PHA.PO_HEADER_ID
   AND RT.PO_HEADER_ID = PLA.PO_HEADER_ID
   AND RT.PO_LINE_ID = PLA.PO_LINE_ID
   AND PLA.PO_HEADER_ID = PLLA.PO_HEADER_ID
   AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
   AND PHA.VENDOR_ID = PV.VENDOR_ID
   --AND H.LEDGER_ID = 2041--UTSC
   AND H.JE_SOURCE = 'COST MANAGEMENT'
   AND RT.SHIPMENT_HEADER_ID=RSH.SHIPMENT_HEADER_ID
   AND RSH.SHIPMENT_HEADER_ID=RSL.SHIPMENT_HEADER_ID
   AND RT.SHIPMENT_LINE_ID =RSL.SHIPMENT_LINE_ID
   AND L.PERIOD_NAME       = GPS.PERIOD_NAME
   AND GPS.LEDGER_ID            = L.LEDGER_ID
   AND GPS.APPLICATION_ID       = 101
AND (    NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
        AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
             )

   --AND H.JE_CATEGORY = 'RECEIVING'
   --AND H.STATUS = 'P'
   --AND H.PERIOD_NAME IN ('MAY-12')
AND H.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND   H.CURRENCY_CODE != 'STAT'
AND   H.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
                                              'ALL',H.CURRENCY_CODE,
                                              'NON INR',DECODE(H.CURRENCY_CODE,'INR','###',H.CURRENCY_CODE))
UNION ALL

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,
       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 APPS.AP_INVOICES_ALL AIA,
    XLA.XLA_TRANSACTION_ENTITIES XTE,
    APPS.XLA_EVENTS XEV,
    APPS.XLA_AE_HEADERS XAH,
    APPS.XLA_AE_LINES XAL,
    APPS.GL_IMPORT_REFERENCES GIR,
    APPS.GL_JE_HEADERS GJH,
    APPS.GL_JE_LINES GJL,
    APPS.GL_CODE_COMBINATIONS_KFV CC,
    APPS.AP_SUPPLIERS APS,
    GL_PERIOD_STATUSES GPS

WHERE AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
    AND XEV.ENTITY_ID= XTE.ENTITY_ID
    AND XAH.ENTITY_ID= XTE.ENTITY_ID
    AND XAH.EVENT_ID= XEV.EVENT_ID
    AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
    --AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
    AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
    AND GJH.STATUS = 'P'
    AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
    AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
    AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
    AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
    AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
    AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
    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 GJH.STATUS='P'
    AND GJH.ACTUAL_FLAG='A'
    AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
    --AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
    --AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
    --AND CC.SEGMENT1=111
    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 24116=:P_ACCOUNT_FROM
--           AND 24116=:P_ACCOUNT_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))



UNION ALL
SELECT
              GJH.PERIOD_NAME,
              NULL REFERENCE_2,
              NULL PO_ORDER_NUMBER,
              NULL "RECEIPT NUMBER",
              NULL "RECEIPT DATE",
          ENT.TRANSACTION_NUMBER "INVOICE NUMBER",
          AIA.CHECK_DATE "INVOICE DATE",
          AV.VENDOR_NAME "VENDOR NAME",
          AV.SEGMENT1 "VENDOR NUMBER",
          GJH.JE_SOURCE "SOURCE",
          GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
          GCCK.CONCATENATED_SEGMENTS SEGMENT,
          GCCK.CONCATENATED_SEGMENTS SEGMENT21,
          AIA.CURRENCY_CODE CURRENCY_CODE
          ,AEL.ENTERED_DR,
          AEL.ENTERED_CR,
          AEL.ACCOUNTED_DR,
          AEL.ACCOUNTED_CR,
          GIR.JE_HEADER_ID,
          GIR.JE_LINE_NUM,
          GCCK.CODE_COMBINATION_ID
          ,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
          ,AEH.ACCOUNTING_DATE "GL_DATE"
          ,NULL "STAFF NUMBER"
          ,NULL "PROJECT"
          ,NULL "CUSTOMER NUM/NAME"
          ,AEL.DESCRIPTION "DESCRIPTION"
          FROM
          XLA_AE_HEADERS                   AEH
         ,XLA_AE_LINES                     AEL
         ,XLA_EVENTS                       XLE
         ,XLA_EVENT_TYPES_TL               XET
         ,XLA_TRANSACTION_ENTITIES         ENT
         ,GL_LEDGERS                       GLG
         ,GL_PERIODS                       GLP
         ,XLA_SUBLEDGERS                   XLS
         ,GL_CODE_COMBINATIONS_KFV         GCCK
               ,GL_IMPORT_REFERENCES             GIR
          ,GL_JE_LINES                      GJL
          ,GL_JE_HEADERS                    GJH
          ,GL_JE_BATCHES                    GJB
          --,AP_INVOICE_DISTRIBUTIONS_ALL AID
--          ,AP_INVOICE_LINES_ALL AILA
          ,AP_CHECKS_ALL AIA
          ,GL_PERIOD_STATUSES GPS
          ,AP_SUPPLIERS AV
          --,PA_PROJECT_CUSTOMERS_V PPCV
          --,PA_PROJECTS_ALL PPA
           WHERE 1=1
           AND AEH.LEDGER_ID              = GLG.LEDGER_ID
           AND AEL.APPLICATION_ID         = AEH.APPLICATION_ID
           AND AEL.AE_HEADER_ID           = AEH.AE_HEADER_ID
           AND XLE.APPLICATION_ID         = AEH.APPLICATION_ID
           AND XLE.EVENT_ID               = AEH.EVENT_ID
           AND XET.APPLICATION_ID         = XLE.APPLICATION_ID
           AND XET.EVENT_TYPE_CODE        = XLE.EVENT_TYPE_CODE
           AND XET.LANGUAGE               = USERENV('LANG')
           AND ENT.APPLICATION_ID         = AEH.APPLICATION_ID
           AND ENT.ENTITY_ID              = AEH.ENTITY_ID
           AND GLP.PERIOD_NAME            = AEH.PERIOD_NAME
           AND GLP.PERIOD_SET_NAME        = GLG.PERIOD_SET_NAME
           AND XLS.APPLICATION_ID         = AEH.APPLICATION_ID
           AND GCCK.CODE_COMBINATION_ID   = AEL.CODE_COMBINATION_ID
           AND AEH.ACCOUNTING_ENTRY_STATUS_CODE <> 'N' AND
AEH.APPLICATION_ID = 200 AND AEH.BALANCE_TYPE_CODE = 'A' AND (
NVL(AEL.ACCOUNTED_CR,0) <> 0
                                       OR NVL(AEL.ACCOUNTED_DR,0) <> 0 )
           AND GIR.GL_SL_LINK_ID(+)       = AEL.GL_SL_LINK_ID
           AND GIR.GL_SL_LINK_TABLE(+)    = AEL.GL_SL_LINK_TABLE
           AND GJL.JE_HEADER_ID(+)        = GIR.JE_HEADER_ID
           AND GJL.JE_LINE_NUM(+)         = GIR.JE_LINE_NUM
           AND GJH.JE_HEADER_ID(+)        = GIR.JE_HEADER_ID
           AND GJB.JE_BATCH_ID (+)        = GIR.JE_BATCH_ID
           AND DECODE(GJH.JE_HEADER_ID,NULL,'Y',GJH.JE_FROM_SLA_FLAG) IN ('U', 'Y')
           --AND AEL.ACCOUNTING_CLASS_CODE   IN ('ITEM EXPENSE','CASH_CLEARING','LIABILITY','PREPAID_EXPENSE','RTAX','INTRA')
AND ENT.SOURCE_ID_INT_1 = AIA.CHECK_ID
          -- AND AIA.DOC_SEQUENCE_VALUE=GIR.SUBLEDGER_DOC_SEQUENCE_VALUE
           AND AIA.VENDOR_ID=AV.VENDOR_ID
           AND GJH.JE_SOURCE ='PAYABLES'
           AND AEH.JE_CATEGORY_NAME='PAYMENTS'
AND GJH.LEDGER_ID=GPS.LEDGER_ID
AND 101=GPS.APPLICATION_ID
AND GPS.SET_OF_BOOKS_ID=GJH.LEDGER_ID
AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
AND GJH.PERIOD_NAME       = GPS.PERIOD_NAME
--AND AIA.ORG_ID=:P_ORG
AND (    NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) >=:CF_PERIOD_FROM
        AND (NVL (GPS.EFFECTIVE_PERIOD_NUM, 0) <=:CF_PERIOD_TO)
             )
                AND (    NVL (GCCK.SEGMENT5, 0) >= :P_ACCOUNT_FROM
        AND (NVL (GCCK.SEGMENT5, 0) <= :P_ACCOUNT_TO)
       )
     AND (    NVL (GCCK.SEGMENT1, 0) >=:P_COMPANY_FROM
        AND (NVL (GCCK.SEGMENT1, 0) <= :P_COMPANY_TO)
       )
       AND AIA.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
                                              'ALL',AIA.CURRENCY_CODE,
                                              'NON INR',DECODE(AIA.CURRENCY_CODE,'INR','###',AIA.CURRENCY_CODE))



UNION ALL

--ADDED BY MANOHAR
SELECT     GJH.PERIOD_NAME,
             NULL REFERENCE_2,
             NULL PO_ORDER_NUMBER,
             CASE
             WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
             XTE.TRANSACTION_NUMBER
             ELSE
               NULL
              END     "RECEIPT NUMBER",
              CASE
             WHEN (GJH.JE_CATEGORY IN ('RECEIPTS','MISC RECEIPTS')) THEN
             XAH.ACCOUNTING_DATE
             ELSE
               NULL
              END     "RECEIPT DATE",
              CASE
              WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
              XTE.TRANSACTION_NUMBER ELSE
             NULL
             END    "INVOICE NUMBER",
             CASE
              WHEN (GJH.JE_CATEGORY NOT IN ('RECEIPTS','MISC RECEIPTS')) THEN
               XAH.ACCOUNTING_DATE
               ELSE
             NULL
             END    "INVOICE DATE",
          CASE
              WHEN XAL.PARTY_TYPE_CODE = 'S' THEN
                 (SELECT         APS.SEGMENT1
                        ||'|'||APS.VENDOR_NAME
                        ||'|'||HZP.JGZZ_FISCAL_CODE
                        ||'|'||HZP.TAX_REFERENCE
                        ||'|'||HPS.PARTY_SITE_NUMBER
                        ||'|'||HPS.PARTY_SITE_NAME
                        ||'|'||NULL
                   FROM AP_SUPPLIERS          APS
                        ,AP_SUPPLIER_SITES_ALL APSS
                        ,HZ_PARTIES            HZP
                        ,HZ_PARTY_SITES        HPS
                        ,XLA_AE_LINES          AEL2
                  WHERE APS.VENDOR_ID          = AEL2.PARTY_ID
                    AND HZP.PARTY_ID           = APS.PARTY_ID
                    AND APSS.VENDOR_SITE_ID(+) = AEL2.PARTY_SITE_ID
                    AND HPS.PARTY_SITE_ID(+)   = APSS.PARTY_SITE_ID
                    AND AEL2.APPLICATION_ID    = XAL.APPLICATION_ID
                    AND AEL2.AE_HEADER_ID      = XAL.AE_HEADER_ID
                    AND AEL2.AE_LINE_NUM       = XAL.AE_LINE_NUM )
              WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
                 (SELECT         --HCA.ACCOUNT_NUMBER||'|'||
                 HZP.PARTY_NAME
                        --||'|'||HZP.JGZZ_FISCAL_CODE
                        --||'|'||HZP.TAX_REFERENCE
                        --||'|'||HPS.PARTY_SITE_NUMBER
                        --||'|'||HPS.PARTY_SITE_NAME
                        --||'|'||HZCU.TAX_REFERENCE
                   FROM HZ_CUST_ACCOUNTS        HCA
                        ,HZ_CUST_ACCT_SITES_ALL HCAS
                        ,HZ_CUST_SITE_USES_ALL   HZCU
                        ,HZ_PARTIES              HZP
                        ,HZ_PARTY_SITES          HPS
                        ,XLA_AE_LINES            AEL2
                  WHERE HCA.CUST_ACCOUNT_ID       = AEL2.PARTY_ID
                    AND HZP.PARTY_ID              = HCA.PARTY_ID
                    AND HZCU.SITE_USE_ID(+)       = AEL2.PARTY_SITE_ID
                    AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
                    AND HPS.PARTY_SITE_ID(+)      = HCAS.PARTY_SITE_ID
                    AND AEL2.APPLICATION_ID       = XAL.APPLICATION_ID
                    AND AEL2.AE_HEADER_ID         = XAL.AE_HEADER_ID
                    AND AEL2.AE_LINE_NUM          = XAL.AE_LINE_NUM )
              ELSE
                NULL
              END       "VENDOR NAME",
              CASE
              WHEN (XAL.PARTY_TYPE_CODE = 'C' AND XAL.PARTY_ID IS NOT NULL) THEN
                 (SELECT         HCA.ACCOUNT_NUMBER
                 --||'|'|| HZP.PARTY_NAME
                        --||'|'||HZP.JGZZ_FISCAL_CODE
                        --||'|'||HZP.TAX_REFERENCE
                        --||'|'||HPS.PARTY_SITE_NUMBER
                        --||'|'||HPS.PARTY_SITE_NAME
                        --||'|'||HZCU.TAX_REFERENCE
                   FROM HZ_CUST_ACCOUNTS        HCA
                        ,HZ_CUST_ACCT_SITES_ALL HCAS
                        ,HZ_CUST_SITE_USES_ALL   HZCU
                        ,HZ_PARTIES              HZP
                        ,HZ_PARTY_SITES          HPS
                        ,XLA_AE_LINES            AEL2
                  WHERE HCA.CUST_ACCOUNT_ID       = AEL2.PARTY_ID
                    AND HZP.PARTY_ID              = HCA.PARTY_ID
                    AND HZCU.SITE_USE_ID(+)       = AEL2.PARTY_SITE_ID
                    AND HCAS.CUST_ACCT_SITE_ID(+) = HZCU.CUST_ACCT_SITE_ID
                    AND HPS.PARTY_SITE_ID(+)      = HCAS.PARTY_SITE_ID
                    AND AEL2.APPLICATION_ID       = XAL.APPLICATION_ID
                    AND AEL2.AE_HEADER_ID         = XAL.AE_HEADER_ID
                    AND AEL2.AE_LINE_NUM          = XAL.AE_LINE_NUM )
              ELSE
                NULL
              END       "VENDOR NUMBER",
              GJH.JE_SOURCE"SOURCE",
              GJH.LEDGER_ID "SET_OF_BOOKS_ID" ,
                        CC.CONCATENATED_SEGMENTS SEGMENT,
            CC.CONCATENATED_SEGMENTS SEGMENT21,
              XAL.CURRENCY_CODE CURRENCY_CODE,
              XAL.ENTERED_DR,XAL.ENTERED_CR,
              XAL.ACCOUNTED_DR,XAL.ACCOUNTED_CR,
              GIR.JE_HEADER_ID,
            GIR.JE_LINE_NUM,
            CC.CODE_COMBINATION_ID
            ,GIR.SUBLEDGER_DOC_SEQUENCE_VALUE "VOCHER NUMBER"
            ,XAH.ACCOUNTING_DATE "GL_DATE"
            ,NULL "STAFF NUMBER"
            ,NULL "PROJECT"
            ,NULL "CUSTOMER NUM/NAME"
            ,XAH.DESCRIPTION "DESCRIPTION"

FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
      APPS.XLA_EVENTS XEV,
      APPS.XLA_AE_HEADERS XAH,
      APPS.XLA_AE_LINES XAL,
      APPS.GL_IMPORT_REFERENCES GIR,
      APPS.GL_JE_HEADERS GJH,
      APPS.GL_JE_LINES GJL,
      APPS.GL_CODE_COMBINATIONS_KFV CC,
      --APPS.AP_SUPPLIERS APS,
      GL_PERIOD_STATUSES GPS


WHERE 1=1--AIA.INVOICE_ID = XTE.SOURCE_ID_INT_1
      AND XEV.ENTITY_ID= XTE.ENTITY_ID
      AND XAH.ENTITY_ID= XTE.ENTITY_ID
      AND XAH.EVENT_ID= XEV.EVENT_ID
      AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
      --AND XAH.JE_CATEGORY_NAME = 'PURCHASE INVOICES'
      AND XAH.GL_TRANSFER_STATUS_CODE= 'Y'
      AND GJH.STATUS = 'P'
      AND XAL.GL_SL_LINK_ID=GIR.GL_SL_LINK_ID
      AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE
      AND GJL.JE_HEADER_ID=GJH.JE_HEADER_ID
      AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
      AND GJL.JE_HEADER_ID=GIR.JE_HEADER_ID
      AND GIR.JE_LINE_NUM=GJL.JE_LINE_NUM
      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 GJH.STATUS='P'
      AND GJH.ACTUAL_FLAG='A'
      AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
      --AND GJL.EFFECTIVE_DATE BETWEEN TO_DATE(:P_START_DATE) AND TO_DATE(:P_END_DATE)
      --AND XAL.ACCOUNTING_CLASS_CODE IN ('PREPAID_EXPENSE','ITEM EXPENSE','ACCRUAL','LIABILITY','INTRA')
      --AND CC.SEGMENT1=111
      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 ='RECEIVABLES'
    AND GJH.LEDGER_ID NOT IN ( 1014,1015,1016 )
    --AND XAL.ENTERED_DR='136547.55'
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 24116=:P_ACCOUNT_FROM
--           AND 24116=:P_ACCOUNT_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 XTE.TRANSACTION_NUMBER='101914 - ZPF TDS APRIL 12'
         AND XAL.CURRENCY_CODE =DECODE(:P_CURRENCY_CODE,'INR','INR',
                                                'ALL',XAL.CURRENCY_CODE,
                                                'NON INR',DECODE(XAL.CURRENCY_CODE,'INR','###',XAL.CURRENCY_CODE));