Solved

I am getting ORA-24344: success with compilation error

Posted on 2013-02-04
5
1,878 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle 10g - insert string with special characters 8 78
Sybase and replication server 13 39
add more rows to hierarchy 3 25
Fill Null values 5 28
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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

856 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