Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

I am getting ORA-24344: success with compilation error

Posted on 2013-02-04
5
Medium Priority
?
2,102 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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.
Suggested Courses

971 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