0 Replies Latest reply on Dec 3, 2013 5:30 AM by f41ed4a8-2a70-4b0d-997f-1138848733b1

    hi all the functionality needed (only hold lines) but iam getting hold and with hold data,please help me

    f41ed4a8-2a70-4b0d-997f-1138848733b1

      SELECT      hou.NAME                     Operating_Unit_Name,

                  pv.SEGMENT1                  Supplier_Num ,

                  pv.VENDOR_NAME               Supplier_Name ,

                  pvs.VENDOR_SITE_CODE         Supplier_Site_Name ,

                  pvs.ADDRESS_LINE1            Address_Line_1 ,

                  aia.DOC_SEQUENCE_VALUE       Invoice_Sequence_Num ,

                  aia.INVOICE_NUM              Invoice_Num ,

                  aia.INVOICE_DATE             Invoice_Date ,

                  aps.DUE_DATE                 Due_Date ,

                  aha.HOLD_LOOKUP_CODE         Hold_Name ,

                  aha.HOLD_DATE                Hold_Date ,

                  aha.HOLD_REASON              Hold_Description ,

                  aha.RELEASE_LOOKUP_CODE      Hold_Release_Description ,

                  aia.DESCRIPTION              Invoice_Description ,

                  aia.INVOICE_CURRENCY_CODE    Invoice_Currency_Code ,

                  min(aia.INVOICE_AMOUNT)      Invoice_Entered_Amount_MIN ,

                  APPS.XXDBD_CURRENCY.GET_VALUE_IN_USD(pha.CURRENCY_CODE,( (aia.INVOICE_AMOUNT) ),pha.CREATION_DATE)  INVOICE_AMOUNT_IN_USD ,

                  min(aia.BASE_AMOUNT)         Invoice_Functional_Amount_MIN ,

                  pha.SEGMENT1                 PO_Document_Number ,

                  pla.LINE_NUM                 PO_Line_Num ,

                  pda.QUANTITY_ORDERED         PO_Ordered_Quantity ,

                  pll.QUANTITY_RECEIVED        Qty_Received_to_Date ,

                  pda.QUANTITY_BILLED          Billed_Quantity_to_Date ,

                  msi.segment1                 Item_Number ,

                  pla.ITEM_DESCRIPTION         Item_Description ,

                  ROUND(pla.UNIT_PRICE,2)      PO_Unit_Price ,

                  ROUND(aid.UNIT_PRICE,2)      Unit_Price_on_Invoice ,

                  aid.AMOUNT                   Invoice_Distribution_Amount ,

                  aha.RELEASE_REASON           Hold_Release_Reason ,

                  ppf.FULL_NAME                Buyer_Or_Requester_Name ,

                  aid.INVOICE_PRICE_VARIANCE   Price_Variance ,

                  APPS.XXDBD_CURRENCY.GET_VALUE_IN_USD(pha.CURRENCY_CODE,( SUM(aid.INVOICE_PRICE_VARIANCE) ),pha.CREATION_DATE)  Price_Variance_in_Usd ,           

                  aid.QUANTITY_INVOICED        Invoice_Matched_Quantity ,

                  SUM(aia.BASE_AMOUNT)         Invoice_Functional_Amount_SUM           

      FROM        ap_invoices_all              aia,

                  ap_invoice_distributions_all aid,

                  po_headers_all               pha,

                  po_lines_all                 pla,

                  po_line_locations_all        pll,

                  po_distributions_all         pda,

                  ap_holds_all                 aha,

                  hr_all_organization_units    hou,

                  po_vendors                   pv,

                  po_vendor_sites_all          pvs,

                  ap_payment_schedules_all     aps,

                  per_people_f                 ppf,

                  mtl_system_items             msi           

      WHERE       pv.VENDOR_ID            =pha.VENDOR_ID

      AND         pv.VENDOR_ID            =pvs.VENDOR_ID

      AND         aps.INVOICE_ID          =aia.INVOICE_ID

      AND         aia.INVOICE_ID          =aid.INVOICE_ID

      AND         aha.INVOICE_ID          =aia.INVOICE_ID

      AND         aha.LINE_LOCATION_ID    =pda.LINE_LOCATION_ID

      AND         pha.PO_HEADER_ID        =pla.PO_HEADER_ID

      AND         pla.PO_LINE_ID          =pll.PO_LINE_ID

      AND         pll.LINE_LOCATION_ID    =pda.LINE_LOCATION_ID

      AND         pda.PO_DISTRIBUTION_ID  =aid.PO_DISTRIBUTION_ID

      AND         pha.VENDOR_SITE_ID      =pvs.VENDOR_SITE_ID

      AND         pha.VENDOR_ID           =pv.VENDOR_ID

      AND         pv.VENDOR_ID            =aia.VENDOR_ID

      AND         pvs.ORG_ID              =hou.ORGANIZATION_ID

      AND         pvs.ORG_ID              =pda.ORG_ID

      AND         ppf.PERSON_ID           =pha.AGENT_ID

      AND         pla.ITEM_ID             =msi.INVENTORY_ITEM_ID

      AND         pll.SHIP_TO_ORGANIZATION_ID     =msi.ORGANIZATION_ID

      AND         aha.RELEASE_REASON   IS NULL

      AND         aha.RELEASE_LOOKUP_CODE IS  NULL

      and         hou.name ='DEL Operating Unit'

      GROUP BY    hou.NAME               ,

                  pv.SEGMENT1            ,

                  pv.VENDOR_NAME         ,

                  pvs.VENDOR_SITE_CODE   ,

                  pvs.ADDRESS_LINE1      ,

                  aia.DOC_SEQUENCE_VALUE ,

                  aia.INVOICE_NUM        ,

                  aia.INVOICE_DATE       ,

                  aps.DUE_DATE           ,

                  aha.HOLD_LOOKUP_CODE   ,

                  aha.HOLD_DATE          ,

                  aha.HOLD_REASON        ,

                  aha.RELEASE_REASON     ,

                  aia.DESCRIPTION        ,

                  aia.INVOICE_CURRENCY_CODE,

                  aia.INVOICE_AMOUNT  ,

                  aia.BASE_AMOUNT     ,

                  aia.BASE_AMOUNT     ,

                  pha.SEGMENT1        ,

                  pha.CURRENCY_CODE   ,

                  pha.CREATION_DATE   ,

                  pla.LINE_NUM        ,

                  pda.QUANTITY_ORDERED,

                  pll.QUANTITY_RECEIVED,

                  pda.QUANTITY_BILLED  ,

                  msi.segment1         ,

                  pla.ITEM_DESCRIPTION ,

                  pla.UNIT_PRICE       ,

                  aid.UNIT_PRICE       ,

                  aid.AMOUNT           ,

                  aha.RELEASE_LOOKUP_CODE,

                  ppf.FULL_NAME          ,

                  aid.INVOICE_PRICE_VARIANCE,

                  aid.QUANTITY_INVOICED

      ORDER BY    aia.invoice_num;