Monday, 25 February 2019

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')
    */

1 comment: