[Home] [Help]
| Product: | PO - Purchasing |
|---|---|
| Description: | This view resolves the foreign key references in POA_BIS_SAVINGS table. Please refer to the base table for further information. |
| Implementation/DBA Data: | Not implemented in this database |
SELECT POA.PURCHASE_AMOUNT
, POA.CONTRACT_AMOUNT
, POA.NON_CONTRACT_AMOUNT
, POA.POT_CONTRACT_AMOUNT
, POA.POTENTIAL_SAVING
, DECODE(LEAST(POA.POTENTIAL_SAVING
, 0)
, 0
, POA.POTENTIAL_SAVING
, 0)
, DECODE(GREATEST(POA.POTENTIAL_SAVING
, 0)
, 0
, POA.POTENTIAL_SAVING
, 0)
, POA.TOTAL_PURCHASE_QTY
, POA.DISTRIBUTION_TRANSACTION_ID
, HDR.SEGMENT1
, POA.DOCUMENT_TYPE_CODE
, POA.PURCHASE_CREATION_DATE
, POA.ITEM_ID
, ITM.CONCATENATED_SEGMENTS || DECODE(ITM.CONCATENATED_SEGMENTS
, NULL
, ''
, ' - ') || POA.ITEM_DESCRIPTION
, POA.CATEGORY_ID
, CAT.CONCATENATED_SEGMENTS
, POA.SUPPLIER_SITE_ID
, SITE.VENDOR_SITE_CODE
, POA.SUPPLIER_ID
, SUPP.VENDOR_NAME
, POA.REQUESTOR_ID
, POA.SHIP_TO_LOCATION_ID
, POA.SHIP_TO_ORGANIZATION_ID
, ORG.NAME
, POA.OPERATING_UNIT_ID
, OPERU.NAME
, POA.BUYER_ID
, AGENT.FULL_NAME
, POA.PROJECT_ID
, POA.TASK_ID
, POA.CURRENCY_CODE
, POA.RATE_TYPE
, POA.RATE_DATE
, POA.COST_CENTER_ID
, POA.ACCOUNT_ID
, POA.COMPANY_ID
, POA.CREATED_BY
, POA.CREATION_DATE
, POA.LAST_UPDATED_BY
, POA.LAST_UPDATE_DATE
, POA.LAST_UPDATE_LOGIN
, POA.REQUEST_ID
, POA.PROGRAM_APPLICATION_ID
, POA.PROGRAM_ID
, POA.PROGRAM_UPDATE_DATE
FROM MTL_SYSTEM_ITEMS_KFV ITM
, MTL_CATEGORIES_KFV CAT
, PO_VENDOR_SITES_ALL SITE
, PO_VENDORS SUPP
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_ALL_ORGANIZATION_UNITS OPERU
, PER_ALL_PEOPLE_F AGENT
, PO_DISTRIBUTIONS_ALL DIST
, PO_HEADERS_ALL HDR
, POA_BIS_SAVINGS POA
WHERE POA.ITEM_ID = ITM.INVENTORY_ITEM_ID (+)
AND POA.SHIP_TO_ORGANIZATION_ID = ITM.ORGANIZATION_ID (+)
AND POA.CATEGORY_ID = CAT.CATEGORY_ID
AND POA.SUPPLIER_SITE_ID = SITE.VENDOR_SITE_ID (+)
AND POA.OPERATING_UNIT_ID = SITE.ORG_ID (+)
AND POA.SUPPLIER_ID = SUPP.VENDOR_ID
AND POA.SHIP_TO_ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND POA.OPERATING_UNIT_ID = OPERU.ORGANIZATION_ID (+)
AND POA.BUYER_ID = AGENT.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN AGENT.EFFECTIVE_START_DATE
AND AGENT.EFFECTIVE_END_DATE
AND POA.DISTRIBUTION_TRANSACTION_ID = DIST.PO_DISTRIBUTION_ID
AND DIST.PO_HEADER_ID = HDR.PO_HEADER_ID
AND NVL(DIST.DISTRIBUTION_TYPE
, '-99') <> 'AGREEMENT'