VIEW: APPS.ITG_SP_REL_POLINE_V

Source

View Text - Preformatted

SELECT pla.po_header_id, pla.po_line_id, pll.line_location_id, r.po_release_id, pll.price_override unit_amount, pha.currency_code unit_currency, pll.quantity unit_quantity, uom.uom_code unit_uom, pla.quantity ordered_quantity, TO_CHAR(pll.shipment_num) polinenum, DECODE(pla.category_id, NULL, NULL, itg_x_utils.segString( 401, 'MCAT', cat.structure_id, cat.segment1, cat.segment2, cat.segment3, cat.segment4, cat.segment5, cat.segment6, cat.segment7, cat.segment8, cat.segment9, cat.segment10, cat.segment11, cat.segment12, cat.segment13, cat.segment14, cat.segment15, cat.segment16, cat.segment17, cat.segment18, cat.segment19, cat.segment20)) commodity, (select segment1 FROM po_headers_all WHERE po_header_id=pla.contract_id) contracts, pha.freight_terms_lookup_code frghtcls, hazTL.hazard_class hazrdmatl, pla.item_revision itemrv, pla.item_description itemtype, pla.note_to_vendor notes, TO_CHAR(pla.org_id) poentity, DECODE(pla.item_id, NULL, NULL, itg_x_utils.segString( 401, 'MSTK', NULL, mi.segment1, mi.segment2, mi.segment3, mi.segment4, mi.segment5, mi.segment6, mi.segment7, mi.segment8, mi.segment9, mi.segment10, mi.segment11, mi.segment12, mi.segment13, mi.segment14, mi.segment15, mi.segment16, mi.segment17, mi.segment18, mi.segment19, mi.segment20)) item, pla.vendor_product_num itemx, DECODE(pha.authorization_status, 'APPROVED', 'OPEN', 'REQUIRES REAPPROVAL', 'HOLD', NULL) UA_ref_status, DECODE(mi.inventory_item_flag, 'Y', 'N', 'Y') UA_ref_exp, pll.attribute1 UA_ref_attr, pll.attribute1 UA_line_attr1, pll.attribute1 UA_line_attr2, pll.attribute1 UA_line_attr3, pll.attribute1 UA_line_attr4, pll.attribute1 UA_line_attr5, pll.attribute1 UA_line_attr6, pll.attribute1 UA_line_attr7, pll.attribute1 UA_line_attr8, pll.attribute1 UA_line_attr9, pll.attribute1 UA_line_attr10, pll.attribute1 UA_line_attr11, pll.attribute1 UA_line_attr12, pll.attribute1 UA_line_attr13, pll.attribute1 UA_line_attr14, pll.attribute1 UA_line_attr15, NULL to_po_internal, NULL to_misc, NULL to_approver, NULL to_buyer, NULL to_payables, NULL to_receiver, NULL to_supplier FROM mtl_categories cat, mtl_units_of_measure uom, mtl_system_items mi, po_lines_all pla, po_hazard_classes_tl hazTL, po_headers_all pha, po_line_locations_all pll, po_releases_all r WHERE uom.unit_of_measure (+) = pla.unit_meas_lookup_code AND cat.category_id (+) = pla.category_id AND pha.po_header_id = pla.po_header_id AND mi.inventory_item_id (+) = pla.item_id AND hazTL.hazard_class_id (+) = pla.hazard_class_id AND pla.po_line_id = pll.po_line_id AND r.po_release_id = pll.po_release_id AND mi.organization_id (+) = itg_x_utils.get_inventory_org_id(pla.org_id) AND hazTL.language(+) = userenv('LANG') and NVL(pll.shipment_type,'xx') <> 'PREPAYMENT'

View Text - HTML Formatted

