2 Replies Latest reply: Apr 24, 2012 4:55 AM by hm RSS

    p2p cycle query -oracle apps

    927274
      Hi...
      I am using oracle 10g and was trying to link the base tables of p2p cycle and need help on my query:

      select prla.unit_meas_lookup_code "UNIT",
      prha.type_lookup_code"PR TYPE",
      prha.segment1"PR NUMBER",
      prha.creation_date,null"SUPPLIER",
      null"INVENTORY CATEGORY",
      null"MFG NO",
      null"BUYER",
      null"CUSTOMER",
      prla.item_id"ITEM CODE",
      apia.invoice_num"INVOICE NO" ,
      prla.item_description "DESCRIPTION",
      null"RFQ No",
      null "RFQ Date",
      null"RFQ Supplier",
      null "QTN No",
      null "QTN Date",
      pha.segment1"PO NO",
      pha.vendor_id"SUPPLIER",
      pha.CREATION_DATE "PO DATE",
      null"ACK.DATE",
      null"NO OF DAYS FROM PR TO PO",
      null "ASBN NO",
      rsh.receipt_num "RECEIPT NO",
      null"RECEIPT DATE",
      rsh.invoice_num"INVOICE NO",
      null"INSPECTION DATE",
      null "INSPECTION RESULT",
      null"STOCKING DATE",
      null "NO OF DAYS FROM PO TO RECEIPT",
      null"PV NO",apca.amount "RELEASE AMOUNT",
      apca.released_date "PAYMENT RELEASE DATE",
      apca.payment_method_code " RELEASE MODE",
      null " DAYS TAKEN "

      from PO_REQUISITION_HEADERS_ALL prha,
      PO_REQUISITION_LINES_ALL prla,
      PO_HEADERS_ALL pha,
      RCV_SHIPMENT_HEADERS rsh,
      AP_INVOICE_DISTRIBUTIONS_ALL apida,
      AP_INVOICES_ALL apia,
      PO_DISTRIBUTIONS_ALL pda,
      PO_REQ_DISTRIBUTIONS_ALL prda,
      RCV_SHIPMENT_LINES rsl,
      AP_CHECKS_ALL apca,
      AP_INVOICE_PAYMENTS_ALL apipa

      where prha.REQUISITION_HEADER_ID = prla.REQUISITION_HEADER_ID
      AND prla.REQUISITION_LINE_ID = prda.REQUISITION_LINE_ID
      AND prda.DISTRIBUTION_NUM = pda.DISTRIBUTION_NUM
      AND pda.PO_HEADER_ID = pha.PO_HEADER_ID
      AND pda.PO_DISTRIBUTION_ID = rsl.PO_DISTRIBUTION_ID
      AND rsl.SHIPMENT_HEADER_ID = rsh.SHIPMENT_HEADER_ID
      AND pda.PO_DISTRIBUTION_ID = apida.PO_DISTRIBUTION_ID
      AND apida.INVOICE_ID = apipa.INVOICE_ID
      AND apipa.CHECK_ID = apca.CHECK_ID


      Cant figure out what I have missed.Please help!!!