I have no enough background in SQL and I need to pull the following data into one report: a. Supplier Name b. PO number c. Business Unit d. Invoice Number e. Invoice Date f. Item number g. Line item type h. Description i. Quantity j. Price per unit k. Value per line item l. Requester
So far, these are all what I've written:
--1. Supplier
SELECT PARTY_NAME--, PARTY_TYPE
FROM INV_SUPPLY, POR_REQUISITION_LINES_ALL, POZ_SUPPLIERS, HZ_PARTIES
WHERE
INV_SUPPLY.SUPPLY_TYPE_CODE = 'REQ' AND
INV_SUPPLY.REQ_LINE_ID = POR_REQUISITION_LINES_ALL.REQUISITION_LINE_ID AND
POR_REQUISITION_LINES_ALL.VENDOR_ID = POZ_SUPPLIERS.VENDOR_ID AND
POZ_SUPPLIERS.PARTY_ID = HZ_PARTIES.PARTY_ID
--2. PO number
--Below SQL can be used to check PO number for a given requisition and the approval action history of the requisition.
SELECT DISTINCT PRHA.REQUISITION_NUMBER,
PHA.SEGMENT1 PO_NUMBER,
PAH.OBJECT_TYPE_CODE,
PAH.OBJECT_SUB_TYPE_CODE,
PAH.SEQUENCE_NUM,
PPTF.FULL_NAME REQUESTER,
PAH.ACTION_CODE,
PAH.ACTION_DATE
FROM
POR_REQUISITION_HEADERS_ALL PRHA,
POR_REQUISITION_LINES_ALL PRLA,
POR_REQ_DISTRIBUTIONS_ALL PRDA,
PO_HEADERS_ALL PHA,
PO_LINES_ALL PLA,
PO_DISTRIBUTIONS_ALL PDA,
PO_ACTION_HISTORY PAH ,
PER_PERSON_NAMES_F PPTF
WHERE
PRHA.REQUISITION_HEADER_ID=PRLA.REQUISITION_HEADER_ID
AND PRLA.REQUISITION_LINE_ID=PRDA.REQUISITION_LINE_ID
AND PRDA.DISTRIBUTION_ID=PDA.REQ_DISTRIBUTION_ID
AND PDA.PO_HEADER_ID=PHA.PO_HEADER_ID
AND PDA.PO_LINE_ID=PLA.PO_LINE_ID
AND PRHA.REQUISITION_HEADER_ID=PAH.OBJECT_ID
AND PPTF.PERSON_ID =PAH.PERFORMER_ID