View: ITG_REQ_LINES_V

Product:	ITG - Internet Procurement Enterprise Connector
Description:	This view is used to generate the XML at the requisition lines level
Implementation/DBA Data:	APPS.ITG_REQ_LINES_V

View Text

SELECT LA.REQUISITION_HEADER_ID REQ_HEADER_ID
, LA.REQUISITION_LINE_ID REQ_LINE_ID
, LA.NEED_BY_DATE DATETIME_NEED_BY_DATE
, LA.QUANTITY QUANTITY_ORDERED
, UOM.UOM_CODE UOM_CODE
, NVL (LA.CURRENCY_UNIT_PRICE
, LA.UNIT_PRICE) OPERAMT_UNIT_PRICE
, DECODE( LA.CURRENCY_UNIT_PRICE
, NULL
, SOB.CURRENCY_CODE
, LA.CURRENCY_CODE ) OPERAMT_CURRENCY_CODE
, LA.QUANTITY OPERAMT_QUANTITY
, TO_CHAR(LA.LINE_NUM) REQLINENUM
, BUYPER.FULL_NAME BUYERID
, DECODE ( LA.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
, SHIPPER.EMPLOYEE_NUMBER DELIVERTO
, HAZMAT.HAZARD_CLASS HAZARDMATL
, LA.ITEM_REVISION ITEMRV
, LA.SUGGESTED_VENDOR_PRODUCT_CODE ITEMX
, LA.NOTE_TO_AGENT NOTES
, LA.ORG_ID POENTITY
, UN.UN_NUMBER PROPERTY_1
, TO_CHAR(LA.KANBAN_CARD_ID) PROPERTY_2
, DESTORG.ORGANIZATION_NAME SITELEVEL_1
, LA.DESTINATION_SUBINVENTORY SITELEVEL_2
, LA.ITEM_DESCRIPTION DESCRIPTN
, DECODE ( LA.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
, NVL(DECODE(LA.DESTINATION_TYPE_CODE
, 'EXPENSE'
, 'Y'
, 'SHOP CODE'
, 'Y'
, 'INVENTORY'
, 'N')
, DECODE(MI.INVENTORY_ITEM_FLAG
, 'Y'
, 'N'
, 'Y')) LINE_UA_EXPENSE
, LA.ATTRIBUTE6 LINE_UA_ATTRIBUTE
, LA.ATTRIBUTE1 LINE_UA_ATTRIBUTE1
, LA.ATTRIBUTE2 LINE_UA_ATTRIBUTE2
, LA.ATTRIBUTE3 LINE_UA_ATTRIBUTE3
, LA.ATTRIBUTE4 LINE_UA_ATTRIBUTE4
, LA.ATTRIBUTE5 LINE_UA_ATTRIBUTE5
, LA.ATTRIBUTE6 LINE_UA_ATTRIBUTE6
, LA.ATTRIBUTE7 LINE_UA_ATTRIBUTE7
, LA.ATTRIBUTE8 LINE_UA_ATTRIBUTE8
, LA.ATTRIBUTE9 LINE_UA_ATTRIBUTE9
, LA.ATTRIBUTE10 LINE_UA_ATTRIBUTE10
, LA.ATTRIBUTE11 LINE_UA_ATTRIBUTE11
, LA.ATTRIBUTE12 LINE_UA_ATTRIBUTE12
, LA.ATTRIBUTE13 LINE_UA_ATTRIBUTE13
, LA.ATTRIBUTE14 LINE_UA_ATTRIBUTE14
, LA.ATTRIBUTE15 LINE_UA_ATTRIBUTE15
, SHIPLOC.LOCATION_CODE EMPLOYEE_NAME
, SHIPPER.EMPLOYEE_NUMBER EMPLOYEE_PARTNID
, 'EMPLOYEE' EMPLOYEE_PARTNRTYPE
, TO_CHAR (LA.DELIVER_TO_LOCATION_ID) EMPLOYEE_PARTNRIDX
, SHIPLOC.BILL_TO_SITE_FLAG EMP_UA_BILL_TO_SITE_FLAG
, SHIPLOC.SHIP_TO_SITE_FLAG EMP_UA_SHIP_TO_SITE_FLAG
, SHIPLOC.RECEIVING_SITE_FLAG EMP_UA_RECV_SITE_FLAG
, SHIPLOC.ADDRESS_LINE_1 EMPLOYEE_ADDR_LINE1
, SHIPLOC.ADDRESS_LINE_2 EMPLOYEE_ADDR_LINE2
, SHIPLOC.ADDRESS_LINE_3 EMPLOYEE_ADDR_LINE3
, 'DELIVERY' EMPLOYEE_ADDR_ADDRTYPE
, SHIPLOC.TOWN_OR_CITY EMPLOYEE_ADDR_CITY
, SHIPLOC.COUNTRY EMPLOYEE_ADDR_COUNTRY
, ITG_X_UTILS.GETCOUNTY ( SHIPLOC.STYLE
, SHIPLOC.REGION_1
, SHIPLOC.REGION_2 ) EMPLOYEE_ADDR_COUNTY
, SHIPLOC.DESCRIPTION EMPLOYEE_ADDR_DESCRIPTN
, SHIPLOC.POSTAL_CODE EMPLOYEE_ADDR_POSTALCODE
, ITG_X_UTILS.GETSTATE ( SHIPLOC.STYLE
, SHIPLOC.REGION_1
, SHIPLOC.REGION_2 ) EMPLOYEE_ADDR_STATEPROVN
, SHIPLOC.TELEPHONE_NUMBER_1 EMPLOYEE_ADDR_TELEPHONE1
, SHIPLOC.TELEPHONE_NUMBER_2 EMPLOYEE_ADDR_TELEPHONE2
, SHIPLOC.TELEPHONE_NUMBER_3 EMPLOYEE_ADDR_TELEPHONE3
, SHIPPER.EMPLOYEE_NUMBER EMPLOYEE_CONTACT_NAME
, SHIPPER.EMAIL_ADDRESS EMPLOYEE_CONTACT_EMAIL
, SHIPPER.WORK_TELEPHONE EMPLOYEE_CONTACT_TELEPHONE1
, DESTORG.ORGANIZATION_NAME SHIPTO_NAME
, DESTORG.ORGANIZATION_CODE SHIPTO_PARTNRID
, 'SHIPTO' SHIPTO_PARTNRTYPE
, TO_CHAR (LA.DESTINATION_ORGANIZATION_ID) SHIPTO_PARTNRIDX
, DECODE (DESTLOC.TAX_NAME
, NULL
, '0'
, '1') SHIPTO_TAXEXEMPT
, ITG_X_UTILS.GETTAXID ( DESTLOC.COUNTRY
, DESTORG.ORGANIZATION_ID
, DESTORG.ORGANIZATION_NAME
, DESTORG.OPERATING_UNIT
, DESTLOC.INVENTORY_ORGANIZATION_ID ) SHIPTO_TAXID
, DESTLOC.BILL_TO_SITE_FLAG SHIPTO_UA_BILLFLG
, DESTLOC.SHIP_TO_SITE_FLAG SHIPTO_UA_SHIPFLG
, DESTLOC.RECEIVING_SITE_FLAG SHIPTO_UA_RECFLG
, DESTLOC.ADDRESS_LINE_1 SHIPTO_ADDR_LINE1
, DESTLOC.ADDRESS_LINE_2 SHIPTO_ADDR_LINE2
, DESTLOC.ADDRESS_LINE_3 SHIPTO_ADDR_LINE3
, 'RECEIVING' SHIPTO_ADDR_ADDRTYPE
, DESTLOC.TOWN_OR_CITY SHIPTO_ADDR_CITY
, DESTLOC.COUNTRY SHIPTO_ADDR_COUNTRY
, ITG_X_UTILS.GETCOUNTY ( DESTLOC.STYLE
, DESTLOC.REGION_1
, DESTLOC.REGION_2 ) SHIPTO_ADDR_COUNTY
, DESTLOC.DESCRIPTION SHIPTO_ADDR_DESCRIPTN
, DESTLOC.POSTAL_CODE SHIPTO_ADDR_POSTALCODE
, ITG_X_UTILS.GETSTATE ( DESTLOC.STYLE
, DESTLOC.REGION_1
, DESTLOC.REGION_2 ) SHIPTO_ADDR_STATEPROVN
, DESTLOC.TELEPHONE_NUMBER_1 SHIPTO_ADDR_TELEPHONE1
, DESTLOC.TELEPHONE_NUMBER_2 SHIPTO_ADDR_TELEPHONE2
, DESTLOC.TELEPHONE_NUMBER_3 SHIPTO_ADDR_TELEPHONE3
, ITG_X_UTILS.GETATTACHMENTS('PO_REQUISITION_LINES'
, 'REQ INTERNAL'
, LA.REQUISITION_LINE_ID) TO_PO_INTERNAL
, ITG_X_UTILS.GETATTACHMENTS('PO_REQUISITION_LINES'
, 'MISC'
, LA.REQUISITION_LINE_ID) TO_MISC
, ITG_X_UTILS.GETATTACHMENTS('PO_REQUISITION_LINES'
, 'APPROVER'
, LA.REQUISITION_LINE_ID) TO_APPROVER
, ITG_X_UTILS.GETATTACHMENTS('PO_REQUISITION_LINES'
, 'BUYER'
, LA.REQUISITION_LINE_ID) TO_BUYER
, ITG_X_UTILS.GETATTACHMENTS('PO_REQUISITION_LINES'
, 'PAYABLES'
, LA.REQUISITION_LINE_ID) TO_PAYABLES
, ITG_X_UTILS.GETATTACHMENTS('PO_REQUISITION_LINES'
, 'RECEIVER'
, LA.REQUISITION_LINE_ID) TO_RECEIVER
, ITG_X_UTILS.GETATTACHMENTS('PO_REQUISITION_LINES'
, 'VENDOR'
, LA.REQUISITION_LINE_ID) TO_SUPPLIER
FROM PO_UN_NUMBERS_TL UN
, ORG_ORGANIZATION_DEFINITIONS ORG
, MTL_SYSTEM_ITEMS MI
, HR_LOCATIONS_ALL DESTLOC
, HR_ALL_ORGANIZATION_UNITS DESTUNIT
, ORG_ORGANIZATION_DEFINITIONS DESTORG
, PO_HAZARD_CLASSES_TL HAZMAT
, PER_PEOPLE_F BUYPER
, HR_LOCATIONS SHIPLOC
, PER_PEOPLE_F SHIPPER
, MTL_UNITS_OF_MEASURE UOM
, MTL_CATEGORIES CAT
, GL_SETS_OF_BOOKS SOB
, ORG_ORGANIZATION_DEFINITIONS SOBORG
, PO_REQUISITION_HEADERS_ALL HA
, PO_REQUISITION_LINES_ALL LA
WHERE UN.UN_NUMBER_ID (+) = LA.UN_NUMBER_ID
AND NVL(UN.LANGUAGE
, USERENV('LANG')) =USERENV('LANG')
AND ORG.ORGANIZATION_ID (+) = LA.ORG_ID
AND MI.INVENTORY_ITEM_ID (+) = LA.ITEM_ID
AND MI.ORGANIZATION_ID (+) = ITG_X_UTILS.GET_INVENTORY_ORG_ID(LA.ORG_ID)
AND HAZMAT.HAZARD_CLASS_ID (+) = LA.HAZARD_CLASS_ID
AND NVL(HAZMAT.LANGUAGE
, USERENV('LANG')) = USERENV('LANG')
AND LA.LAST_UPDATE_DATE BETWEEN NVL(BUYPER.EFFECTIVE_START_DATE
, LA.LAST_UPDATE_DATE)
AND NVL(BUYPER.EFFECTIVE_END_DATE
, LA.LAST_UPDATE_DATE)
AND BUYPER.PERSON_ID (+) = NVL (LA.PURCHASING_AGENT_ID
, LA.SUGGESTED_BUYER_ID)
AND DESTLOC.LOCATION_ID (+) = DESTUNIT.LOCATION_ID
AND DESTUNIT.ORGANIZATION_ID (+) = LA.DESTINATION_ORGANIZATION_ID
AND DESTORG.ORGANIZATION_ID (+) = LA.DESTINATION_ORGANIZATION_ID
AND LA.LAST_UPDATE_DATE BETWEEN SHIPPER.EFFECTIVE_START_DATE
AND SHIPPER.EFFECTIVE_END_DATE
AND SHIPPER.PERSON_ID = LA.TO_PERSON_ID
AND SHIPLOC.LOCATION_ID = LA.DELIVER_TO_LOCATION_ID
AND UOM.UNIT_OF_MEASURE = LA.UNIT_MEAS_LOOKUP_CODE
AND CAT.CATEGORY_ID = LA.CATEGORY_ID
AND SOB.SET_OF_BOOKS_ID (+) = SOBORG.SET_OF_BOOKS_ID
AND SOBORG.ORGANIZATION_ID (+) = ITG_X_UTILS.GET_INVENTORY_ORG_ID(HA.ORG_ID)
AND HA.REQUISITION_HEADER_ID = LA.REQUISITION_HEADER_ID
AND NVL(LA.CANCEL_FLAG
, 'N') = 'N'

Columns