Solved

I am getting ORA-24344: success with compilation error

Posted on 2013-02-04
5
1,925 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 77

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 77

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

749 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