VIEW: APPS.ITG_REQ_LINES_V

Source

View Text - Preformatted

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, NULL to_po_internal, NULL to_misc, NULL to_approver, NULL to_buyer, NULL to_payables, NULL to_receiver, NULL 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'

View Text - HTML Formatted

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
, NULL TO_PO_INTERNAL
, NULL TO_MISC
, NULL TO_APPROVER
, NULL TO_BUYER
, NULL TO_PAYABLES
, NULL TO_RECEIVER
, NULL 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'