Solved

I am getting ORA-24344: success with compilation error

Posted on 2013-02-04
5
2,004 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 38

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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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.
Suggested Courses

623 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