Hi Friends,
I had mentioned in my last
published post that my next post would be about requisitions, against which no
Purchase Orders are not entered. So here it is,
Our base here is
requisition so we will start with a sample record with MR# 'AB1234' which is
SEGMENT1 and 666765 be the REQUISITION_HEADER_ID of PO_REQUISITION_HEADERS_ALL.
We will make sure that
there is no PO entered against this requisition by checking through the
following query where we check entry in PO_DISTRIBUTIONS_ALL against the said
requisition’s distribution records
The only catch here in
this case is making sure that we don’t have any entry against the said
requisition in Purchase order’s tables. We, will make it sure through the
following query
SELECT
COUNT(REQ_DISTRIBUTION_ID)
FROM
PO_DISTRIBUTIONS_ALL PDA
WHERE
REQ_DISTRIBUTION_ID IN (SELECT DISTINCT PRDA.DISTRIBUTION_ID
FROM PO_REQ_DISTRIBUTIONS_ALL PRDA
, PO_REQUISITION_LINES_ALL PRLA
WHERE
PRLA.REQUISITION_HEADER_ID = 666765
AND PRLA.REQUISITION_LINE_ID =
PRDA.REQUISITION_LINE_ID)
We must get the result
of above query as 0 in order to get to the correct sample record.
Now, to get the requisition details of the
sample record, we join all three headers, lines and distribution level tables
in the query. Our query will be
SELECT
*
FROM
PO_REQUISITION_HEADERS_ALL PRHA
,
PO_ REQUISITION_LINES_ALL PRLA
,
PO_REQ_DISTRIBUTIONS_ALL PRDA
WHERE
PRHA. REQUISITION_HEADER_ID = 666765
AND
PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND
PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
AND
PRDA. DISTRIBUTION_ID NOT IN SELECT COUNT(REQ_DISTRIBUTION_ID)
FROM
PO_DISTRIBUTIONS_ALL PDA
WHERE
REQ_DISTRIBUTION_ID IN (SELECT DISTINCT REQ_DISTRIBUTION_ID
FROM
PO_DISTRIBUTIONS_ALL PDA
WHERE
REQ_DISTRIBUTION_ID IS NOT NULL);
Now the last step is
adding the columns to get the information against requisition without PO.
SELECT
PRHA.SEGMENT1 REQUISITION_NUMBER
,
PRLA.LINE_NUM,
PRLA.ITEM_DESCRIPTION
MATERIAL_DESCRIPTION,
PRLA.UNIT_MEAS_LOOKUP_CODE UOM
,
PRLA.QUANTITY PR_QUANTITY,
, PRLA.QUANTITY_RECEIVED
QUANTITY_RECEIVED,
PRLA.QUANTITY_DELIVERED
QUANTITY_DELIVERED
,
UPPER(PRHA.AUTHORIZATION_STATUS) MR_STATUS
, CASE
WHEN PRLA.QUANTITY = NVL(PRLA.QUANTITY_DELIVERED,0)
THEN 'DELIVERED'
WHEN PRLA.QUANTITY <
NVL(PRLA.QUANTITY_DELIVERED,0) THEN 'EXTRA DELIVERED'
WHEN PRLA.QUANTITY >
NVL(PRLA.QUANTITY_DELIVERED,0) THEN 'NOT DELIVERED'
ELSE NULL
END
MATERIAL_STATUS
, PRLA.DESTINATION_ORGANIZATION_ID
,
PRLA.ITEM_ID
, PRHA.REQUISITION_HEADER_ID
,
(SELECT GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7
FROM
GL_CODE_COMBINATIONS GCC
WHERE
GCC.CODE_COMBINATION_ID = PRDA.CODE_COMBINATION_ID
)
BUDGET_NO,
(
SELECT
VENDOR_NAME
FROM
AP_SUPPLIERS
WHERE
VENDOR_ID = PRLA.VENDOR_ID
)
SUPPLIER_NAME
FROM
PO_REQUISITION_HEADERS_ALL PRHA
,
PO_ REQUISITION_LINES_ALL PRLA
,
PO_REQ_DISTRIBUTIONS_ALL PRDA
WHERE
PRHA. REQUISITION_HEADER_ID = 666765
AND
PRHA.REQUISITION_HEADER_ID = PRLA.REQUISITION_HEADER_ID
AND
PRLA.REQUISITION_LINE_ID = PRDA.REQUISITION_LINE_ID
AND
PRDA. DISTRIBUTION_ID NOT IN SELECT COUNT(REQ_DISTRIBUTION_ID)
FROM
PO_DISTRIBUTIONS_ALL PDA
WHERE
REQ_DISTRIBUTION_ID IN (SELECT DISTINCT REQ_DISTRIBUTION_ID
FROM
PO_DISTRIBUTIONS_ALL PDA
WHERE
REQ_DISTRIBUTION_ID IS NOT NULL);
So this is all about
getting to requisitions without PO, we can get all requisition without PO by
removing the check PRHA. REQUISITION_HEADER_ID = 666765.
Feel free to catch me
for any related queries. Thank You,
Stay blessed J
No comments:
Post a Comment