Solved

Error message when deploying an IAL

  • 2 August 2023
  • 2 replies
  • 216 views

Userlevel 3
Badge +10

Hello IFS super brains,

When I run the following code in PLSQL developer, I have no problems.  When I try to deploy the IAL, I am getting the following error.

select
ro.rental_no, 
case 
when ro.RENTAL_TYPE = 'Customer Order'
and ro.contract In ( 'CPH','CTM','VMS')
then 'Internal' 
when ro.RENTAL_TYPE = 'Project'
and ro.contract = 'CPH'
then 'Internal' 
when ro.RENTAL_TYPE = 'Purchase Order'
and ro.party_id Not In  ( 'CPH','CTM','VMS')
then 'External' 
end Hire_Type,
NULL order_ref1 ,
NULL order_ref2 , 
NULL order_ref3 , 
NULL order_ref4 , 
coj.order_no as Cust_order_no,
coj.line_no as Cust_line_no,
coj.rel_no as Cust_Rel_no,
coj.line_item_no as Cust_line_item_no,
DECODE(coj.CATALOG_TYPE_DB, 'INV', coj.QTY_SHIPPED, NULL) as Cust_logistics_qty,
&AO..CUSTOMER_ORDER_API.Get_State(coj.order_no) as CO_STATE,
ril.Location_NO,
ro.contract as site,
ro.part_no,
 '' Location, 
'' WAREHOUSE, 
ro.part_description,
ro.party_id as customer_id,
ro.party_name as customer_name,
NULL as supplier_id, 
NULL as supplier_name, 
&AO..INVENTORY_PART_API.Get_Accounting_Group(ro.contract,ro.part_no) AS ACC_GROUP,
&AO..Accounting_Group_API.Get_Description(&AO..INVENTORY_PART_API.Get_Accounting_Group(ro.contract,ro.part_no)) AS ACC_GROUP_DESC,
&AO..INVENTORY_PART_API.Get_Part_Product_Family(ro.contract,ro.part_no) AS PRODUCT_FAMILY,
&AO..Inventory_Product_Family_API.Get_Description(&AO..INVENTORY_PART_API.Get_Part_Product_Family(ro.contract,ro.part_no)) AS PRODUCT_FAMILY_DESC,
ro.rental_type,
cf$_project  as project_id ,
&AO..PROJECT_API.Get_Program_Id(cf$_project) as Program_ID,
&AO..PROJECT_PROGRAM_GLOBAL_API.Get_Description(&AO..PROJECT_API.Get_Program_Id(cf$_project)) as Program_DESC,
&AO..PROJECT_API.Get_Category1_Id(cf$_project) as CATEGORY_1,
&AO..PROJECT_CATEGORY1_GLOBAL_API.Get_Description(&AO..PROJECT_API.Get_Category1_Id(cf$_project)) AS CATEGORY_1_DESC,
&AO..PROJECT_API.Get_Category2_Id(cf$_project) as CATEGORY_2,
&AO..PROJECT_CATEGORY2_GLOBAL_API.Get_Description(&AO..PROJECT_API.Get_Category2_Id(cf$_project)) AS CATEGORY_2_DESC,
cf$_activity_seq as activity_seq ,
&AO..activity_api.get_short_name(cf$_activity_Seq) as activity_short_name,
&AO..activity_api.Get_Activity_No(cf$_activity_Seq) as activity_id,
&AO..activity_api.Get_Description(cf$_activity_Seq) as activity_description,
ro.matr_seq_no,
ro.planned_rental_duration,
eh.RENTAL_DATE as Rental_Start_Date,
ro.planned_rental_end_date as planned_rental_end_date,
ro.planned_rental_return_date as planned_rental_return_date,
ro.planned_rental_qty,
ro.rental_duration_unit_meas,
ro.on_rental_qty as Qty_On_Hire,
ro.logistics_qty,
ro.unit_meas,
ROUND(eh.PRICE_CURR,2) as daliy_rate,
trunc(TO_DATE(sysdate, 'DD-MON-YY') - TO_DATE(eh.RENTAL_DATE, 'DD-MON-YY')) as DAYS_ON_HIRE,
ROUND(eh.PRICE_CURR_INCL_TAX,2) as daliy_rate_incl_tax,
ro.conv_factor,
ro.chargeable_id,
&AO..Customer_Order_line_CFP.Get_Cf$_Exception_Id (
&AO..Customer_Order_line_CFP.Get_Objkey(coj.order_no , coj.line_no, coj.rel_no, coj.line_item_no)) as exception_id ,
ro.trans_schedule_id,
ro.discount,
ro.supply_demand_code_db,
ro.header_status,
ro.created_by,
ro.created_date,
ro.ancillary,
Case When ro.contract = 'CTM' then '519' When ro.contract = 'VMS' Then '530' else  cc.code_part_value End  as cost_centre, 
ro.c_serial_no,    
ro.c_lot_batch_no,
&AO..PURCHASE_ORDER_LINE_API.Get_Requisition_No(ro.order_ref1,ro.order_ref2,ro.order_ref3) as Req_No,
&AO..Customer_Order_API.Get_C_Depot(ro.order_ref1) as Depot_Assigned,
eh.RE_ID as Rental_Event_ID,
&AO..Customer_Info_Address_API.Get_Address1(po.customer_no , po.ship_addr_no ) address_1,
&AO..Customer_Info_Address_API.Get_Address2(po.customer_no , po.ship_addr_no ) address_2,
&AO..Customer_Info_Address_API.Get_Address3(po.customer_no , po.ship_addr_no ) address_3,
&AO..Customer_Info_Address_API.Get_Address4(po.customer_no , po.ship_addr_no ) address_4,
&AO..Customer_Info_Address_API.Get_Address5(po.customer_no , po.ship_addr_no ) address_5, '' address_6,
&AO..Customer_Info_Address_API.Get_city(po.customer_no , po.ship_addr_no ) city,
&AO..Customer_Info_Address_API.Get_county(po.customer_no , po.ship_addr_no ) county,
&AO..Customer_Info_Address_API.Get_zip_code(po.customer_no , po.ship_addr_no ) postcode,
&AO..ISO_COUNTRY_API.Get_Description(po.COUNTRY_CODE) as Country,
  (select ec.cf$_def_proj_act_seq
   from &IAL..employee_cache ec
   where ec.emp_no = &AO..activity_api.Get_Activity_No(cf$_activity_Seq) 
   and ec.EMP_STATUS = 'ACTIVE') as emp_def_activity_seq 
