PO View Award Changes Data Definition

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Application: Purchasing
Source:
Short Name: PO_VIEW_AWARD_CHANGES
DB package:

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Download
 
Related Blitz Reports
GL Account Analysis XXRG GL Account Analysis GL Account Analysis (Distributions) ECC Procurement, Agreements ECC Procurement, Purchase Orders ECC Procurement, Procurement Cycle Time Graph GL Account Analysis 11g PO Award Mod data template for FPDS-NG EOS GL Account Analysis
Blitz Report™

Blitz Report™ provides multiple benefits: