PO Purchasing Document Upload

➜ Back to Library
Description
Categories: Enginatics, Upload
Repository: Github
Upload to create and update purchase documents

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

with reporting_table as
( select * from &report_table_name),
document_types_ as
(
select
case when pdtav.document_type_code='QUOTATION' then pdtav.document_type_code else pdtav.document_subtype end document_type,
pdtav.type_name document_type_name
from
po_document_types_all_vl pdtav,
hr_all_organization_units_vl haouv
where
2=2 and
3=3 and
pdtav.org_id=haouv.organization_id and
pdtav.document_type_code in ('PO','PA','QUOTATION') and
pdtav.document_subtype in ('BLANKET','CONTRACT','STANDARD')
),
po_lines_ as
(
select
pla.po_line_id,
pla.line_reference_num,
pla.po_header_id,
pla.line_num,
pltt.line_type,
msiv.concatenated_segments item,
pla.unit_meas_lookup_code unit_of_measure,
pla.unit_price,
pla.quantity line_quantity,
pla.item_description description,
mcv.category_concat_segs category,
pla.attribute_category line_dff_context,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE1',pla.rowid,pla.attribute1) line_attribute1,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE2',pla.rowid,pla.attribute2) line_attribute2,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE3',pla.rowid,pla.attribute3) line_attribute3,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE4',pla.rowid,pla.attribute4) line_attribute4,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE5',pla.rowid,pla.attribute5) line_attribute5,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE6',pla.rowid,pla.attribute6) line_attribute6,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE7',pla.rowid,pla.attribute7) line_attribute7,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE8',pla.rowid,pla.attribute8) line_attribute8,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE9',pla.rowid,pla.attribute9) line_attribute9,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE10',pla.rowid,pla.attribute10) line_attribute10,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE11',pla.rowid,pla.attribute11) line_attribute11,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE12',pla.rowid,pla.attribute12) line_attribute12,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE13',pla.rowid,pla.attribute13) line_attribute13,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE14',pla.rowid,pla.attribute14) line_attribute14,
xxen_util.display_flexfield_value(201,'PO_LINES',pla.attribute_category,'ATTRIBUTE15',pla.rowid,pla.attribute15) line_attribute15
from
po_lines_all pla,
po_line_types_tl pltt,
mtl_categories_v mcv,
mtl_system_items_vl msiv
where
nvl(pla.cancel_flag,'N') = 'N' and
nvl(pla.closed_code,'OPEN') != 'FINALLY CLOSED' and
pltt.language=userenv('lang') and
pltt.line_type_id=pla.line_type_id and
pla.category_id=mcv.category_id(+) and
msiv.inventory_item_id(+) = pla.item_id and
msiv.organization_id(+) = po_lines_sv4.get_inventory_orgid(pla.org_id)
),
po_line_locations_ as
(
select
plla.po_line_id,
plla.line_location_id,
plla.shipment_num,
plla.quantity ship_quantity,
plla.price_override break_price,
plla.unit_meas_lookup_code break_unit_of_measure,
plla.price_discount discount,
mp.organization_code ship_to_organization_code,
hla.location_code ship_to_location_code,
plla.need_by_date,
plla.promised_date,
plla.attribute_category line_location_dff_context,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE1',plla.rowid,plla.attribute1) line_location_attribute1,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE2',plla.rowid,plla.attribute2) line_location_attribute2,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE3',plla.rowid,plla.attribute3) line_location_attribute3,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE4',plla.rowid,plla.attribute4) line_location_attribute4,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE5',plla.rowid,plla.attribute5) line_location_attribute5,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE6',plla.rowid,plla.attribute6) line_location_attribute6,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE7',plla.rowid,plla.attribute7) line_location_attribute7,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE8',plla.rowid,plla.attribute8) line_location_attribute8,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE9',plla.rowid,plla.attribute9) line_location_attribute9,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE10',plla.rowid,plla.attribute10) line_location_attribute10,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE11',plla.rowid,plla.attribute11) line_location_attribute11,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE12',plla.rowid,plla.attribute12) line_location_attribute12,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE13',plla.rowid,plla.attribute13) line_location_attribute13,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE14',plla.rowid,plla.attribute14) line_location_attribute14,
xxen_util.display_flexfield_value(201,'PO_LINE_LOCATIONS',plla.attribute_category,'ATTRIBUTE15',plla.rowid,plla.attribute15) line_location_attribute15
from
po_line_locations_all plla,
mtl_parameters mp,
hr_locations_all hla
where
plla.ship_to_location_id=hla.location_id(+) and
plla.ship_to_organization_id=mp.organization_id(+) and
plla.po_release_id is null
)
select
null action_,
null status_,
null message_,
pha.org_id,
to_number(null) request_id_,
to_number(null) interface_header_id,
pha.po_header_id,
pha.segment1 po_number,
null group_,
pha.revision_num,
pha.creation_date,
pt.document_type_name document_type,
po_headers_sv3.get_po_status(pha.po_header_id) document_status,
pha.currency_code,
pha.reference_num header_reference_num,
pha.comments header_description,
papf.full_name buyer,
pv.vendor_name supplier_name,
pvsa.vendor_site_code supplier_site,
pha.blanket_total_amount amount_agreed,
pha.attribute_category header_dff_context,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE1',pha.rowid,pha.attribute1) header_attribute1,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE2',pha.rowid,pha.attribute2) header_attribute2,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE3',pha.rowid,pha.attribute3) header_attribute3,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE4',pha.rowid,pha.attribute4) header_attribute4,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE5',pha.rowid,pha.attribute5) header_attribute5,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE6',pha.rowid,pha.attribute6) header_attribute6,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE7',pha.rowid,pha.attribute7) header_attribute7,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE8',pha.rowid,pha.attribute8) header_attribute8,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE9',pha.rowid,pha.attribute9) header_attribute9,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE10',pha.rowid,pha.attribute10) header_attribute10,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE11',pha.rowid,pha.attribute11) header_attribute11,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE12',pha.rowid,pha.attribute12) header_attribute12,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE13',pha.rowid,pha.attribute13) header_attribute13,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE14',pha.rowid,pha.attribute14) header_attribute14,
xxen_util.display_flexfield_value(201,'PO_HEADERS',pha.attribute_category,'ATTRIBUTE15',pha.rowid,pha.attribute15) header_attribute15,
hla2.location_code header_ship_to_location,
hla1.location_code header_bill_to_location,
to_number(null) interface_line_id,
pl.po_line_id,
pl.line_num,
pl.line_reference_num,
pl.line_type,
pl.item,
pl.category,
pl.description,
pl.unit_of_measure,
pl.unit_price price,
pl.line_quantity, 
pl.line_dff_context,
pl.line_attribute1,
pl.line_attribute2,
pl.line_attribute3,
pl.line_attribute4,
pl.line_attribute5,
pl.line_attribute6,
pl.line_attribute7,
pl.line_attribute8,
pl.line_attribute9,
pl.line_attribute10,
pl.line_attribute11,
pl.line_attribute12,
pl.line_attribute13,
pl.line_attribute14,
pl.line_attribute15,
to_number(null) interface_line_location_id,
pll.line_location_id,
pll.shipment_num,
pll.ship_quantity,
pll.break_price,
pll.break_unit_of_measure,
--pll.discount,
pll.ship_to_organization_code,
pll.ship_to_location_code,
pll.need_by_date,
pll.promised_date,
pll.line_location_dff_context,
pll.line_location_attribute1,
pll.line_location_attribute2,
pll.line_location_attribute3,
pll.line_location_attribute4,
pll.line_location_attribute5,
pll.line_location_attribute6,
pll.line_location_attribute7,
pll.line_location_attribute8,
pll.line_location_attribute9,
pll.line_location_attribute10,
pll.line_location_attribute11,
pll.line_location_attribute12,
pll.line_location_attribute13,
pll.line_location_attribute14,
pll.line_location_attribute15,
to_number(null) interface_distribution_id,
pda.po_distribution_id,
pda.distribution_num,
pda.quantity_ordered,
gcck.concatenated_segments charge_account,
(select papf.full_name from per_all_people_f papf where papf.person_id=pda.deliver_to_person_id and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date) requestor,
pda.attribute_category distribution_dff_context,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE1',pda.rowid,pda.attribute1) distribution_attribute1,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE2',pda.rowid,pda.attribute2) distribution_attribute2,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE3',pda.rowid,pda.attribute3) distribution_attribute3,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE4',pda.rowid,pda.attribute4) distribution_attribute4,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE5',pda.rowid,pda.attribute5) distribution_attribute5,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE6',pda.rowid,pda.attribute6) distribution_attribute6,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE7',pda.rowid,pda.attribute7) distribution_attribute7,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE8',pda.rowid,pda.attribute8) distribution_attribute8,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE9',pda.rowid,pda.attribute9) distribution_attribute9,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE10',pda.rowid,pda.attribute10) distribution_attribute10,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE11',pda.rowid,pda.attribute11) distribution_attribute11,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE12',pda.rowid,pda.attribute12) distribution_attribute12,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE13',pda.rowid,pda.attribute13) distribution_attribute13,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE14',pda.rowid,pda.attribute14) distribution_attribute14,
xxen_util.display_flexfield_value(201,'PO_DISTRIBUTIONS',pda.attribute_category,'ATTRIBUTE15',pda.rowid,pda.attribute15) distribution_attribute15,
null approval_status,
null create_or_update_items,
null group_lines
from
po_headers_all pha,
per_all_people_f papf,
po_vendors pv,
po_vendor_sites_all pvsa,
hr_locations_all hla1,
hr_locations_all hla2,
po_distributions_all pda,
gl_code_combinations_kfv gcck,
hr_all_organization_units_vl haouv,
document_types_ pt,
po_lines_ pl,
po_line_locations_ pll
where
1=1 and
2=2 and
pha.org_id=haouv.organization_id and
pha.type_lookup_code=pt.document_type and
papf.person_id=pha.agent_id and
trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and
pha.vendor_id=pv.vendor_id(+) and
pha.vendor_site_id=pvsa.vendor_site_id(+) and
pha.bill_to_location_id=hla1.location_id(+) and
pha.ship_to_location_id=hla2.location_id(+) and
pha.po_header_id=pl.po_header_id(+) and
pl.po_line_id=pll.po_line_id(+) and
pll.line_location_id=pda.line_location_id(+) and
pda.code_combination_id=gcck.code_combination_id(+) and
nvl(pha.cancel_flag,'N')='N' and
nvl(pha.closed_code,'OPEN')='OPEN' and
nvl(pha.frozen_flag,'N')='N' and
nvl(pha.user_hold_flag,'N')='N'
&not_use_first_block
&report_table_select &report_table_where_clause &success_records
&processed_run
order by 7,11,25,36,46
Parameter Name SQL text Validation
Operating Unit 
haouv.name=:operating_unit
 LOV
