Solved

I am getting ORA-24344: success with compilation error

Posted on 2013-02-04
5
1,804 Views
Last Modified: 2013-02-05
but same view i can create in APPS, specifing apps.view name

DROP VIEW BOLINF.TPCO_PO_LINES_PRINT;

/* Formatted on 2/4/2013 10:38:29 AM (QP5 v5.149.1003.31008) */
CREATE OR REPLACE FORCE VIEW BOLINF.TPCO_PO_LINES_PRINT
(
   SHIP_TO_LOCATION_ID,
   REVISION_NUM,
   LINE_NUM,
   ITEM_DESCRIPTION,
   CANCEL_FLAG,
   CANCEL_DATE,
   CANCEL_REASON,
   VENDOR_PRODUCT_NUM,
   NOTE_TO_VENDOR,
   PRICE_TO_PRINT,
   QUANTITY_TO_PRINT,
   AMOUNT_TO_PRINT,
   UNIT_OF_MEASURE,
   UN_NUMBER_AND_DESC,
   HAZARD_CLASS,
   LINE_TYPE,
   CONTRACT_NUM,
   VENDOR_QUOTE_NUM,
   QUOTATION_LINE,
   ATTRIBUTE_CATEGORY,
   ATTRIBUTE1,
   ATTRIBUTE2,
   ATTRIBUTE3,
   ATTRIBUTE4,
   ATTRIBUTE5,
   ATTRIBUTE6,
   ATTRIBUTE7,
   ATTRIBUTE8,
   ATTRIBUTE9,
   ATTRIBUTE10,
   ATTRIBUTE11,
   ATTRIBUTE12,
   ATTRIBUTE13,
   ATTRIBUTE14,
   ATTRIBUTE15,
   PRICE,
   QUANTITY,
   QUANTITY_COMITTED,
   PO_HEADER_ID,
   PO_LINE_ID,
   PO_RELEASE_ID,
   PO_ITEM_ID,
   PO_QUOTE_NUM,
   SRC_GA_FLAG,
   FROM_HEADER_ID,
   FROM_LINE_ID
)
AS
   SELECT PLL.SHIP_TO_LOCATION_ID,
          PL1.ITEM_REVISION,
          PL1.LINE_NUM,
          DECODE (
             NVL (MSI.ALLOW_ITEM_DESC_UPDATE_FLAG, 'Y'),
             'Y', PL1.ITEM_DESCRIPTION,
             DECODE (PL1.ORDER_TYPE_LOOKUP_CODE,
                     'QUANTITY', MSIT.DESCRIPTION,
                     PL1.ITEM_DESCRIPTION))
             ITEM_DESCRIPTION,
          NVL (PL1.CANCEL_FLAG, 'N'),
          PL1.CANCEL_DATE,
          PL1.CANCEL_REASON,
          PL1.VENDOR_PRODUCT_NUM,
          PL1.NOTE_TO_VENDOR,
          DECODE (PLT.ORDER_TYPE_LOOKUP_CODE,
                  'AMOUNT', TO_NUMBER (NULL),
                  PL1.UNIT_PRICE),
          DECODE (
             PLT.ORDER_TYPE_LOOKUP_CODE,
             'QUANTITY', DECODE (PH1.TYPE_LOOKUP_CODE,
                                 'STANDARD', PL1.QUANTITY,
                                 'PLANNED', PL1.QUANTITY,
                                 NULL),
             NULL),
          DECODE (
             PLT.ORDER_TYPE_LOOKUP_CODE,
             'QUANTITY', DECODE (PH1.TYPE_LOOKUP_CODE,
                                 'STANDARD', PL1.UNIT_PRICE * PL1.QUANTITY,
                                 'PLANNED', PL1.UNIT_PRICE * PL1.QUANTITY,
                                 TO_NUMBER (NULL)),
             'AMOUNT', DECODE (PH1.TYPE_LOOKUP_CODE,
                               'STANDARD', PL1.UNIT_PRICE * PL1.QUANTITY,
                               'PLANNED', PL1.UNIT_PRICE * PL1.QUANTITY,
                               TO_NUMBER (NULL)),
             'RATE', DECODE (PH1.type_lookup_code,
                             'STANDARD', PL1.amount,
                             TO_NUMBER (NULL)),
             'FIXED PRICE', DECODE (PH1.type_lookup_code,
                                    'STANDARD', PL1.amount,
                                    TO_NUMBER (NULL)),
             TO_NUMBER (NULL)),
          NVL (MUM.UNIT_OF_MEASURE_TL, PL1.UNIT_MEAS_LOOKUP_CODE)
             UNIT_MEAS_LOOKUP_CODE,
          PUN.UN_NUMBER || ' ' || PUN.DESCRIPTION,
          PHC.HAZARD_CLASS,
          PLT.ORDER_TYPE_LOOKUP_CODE,
          PO_COMMUNICATION_PVT.getSegmentNum (PL1.CONTRACT_ID),
          PH2.QUOTE_VENDOR_QUOTE_NUMBER,
          PL2.LINE_NUM,
          PL1.ATTRIBUTE_CATEGORY,
          PL1.ATTRIBUTE1,
          PL1.ATTRIBUTE2,
          PL1.ATTRIBUTE3,
          PL1.ATTRIBUTE4,
          PL1.ATTRIBUTE5,
          PL1.ATTRIBUTE6,
          PL1.ATTRIBUTE7,
          PL1.ATTRIBUTE8,
          PL1.ATTRIBUTE9,
          PL1.ATTRIBUTE10,
          PL1.ATTRIBUTE11,
          PL1.ATTRIBUTE12,
          PL1.ATTRIBUTE13,
          PL1.ATTRIBUTE14,
          PL1.ATTRIBUTE15,
          PL1.UNIT_PRICE,
          PL1.QUANTITY,
          PL1.QUANTITY_COMMITTED,
          PL1.PO_HEADER_ID,
          PL1.PO_LINE_ID,
          TO_NUMBER (NULL),
          PL1.ITEM_ID,
          PH2.SEGMENT1,
          PH2.GLOBAL_AGREEMENT_FLAG,
          PL1.FROM_HEADER_ID,
          PL1.FROM_LINE_ID
     FROM PO_LINE_TYPES PLT,
          PO_LINE_LOCATIONS PLL,
          PO_HEADERS_ALL PH2,
          PO_LINES_ALL PL2,
          PO_HEADERS PH1,
          PO_LINES PL1,
          PO_UN_NUMBERS PUN,
          PO_HAZARD_CLASSES PHC,
          MTL_UNITS_OF_MEASURE MUM,
          FINANCIALS_SYSTEM_PARAMS_ALL FSP,
          MTL_SYSTEM_ITEMS_KFV MSI,
          MTL_SYSTEM_ITEMS_TL MSIT
    WHERE     PL1.LINE_TYPE_ID = PLT.LINE_TYPE_ID
          AND PLL.PO_LINE_ID = PL1.PO_LINE_ID
          AND PLL.PO_HEADER_ID = PH1.PO_HEADER_ID
          AND PL1.PO_HEADER_ID = PH1.PO_HEADER_ID
          AND PH1.TYPE_LOOKUP_CODE IN ('STANDARD', 'PLANNED')
          AND PL1.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID(+)
          AND PL1.UN_NUMBER_ID = PUN.UN_NUMBER_ID(+)
          AND PL2.PO_HEADER_ID = PH2.PO_HEADER_ID(+)
          AND PL1.FROM_LINE_ID = PL2.PO_LINE_ID(+)
          AND PL1.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
          AND NVL (PL1.ORG_ID, -99) = NVL (FSP.ORG_ID, -99)
          AND PL1.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
          AND NVL (MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND PL1.ITEM_ID = MSIT.INVENTORY_ITEM_ID(+)
          AND NVL (MSIT.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND MSIT.LANGUAGE(+) = USERENV ('LANG')
   UNION
   SELECT PLL.SHIP_TO_LOCATION_ID,
          PL1.ITEM_REVISION,
          PL1.LINE_NUM,
          DECODE (
             NVL (MSI.ALLOW_ITEM_DESC_UPDATE_FLAG, 'Y'),
             'Y', PL1.ITEM_DESCRIPTION,
             DECODE (PL1.ORDER_TYPE_LOOKUP_CODE,
                     'QUANTITY', MSIT.DESCRIPTION,
                     PL1.ITEM_DESCRIPTION))
             ITEM_DESCRIPTION,
          NVL (PL1.CANCEL_FLAG, 'N'),
          PL1.CANCEL_DATE,
          PL1.CANCEL_REASON,
          PL1.VENDOR_PRODUCT_NUM,
          PL1.NOTE_TO_VENDOR,
          DECODE (PLT.ORDER_TYPE_LOOKUP_CODE,
                  'AMOUNT', TO_NUMBER (NULL),
                  PL1.UNIT_PRICE),
          DECODE (
             PLT.ORDER_TYPE_LOOKUP_CODE,
             'QUANTITY', DECODE (PH1.TYPE_LOOKUP_CODE,
                                 'STANDARD', PL1.QUANTITY,
                                 'PLANNED', PL1.QUANTITY,
                                 NULL),
             NULL),
          DECODE (
             PLT.ORDER_TYPE_LOOKUP_CODE,
             'QUANTITY', DECODE (PH1.TYPE_LOOKUP_CODE,
                                 'STANDARD', PL1.UNIT_PRICE * PL1.QUANTITY,
                                 'PLANNED', PL1.UNIT_PRICE * PL1.QUANTITY,
                                 TO_NUMBER (NULL)),
             'AMOUNT', DECODE (PH1.TYPE_LOOKUP_CODE,
                               'STANDARD', PL1.UNIT_PRICE * PL1.QUANTITY,
                               'PLANNED', PL1.UNIT_PRICE * PL1.QUANTITY,
                               TO_NUMBER (NULL)),
             TO_NUMBER (NULL)),
          NVL (MUM.UNIT_OF_MEASURE_TL, PL1.UNIT_MEAS_LOOKUP_CODE)
             UNIT_MEAS_LOOKUP_CODE,
          PUN.UN_NUMBER || ' ' || PUN.DESCRIPTION,
          PHC.HAZARD_CLASS,
          PLT.ORDER_TYPE_LOOKUP_CODE,
          PL1.CONTRACT_NUM,
          PH2.QUOTE_VENDOR_QUOTE_NUMBER,
          PL2.LINE_NUM,
          PL1.ATTRIBUTE_CATEGORY,
          PL1.ATTRIBUTE1,
          PL1.ATTRIBUTE2,
          PL1.ATTRIBUTE3,
          PL1.ATTRIBUTE4,
          PL1.ATTRIBUTE5,
          PL1.ATTRIBUTE6,
          PL1.ATTRIBUTE7,
          PL1.ATTRIBUTE8,
          PL1.ATTRIBUTE9,
          PL1.ATTRIBUTE10,
          PL1.ATTRIBUTE11,
          PL1.ATTRIBUTE12,
          PL1.ATTRIBUTE13,
          PL1.ATTRIBUTE14,
          PL1.ATTRIBUTE15,
          PL1.UNIT_PRICE,
          PL1.QUANTITY,
          PL1.QUANTITY_COMMITTED,
          PL1.PO_HEADER_ID,
          PL1.PO_LINE_ID,
          PR.PO_RELEASE_ID,
          PL1.ITEM_ID,
          PH2.SEGMENT1,
          PH2.GLOBAL_AGREEMENT_FLAG,
          PL1.FROM_HEADER_ID,
          PL1.FROM_LINE_ID
     FROM PO_RELEASES PR,
          PO_LINE_LOCATIONS PLL,
          PO_LINE_TYPES PLT,
          PO_HEADERS_ALL PH2,
          PO_LINES_ALL PL2,
          PO_HEADERS PH1,
          PO_LINES PL1,
          PO_UN_NUMBERS PUN,
          PO_HAZARD_CLASSES PHC,
          MTL_UNITS_OF_MEASURE MUM,
          FINANCIALS_SYSTEM_PARAMS_ALL FSP,
          MTL_SYSTEM_ITEMS_KFV MSI,
          MTL_SYSTEM_ITEMS_TL MSIT
    WHERE     PL1.LINE_TYPE_ID = PLT.LINE_TYPE_ID
          -- AND PH1.SHIP_TO_LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
          AND PL1.PO_HEADER_ID = PH1.PO_HEADER_ID
          AND PH1.TYPE_LOOKUP_CODE IN ('STANDARD', 'PLANNED')
          AND PH1.PO_HEADER_ID = PR.PO_HEADER_ID
          AND PLL.PO_LINE_ID = PL1.PO_LINE_ID
          AND PLL.PO_HEADER_ID = PH1.PO_HEADER_ID
          AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID
          AND PL1.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID(+)
          AND PL1.UN_NUMBER_ID = PUN.UN_NUMBER_ID(+)
          AND PL2.PO_HEADER_ID = PH2.PO_HEADER_ID(+)
          AND PL1.FROM_LINE_ID = PL2.PO_LINE_ID(+)
          AND PL1.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
          AND NVL (PL1.ORG_ID, -99) = NVL (FSP.ORG_ID, -99)
          AND PL1.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
          AND NVL (MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND PL1.ITEM_ID = MSIT.INVENTORY_ITEM_ID(+)
          AND NVL (MSIT.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND MSIT.LANGUAGE(+) = USERENV ('LANG')
   UNION
   SELECT PLL.ship_to_location_id,
          PL1.ITEM_REVISION,
          PL1.LINE_NUM,
          DECODE (
             NVL (MSI.ALLOW_ITEM_DESC_UPDATE_FLAG, 'Y'),
             'Y', PL1.ITEM_DESCRIPTION,
             DECODE (PL1.ORDER_TYPE_LOOKUP_CODE,
                     'QUANTITY', MSIT.DESCRIPTION,
                     PL1.ITEM_DESCRIPTION))
             ITEM_DESCRIPTION,
          NVL (PL1.CANCEL_FLAG, 'N'),
          PL1.CANCEL_DATE,
          PL1.CANCEL_REASON,
          PL1.VENDOR_PRODUCT_NUM,
          PL1.NOTE_TO_VENDOR,
          DECODE (PLT.ORDER_TYPE_LOOKUP_CODE,
                  'AMOUNT', TO_NUMBER (NULL),
                  'QUANTITY', PL1.UNIT_PRICE,
                  'FIXED PRICE', PL1.AMOUNT,
                  'RATE', PL1.UNIT_PRICE),
          DECODE (PLT.ORDER_TYPE_LOOKUP_CODE,
                  'QUANTITY', PL1.QUANTITY_COMMITTED,
                  NULL),
          PL1.COMMITTED_AMOUNT,
          NVL (MUM.UNIT_OF_MEASURE_TL, PL1.UNIT_MEAS_LOOKUP_CODE)
             UNIT_MEAS_LOOKUP_CODE,
          PUN.UN_NUMBER || ' ' || PUN.DESCRIPTION,
          PHC.HAZARD_CLASS,
          PLT.ORDER_TYPE_LOOKUP_CODE,
          PL1.CONTRACT_NUM,
          PH2.QUOTE_VENDOR_QUOTE_NUMBER,
          PL2.LINE_NUM,
          PL1.ATTRIBUTE_CATEGORY,
          PL1.ATTRIBUTE1,
          PL1.ATTRIBUTE2,
          PL1.ATTRIBUTE3,
          PL1.ATTRIBUTE4,
          PL1.ATTRIBUTE5,
          PL1.ATTRIBUTE6,
          PL1.ATTRIBUTE7,
          PL1.ATTRIBUTE8,
          PL1.ATTRIBUTE9,
          PL1.ATTRIBUTE10,
          PL1.ATTRIBUTE11,
          PL1.ATTRIBUTE12,
          PL1.ATTRIBUTE13,
          PL1.ATTRIBUTE14,
          PL1.ATTRIBUTE15,
          PL1.UNIT_PRICE,
          PL1.QUANTITY,
          PL1.QUANTITY_COMMITTED,
          PL1.PO_HEADER_ID,
          PL1.PO_LINE_ID,
          TO_NUMBER (NULL),
          PL1.ITEM_ID,
          PH2.SEGMENT1,
          PH2.GLOBAL_AGREEMENT_FLAG,
          PL1.FROM_HEADER_ID,
          PL1.FROM_LINE_ID
     FROM PO_LINE_TYPES PLT,
          PO_UN_NUMBERS PUN,
          PO_HAZARD_CLASSES PHC,
          PO_HEADERS PH2,
          PO_LINES PL2,
          PO_HEADERS PH1,
          Po_LINE_LOCATIONS PLL,
          PO_LINES PL1,
          MTL_UNITS_OF_MEASURE MUM,
          FINANCIALS_SYSTEM_PARAMS_ALL FSP,
          MTL_SYSTEM_ITEMS_KFV MSI,
          MTL_SYSTEM_ITEMS_TL MSIT
    WHERE     PL1.LINE_TYPE_ID = PLT.LINE_TYPE_ID
          AND PL1.PO_HEADER_ID = PH1.PO_HEADER_ID
          AND PH1.po_header_id = pll.po_header_id
          AND PL1.po_line_id = pll.po_line_id
          AND PH1.TYPE_LOOKUP_CODE = 'BLANKET'
          AND PL1.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID(+)
          AND PL1.UN_NUMBER_ID = PUN.UN_NUMBER_ID(+)
          AND PL2.PO_HEADER_ID = PH2.PO_HEADER_ID(+)
          AND PL1.FROM_LINE_ID = PL2.PO_LINE_ID(+)
          AND PL1.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
          AND NVL (PL1.ORG_ID, -99) = NVL (FSP.ORG_ID, -99)
          AND PL1.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
          AND NVL (MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND PL1.ITEM_ID = MSIT.INVENTORY_ITEM_ID(+)
          AND NVL (MSIT.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND MSIT.LANGUAGE(+) = USERENV ('LANG')
   UNION
   SELECT PLL.ship_to_location_id,
          PL1.ITEM_REVISION,
          PL1.LINE_NUM,
          DECODE (
             NVL (MSI.ALLOW_ITEM_DESC_UPDATE_FLAG, 'Y'),
             'Y', PL1.ITEM_DESCRIPTION,
             DECODE (PL1.ORDER_TYPE_LOOKUP_CODE,
                     'QUANTITY', MSIT.DESCRIPTION,
                     PL1.ITEM_DESCRIPTION))
             ITEM_DESCRIPTION,
          NVL (PL1.CANCEL_FLAG, 'N'),
          PL1.CANCEL_DATE,
          PL1.CANCEL_REASON,
          PL1.VENDOR_PRODUCT_NUM,
          PL1.NOTE_TO_VENDOR,
          DECODE (PLT.ORDER_TYPE_LOOKUP_CODE,
                  'AMOUNT', TO_NUMBER (NULL),
                  PL1.UNIT_PRICE),
          DECODE (PLT.ORDER_TYPE_LOOKUP_CODE,
                  'QUANTITY', PL1.QUANTITY_COMMITTED,
                  TO_NUMBER (NULL)),
          PL1.COMMITTED_AMOUNT,
          NVL (MUM.UNIT_OF_MEASURE_TL, PL1.UNIT_MEAS_LOOKUP_CODE)
             UNIT_MEAS_LOOKUP_CODE,
          PUN.UN_NUMBER || ' ' || PUN.DESCRIPTION,
          PHC.HAZARD_CLASS,
          PLT.ORDER_TYPE_LOOKUP_CODE,
          PL1.CONTRACT_NUM,
          PH2.QUOTE_VENDOR_QUOTE_NUMBER,
          PL2.LINE_NUM,
          PL1.ATTRIBUTE_CATEGORY,
          PL1.ATTRIBUTE1,
          PL1.ATTRIBUTE2,
          PL1.ATTRIBUTE3,
          PL1.ATTRIBUTE4,
          PL1.ATTRIBUTE5,
          PL1.ATTRIBUTE6,
          PL1.ATTRIBUTE7,
          PL1.ATTRIBUTE8,
          PL1.ATTRIBUTE9,
          PL1.ATTRIBUTE10,
          PL1.ATTRIBUTE11,
          PL1.ATTRIBUTE12,
          PL1.ATTRIBUTE13,
          PL1.ATTRIBUTE14,
          PL1.ATTRIBUTE15,
          PL1.UNIT_PRICE,
          PL1.QUANTITY,
          PL1.QUANTITY_COMMITTED,
          PL1.PO_HEADER_ID,
          PL1.PO_LINE_ID,
          PR.PO_RELEASE_ID,
          PL1.ITEM_ID,
          PH2.SEGMENT1,
          PH2.GLOBAL_AGREEMENT_FLAG,
          PL1.FROM_HEADER_ID,
          PL1.FROM_LINE_ID
     FROM PO_RELEASES PR,
          PO_LINE_LOCATIONS PLL,
          PO_LINE_TYPES PLT,
          PO_HEADERS PH2,
          PO_LINES PL2,
          PO_HEADERS PH1,
          PO_LINES PL1,
          PO_UN_NUMBERS PUN,
          PO_HAZARD_CLASSES PHC,
          MTL_UNITS_OF_MEASURE MUM,
          FINANCIALS_SYSTEM_PARAMS_ALL FSP,
          MTL_SYSTEM_ITEMS_KFV MSI,
          MTL_SYSTEM_ITEMS_TL MSIT
    WHERE     PL1.LINE_TYPE_ID = PLT.LINE_TYPE_ID
          AND PL1.PO_HEADER_ID = PH1.PO_HEADER_ID
          AND PH1.PO_HEADER_ID = PR.PO_HEADER_ID
          AND PLL.PO_LINE_ID = PL1.PO_LINE_ID
          AND PLL.PO_HEADER_ID = PH1.PO_HEADER_ID
          AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID
          AND PH1.TYPE_LOOKUP_CODE = 'BLANKET'
          AND PL1.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID(+)
          AND PL1.UN_NUMBER_ID = PUN.UN_NUMBER_ID(+)
          AND PL2.PO_HEADER_ID = PH2.PO_HEADER_ID(+)
          AND PL1.FROM_LINE_ID = PL2.PO_LINE_ID(+)
          AND PL1.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
          AND NVL (PL1.ORG_ID, -99) = NVL (FSP.ORG_ID, -99)
          AND PL1.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
          AND NVL (MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND PL1.ITEM_ID = MSIT.INVENTORY_ITEM_ID(+)
          AND NVL (MSIT.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND MSIT.LANGUAGE(+) = USERENV ('LANG');
0
Comment
Question by:pardeshirahul
5 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38852954
Connect as the BOLINF user and try running the select statement used to create the view.
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38854034
Can you paste the error lines here - i am interested to look into the few below lines following this line "ORA-24344: success with compilation error" to understand what exactly is the error.

Thanks
0
 
LVL 37

Expert Comment

by:Geert Gruwez
ID: 38855110
the tables in the view don't have the schema name prepended
so they can only be run as the owner of the tables
... unless synonyms are setup or alter session set current_schema=BOLINF; is run first
0
 

Author Comment

by:pardeshirahul
ID: 38855293
i ran the query independently

SELECT PLL.SHIP_TO_LOCATION_ID,
          PL1.ITEM_REVISION,
          PL1.LINE_NUM,
          DECODE (
             NVL (MSI.ALLOW_ITEM_DESC_UPDATE_FLAG, 'Y'),
             'Y', PL1.ITEM_DESCRIPTION,
             DECODE (PL1.ORDER_TYPE_LOOKUP_CODE,
                     'QUANTITY', MSIT.DESCRIPTION,
                     PL1.ITEM_DESCRIPTION))
             ITEM_DESCRIPTION,
          NVL (PL1.CANCEL_FLAG, 'N'),
          PL1.CANCEL_DATE,
          PL1.CANCEL_REASON,
          PL1.VENDOR_PRODUCT_NUM,
          PL1.NOTE_TO_VENDOR,
          DECODE (PLT.ORDER_TYPE_LOOKUP_CODE,
                  'AMOUNT', TO_NUMBER (NULL),
                  PL1.UNIT_PRICE),
          DECODE (
             PLT.ORDER_TYPE_LOOKUP_CODE,
             'QUANTITY', DECODE (PH1.TYPE_LOOKUP_CODE,
                                 'STANDARD', PL1.QUANTITY,
                                 'PLANNED', PL1.QUANTITY,
                                 NULL),
             NULL),
          DECODE (
             PLT.ORDER_TYPE_LOOKUP_CODE,
             'QUANTITY', DECODE (PH1.TYPE_LOOKUP_CODE,
                                 'STANDARD', PL1.UNIT_PRICE * PL1.QUANTITY,
                                 'PLANNED', PL1.UNIT_PRICE * PL1.QUANTITY,
                                 TO_NUMBER (NULL)),
             'AMOUNT', DECODE (PH1.TYPE_LOOKUP_CODE,
                               'STANDARD', PL1.UNIT_PRICE * PL1.QUANTITY,
                               'PLANNED', PL1.UNIT_PRICE * PL1.QUANTITY,
                               TO_NUMBER (NULL)),
             'RATE', DECODE (PH1.type_lookup_code,
                             'STANDARD', PL1.amount,
                             TO_NUMBER (NULL)),
             'FIXED PRICE', DECODE (PH1.type_lookup_code,
                                    'STANDARD', PL1.amount,
                                    TO_NUMBER (NULL)),
             TO_NUMBER (NULL)),
          NVL (MUM.UNIT_OF_MEASURE_TL, PL1.UNIT_MEAS_LOOKUP_CODE)
             UNIT_MEAS_LOOKUP_CODE,
          PUN.UN_NUMBER || ' ' || PUN.DESCRIPTION,
          PHC.HAZARD_CLASS,
          PLT.ORDER_TYPE_LOOKUP_CODE,
          PO_COMMUNICATION_PVT.getSegmentNum (PL1.CONTRACT_ID),
          PH2.QUOTE_VENDOR_QUOTE_NUMBER,
          PL2.LINE_NUM,
          PL1.ATTRIBUTE_CATEGORY,
          PL1.ATTRIBUTE1,
          PL1.ATTRIBUTE2,
          PL1.ATTRIBUTE3,
          PL1.ATTRIBUTE4,
          PL1.ATTRIBUTE5,
          PL1.ATTRIBUTE6,
          PL1.ATTRIBUTE7,
          PL1.ATTRIBUTE8,
          PL1.ATTRIBUTE9,
          PL1.ATTRIBUTE10,
          PL1.ATTRIBUTE11,
          PL1.ATTRIBUTE12,
          PL1.ATTRIBUTE13,
          PL1.ATTRIBUTE14,
          PL1.ATTRIBUTE15,
          PL1.UNIT_PRICE,
          PL1.QUANTITY,
          PL1.QUANTITY_COMMITTED,
          PL1.PO_HEADER_ID,
          PL1.PO_LINE_ID,
          TO_NUMBER (NULL),
          PL1.ITEM_ID,
          PH2.SEGMENT1,
          PH2.GLOBAL_AGREEMENT_FLAG,
          PL1.FROM_HEADER_ID,
          PL1.FROM_LINE_ID
     FROM apps.PO_LINE_TYPES PLT,
          apps.PO_LINE_LOCATIONS PLL,
          apps.PO_HEADERS_ALL PH2,
          apps.PO_LINES_ALL PL2,
          apps.PO_HEADERS PH1,
          apps.PO_LINES PL1,
          apps.PO_UN_NUMBERS PUN,
          apps.PO_HAZARD_CLASSES PHC,
          apps.MTL_UNITS_OF_MEASURE MUM,
          apps.FINANCIALS_SYSTEM_PARAMS_ALL FSP,
          apps.MTL_SYSTEM_ITEMS_KFV MSI,
          apps.MTL_SYSTEM_ITEMS_TL MSIT
    WHERE     PL1.LINE_TYPE_ID = PLT.LINE_TYPE_ID
          AND PLL.PO_LINE_ID = PL1.PO_LINE_ID
          AND PLL.PO_HEADER_ID = PH1.PO_HEADER_ID
          AND PL1.PO_HEADER_ID = PH1.PO_HEADER_ID
          AND PH1.TYPE_LOOKUP_CODE IN ('STANDARD', 'PLANNED')
          AND PL1.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID(+)
          AND PL1.UN_NUMBER_ID = PUN.UN_NUMBER_ID(+)
          AND PL2.PO_HEADER_ID = PH2.PO_HEADER_ID(+)
          AND PL1.FROM_LINE_ID = PL2.PO_LINE_ID(+)
          AND PL1.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
          AND NVL (PL1.ORG_ID, -99) = NVL (FSP.ORG_ID, -99)
          AND PL1.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
          AND NVL (MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND PL1.ITEM_ID = MSIT.INVENTORY_ITEM_ID(+)
          AND NVL (MSIT.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) =
                 FSP.INVENTORY_ORGANIZATION_ID
          AND MSIT.LANGUAGE(+) = USERENV ('LANG')

this is the one which is creating the issue

PO_COMMUNICATION_PVT.getSegmentNum (PL1.CONTRACT_ID),

the owner is apps for it though
but somehow
apps.PO_COMMUNICATION_PVT.getSegmentNum (PL1.CONTRACT_ID) is not working fine
i am getting

ORA-00904: "PO_COMMUNICATION_PVT"."GETSEGMENTNUM": invalid identifier
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 38855321
Looks like you need to grant execute to BOLINF on PO_COMMUNICATION_PVT.  This must be an explicit grant (not through a role).
1

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now