CST Periodic Acquisition Cost

Description
Categories: BI Publisher
Application: Bills of Material
Source: Periodic Acquisition Cost Report (XML)
Short Name: CSTRPACQ_XML
DB package: BOM_CSTRPACQ_XMLP_PKG
Run CST Periodic Acquisition Cost and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Legal Entity
 
LOV Oracle
Cost Type
 
LOV Oracle
Period
 
LOV Oracle
Cost Group
 
LOV Oracle
Item From
 
Char
Item To
 
Char
Run Option
 
LOV Oracle
Blitz Report™