Upload Mode 
:upload_mode like '%' || xxen_upload.action_update
 LOV
Document Type 
case when pdtav.document_type_code='QUOTATION' then pdtav.document_type_code else pdtav.document_subtype end=:document_type
 LOV
Document Status 
po_headers_sv3.get_po_status(pha.po_header_id) =:document_status
 LOV
Document Number From 
pha.segment1>=:document_number_from
 LOV
Document Number To 
pha.segment1<=:document_number_to
 LOV
Creation Date From 
pha.creation_date>=:creation_date_from
 Date
Creation Date To 
pha.creation_date<:creation_date_to+1
 Date
Need By Date From 
pll.need_by_date>=:need_by_date_from
 Date
Need By Date To 
pll.need_by_date<:need_by_date_to+1
 Date
Agent Name 
papf.full_name=:agent
 LOV
Created By 
pha.created_by in (select user_id from fnd_user where user_name=:created_by)
 LOV
Download
 
Related Blitz Reports
AP Supplier Upload CAC Receiving Value (Period-End) GL Account Analysis (Distributions) PO Requisition Template Upload PO Requisition Upload GL Account Analysis AR Customer Upload PPF_WP3_OM_DETAILS ONT DIFOT
Blitz Report™

Blitz Report™ provides multiple benefits: