SELECT
cracd.header_id c_rcv_acv_cost_header_id
, 1 c_sort
, cracd.parent_distribution_id c_parent_distribution_num
, cracd.distribution_num c_distribution_num
, aia.invoice_num c_invoice_po_number
, :ptxt_source_invoice c_source_type
, aida.distribution_line_number c_distribution_line_number
, LPAD(cracd.level_num, cracd.level_num) c_level
, alc.displayed_field c_line_type
, cracd.quantity c_quantity
, round(cracd.price,:P_EXT_PRECISION) c_price
, round(cracd.amount/:P_PRECISION)*:P_PRECISION
c_amount
FROM
ap_invoices_all aia
, ap_invoice_distributions_all aida
, ap_lookup_codes alc
, cst_rcv_acq_cost_details cracd
WHERE alc.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND alc.lookup_code = cracd.line_type
AND aida.invoice_distribution_id = cracd.invoice_distribution_id
AND aia.invoice_id = aida.invoice_id
AND cracd.header_id=:c_rcv_acq_cost_id
/* UNION
SELECT
cracd.header_id c_rcv_acv_cost_header_id
, 1 c_sort
, cracd.parent_distribution_id c_parent_distribution_num
, cracd.distribution_num c_distribution_num
, aia.invoice_num c_invoice_po_number
, :ptxt_source_invoice c_source_type
, aida.distribution_line_number c_distribution_line_number
, LPAD(cracd.level_num, cracd.level_num) c_level
, alc.displayed_field c_line_type
, cracd.quantity c_quantity
, round(cracd.price,:P_EXT_PRECISION) c_price
, round(cracd.amount/:P_PRECISION)*:P_PRECISION
c_amount
FROM
ap_invoices_all aia
, ap_invoice_distributions_all aida
, ap_lookup_codes alc
, cst_rcv_acq_cost_details cracd
WHERE alc.lookup_type = 'INVOICE DISTRIBUTION TYPE'
AND alc.lookup_code = cracd.line_type
AND aida.invoice_distribution_id = cracd.invoice_distribution_id
AND aia.invoice_id = aida.invoice_id
*/
UNION
ALL
SELECT
cracd.header_id c_rcv_acv_cost_header_id
, 2 c_sort
, cracd.parent_distribution_id c_parent_distribution_num
, cracd.distribution_num c_distribution_num
, pha.segment1
c_invoice_po_num
, decode(cracd.source_type,'LCM',:ptxt_source_lcm,:ptxt_source_po ) c_source_type
, TO_NUMBER(NULL)
c_distribution_line_number
, TO_CHAR(NULL)
c_level
, decode(cracd.source_type,'LCM',:ptxt_line_type_lcm,:ptxt_line_type_po) c_line_type
, cracd.quantity
c_quantity
, round(cracd.price,:P_EXT_PRECISION) c_price
, round(cracd.amount/:P_PRECISION)*:P_PRECISION
c_amount
FROM
po_headers_all pha
, po_line_locations_all plla
, cst_rcv_acq_cost_details cracd
WHERE cracd.po_line_location_id = plla.line_location_id
AND pha.po_header_id = plla.po_header_id
and cracd.header_id=:c_rcv_acq_cost_id
ORDER BY
c_sort
, c_parent_distribution_num
, c_distribution_num |