Purchase Order Change History Query

--Submitted Change Orders--

select version.co_num,

            version.change_order_desc comments,

            version.creation_date,

            version.originator_role,

            (

                        select full_name

                        from per_person_names_f

                        where person_id = version.originator_id

                                    and name_type = 'global'

                        ) initiator,

            version.change_order_status status,

            nvl(plaa.amount, (plaa.quantity * plaa.unit_price)) initial_amount,

            (

                        select sum(nvl(plaa1.amount, (plaa1.quantity * plaa1.list_price)))

                        from po_lines_archive_all plaa1,

                                    po_versions pov1

                        where 1 = 1

                                    and pov1.co_num = version.co_num

                                    and pov1.co_sequence = plaa1.from_co_seq

                                    and pov1.po_header_id = plaa1.po_header_id

                                    and plaa1.po_line_id = plaa.po_line_id

                                    and plaa1.po_header_id = plaa.po_header_id

                        ) - nvl(plaa.amount, (plaa.quantity * plaa.unit_price)) amount_changed,

            (

                        select sum(nvl(plaa1.amount, (plaa1.quantity * plaa1.list_price)))

                        from po_lines_archive_all plaa1,

                                    po_versions pov1

                        where 1 = 1

                                    and pov1.co_num = version.co_num

                                    and pov1.co_sequence = plaa1.from_co_seq

                                    and pov1.po_header_id = plaa1.po_header_id

                                    and plaa1.po_line_id = plaa.po_line_id

                                    and plaa1.po_header_id = plaa.po_header_id

                        ) final_amount

from po_lines_archive_all plaa,

            po_lines_all pla,

            po_versions version,

            po_headers_all pha

where pla.po_header_id(+) = pha.po_header_id

            and version.po_header_id(+) = pha.po_header_id

            and version.co_sequence between plaa.from_co_seq

                        and plaa.to_co_seq

            and version.co_sequence = plaa.to_co_seq(+)

            and version.co_canceled_flag != 'y'

            and plaa.po_line_id(+) = pla.po_line_id

            and change_order_status != 'incomplete'

            and version.co_num is not null

            and pha.segment1 = :p_po_num

 

--Pending Change Orders--

select version.co_num,

            version.change_order_desc comments,

            version.creation_date,

            version.originator_role,

            (

                        select full_name

                        from per_person_names_f

                        where person_id = version.originator_id

                                    and name_type = 'global'

                        ) initiator,

            version.change_order_status status,

            nvl(pla.amount, (pla.quantity * pla.unit_price)) initial_amount,

            (

                        select sum(nvl(plaa1.amount, (plaa1.quantity * plaa1.list_price)))

                        from po_lines_draft_all plaa1

                        where 1 = 1

                                    and plaa1.po_line_id = pla.po_line_id

                                    and plaa1.po_header_id = pla.po_header_id

                        ) - nvl(pla.amount, (pla.quantity * pla.unit_price)) amount_changed,

            (

                        select sum(nvl(plaa1.amount, (plaa1.quantity * plaa1.list_price)))

                        from po_lines_draft_all plaa1

                        where 1 = 1

                                    and plaa1.po_line_id = pla.po_line_id

                                    and plaa1.po_header_id = pla.po_header_id

                        ) final_amount

from po_lines_all pla,

            po_versions version,

            po_headers_draft_all pha

where pla.po_header_id(+) = pha.po_header_id

            and version.po_header_id(+) = pha.po_header_id

            and version.change_order_status = 'incomplete'

            and version.co_canceled_flag != 'y'

            and version.co_num is not null

            and pha.segment1 = :p_po_num