oracle error ora-00984 error when compliing procedure
it gave 55 oracle error ora-00984
144 pl/sql: oracle error ora-00984 column not allowed here 98 pl/sql sql statement ignored
when i complie it...
i ve marked the error line number in the script
CREATE OR REPLACE procedure APPS.SP_IMP_VSC_SLP_INVOICE ( --------------------- eis sales order summary -------------------------------- -- program: sp_so_summary.sql -- author: fei.lam -- creation date: 02-jul-2010 -- remark: for oracle applications 11i 11.5.5 -- -- modification history: -- --------------------------------------------------------------- errbuff out varchar2, retcode out varchar2 )
as
-- variables declaration v_cnt_succ integer; v_cnt_fail integer; v_cnt_dup_rec integer:=0;
cursor cur_bk is select ral.CUSTOMER_TRX_LINE_ID ,inv.TRX_NUMBER as INVOICE_NO ,inv.CUSTOMER_NAME ,arc.TAX_REFERENCE ,null as tel_no ,arc.TAXPAYER_ID ,ral.SALES_ORDER ||' '||ooh.CUST_PO_NUMBER as invoice_reference ,mic.segment1 as cat ,msib.SEGMENT1 as item_code ,null as UOM_CODE ,ral.QUANTITY_ORDERED ,null UNIT_SELLING_PRICE --ral.QUANTITY_INVOICED ,ral.GROSS_EXTENDED_AMOUNT as TOTAL_AMOUNT ,avt.TAX_RATE/100 as tax_rate ,gl.SEGMENT1 ||'.'||gl.SEGMENT2||'.'||gl.SEGMENT3||'.'||gl.SEGMENT4 as tax_com ,'x' as audi_ppl ,'x' as payee ,'x' as discount ,'x' as discount_rate ,trunc(sysdate) cutoffdate --, fvl.MEANING VAT from AR.RA_CUSTOMER_TRX_LINES_ALL RAL ,RA_CUST_TRX_LINE_GL_DIST_ALL rxal ,NETV_INVOICE inv ,oe_order_headers_all ooh ,GL_CODE_COMBINATIONS gl -- ,FND_LOOKUP_VALUES_VL fvl ,AR_VAT_TAX_all avt ,AR_CUSTOMERS_V arc 55 ,MTL_ITEM_CATEGORIES_V mic ,inv.mtl_system_items_b msib where --inv.TRX_NUMBER='1003309' ooh.ORG_ID=249 and inv.ORG_ID=ooh.ORG_ID and ral.CUSTOMER_TRX_ID=inv.CUSTOMER_TRX_ID and ral.SALES_ORDER is not null and rxal.CUSTOMER_TRX_ID= ral.CUSTOMER_TRX_ID and gl.CODE_COMBINATION_ID=rxal.CODE_COMBINATION_ID and ooh.ORDER_NUMBER=ral.SALES_ORDER and rxal.CODE_COMBINATION_ID=gl.CODE_COMBINATION_ID and rxal.ACCOUNT_CLASS='REC' --and ooh.SALES_CHANNEL_CODE=fvl.LOOKUP_CODE -- A(VIP), B(Normal), C(Business) --and fvl.LOOKUP_TYPE='SALES_CHANNEL' and avt.VAT_TAX_ID=ral.VAT_TAX_ID and ooh.SOLD_TO_ORG_ID=arc.CUSTOMER_ID and inv.CUSTOMER_ID=arc.CUSTOMER_ID and ooh.ORDER_TYPE_ID in (1317,1355) --Wholesales & Project and mic.INVENTORY_ITEM_ID=ral.INVENTORY_ITEM_ID and msib.INVENTORY_ITEM_ID=ral.INVENTORY_ITEM_ID and mic.ORGANIZATION_ID=250 and msib.ORGANIZATION_ID=mic.ORGANIZATION_ID and ral.CUSTOMER_TRX_LINE_ID not in (select a.CUSTOMER_TRX_LINE_ID from VSCEIS.VSC_SLP_INVOICE a) ;
144 pl/sql: oracle error ora-00984 column not allowed here 98 pl/sql sql statement ignored
when i complie it...
i ve marked the error line number in the script
CREATE OR REPLACE procedure APPS.SP_IMP_VSC_SLP_INVOICE ( --------------------- eis sales order summary -------------------------------- -- program: sp_so_summary.sql -- author: fei.lam -- creation date: 02-jul-2010 -- remark: for oracle applications 11i 11.5.5 -- -- modification history: -- --------------------------------------------------------------- errbuff out varchar2, retcode out varchar2 )
as
-- variables declaration v_cnt_succ integer; v_cnt_fail integer; v_cnt_dup_rec integer:=0;
cursor cur_bk is select ral.CUSTOMER_TRX_LINE_ID ,inv.TRX_NUMBER as INVOICE_NO ,inv.CUSTOMER_NAME ,arc.TAX_REFERENCE ,null as tel_no ,arc.TAXPAYER_ID ,ral.SALES_ORDER ||' '||ooh.CUST_PO_NUMBER as invoice_reference ,mic.segment1 as cat ,msib.SEGMENT1 as item_code ,null as UOM_CODE ,ral.QUANTITY_ORDERED ,null UNIT_SELLING_PRICE --ral.QUANTITY_INVOICED ,ral.GROSS_EXTENDED_AMOUNT as TOTAL_AMOUNT ,avt.TAX_RATE/100 as tax_rate ,gl.SEGMENT1 ||'.'||gl.SEGMENT2||'.'||gl.SEGMENT3||'.'||gl.SEGMENT4 as tax_com ,'x' as audi_ppl ,'x' as payee ,'x' as discount ,'x' as discount_rate ,trunc(sysdate) cutoffdate --, fvl.MEANING VAT from AR.RA_CUSTOMER_TRX_LINES_ALL RAL ,RA_CUST_TRX_LINE_GL_DIST_ALL rxal ,NETV_INVOICE inv ,oe_order_headers_all ooh ,GL_CODE_COMBINATIONS gl -- ,FND_LOOKUP_VALUES_VL fvl ,AR_VAT_TAX_all avt ,AR_CUSTOMERS_V arc 55 ,MTL_ITEM_CATEGORIES_V mic ,inv.mtl_system_items_b msib where --inv.TRX_NUMBER='1003309' ooh.ORG_ID=249 and inv.ORG_ID=ooh.ORG_ID and ral.CUSTOMER_TRX_ID=inv.CUSTOMER_TRX_ID and ral.SALES_ORDER is not null and rxal.CUSTOMER_TRX_ID= ral.CUSTOMER_TRX_ID and gl.CODE_COMBINATION_ID=rxal.CODE_COMBINATION_ID and ooh.ORDER_NUMBER=ral.SALES_ORDER and rxal.CODE_COMBINATION_ID=gl.CODE_COMBINATION_ID and rxal.ACCOUNT_CLASS='REC' --and ooh.SALES_CHANNEL_CODE=fvl.LOOKUP_CODE -- A(VIP), B(Normal), C(Business) --and fvl.LOOKUP_TYPE='SALES_CHANNEL' and avt.VAT_TAX_ID=ral.VAT_TAX_ID and ooh.SOLD_TO_ORG_ID=arc.CUSTOMER_ID and inv.CUSTOMER_ID=arc.CUSTOMER_ID and ooh.ORDER_TYPE_ID in (1317,1355) --Wholesales & Project and mic.INVENTORY_ITEM_ID=ral.INVENTORY_ITEM_ID and msib.INVENTORY_ITEM_ID=ral.INVENTORY_ITEM_ID and mic.ORGANIZATION_ID=250 and msib.ORGANIZATION_ID=mic.ORGANIZATION_ID and ral.CUSTOMER_TRX_LINE_ID not in (select a.CUSTOMER_TRX_LINE_ID from VSCEIS.VSC_SLP_INVOICE a) ;
0