This discussion is archived
0 Replies Latest reply: Dec 2, 2013 9:30 PM by f41ed4a8-2a70-4b0d-997f-1138848733b1 RSS

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

f41ed4a8-2a70-4b0d-997f-1138848733b1 Newbie
Currently Being Moderated

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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points