Saturday, November 9, 2013

To Get QUOTATION NUMBER, QUOTATION TYPE, SUPPLIER NAME, SUPPLIER SITE,SUPPLIER CONTACT,SHIP TO LOCATION,BILL TO LOCATION, BUYER, ITEM, ITEM DESCRIPTION,QUANTITY, PRICE, TOTAL AMOUNT



SELECT
PHA.SEGMENT1 "QUOTATION NUMBER",
PHA.QUOTATION_CLASS_CODE "QUOTATION TYPE",
ASS.VENDOR_NAME "SUPPLIER NAME",
ASSA.VENDOR_SITE_CODE "SUPPLIER SITE",
ASCA.FIRST_NAME||ASCA.MIDDLE_NAME||ASCA.LAST_NAME"SUPPLIER CONTACT",
HR1.LOCATION_CODE "SHIP TO LOC",
HR2.LOCATION_CODE "BILL TO LOC",
PPF.FULL_NAME "BUYER",
MSI.SEGMENT1 "ITEM",
MSI.DESCRIPTION "ITEM DESCRIPTION",
PLA.QUANTITY "QUANTITY",
PLA.UNIT_PRICE "PRICE",
PLA.QUANTITY*PLA.UNIT_PRICE "TOTAL AMOUNT"

FROM
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PER_ALL_PEOPLE_F PPF,
AP_SUPPLIERS ASS,
AP_SUPPLIER_SITES_ALL ASSA,
AP_SUPPLIER_CONTACTS ASCA,
HR_LOCATIONS_ALL HR1,
HR_LOCATIONS_ALL HR2,
MTL_SYSTEM_ITEMS_B MSI

WHERE 1=1
AND PHA.PO_HEADER_ID = PLA.PO_HEADER_ID
AND PHA.AGENT_ID = PPF.PERSON_ID
AND ASS.VENDOR_ID = PHA.VENDOR_ID
AND ASSA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND ASCA.VENDOR_CONTACT_ID = PHA.VENDOR_CONTACT_ID
AND ASCA.VENDOR_SITE_ID = PHA.VENDOR_SITE_ID
AND HR1.LOCATION_ID = PHA.SHIP_TO_LOCATION_ID
AND HR2.LOCATION_ID = PHA.BILL_TO_LOCATION_ID
AND MSI.INVENTORY_ITEM_ID = PLA.ITEM_ID

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect