eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.POR_VIEW_LINE_DETAILS_V

Source

View Text - Preformatted


SELECT prl.requisition_line_id, prl.requisition_header_id, nvl(prl.item_id, -9999), msi.segment1, prl.item_revision, prl.item_description, prl.category_id, mtlc.concatenated_segments, mtlc.description, to_char(prl.need_by_date, fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')), prl.unit_meas_lookup_code, prl.unit_price, prl.unit_price, prl.unit_price, prl.quantity, prl.tax_status_indicator, plc_supp.displayed_field, prl.suggested_vendor_name, prl.suggested_vendor_product_code, prl.suggested_vendor_location, prl.suggested_vendor_contact, prl.suggested_vendor_phone, pv.segment1, prl.supplier_duns, prl.manufacturer_name, prl.manufacturer_part_number, prl.to_person_id, hre.full_name, prl.requester_email, prl.requester_phone, prl.requester_fax, prl.deliver_to_location_id, hrl.location_code, prl.destination_organization_id, ood.organization_code, plc_inv_repl.displayed_field, prl.destination_subinventory, dist.project_id, dist.project, dist.task_id, dist.task_name, dist.expenditure_type, dist.expenditure_org_id, dist.expenditure_org, to_char(dist.expenditure_item_date, fnd_profile.value_wnps('ICX_DATE_FORMAT_MASK')), prh.note_to_authorizer, prl.note_to_agent, prl.note_to_receiver, prl.note_to_vendor, to_char(prl.unit_price * (prl.quantity - nvl(prl.quantity_cancelled,0)), fnd_currency.safe_get_format_mask(sob.currency_code, 30)), prl.currency_code, prl.hazard_class_id, phc.hazard_class, prl.line_num, prl.line_type_id, plt.line_type, ph_source_doc.segment1, prl.blanket_po_line_num, prl.document_type_code, prl.unspsc_code, prl.attribute1, prl.attribute2, prl.attribute3, prl.attribute4, prl.attribute5, prl.attribute6, prl.attribute7, prl.attribute8, prl.attribute9, prl.attribute10, prl.attribute11, prl.attribute12, prl.attribute13, prl.attribute14, prl.attribute15, prl.pcard_flag, dist.concatenated_segments, prl.catalog_type, ph.segment1, plc.displayed_field, agent.full_name FROM po_requisition_headers prh, po_requisition_lines prl, mtl_system_items msi, mtl_categories_kfv mtlc, po_vendors pv, po_headers ph_source_doc, po_line_locations pll, po_headers ph, po_lookup_codes plc, hr_employees_current_v agent, po_hazard_classes phc, po_line_types plt, hr_employees_current_v hre, hr_locations hrl, gl_sets_of_books sob, po_lookup_codes plc_supp, po_lookup_codes plc_inv_repl, org_organization_definitions ood, financials_system_parameters fsp, (SELECT /*+ INDEX(prd2$PO_REQ_DISTRIBUTIONS_N1) */ prd2.requisition_line_id, decode (count(distinct nvl(prd2.code_combination_id,0)), 1, min(cc.concatenated_segments), min('MULTIPLE_VALUE')) AS concatenated_segments, min(prd2.project_id) AS project_id, min(projects.segment1) AS project, min(prd2.task_id) AS task_id, min(tasks.task_number) AS task_name, min(prd2.expenditure_type) AS expenditure_type, min(prd2.expenditure_organization_id) AS expenditure_org_id, min(orgs.name) AS expenditure_org, min(prd2.expenditure_item_date) AS expenditure_item_date FROM po_req_distributions prd2, gl_code_combinations_kfv cc, pa_projects projects, pa_tasks_expend_v tasks, pa_organizations_expend_v orgs, gl_sets_of_books sob2, financials_system_parameters fsp2 WHERE prd2.expenditure_organization_id = orgs.organization_id(+) and prd2.project_id = projects.project_id(+) and prd2.task_id = tasks.task_id(+) and cc.code_combination_id = prd2.code_combination_id and cc.chart_of_accounts_id = sob2.chart_of_accounts_id and sob2.set_of_books_id = fsp2.set_of_books_id GROUP BY prd2.requisition_line_id) dist WHERE prl.line_type_id = plt.line_type_id AND prh.requisition_header_id = prl.requisition_header_id AND prl.item_id = msi.inventory_item_id (+) AND prl.destination_organization_id = msi.organization_id (+) AND prl.category_id = mtlc.category_id AND prl.vendor_id = pv.vendor_id (+) AND prl.to_person_id = hre.employee_id AND prl.deliver_to_location_id = hrl.location_id AND prl.destination_organization_id = ood.organization_id (+) AND prl.requisition_line_id = dist.requisition_line_id AND prl.blanket_po_header_id = ph_source_doc.po_header_id(+) AND prl.line_location_id = pll.line_location_id(+) and pll.po_header_id = ph.po_header_id(+) and nvl(ph.authorization_status, 'INCOMPLETE') = plc.lookup_code(+) AND plc.lookup_type(+) = 'AUTHORIZATION STATUS' AND ph.agent_id = agent.employee_id(+) AND prl.hazard_class_id = phc.hazard_class_id(+) AND plc_supp.lookup_code = nvl(prl.new_supplier_flag, 'N') AND plc_supp.lookup_type = 'YES/NO' AND plc_inv_repl.lookup_code = decode(prl.destination_type_code, 'INVENTORY', 'Y', 'N') AND plc_inv_repl.lookup_type = 'YES/NO' AND sob.set_of_books_id = fsp.set_of_books_id

View Text - HTML Formatted

SELECT PRL.REQUISITION_LINE_ID
, PRL.REQUISITION_HEADER_ID
, NVL(PRL.ITEM_ID
, -9999)
, MSI.SEGMENT1
, PRL.ITEM_REVISION
, PRL.ITEM_DESCRIPTION
, PRL.CATEGORY_ID
, MTLC.CONCATENATED_SEGMENTS
, MTLC.DESCRIPTION
, TO_CHAR(PRL.NEED_BY_DATE
, FND_PROFILE.VALUE_WNPS('ICX_DATE_FORMAT_MASK'))
, PRL.UNIT_MEAS_LOOKUP_CODE
, PRL.UNIT_PRICE
, PRL.UNIT_PRICE
, PRL.UNIT_PRICE
, PRL.QUANTITY
, PRL.TAX_STATUS_INDICATOR
, PLC_SUPP.DISPLAYED_FIELD
, PRL.SUGGESTED_VENDOR_NAME
, PRL.SUGGESTED_VENDOR_PRODUCT_CODE
, PRL.SUGGESTED_VENDOR_LOCATION
, PRL.SUGGESTED_VENDOR_CONTACT
, PRL.SUGGESTED_VENDOR_PHONE
, PV.SEGMENT1
, PRL.SUPPLIER_DUNS
, PRL.MANUFACTURER_NAME
, PRL.MANUFACTURER_PART_NUMBER
, PRL.TO_PERSON_ID
, HRE.FULL_NAME
, PRL.REQUESTER_EMAIL
, PRL.REQUESTER_PHONE
, PRL.REQUESTER_FAX
, PRL.DELIVER_TO_LOCATION_ID
, HRL.LOCATION_CODE
, PRL.DESTINATION_ORGANIZATION_ID
, OOD.ORGANIZATION_CODE
, PLC_INV_REPL.DISPLAYED_FIELD
, PRL.DESTINATION_SUBINVENTORY
, DIST.PROJECT_ID
, DIST.PROJECT
, DIST.TASK_ID
, DIST.TASK_NAME
, DIST.EXPENDITURE_TYPE
, DIST.EXPENDITURE_ORG_ID
, DIST.EXPENDITURE_ORG
, TO_CHAR(DIST.EXPENDITURE_ITEM_DATE
, FND_PROFILE.VALUE_WNPS('ICX_DATE_FORMAT_MASK'))
, PRH.NOTE_TO_AUTHORIZER
, PRL.NOTE_TO_AGENT
, PRL.NOTE_TO_RECEIVER
, PRL.NOTE_TO_VENDOR
, TO_CHAR(PRL.UNIT_PRICE * (PRL.QUANTITY - NVL(PRL.QUANTITY_CANCELLED
, 0))
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(SOB.CURRENCY_CODE
, 30))
, PRL.CURRENCY_CODE
, PRL.HAZARD_CLASS_ID
, PHC.HAZARD_CLASS
, PRL.LINE_NUM
, PRL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PH_SOURCE_DOC.SEGMENT1
, PRL.BLANKET_PO_LINE_NUM
, PRL.DOCUMENT_TYPE_CODE
, PRL.UNSPSC_CODE
, PRL.ATTRIBUTE1
, PRL.ATTRIBUTE2
, PRL.ATTRIBUTE3
, PRL.ATTRIBUTE4
, PRL.ATTRIBUTE5
, PRL.ATTRIBUTE6
, PRL.ATTRIBUTE7
, PRL.ATTRIBUTE8
, PRL.ATTRIBUTE9
, PRL.ATTRIBUTE10
, PRL.ATTRIBUTE11
, PRL.ATTRIBUTE12
, PRL.ATTRIBUTE13
, PRL.ATTRIBUTE14
, PRL.ATTRIBUTE15
, PRL.PCARD_FLAG
, DIST.CONCATENATED_SEGMENTS
, PRL.CATALOG_TYPE
, PH.SEGMENT1
, PLC.DISPLAYED_FIELD
, AGENT.FULL_NAME
FROM PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, MTL_SYSTEM_ITEMS MSI
, MTL_CATEGORIES_KFV MTLC
, PO_VENDORS PV
, PO_HEADERS PH_SOURCE_DOC
, PO_LINE_LOCATIONS PLL
, PO_HEADERS PH
, PO_LOOKUP_CODES PLC
, HR_EMPLOYEES_CURRENT_V AGENT
, PO_HAZARD_CLASSES PHC
, PO_LINE_TYPES PLT
, HR_EMPLOYEES_CURRENT_V HRE
, HR_LOCATIONS HRL
, GL_SETS_OF_BOOKS SOB
, PO_LOOKUP_CODES PLC_SUPP
, PO_LOOKUP_CODES PLC_INV_REPL
, ORG_ORGANIZATION_DEFINITIONS OOD
, FINANCIALS_SYSTEM_PARAMETERS FSP
, (SELECT /*+ INDEX(PRD2$PO_REQ_DISTRIBUTIONS_N1) */ PRD2.REQUISITION_LINE_ID
, DECODE (COUNT(DISTINCT NVL(PRD2.CODE_COMBINATION_ID
, 0))
, 1
, MIN(CC.CONCATENATED_SEGMENTS)
, MIN('MULTIPLE_VALUE')) AS CONCATENATED_SEGMENTS
, MIN(PRD2.PROJECT_ID) AS PROJECT_ID
, MIN(PROJECTS.SEGMENT1) AS PROJECT
, MIN(PRD2.TASK_ID) AS TASK_ID
, MIN(TASKS.TASK_NUMBER) AS TASK_NAME
, MIN(PRD2.EXPENDITURE_TYPE) AS EXPENDITURE_TYPE
, MIN(PRD2.EXPENDITURE_ORGANIZATION_ID) AS EXPENDITURE_ORG_ID
, MIN(ORGS.NAME) AS EXPENDITURE_ORG
, MIN(PRD2.EXPENDITURE_ITEM_DATE) AS EXPENDITURE_ITEM_DATE
FROM PO_REQ_DISTRIBUTIONS PRD2
, GL_CODE_COMBINATIONS_KFV CC
, PA_PROJECTS PROJECTS
, PA_TASKS_EXPEND_V TASKS
, PA_ORGANIZATIONS_EXPEND_V ORGS
, GL_SETS_OF_BOOKS SOB2
, FINANCIALS_SYSTEM_PARAMETERS FSP2
WHERE PRD2.EXPENDITURE_ORGANIZATION_ID = ORGS.ORGANIZATION_ID(+)
AND PRD2.PROJECT_ID = PROJECTS.PROJECT_ID(+)
AND PRD2.TASK_ID = TASKS.TASK_ID(+)
AND CC.CODE_COMBINATION_ID = PRD2.CODE_COMBINATION_ID
AND CC.CHART_OF_ACCOUNTS_ID = SOB2.CHART_OF_ACCOUNTS_ID
AND SOB2.SET_OF_BOOKS_ID = FSP2.SET_OF_BOOKS_ID GROUP BY PRD2.REQUISITION_LINE_ID) DIST
WHERE PRL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND PRL.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND PRL.CATEGORY_ID = MTLC.CATEGORY_ID
AND PRL.VENDOR_ID = PV.VENDOR_ID (+)
AND PRL.TO_PERSON_ID = HRE.EMPLOYEE_ID
AND PRL.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID
AND PRL.DESTINATION_ORGANIZATION_ID = OOD.ORGANIZATION_ID (+)
AND PRL.REQUISITION_LINE_ID = DIST.REQUISITION_LINE_ID
AND PRL.BLANKET_PO_HEADER_ID = PH_SOURCE_DOC.PO_HEADER_ID(+)
AND PRL.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)
AND PLL.PO_HEADER_ID = PH.PO_HEADER_ID(+)
AND NVL(PH.AUTHORIZATION_STATUS
, 'INCOMPLETE') = PLC.LOOKUP_CODE(+)
AND PLC.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS'
AND PH.AGENT_ID = AGENT.EMPLOYEE_ID(+)
AND PRL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID(+)
AND PLC_SUPP.LOOKUP_CODE = NVL(PRL.NEW_SUPPLIER_FLAG
, 'N')
AND PLC_SUPP.LOOKUP_TYPE = 'YES/NO'
AND PLC_INV_REPL.LOOKUP_CODE = DECODE(PRL.DESTINATION_TYPE_CODE
, 'INVENTORY'
, 'Y'
, 'N')
AND PLC_INV_REPL.LOOKUP_TYPE = 'YES/NO'
AND SOB.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID