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;
No comments:
Post a Comment