PO View Award Changes Data Definition

Description
Categories: BI Publisher
Imported from BI Publisher
Application: Purchasing
Source:
Short Name: PO_VIEW_AWARD_CHANGES
DB package:
Run PO View Award Changes Data Definition and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT pohd.po_header_id,
       pod.modification_number,
       pod.draft_type, 
       diff.entity_name, 
       pold.line_num_display, 
       polld.shipment_num, 
       podd.distribution_num, 
       po_uda_pub.Get_single_attr_value(pohd.uda_template_id, NULL, 
       pohd.po_header_id, 
       pohd.draft_id, NULL, NULL, NULL, NULL, 'mod_Just', NULL, 'justifiction', 
       'INTERNAL_VALUE')                       justification, 
       po_uda_pub.Get_single_attr_value(pohd.uda_template_id, NULL, 
       pohd.po_header_id, 
       pohd.draft_id, NULL, NULL, NULL, NULL, 'mod_Just', NULL, 'mod_auth', 
       'DISPLAY_VALUE')                        modification_authority, 
       Nvl(msg.message_text, diff.column_name) FIELD_ALTERED, 
       Nvl(orig_value_desc, orig_value)        CHANGED_FROM, 
       Nvl(mod_value_desc, mod_value)          CHANGED_TO, 
       Nvl(base_value_desc, base_value)        BASE_VALUE,
       Decode(pod.status, 'COMPLETED', pohd.approved_date, 
                          NULL)                APPROVED_DATE,
       pohd.last_update_date,
       (select ppf.full_name 
        from per_all_people_f ppf, fnd_user fu
        where fu.user_id = pohd.last_updated_by
        and fu.employee_id = ppf.person_id 
        and trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date) updated_by,
       (select ppf.full_name
        from po_action_history poah, per_all_people_f ppf 
        where  poah.object_id = pohd.draft_id 
				and poah.object_type_code = decode(pohd.type_lookup_code, 'STANDARD', 'PO', 'PA')
        and poah.object_sub_type_code =  decode(pod.draft_type, 'MOD', 'MODIFICATION', 'PAR', 'POST_AWARD_REQUEST')
        and poah.sequence_num = (select max(sequence_num) from po_action_history poah1 where poah1.object_id = poah.object_id 
				and poah1.object_type_code = poah.object_type_code
        and poah1.action_code = 'APPROVE')
        and poah.employee_id = ppf.person_id 
        and trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date) approved_by,
        pod.status draft_status
FROM   po_entity_differences diff, 
       po_headers_draft_all pohd,
       po_lines_draft_all pold,
       po_line_locations_draft_all polld, 
       po_distributions_draft_all podd, 
       po_drafts pod, 
       po_diff_config c, 
       fnd_new_messages msg
WHERE      pohd.po_header_id = pod.document_id 
       AND pohd.draft_id = pod.draft_id 
       AND msg.language_code(+) = Userenv('LANG') 
       AND msg.application_id(+) = 201 
       AND c.label_message_code = msg.message_name(+)
	   AND pohd.po_header_id = pold.po_header_id (+)
	   AND pohd.draft_id = pold.draft_id (+)
       AND pold.po_header_id = polld.po_header_id (+)
	   AND pold.draft_id = polld.draft_id (+) 
       AND pold.po_line_id = polld.po_line_id (+) 
       AND polld.po_header_id = podd.po_header_id (+)
	   AND polld.draft_id = podd.draft_id (+)
       AND polld.po_line_id = podd.po_line_id (+)
       AND polld.line_location_id = podd.line_location_id (+) 
       AND diff.mod_doc_pk1_val = pohd.po_header_id 
       AND diff.mod_doc_pk2_val = pohd.draft_id
       AND diff.mod_doc_pk3_val = pold.po_line_id (+)
       AND diff.mod_doc_pk4_val = polld.line_location_id (+) 
       AND diff.mod_doc_pk5_val = podd.po_distribution_id (+)
       AND diff.base_doc_pk1_val = pohd.po_header_id 
       AND diff.base_doc_pk2_val = -1
       AND diff.base_doc_pk3_val = pold.po_line_id (+)
       AND diff.base_doc_pk4_val = polld.line_location_id (+)  
       AND diff.base_doc_pk5_val = podd.po_distribution_id (+)     
       AND c.document_type = diff.document_type 
       AND c.entity_name = diff.entity_name 
       AND c.column_name = diff.column_name 
       AND c.mod_doc_source_name = diff.mod_doc_source_name 
       AND Nvl(c.is_printable_flag, 'N') = 'Y' 
       AND Nvl(c.ignore_source_flag, 'N') = 'N'
       AND pohd.po_header_id = :p_header_id