FROM &AO..rental_object_uiv RO 
LEFT OUTER JOIN &IAL..Event_History_Max_Re_Id eh On eh.rental_no = ro.rental_no 
LEFT OUTER JOIN &IAL..Part_Product_Location_bm ppl   ON ppl.PART_NO = ro.part_no   AND ppl.serial_no =  eh.serial_no
LEFT OUTER JOIN &AO..CUSTOMER_ORDER po ON ro.order_ref1 = po.ORDER_NO
LEFT OUTER JOIN &AO..Receipt_Inv_Location ril  
ON ro.order_ref1 = ril.source_ref1  
and ro.order_ref2 = ril.source_ref2  
and ro.order_ref3 = ril.source_ref3  
AND ro.activity_seq = ril.activity_seq
  and ro.contract = ril.contract
  LEFT OUTER JOIN &AO..CUSTOMER_ORDER_JOIN_CFV COJ
  ON ro.order_ref1 = COJ.order_no
  AND ro.order_ref2 = COJ.line_no
  AND ro.order_ref3 = COJ.rel_no
Left Outer Join
  (select control_type_value, spec_control_type_value, code_part_value 
    from &AO..POSTING_CTRL_DETAIL_SPEC
    where company = '105' and posting_type = 'M231' and code_part='B' and control_type_value = 'CPH'
   ) CC On CC.spec_control_type_value =&AO..sales_part_api.Get_Catalog_Group(coj.contract, coj.Catalog_no)
where ro.primary_rental_no = ro.rental_no
and ro.on_rental_qty > 0
and (CASE   WHEN ro.rental_type = 'Project'
    AND ro.CONTRACT = 'CPH' THEN 'A'
    WHEN ro.rental_type = 'Customer Order' then 'A'
      Else 'B' End ) = 'A'

 

  Can anyone advise what the issue may be please.

icon

Best answer by NiyomalN 3 August 2023, 22:46

View original

2 replies

Userlevel 4
Badge +12

@JannetteC 

I think the things to check are the differences between what happens when you run the SQL in your developer and what happens when you try to create the IAL table.  

  1. Duplicate column names - this would be evident in the query results as the second appearance would appear as COLUMN_1
  2. Oracle limits on table names and column names (30 characters)
  3. IFSINFO (assuming that’s your &IAL owner) grants to custom objects
  4. IFS grants for IFSINFO - check the permission sets

Your query is a little long and has a lot of custom fields which makes reproducing the issue a little trouble-some for anyone trying to help.  If none of the above helps, I suggest that you comment out part of your code and retry.  I usually comment out half, if the error persists, I know the issue is in the uncommented part.

 

Userlevel 3
Badge +9

Hi

 

it seems like you don't have access to create a table in the database, as a workaround try to create this IAL for online data access only mode by checking the check box  (toggle button in cloud) “Create IAL for Live Data Only”.

 

Kind Regards!

Niyomal

Reply