SELECT PLA.PO_HEADER_ID
, PLA.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, R.PO_RELEASE_ID
, PLL.PRICE_OVERRIDE UNIT_AMOUNT
, PHA.CURRENCY_CODE UNIT_CURRENCY
, PLL.QUANTITY UNIT_QUANTITY
, UOM.UOM_CODE UNIT_UOM
, PLA.QUANTITY ORDERED_QUANTITY
, TO_CHAR(PLL.SHIPMENT_NUM) POLINENUM
, DECODE(PLA.CATEGORY_ID
, NULL
, NULL
, ITG_X_UTILS.SEGSTRING( 401
, 'MCAT'
, CAT.STRUCTURE_ID
, CAT.SEGMENT1
, CAT.SEGMENT2
, CAT.SEGMENT3
, CAT.SEGMENT4
, CAT.SEGMENT5
, CAT.SEGMENT6
, CAT.SEGMENT7
, CAT.SEGMENT8
, CAT.SEGMENT9
, CAT.SEGMENT10
, CAT.SEGMENT11
, CAT.SEGMENT12
, CAT.SEGMENT13
, CAT.SEGMENT14
, CAT.SEGMENT15
, CAT.SEGMENT16
, CAT.SEGMENT17
, CAT.SEGMENT18
, CAT.SEGMENT19
, CAT.SEGMENT20)) COMMODITY
, (SELECT SEGMENT1
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID=PLA.CONTRACT_ID) CONTRACTS
, PHA.FREIGHT_TERMS_LOOKUP_CODE FRGHTCLS
, HAZTL.HAZARD_CLASS HAZRDMATL
, PLA.ITEM_REVISION ITEMRV
, PLA.ITEM_DESCRIPTION ITEMTYPE
, PLA.NOTE_TO_VENDOR NOTES
, TO_CHAR(PLA.ORG_ID) POENTITY
, DECODE(PLA.ITEM_ID
, NULL
, NULL
, ITG_X_UTILS.SEGSTRING( 401
, 'MSTK'
, NULL
, MI.SEGMENT1
, MI.SEGMENT2
, MI.SEGMENT3
, MI.SEGMENT4
, MI.SEGMENT5
, MI.SEGMENT6
, MI.SEGMENT7
, MI.SEGMENT8
, MI.SEGMENT9
, MI.SEGMENT10
, MI.SEGMENT11
, MI.SEGMENT12
, MI.SEGMENT13
, MI.SEGMENT14
, MI.SEGMENT15
, MI.SEGMENT16
, MI.SEGMENT17
, MI.SEGMENT18
, MI.SEGMENT19
, MI.SEGMENT20)) ITEM
, PLA.VENDOR_PRODUCT_NUM ITEMX
, DECODE(PHA.AUTHORIZATION_STATUS
, 'APPROVED'
, 'OPEN'
, 'REQUIRES REAPPROVAL'
, 'HOLD'
, NULL) UA_REF_STATUS
, DECODE(MI.INVENTORY_ITEM_FLAG
, 'Y'
, 'N'
, 'Y') UA_REF_EXP
, PLL.ATTRIBUTE1 UA_REF_ATTR
, PLL.ATTRIBUTE1 UA_LINE_ATTR1
, PLL.ATTRIBUTE1 UA_LINE_ATTR2
, PLL.ATTRIBUTE1 UA_LINE_ATTR3
, PLL.ATTRIBUTE1 UA_LINE_ATTR4
, PLL.ATTRIBUTE1 UA_LINE_ATTR5
, PLL.ATTRIBUTE1 UA_LINE_ATTR6
, PLL.ATTRIBUTE1 UA_LINE_ATTR7
, PLL.ATTRIBUTE1 UA_LINE_ATTR8
, PLL.ATTRIBUTE1 UA_LINE_ATTR9
, PLL.ATTRIBUTE1 UA_LINE_ATTR10
, PLL.ATTRIBUTE1 UA_LINE_ATTR11
, PLL.ATTRIBUTE1 UA_LINE_ATTR12
, PLL.ATTRIBUTE1 UA_LINE_ATTR13
, PLL.ATTRIBUTE1 UA_LINE_ATTR14
, PLL.ATTRIBUTE1 UA_LINE_ATTR15
, NULL TO_PO_INTERNAL
, NULL TO_MISC
, NULL TO_APPROVER
, NULL TO_BUYER
, NULL TO_PAYABLES
, NULL TO_RECEIVER
, NULL TO_SUPPLIER
FROM MTL_CATEGORIES CAT
, MTL_UNITS_OF_MEASURE UOM
, MTL_SYSTEM_ITEMS MI
, PO_LINES_ALL PLA
, PO_HAZARD_CLASSES_TL HAZTL
, PO_HEADERS_ALL PHA
, PO_LINE_LOCATIONS_ALL PLL
, PO_RELEASES_ALL R
WHERE UOM.UNIT_OF_MEASURE (+) = PLA.UNIT_MEAS_LOOKUP_CODE
AND CAT.CATEGORY_ID (+) = PLA.CATEGORY_ID
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND MI.INVENTORY_ITEM_ID (+) = PLA.ITEM_ID
AND HAZTL.HAZARD_CLASS_ID (+) = PLA.HAZARD_CLASS_ID
AND PLA.PO_LINE_ID = PLL.PO_LINE_ID
AND R.PO_RELEASE_ID = PLL.PO_RELEASE_ID
AND MI.ORGANIZATION_ID (+) = ITG_X_UTILS.GET_INVENTORY_ORG_ID(PLA.ORG_ID)
AND HAZTL.LANGUAGE(+) = USERENV('LANG')
AND NVL(PLL.SHIPMENT_TYPE
, 'XX') <> 'PREPAYMENT'