All approved requisition report for the month including PO

SELECT R.SEGMENT1
“Req number”,
  Rl.Line_Num “Req
line”,
  rl.suggested_vendor_product_code
supplier_item,
  (SELECT DISTINCT Description
  FROM Apps.Mtl_Categories
  WHERE
Category_Id=Rl.Category_Id
  ) “Req Line Category
Description”,
  (SELECT DISTINCT segment1
    ||’.’
    ||segment2
  FROM Apps.Mtl_Categories
  WHERE
Category_Id=rl.category_id
  ) “Requisition Line Category”
,
  TO_CHAR(R.APPROVED_DATE,’dd-MON-RR
HH24:MI:SS’) “Req Approved Date”,
  P.SEGMENT1 “PO
Number”,
  p.revision_num “Revision
number”,
  PL.LINE_NUM “PO
Line”, 
  TO_CHAR(P.CREATION_DATE,’DD-MON-RRRR
HH24:MI:SS’) “PO Created Date”,
  pagen.agent_name “PO
buyer”,
  p.attribute10 “Contract
Type”,
  p.org_id org_id,
  (SELECT NVL(short_code,name)
  FROM apps.hr_operating_units
  WHERE organization_id =
p.org_id
  ) org_code,
  aps.vendor_name
FROM APPS.PO_HEADERS_ALL P,
  apps.po_lines_all pl,
  APPS.PO_DISTRIBUTIONS_ALL D,
  apps.po_agents_v pagen,
  apps.po_req_distributions_all
rd,
  apps.po_requisition_lines_all
rl,
  apps.po_requisition_headers_all
r,
  apps.ap_suppliers aps
WHERE P.PO_HEADER_ID         =
D.PO_HEADER_ID
AND P.PO_HEADER_ID           =
PL.PO_HEADER_ID
AND PL.PO_LINE_ID            =
D.PO_LINE_ID
AND pagen.agent_id           =
p.agent_id
and aps.vendor_id            =
p.vendor_id
AND d.req_distribution_id    =
rd.distribution_id
AND rd.requisition_line_id   =
rl.requisition_line_id
AND RL.REQUISITION_HEADER_ID = R.REQUISITION_HEADER_ID
AND P.Org_Id                IN
(82,83, 367, 370, 627, 628, 629, 388, 630, 395, 376, 396, 382,393,378 )
AND TRUNC(R.APPROVED_DATE) 
>=’01-NOV-2018′
AND TRUNC(R.APPROVED_DATE) 
<=’30-NOV-2018′
/*ORDER BY org_id,
   
TO_CHAR(R.APPROVED_DATE,’DD-MON-RRRR HH24:MI:SS’)*/
      UNION   
    SELECT R.SEGMENT1 “Req
number”,
  Rl.Line_Num “Req
line”,
  rl.suggested_vendor_product_code
supplier_item,
  (SELECT DISTINCT Description
  FROM Apps.Mtl_Categories
  WHERE
Category_Id=Rl.Category_Id
  ) “Req Line Category
Description”,
  (SELECT DISTINCT segment1
    ||’.’
    ||segment2
  FROM Apps.Mtl_Categories
  WHERE
Category_Id=rl.category_id
  ) “Requisition Line
Category” ,
  TO_CHAR(R.APPROVED_DATE,’dd-MON-RR
HH24:MI:SS’) “Req Approved Date”,
  NULL “PO Number”,
  NULL “PO Revision
number”,
  NULL “PO Line”,
  NULL “PO Created
Date”,
  NULL “PO buyer”,
  NULL “Contract
Type”,
  r.org_id org_id,
  (SELECT NVL(short_code,name)
  FROM apps.hr_operating_units
  WHERE organization_id =
r.org_id
  ) org_code,
  aps.vendor_name
FROM  apps.po_requisition_headers_all
r,
      apps.po_requisition_lines_all
rl,
      apps.po_req_distributions_all
rd
    , apps.ap_suppliers aps 
WHERE 1=1
AND RL.REQUISITION_HEADER_ID = R.REQUISITION_HEADER_ID
AND rd.requisition_line_id   =
rl.requisition_line_id
and rl.vendor_id = aps.vendor_id
AND Not exists ( select req_distribution_id
     from
apps.po_distributions_all
     where req_distribution_id =
rd.distribution_id)
AND r.Org_Id                IN
(82,83, 367, 370, 627, 628, 629, 388, 630, 395, 376, 396, 382,393,378 )
AND TRUNC(R.APPROVED_DATE) 
>=’01-NOV-2018′
AND TRUNC(R.APPROVED_DATE) 
<=’30-NOV-2018′
/*ORDER BY –org_id,
   
TO_CHAR(R.APPROVED_DATE,’DD-MON-RRRR HH24:MI:SS’)
    */
  • February 25, 2019 | 19 views
  • Comments