SELECT pha.segment1 po_number
,pha.revision_num
,comments description
,pha.type_lookup_code po_type
,TO_CHAR(pha.creation_date, 'DD-MON-RRRR') order_date
,pv.vendor_name vendor_name
,pv.segment1 vendor_number
,pvs.vendor_site_code supplier_site
,pha.currency_code
,pha.rate_type currency_rate_type
,TO_CHAR(pha.rate_date, 'DD-MON-RRRR') currency_rate_date
,pha.rate currency_rate
,(SELECT SUM(unit_price * quantity)
FROM po_lines_all pla
WHERE pla.po_header_id = pha.po_header_id
AND pha.org_id = pla.org_id
AND NVL(pla.closed_code, 'OPEN') <> 'FINALLY CLOSED'
) po_header_amount
,(SELECT SUM(aiDa.amount)
FROM po_headers_all pha1
,po_distributions_all pda
,ap_invoice_distributions_all aida
,ap_invoices_all aia
WHERE 1=1
AND pha1.po_header_id = pha.po_header_id
AND pha.po_header_id = pda.po_header_id
AND pda.po_distribution_id = aida.po_distribution_id
AND PDA.PO_LINE_ID = lines.PO_LINE_ID
AND pda.po_distribution_id = po_dist.po_distribution_id
AND pda.org_id =aida.org_id
AND aida.invoice_id = aia.invoice_id) matched_amount
,pap.full_name agent_name
,pap.employee_number buyer_number
,NVL (PHA.CLOSED_CODE, 'OPEN') closure_status
,att.name payment_term
,(SELECT location_code
FROM hr_locations
WHERE location_id = pha.ship_to_location_id) ship_to_location
,(SELECT location_code
FROM hr_locations
WHERE location_id = pha.bill_to_location_id) bill_to_location
,lines.line_num
,(SELECT line_type
FROM po_line_types
WHERE line_type_id = lines.line_type_id) line_type
,(SELECT segment1
FROM mtl_system_items_b
WHERE inventory_item_id = lines.item_id
AND organization_id = line_loc.ship_to_organization_id) item
,lines.item_id
,REPLACE(TRIM(lines.item_description), CHR(10), '') existing_line_description
,lines.quantity
,lines.unit_meas_lookup_code uom
,lines.unit_price
,(lines.quantity * lines.unit_price) price
,line_loc.shipment_num
,(SELECT organization_code
FROM org_organization_definitions
WHERE organization_id = line_loc.ship_to_organization_id) ship_to_organization_code
,(SELECT location_code
FROM hr_locations
WHERE location_id = line_loc.ship_to_location_id) shipment_ship_to_location
,line_loc.ship_to_location_id
,line_loc.ship_to_organization_id
,TO_CHAR(line_loc.need_by_date, 'DD-MON-RRRR') need_by_date
,TO_CHAR(line_loc.PROMISED_DATE, 'DD-MON-RRRR') PROMISED_DATE
,line_loc.quantity shipment_quantity
,line_loc.QUANTITY_RECEIVED shipment_quantity_received
,(SELECT meaning
FROM fnd_lookup_values
WHERE 1=1
AND lookup_code = line_loc.match_option
AND lookup_type LIKE '%POS%INVOICE%MATCH%'
AND enabled_flag = 'Y' AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE)
) match_option
,line_loc.receipt_required_flag
,line_loc.inspection_required_flag
,line_loc.accrue_on_receipt_flag
,line_loc.receiving_routing_id
,line_loc.unit_meas_lookup_code
,po_dist.distribution_num
,(SELECT location_code
FROM hr_locations
WHERE location_id = po_dist.deliver_to_location_id) deliver_to_location
,po_dist.destination_type_code
,po_dist.quantity_ordered
,po_dist.destination_subinventory
,(SELECT full_name
FROM per_all_people_f
WHERE person_id = deliver_to_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date) deliver_to_person_name
,(SELECT employee_number
FROM per_all_people_f
WHERE person_id = deliver_to_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date) deliver_to_person_number
,(SELECT employee_number
FROM per_all_people_f
WHERE person_id = deliver_to_person_id AND SYSDATE BETWEEN effective_start_date AND effective_end_date) deliver_to_person_id
,code_comb.segment1 business_unit
,code_comb.segment2 department
,code_comb.segment3 account
,code_comb.segment4 product
,code_comb.segment5 project
,code_comb.segment6 property
,code_comb.segment7 spare
,code_comb.concatenated_segments
FROM apps.po_headers_all pha
,apps.hr_operating_units hou
--,fnd_lookup_values po_type
,apps.per_all_people_f pap
,apps.ap_suppliers pv
,apps.ap_supplier_sites_all pvs
,apps.ap_terms_tl att
,po_lines_all lines
,po_line_locations_all line_loc
,po_distributions_all po_dist
,gl_code_combinations_kfv code_comb
WHERE 1 = 1
AND pha.approved_flag = 'Y'
AND NVL (pha.closed_code, 'OPEN')= 'OPEN'
AND NVL (pha.cancel_flag, 'N') = 'N'
AND NVL(lines.cancel_flag, 'N') = 'N'
AND NVL(line_loc.cancel_flag, 'N') = 'N'
--AND pha.type_lookup_code IN ('STANDARD')
AND pha.org_id = hou.organization_id
--AND pha.type_lookup_code = po_type.lookup_code
--AND po_type.lookup_type = 'POO:DOCUMENT_TYPE'
AND pha.agent_id = pap.person_id
AND pha.vendor_id = pv.vendor_id(+)
AND pha.vendor_site_id = pvs.vendor_site_id(+)
AND pha.terms_id = att.term_id(+)
AND ATT.language = USERENV ('LANG')
AND pha.po_header_id = lines.po_header_id
AND pha.org_id = lines.org_id
AND line_loc.po_line_id = lines.po_line_id
AND line_loc.po_header_id = pha.po_header_id
AND po_dist.po_header_id = pha.po_header_id
AND po_dist.po_line_id = lines.po_line_id
AND po_dist.line_location_id = line_loc.line_location_id
AND po_dist.org_id = line_loc.org_id
AND code_comb.code_combination_id = po_dist.code_combination_id
AND pha.org_id = :p_org_id
AND SYSDATE BETWEEN effective_start_Date AND effective_end_date
--AND pha.segment1 = '20'
ORDER BY pha.segment1, lines.line_num
No comments:
Post a Comment