Hi @baris.halici Thank you for your reply. You are right, when I choose a font I can also use bold and italic. But now my next question… the font that is used in my reports is ‘Calibri’. But this font is not available in the drop down section. For example, when I choose ‘Arial’ from the drop down section I can use bold and italic. But I want to use the fond ‘Calibri’ which is not available. Do you know how to add font families in Report Designer tool?
Hi @CODUGBHW I did not manage to find an answer, sorry.
Hi @M1SS Melissa Imsirovic , I created a quick report based on the views PURCHASE_ORDER_INVOICE and POSTING_PROPOSAL_COST_AUTH. And for hours based on the view PROJECT_TRANSACTION. These two quick reports give me the information I requested and I think will also provide you the information you need. Sidenote: I was not able to match these Quick Reports with the Actual Cost in Project Navigator. But they still give the required info. It will only match with the Actual Cost when all the invoices for the purchase orders are booked.
Thank you @mmoss Ofcourse that is also an option, I didn't think of that one.
Hi, I also have the same problem. Someone found the solution yet?
Hi @Kelum Niranjana Thank you! I managed to import the file correctly.
Hi @jporter I am sorry for my late reply. I was on holiday. I did not create a case. I created a new custom item in the menu ‘Relastions for Facts’ fetching the data from ‘Probability’ and 'Description’ using SQL. So I created a work around for the issue instead of really solving it.
Hi @ThushAsanka Many thanks. The query worked!
Thank you @djayThat did the job.
Hi @Oshada Samarasinghe , Thank you for the link. But I receive an error message when I want to deploy the IAL:bind variables not allowed for data definition operationsFailed executing statement (ORA-01027: bind variables not allowed for data definition operations My Select Statement: select OPPORTUNITY_NO "ID", DESCRIPTION as Description, 'BO' "Type", EST_OPPORTUNITY_VALUE as Valuefrom BUSINESS_OPPORTUNITYWHERE OBJSTATE <> (select &AO.BUSINESS_OPPORTUNITY_API.FINITE_STATE_ENCODE__('Cancelled') from dual) and OBJSTATE <> (select &AO.BUSINESS_OPPORTUNITY_API.FINITE_STATE_ENCODE__('Closed') from dual)UNIONselect QUOTATION_NO "ID", CATALOG_DESC as Description, 'SQ' "Type", &AO.Order_Quotation_Line_API.Get_Base_Sale_Price_Total(QUOTATION_NO, LINE_NO, REL_NO, LINE_ITEM_NO) as Valuefrom Order_Quotation_LineWHERE OBJSTATE = (select &AO.ORDER_QUOTATION_LINE_API.FINITE_STATE_ENCODE__('Planned') from dual) or OBJSTATE = (select &AO.ORDER_QUOTATION_LINE_API.FINITE_
Hi @Randini Jayasundara Thank you for your reply. I did remove the 2 previously created custom fields. In the meantine, I created a custom field on the header of the Purchase Order instead of on the No Part/Part Lines. And this also worked for us, so the initial problem is no longer an issue.
Hi @Praveen Wijetunge and @AanHGTS Thank you both for your replies. Is it also not possible to create a Migration Job for this? @AanHGTS, the business scenario is kind of complex, but I will try to explain. We have a lot of ‘dummy’ parts which act as phantom parts. I created those parts using Migration Jobs. We use XML’s to build the Project Deliverables Structure and in those XML’s the dummy parts are mentioned. The revision is a datafield in the XML, so the import will result in an error when the revision is not R01.
Hi @Kelum Niranjana Thanks a lot for your help and the info. I managed to make a statement excluding the CLOB and BLOB columns and get the data I wanted! Kind regards,Simon
Hi @EntNadeeL Yes, I can see the custom field and I can also add it to the visible columns. But when I try to populate the table, the error message occurs. Kind regards,Simon
Hi @EntNadeeL Thank you for your reply. That is not correct, I tried to add the custom field to the view PurchaseOrderLine. See the screenshot below. Kind regards, Simon
Hi @EntNadeeL I created the custom fields in two views, because they are applicable on Part Order Lines and No Part Order Lines (two different views): I want to display the value from the Part/No Part Order Line in the Register Arrival menu. But I do not kwow how to create the custom field in the Register Arrivals view and get the value from the Part/No Part Order Line view. I think I need to use sql, but I do not know the correct statement. I tried: SELECT cf$_receipt FROM purchase_order_line_cfv t WHERE t.objkey = :objkeyBut this results in an error: Kind regards,Simon
I’m not sure if this helps, but back in the day when I worked a lot with reports and report designer, I sometimes had problem clicking the right object in the design window when they started to overlap so then it might be easier to do some of these operations by finding the the right object in the layout tree instead and add cell/rows that way. Hi @Dario Zani I managed to add the fields in the new cells I created. I just pasted the fields in a random place in the layout where I had access to and then copy/paste them to the newly created cells! Thanks a lot for your help and suggestions.
Hi @Dario Zani , Thank you, I managed to add the new row/cells as you suggested. But how do I fill the cells with the field I want? I marked the field and the new row/cells with the green arrows in the screenshot below.
Hi @dhlelk , That is correct, the "INVOICE_LINE_TYPE" and "INVOICE_LINE" tables overlap. And both also overlap with the table I try to add a new field to. Sadly, your suggestion is not working, because when I add new rows, the table does not change and still overlaps with the table I try to add a field to. We are in APPS10 update 7. The Report ID is ‘Invoice’.
Hi @VivekBhandiwad , I should have seen the extra ")”, my fault ;) Many thanks for your help. The statement returns exactly what I wanted!
Hi @EntNadeeL and @ola.nissen , Thank you both for your replies and your help. @EntNadeeL I tried your suggestion, but this didn’t change the outcome. But instead of 10 I tried 8, and then I got my desired result, see below, all date's returned weeknumber 12. 22-3-2021 0:00 12 23-3-2021 0:00 12 24-3-2021 0:00 12 24-3-2021 0:00 12 25-3-2021 0:00 12 26-3-2021 0:00 12 SELECT to_char(to_date(substr(cf$_co_date_order,1,8), 'DD-MM-YYYY'), 'iw') FROM customer_order_cfv
Hi @VivekBhandiwad I am not familiar with a PLSQL tool. I always built my queries in the SQL Query Tool or in Create New Quick Report. But I have done as you suggested, but still IFS returns an error when I try to save the query. The error is ‘From keyword not found where expected’. Do you know what is wrong? SELECT to_char(order_date,'IW') "Week", to_char(order_date, 'dd-mm-yyyy') "Order Date", ifsapp.Supplier_API.Get_Vendor_Name(vendor_no) "Supplier", Cf$_Pur_Or_Description "Description", project_id "Project ID", ifsapp.PROJECT_API.Get_Name(PROJECT_ID) "Project Name", Cf$_Coordinator_Name "Coordinator", order_no "Purchase Order", to_char(wanted_receipt_date, 'dd-mm-yyyy') "Wanted Receipt Date", to_char(wanted_receipt_date,'IW') "Receipt Week", state "Status", ifsapp.Purchase_Order_API.Calc_Order_Total(Order_No) "Total Amount",
Hi @ola.nissen ,Thank you for your reply. I also tested your statement in SQL Fiddle and indeed it returns the correct weeknumber. But when I run the statement in IFS like this:SELECT to_char(to_date(substr(cf$_co_date_order,1,9), 'DD-MM-YYYY'), 'iw') FROM customer_order_cfv I still get the wrong results: 22-3-2021 0:00 12 23-3-2021 0:00 12 24-3-2021 0:00 13 24-3-2021 0:00 13 25-3-2021 0:00 13 26-3-2021 0:00 13 Do you know what I am doing wrong? I expected the statement to return weeknumber 12 for all the dates above.
Hi @VivekBhandiwad I did as you suggested but IFS gives an error when I try to save the query. Do you know what is wrong? This is the statement I try to save: SELECT to_char(order_date,'IW') "Week", to_char(order_date, 'dd-mm-yyyy') "Order Date", Supplier_API.Get_Vendor_Name(vendor_no) "Supplier", Cf$_Pur_Or_Description "Description", project_id "Project ID", PROJECT_API.Get_Name(PROJECT_ID) "Project Name", Cf$_Coordinator_Name "Coordinator", order_no "Purchase Order", to_char(wanted_receipt_date, 'dd-mm-yyyy') "Wanted Receipt Date", to_char(wanted_receipt_date,'IW') "Receipt Week", state "Status", Purchase_Order_API.Calc_Order_Total(Order_No) "Total Amount", ( select sum(d.charge_amount) from purchase_order_charge d where d.order_no = purchase_order_cfv.order_no and d.contract = purchase_order_cfv.contract ) ) sum_chargesFROM pu
Hi @VivekBhandiwad Thank you for your reply. The result from the API Purchase_Order_API.Calc_Total_Gross_Incl_Chg(purchase_order_cfv.order_no) is the total gross amount of the Purchase Order including charges. But I am looking for the total net amount including charges (the amount of the charges also excluding tax). But I can’t find the correct API to do so. Do you know how I can get the result I want?
Already have an account? Login
No account yet? Create an account
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
Sorry, we're still checking this file's contents to make sure it's safe to download. Please try again in a few minutes.
Sorry, our virus scanner detected that this file isn't safe to download.
