VIEW: APPS.POA_BIS_SAVINGS_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'