Find Document Type Template
Document Name: Standard Purchase Order
Document Type Layout : XXJG Standard Purchase Order XML
Contract Terms Layout : Oracle Contract Terms Template
XML Publisher -> Home -> Template
Data Definition: Standard Purchase Order Data Source
Application: Purchasing
Main View:
PO_HEADERS_XML
PO_LINES_XML
PO_LINE_LOCATIONS_XML
PO_DISTRIBUTION_XML
PO_HEADERS_ARCHIVE_XML
PO_LINES_ARCHIVE_XML
PO_LINE_LOCATIONS_ARCHIVE_XML
SELECT phx.*, PO_COMMUNICATION_PVT.getIsComplexWorkPO() is_complex_work_po, PO_COMMUNICATION_PVT.getDocumentType() document_type, PO_COMMUNICATION_PVT.getCoverMessage() cover_message,
PO_COMMUNICATION_PVT.getTimezone() timezone,PO_COMMUNICATION_PVT.getAmendmentMessage() ammendment_message,PO_COMMUNICATION_PVT.getTestFlag() test_flag,
PO_COMMUNICATION_PVT.getDistinctShipmentCount() DIST_SHIPMENT_COUNT, PO_COMMUNICATION_PVT.getFirstNonCancelledPll() NON_CANCELLED_PLL ,
PO_COMMUNICATION_PVT.getDocumentName() document_name,
PO_COMMUNICATION_PVT.IsDocumentSigned(PO_COMMUNICATION_PVT.getDocumentId()) Signed,
fnd_profile.value('PO_GENERATE_AMENDMENT_DOCS') amendment_profile,PO_COMMUNICATION_PVT.getWithTerms() With_Terms , PO_COMMUNICATION_PVT.getIsContractAttachedDoc() Is_Attached_Doc , CURSOR (SELECT message_name message, message_text text FROM fnd_new_messages WHERE message_name in (
'PO_WF_NOTIF_REVISION',
'PO_WF_NOTIF_VENDOR_NO',
'PO_WF_NOTIF_PAYMENT_TERMS',
'PO_WF_NOTIF_FREIGHT_TERMS',
'PO_WF_NOTIF_FOB',
'PO_WF_NOTIF_SHIP_VIA',
'PO_WF_NOTIF_CONFIRM_TO_TELE',
'PO_WF_NOTIF_REQUESTER_DELIVER',
'PO_WF_NOTIF_DESCRIPTION',
'PO_WF_NOTIF_TAX',
'PO_WF_NOTIF_UOM',
'PO_WF_NOTIF_UNIT_PRICE',
'PO_WF_NOTIF_QUANTITY',
'PO_WF_NOTIF_PURCHASE_ORDER',
'PO_WF_NOTIF_BUYER',
'PO_WF_NOTIF_AMOUNT',
'PO_WF_NOTIF_EFFECTIVE_DATE',
'PO_WF_NOTIF_HEADER_NOTE',
'PO_WF_NOTIF_LINE_NUMBER',
'PO_WF_NOTIF_LINE_PAYITEM_NUM', -- <Complex Work R12>
'PO_WF_NOTIF_MULTIPLE',
'PO_WF_NOTIF_PART_NO_DESC',
'PO_WF_NOTIF_SUPPLIER_ITEM',
'PO_WF_NOTIF_TOTAL',
'PO_WF_NOTIF_NOTE',
'PO_FO_PACKING_INSTRUCTION',
'PO_FO_CUST_PO_NUMBER',
'PO_FO_CUST_ITEM_DESC',
'PO_FO_LINE_NUMBER',
'PO_FO_SHIP_NUMBER',
'PO_FO_AMOUNT_BASED',
'PO_FO_CONTRACTOR_NAME',
'PO_FO_START_DATE',
'PO_FO_END_DATE',
'PO_FO_WORK_SCHEDULE',
'PO_FO_SHIFT_PATTERN',
'PO_FO_PRICE_DIFFERENTIALS',
'PO_FO_DELIVER_TO_LOCATION',
'PO_FO_EFFECTIVE_START_DATE',
'PO_FO_AMOUNT_AGREED',
'PO_FO_ADVANCE', -- <Complex Work R12>
'PO_FO_RETAINAGE_RATE', -- <Complex Work R12>
'PO_FO_MAX_RETAINAGE_AMOUNT', -- <Complex Work R12>
'PO_FO_PROGRESS_PAYMENT_RATE', -- <Complex Work R12>
'PO_FO_RECOUPMENT_RATE', -- <Complex Work R12>
'PO_FO_PRICE_BREAK',
'PO_FO_CHARGE_ACCOUNT',
'PO_FO_CONTRACTOR',
'PO_FO_CONTACT_NAME',
'PO_FO_TELEPHONE',
'PO_FO_FAX',
'PO_FO_NAME',
'PO_FO_TITLE',
'PO_FO_DATE',
'PO_FO_REVISION',
'PO_FO_AMENDMENT',
'PO_FO_SHIP_METHOD',
'PO_FO_SHIPPING_INSTRUCTION',
'PO_FO_DRAFT',
'PO_FO_PROPRIETARY_INFORMATION',
'PO_FO_TRANSPORTAION_ARRANGED',
'PO_FO_DELIVER_TO_LOCATION',
'PO_FO_NO',
'PO_FO_COMPANY',
'PO_FO_SUBMIT_RESPONSE',
'PO_FO_EMAIL',
'PO_WF_NOTIF_EXPIRES_ON',
'PO_FO_TEST',
'PO_FO_ORG_AGR_ASS',
'PO_FO_EFFECTIVE_END_DATE',
'PO_FO_PURCHASING_ORGANIZATION',
'PO_FO_PURCHASING_SUPPLIER_SITE',
'PO_FO_TRANSPORTATION_ARRANGED',
'PO_WF_NOTIF_ADDRESS',
'PO_WF_NOTIF_ORDER',
'PO_WF_NOTIF_ORDER_DATE',
'PO_FO_VENDOR',
'PO_FO_SHIP_TO',
'PO_FO_BILL_TO',
'PO_FO_CONFIRM_NOT_DUPLICATE',
'PO_FO_AGREEMENT_CANCELED',
'PO_FO_FORMAL_ACCEPT',
'PO_FO_TYPE',
'PO_FO_REVISION_DATE',
'PO_FO_REVISED_BY',
'PO_FO_PRICES_EXPRESSED',
'PO_FO_NOTES',
'PO_WF_NOTIF_PREPARER',
'PO_FO_SUPPLIER_CONFIGURATION',
'PO_FO_DELIVER_DATE_TIME',
'PO_FO_LINE_REF_BPA',
'PO_FO_LINE_REF_CONTRACT',
'PO_FO_LINE_SUPPLIER_QUOTATION',
'PO_FO_USE_SHIP_ADDRESS_TOP',
'PO_FO_LINE_CANCELED',
'PO_FO_ORIGINAL_QTY_ORDERED',
'PO_FO_QUANTITY_CANCELED',
'PO_FO_SHIPMENT_CANCELED',
'PO_FO_ORIGINAL_SHIPMENT_QTY',
'PO_FO_CUSTOMER_ACCOUNT_NO',
'PO_FO_RELEASE_CANCELED',
'PO_FO_PO_CANCELED',
'PO_FO_TOTAL',
'PO_FO_SUPPLIER_ITEM',
'PO_FO_ORIGINAL_AMOUNT_ORDERED',
'PO_FO_AMOUNT_CANCELED',
'PO_FO_UN_NUMBER',
'PO_WF_NOTIF_PROMISED_DATE',
'PO_WF_NOTIF_NEEDBY_DATE',
'PO_FO_HAZARD_CLASS',
'PO_FO_PAGE',
'PO_FO_REFERENCE_DOCUMENTS',
'PO_FO_PAYITEM_CANCELED', --<Bug#: 4899200>
'PO_FO_ORIGINAL_PAYITEM_QTY', --<Bug#: 4899200>
'PO_FO_PAYITEM_QTY_CANCELED', --<Bug#: 4899200>
'PO_FO_ORIGINAL_PAYITEM_AMT', --<Bug#: 5464968>
'PO_FO_MODIFIER_TYPE', --Enhanced Pricing
'PO_FO_BLANKET', --Enhanced Pricing
'PO_FO_BLANKET_PRICE_STRUCT', --Enhanced Pricing
'PO_FO_MODIFIER_DESC', --Enhanced Pricing
'PO_FO_RATE', --Enhanced Pricing
'PO_FO_ADJUSTED_AMT', --Enhanced Pricing
'PO_FO_RATE_APP_METHOD', --Enhanced Pricing
'PO_FO_LIST_LINE_PRICE', --Enhanced Pricing
'PO_FO_PAYITEM_AMT_CANCELED', --<Bug#: 5464968>
'PO_FO_USE_SHIP_ADDRESS' --Bug 9855114
) AND application_id = 201 AND language_code = 'US') AS message,CURSOR( SELECT fds.short_text
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_short_text fds
WHERE ((entity_name = 'PO_HEADERS' AND
pk1_value = to_char(phx.po_header_id)) OR
(entity_name = 'PO_VENDORS' AND
pk1_value = to_char(phx.vendor_id)) OR
(entity_name = 'PO_VENDOR_SITES' AND
pk1_value = to_char(phx.vendor_site_id))) AND -- bug6154354
function_name = 'PO_PRINTPO'
AND fad.media_id = fds.media_id
AND fad.datatype_id=1 order by fad.seq_num) AS header_short_text,CURSOR(
SELECT fad.url web_page --Bug#4958642
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(phx.po_header_id)) OR
(fad.entity_name='PO_VENDORS' AND fad.pk1_value=to_char(phx.vendor_id)) OR
(fad.entity_name='PO_VENDOR_SITES' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
)
AND fad.datatype_id=5
AND fad.function_name='PO_PRINTPO'
order by fad.entity_name,fad.seq_num) AS header_url_attachments,CURSOR(
SELECT fad.file_name
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(phx.po_header_id)) OR
(fad.entity_name='PO_VENDORS' AND fad.pk1_value=to_char(phx.vendor_id)) OR
(fad.entity_name='PO_VENDOR_SITES' AND fad.pk1_value=to_char(phx.vendor_site_id)) --bug6154354
)
AND fad.datatype_id=6
AND fad.function_name='PO_PRINTPO'
order by fad.entity_name,fad.seq_num) AS header_file_attachments,
CURSOR(SELECT plx.*, CURSOR(SELECT PRICE_TYPE_DSP PRICE_TYPE, MULTIPLIER FROM po_price_differentials_v
WHERE entity_type='PO LINE' AND entity_id = plx.po_line_id and enabled_flag='Y') AS price_diff,
CURSOR( SELECT plx.po_line_id , fds.short_text
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_short_text fds
WHERE ((fad.entity_name='PO_LINES' AND fad.pk1_value=to_char(plx.po_line_id))
OR
(fad.entity_name='MTL_SYSTEM_ITEMS' AND
fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND --Bug6139548
fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)
OR
(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.from_header_id)
AND plx.from_header_id IS NOT NULL)
OR
(fad.entity_name='PO_IN_GA_LINES' AND fad.pk1_value=to_char(plx.from_line_id)
AND plx.from_line_id IS NOT NULL)
OR
(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.CONTRACT_ID)
AND plx.CONTRACT_ID IS NOT NULL)
) AND
function_name = 'PO_PRINTPO'
AND fad.media_id = fds.media_id
AND fad.datatype_id=1 order by fad.seq_num) AS line_short_text,CURSOR (SELECT pax1.* FROM PO_PRICE_ADJUSTMENTS_XML pax1 WHERE pax1.po_header_id = plx.po_header_id AND pax1.po_line_id = plx.po_line_id AND pax1.parent_adjustment_id is null order by pax1.pricing_group_sequence) AS PRICE_MODIFIERS,CURSOR (SELECT pax2.* FROM PO_PRICE_ADJUSTMENTS_XML pax2 WHERE pax2.po_header_id = plx.from_header_id AND pax2.po_line_id = plx.from_line_id AND pax2.parent_adjustment_id is null order by pax2.pricing_group_sequence) AS ADD_PRICE_MODIFIERS,CURSOR (SELECT pha.segment1 ponum FROM po_headers_all pha WHERE pha.po_header_id = plx.from_header_id) AS ADD_PRICE_PONUM,CURSOR(
SELECT fad.url web_page --Bug#4958642
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name='PO_LINES' AND fad.pk1_value=to_char(plx.po_line_id))
OR
(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.from_header_id)
AND plx.from_header_id IS NOT NULL)
OR
(fad.entity_name='PO_IN_GA_LINES' AND fad.pk1_value=to_char(plx.from_line_id)
AND plx.from_line_id IS NOT NULL)
OR
(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.contract_id)
AND plx.contract_id IS NOT NULL)
OR
(fad.entity_name='MTL_SYSTEM_ITEMS' AND
fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND
fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)
)
AND fad.datatype_id=5
AND fad.function_name='PO_PRINTPO' order by fad.seq_num) AS line_url_attachments,CURSOR(
SELECT fad.file_name
FROM fnd_attached_docs_form_vl fad
WHERE ((fad.entity_name='PO_LINES' AND fad.pk1_value=to_char(plx.po_line_id))
OR
(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.from_header_id)
AND plx.from_header_id IS NOT NULL)
OR
(fad.entity_name='PO_IN_GA_LINES' AND fad.pk1_value=to_char(plx.from_line_id)
AND plx.from_line_id IS NOT NULL)
OR
(fad.entity_name='PO_HEADERS' AND fad.pk1_value=to_char(plx.contract_id)
AND plx.contract_id IS NOT NULL)
OR
(fad.entity_name='MTL_SYSTEM_ITEMS' AND
fad.pk1_value=to_char(PO_COMMUNICATION_PVT.getInventoryOrgId()) AND
fad.pk2_value=to_char(plx.item_id) AND plx.item_id is not null)
)
AND fad.datatype_id=6
AND fad.function_name='PO_PRINTPO'
order by fad.seq_num) AS line_file_attachments,
CURSOR(SELECT pllx.*, CURSOR(SELECT zl.tax_rate_id tax_code_id, zl.tax_rate_code tax_name
, zl.TAX_RATE
, zl.TAX_RATE_TYPE
, zl.ENTITY_CODE
, zl.EVENT_CLASS_CODE
, zl.TRX_ID
, zl.APPLICATION_ID
, zl.TRX_LEVEL_TYPE
, zl.TRX_LINE_ID
, zl.TRX_NUMBER
, zl.TRX_LINE_NUMBER
, zl.TAX_LINE_NUMBER
, zl.TAX_REGIME_CODE
, zl.TAX
, zl.TAX_APPORTIONMENT_LINE_NUMBER
, zl.UNIT_PRICE
, zl.LINE_AMT
, zl.TRX_LINE_QUANTITY
, zl.UNROUNDED_TAXABLE_AMT
, zl.UNROUNDED_TAX_AMT
, zl.TAX_CURRENCY_CODE
, zl.TAX_AMT
, zl.TAX_AMT_TAX_CURR
, zl.TAX_AMT_FUNCL_CURR
, zl.TAXABLE_AMT_TAX_CURR
, zl.TAXABLE_AMT_FUNCL_CURR
, zl.TAX_LINE_ID
, zl.TAX_STATUS_CODE
, zl.TAX_JURISDICTION_CODE
, zl.TAXABLE_AMT
, zl.TAX_TYPE_CODE
FROM zx_lines zl
WHERE zl.application_id = 201
AND zl.entity_code = 'PURCHASE_ORDER'
AND zl.event_class_code = 'PO_PA'
AND zl.trx_id = pllx.po_header_id
AND zl.trx_line_id = pllx.line_location_id
) AS tax_names,CURSOR( SELECT pllx.line_location_id, fds.short_text
FROM
fnd_attached_docs_form_vl fad,
fnd_documents_short_text fds
WHERE entity_name = 'PO_SHIPMENTS' AND
pk1_value = to_char(pllx.line_location_id) AND
function_name = 'PO_PRINTPO'
AND fad.media_id = fds.media_id
AND fad.datatype_id=1
order by fad.seq_num) AS line_loc_short_text,CURSOR(
SELECT fad.url web_page --Bug#4958642
FROM fnd_attached_docs_form_vl fad
WHERE fad.entity_name='PO_SHIPMENTS'
AND fad.pk1_value=to_char(pllx.line_location_id)
AND fad.datatype_id=5
AND fad.function_name='PO_PRINTPO'
order by fad.seq_num ) AS line_loc_url_attachments,CURSOR(
SELECT fad.file_name
FROM fnd_attached_docs_form_vl fad
WHERE fad.entity_name='PO_SHIPMENTS'
AND fad.pk1_value=to_char(pllx.line_location_id)
AND fad.datatype_id=6
AND fad.function_name='PO_PRINTPO'
order by fad.seq_num) AS line_loc_file_attachments,
CURSOR(SELECT pdx.* FROM po_distribution_xml pdx WHERE pdx.draft_id = -1 AND pdx.po_header_id = phx.po_header_id and pdx.LINE_LOCATION_ID = pllx.LINE_LOCATION_ID) AS distributions
FROM po_line_locations_xml pllx WHERE pllx.draft_id = -1 AND pllx.po_line_id = plx.po_line_id AND NVL(pllx.payment_type,'NONE') NOT IN ('ADVANCE','DELIVERY') order by pllx.shipment_num ) AS line_locations
FROM po_lines_xml plx WHERE plx.draft_id = -1 AND plx.po_header_id = phx.po_header_id
and nvl(plx.cancel_flag, 'N') = decode(PO_COMMUNICATION_PVT.getWithCanceledLines(), 'N', 'N', nvl(plx.cancel_flag, 'N'))
and PO_COMMUNICATION_PVT.getWithClosedLines() = decode(instr(nvl(plx.closed_code, ' '), 'CLOSED'), 0, PO_COMMUNICATION_PVT.getWithClosedLines(), 'Y') order by plx.line_num) AS lines
FROM PO_HEADERS_XML phx WHERE phx.draft_id = -1 AND phx.PO_HEADER_ID = PO_COMMUNICATION_PVT.getDocumentId() AND
phx.revision_num = PO_COMMUNICATION_PVT.getRevisionNum()