UNION ALL
select pohd.po_header_id,
       pod.modification_number,
       pod.draft_type,
       'LINE' entity_name, 
       pold.line_num_display, 
       null shipment_num,
       null distribution_num, 
       po_uda_pub.Get_single_attr_value(pohd.uda_template_id, NULL, 
       pohd.po_header_id, 
       pohd.draft_id, NULL, NULL, NULL, NULL, 'mod_Just', NULL, 'justifiction', 
       'INTERNAL_VALUE')                       justification, 
       po_uda_pub.Get_single_attr_value(pohd.uda_template_id, NULL, 
       pohd.po_header_id, 
       pohd.draft_id, NULL, NULL, NULL, NULL, 'mod_Just', NULL, 'mod_auth', 
       'DISPLAY_VALUE')                        modification_authority,
       'New' FIELD_ALTERED, 
       null        CHANGED_FROM, 
       null        CHANGED_TO, 
       null        BASE_VALUE, 
       Decode(pod.status, 'COMPLETED', pohd.approved_date, 
                          NULL)                APPROVED_DATE,
       pohd.last_update_date,
       (select ppf.full_name 
        from per_all_people_f ppf, fnd_user fu
        where fu.user_id = pohd.last_updated_by
        and fu.employee_id = ppf.person_id 
        and trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date) updated_by,
       (select ppf.full_name
        from po_action_history poah, per_all_people_f ppf 
        where  poah.object_id = pohd.draft_id 
				and poah.object_type_code = decode(pohd.type_lookup_code, 'STANDARD', 'PO', 'PA')
        and poah.object_sub_type_code =  decode(pod.draft_type, 'MOD', 'MODIFICATION', 'PAR', 'POST_AWARD_REQUEST')
        and poah.sequence_num = (select max(sequence_num) from po_action_history poah1 where poah1.object_id = poah.object_id 
				and poah1.object_type_code = poah.object_type_code
        and poah1.action_code = 'APPROVE')
        and poah.employee_id = ppf.person_id 
        and trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date) approved_by,
        pod.status draft_status
from   po_headers_draft_all pohd,
       po_lines_draft_all pold, 
       po_drafts pod
where pohd.po_header_id = pod.document_id 
       AND pohd.draft_id = pod.draft_id 
       AND pohd.po_header_id = pold.po_header_id 
       AND pohd.draft_id = pold.draft_id 
       AND pold.change_status = 'NEW'
       AND pohd.po_header_id = :p_header_id
UNION ALL 
select pohd.po_header_id,
       pod.modification_number,
       pod.draft_type,
       'LINELOCATION' entity_name,
       pold.line_num_display, 
       polld.shipment_num,
       null distribution_num, 
       po_uda_pub.Get_single_attr_value(pohd.uda_template_id, NULL, 
       pohd.po_header_id, 
       pohd.draft_id, NULL, NULL, NULL, NULL, 'mod_Just', NULL, 'justifiction', 
       'INTERNAL_VALUE')                       justification, 
       po_uda_pub.Get_single_attr_value(pohd.uda_template_id, NULL, 
       pohd.po_header_id, 
       pohd.draft_id, NULL, NULL, NULL, NULL, 'mod_Just', NULL, 'mod_auth', 
       'DISPLAY_VALUE')                        modification_authority,
       'New' FIELD_ALTERED, 
       null        CHANGED_FROM, 
       null        CHANGED_TO, 
       null        BASE_VALUE,
       Decode(pod.status, 'COMPLETED', pohd.approved_date, 
                          NULL)                APPROVED_DATE,
       pohd.last_update_date,
       (select ppf.full_name 
        from per_all_people_f ppf, fnd_user fu
        where fu.user_id = pohd.last_updated_by
        and fu.employee_id = ppf.person_id 
        and trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date) updated_by,
       (select ppf.full_name
        from po_action_history poah, per_all_people_f ppf 
        where  poah.object_id = pohd.draft_id 
				and poah.object_type_code = decode(pohd.type_lookup_code, 'STANDARD', 'PO', 'PA')
        and poah.object_sub_type_code =  decode(pod.draft_type, 'MOD', 'MODIFICATION', 'PAR', 'POST_AWARD_REQUEST')
        and poah.sequence_num = (select max(sequence_num) from po_action_history poah1 where poah1.object_id = poah.object_id 
				and poah1.object_type_code = poah.object_type_code
        and poah1.action_code = 'APPROVE')
        and poah.employee_id = ppf.person_id 
        and trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date) approved_by,
        pod.status draft_status
from   po_headers_draft_all pohd,
       po_lines_draft_all pold,
       po_line_locations_draft_all polld,  
       po_drafts pod
where pohd.po_header_id = pod.document_id 
       AND pohd.draft_id = pod.draft_id 
       AND pohd.po_header_id = pold.po_header_id 
       AND pohd.draft_id = pold.draft_id 
       AND pold.po_header_id = polld.po_header_id
	   AND pold.draft_id = polld.draft_id 
       AND pold.po_line_id = polld.po_line_id 
	   AND polld.change_status = 'NEW'
       AND pohd.po_header_id = :p_header_id
UNION ALl
select pohd.po_header_id,
       pod.modification_number,
       pod.draft_type,
       'DISTRIBUTION' entity_name,
       pold.line_num_display, 
       polld.shipment_num,
       podd.distribution_num, 
       po_uda_pub.Get_single_attr_value(pohd.uda_template_id, NULL, 
       pohd.po_header_id, 
       pohd.draft_id, NULL, NULL, NULL, NULL, 'mod_Just', NULL, 'justifiction', 
       'INTERNAL_VALUE')                       justification, 
       po_uda_pub.Get_single_attr_value(pohd.uda_template_id, NULL, 
       pohd.po_header_id, 
       pohd.draft_id, NULL, NULL, NULL, NULL, 'mod_Just', NULL, 'mod_auth', 
       'DISPLAY_VALUE')                        modification_authority,
       'New' FIELD_ALTERED, 
       null        CHANGED_FROM, 
       null        CHANGED_TO, 
       null        BASE_VALUE, 
       Decode(pod.status, 'COMPLETED', pohd.approved_date, 
                          NULL)                APPROVED_DATE,
       pohd.last_update_date,
       (select ppf.full_name 
        from per_all_people_f ppf, fnd_user fu
        where fu.user_id = pohd.last_updated_by
        and fu.employee_id = ppf.person_id 
        and trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date) updated_by,
       (select ppf.full_name
        from po_action_history poah, per_all_people_f ppf 
        where  poah.object_id = pohd.draft_id 
				and poah.object_type_code = decode(pohd.type_lookup_code, 'STANDARD', 'PO', 'PA')
        and poah.object_sub_type_code =  decode(pod.draft_type, 'MOD', 'MODIFICATION', 'PAR', 'POST_AWARD_REQUEST')
        and poah.sequence_num = (select max(sequence_num) from po_action_history poah1 where poah1.object_id = poah.object_id 
				and poah1.object_type_code = poah.object_type_code
        and poah1.action_code = 'APPROVE')
        and poah.employee_id = ppf.person_id 
        and trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date) approved_by,
        pod.status draft_status
from   po_headers_draft_all pohd,
       po_lines_draft_all pold,
       po_line_locations_draft_all polld, 
       po_distributions_draft_all podd,  
       po_drafts pod
where pohd.po_header_id = pod.document_id 
       AND pohd.draft_id = pod.draft_id 
       AND pohd.po_header_id = pold.po_header_id 
       AND pohd.draft_id = pold.draft_id 
       AND pold.po_header_id = polld.po_header_id
       AND pold.draft_id = polld.draft_id 
       AND pold.po_line_id = polld.po_line_id 
       AND polld.po_header_id = podd.po_header_id
	   AND polld.draft_id = podd.draft_id 
       AND polld.po_line_id = podd.po_line_id
       AND polld.line_location_id = podd.line_location_id 
	   AND podd.change_status = 'NEW'
       AND pohd.po_header_id = :p_header_id