SQL & PL/SQL :: Query Against EBS Order Lines And Headers
Sep 6, 2012
I want added one new column in the below report which will shows only the items purchased on Cash basis but when i am running the query the column not showing any data.
Customer sales order lines are fulfilled by IR-ISO. When line progresses, it is set to have Internal Req Requested status. A scheduled program runs amd IR are created. However, sales order line status does not change to Internal Req Open. It remains in Internal Req Requested status.
As a work around, workflow is rewound to 'Check ATR Qty' which is customized activity and then status changes to Internal Req Open. What could be the reason that workflow is not setting the status to Internal Req Open?
My company recently purchased a package that is capable of automatically printing/caching reports generated by Oracle Reports at specified points in workflows, and so I am trying to design some reports that match the output of previous systems.
I'm having particular difficulty making one of them work the way I need it to. I've put a screenshot of how the report should be laid out (and how I've currently laid it out in Oracle Reports Builder) in the attachment. I'm using Oracle Reports Builder 10g against an 11g database.
Here's what I'm trying to accomplish:
The Header Information in Repeating Frame 1 should be visible on every page of the report. Shipments may cover multiple pages, and I want this section (logo, shipment number, date, carrier) on all of them. The Logo is in a BLOB in the database.
The information in Repeating Frame 2 should be visible on the first page for a shipment, but not on any other pages for the same shipment. Multiple shipments may be printed at a time, so this should only appear at the beginning of the group.
The information in Repeating Frame 3 and the nested Repeating Frame 4 should be visible on every page. I've tried multiple methods to make this work.
I've tried setting the main frame (Repeating Frame 1) to print on all pages, as well as Repeating Frames 3 & 4, with Repeating Frame 2 set to print only on the first page. This combination only yields the first page of the first shipment - no more.
I've tried removing frames 2, 3 and 4 from frame 1 and making them all independent of one another. This seems to yield a cartesian product-like result with all shipment lines attached to the first page and only frame 1 showing up on subsequent pages.
I've tried moving the "Header" information into the top margin. When I did, I got a "frequency below the group..." type of error for the fields in the margin. I then tried to create summary columns with breaks on pages that I could reference in the margin, but I couldn't get the image (from the BLOB) to output.
if when you are querying a table in 11g and you use the order by clause and there is more than one occurrences with the same values in the order by, if the 11g default is different than from 10g.
For instance.
DECLARE MHBulk CURSOR FOR select invoice_nbr, customer_nbr, post_century, post_yymmdd, from CUSTOMERS where customer_nbr = 1234 order by post_century, post_yymmdd;
If you have more than one occurrence of the same customer_nbr, post_century, and post_yymmdd
it looks like the default order for how 11g retrieves the records is a bit different than the 10g default.
I'm planning to do a single-sing-on in one of my applications. To get to my application, the user has to log in to a portal-application befor. From this application i receive 2 headers, to verify this person has authenticated successfully. But how can I read these 2 headers? It doesn't work with the UTL_HTTP package, because i don't do an active request on an url.
Does ascending index ensures that query without order by will have the result set sorted?
E.g. the query is
select * from table_t where odm_type='I' and odm_uid>nvl(OpUidParm,-1); column odm_type has index created like this (default is ASC): create index ODM_UID_I on table_t (ODM_UID);
Will such a query always return the first record having the minimal odm_uid in all Oracle versions?
In a classic report, I'm using the Sum functionality and breaks by First Column to get subtotals and report total. I Repeat Headers on Break which works great for the subtotals but I would like for the report to display the column headers above the report total for easier reading. If the last grouping has a lot of data, the user needs to scroll up to read the column headers when looking at the Grand Total.
Is there a way to Repeat Headers prior to report total?
Name Null Type --------------------------- -------- ------------- RPTNO NOT NULL NUMBER RPTDATE NOT NULL DATE RPTD_BY NOT NULL VARCHAR2(25) PRODUCT_ID NOT NULL NUMBER
describe rptbody
Name Null Type ------------- -------- ------------- RPTNO NOT NULL NUMBER LINENO NOT NULL NUMBER COMMENTS VARCHAR2(240) UPD_DATE DATE
The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.
SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE FROM RPTBODY t0 , RPTHEAD rpthead WHERE ( t0.RPTNO = rpthead.RPTNO AND t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss') AND rpthead.PRODUCT_ID IN ('4647') )
I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.
In Oracle XE when I run PL/SQL in the SQL Commands window I will sometimes get a "Not found" error. It further reads, "The requested URL /apex/wwv_ flow.show was not found on this server". I found that if I remove all of the indentations from the lines of the PL/SQL code everything will work fine. Talk about a screwy problem. So, if you have that problem just take all of your PL/SQL code and slam it up against the left margin.
formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is in single line..find below the code i have written along with test case, you may substitute your emailid for test, especially my main focus is on the column mrk , where all the mrk are comming in one line but i want to be arranged in a line not more thant 10 rows, if it exceeds it should go to next line.
CREATE TABLE FAB_LOG ( MRK VARCHAR2(30)) INSERT ALL INTO FAB_LOG VALUES ('1018017M-6001') INTO FAB_LOG VALUES ('1018017M-6002') INTO FAB_LOG VALUES ('1018017M-6003') INTO FAB_LOG VALUES ('1018017M-6004') INTO FAB_LOG VALUES ('1018017M-6005') INTO FAB_LOG VALUES ('1018017M-6006') [code]....
As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row
When referencing a procedure during a trigger, can I split the parameters across multiple lines? Similar to a backslash in perl? I've written a simple send mail procedure and it works well, though the parameter list is large and I'd like to be able to format the code for readability, i.e.:
BEGIN send_mail('from@domain.com', 'to@domain.com, ??? 'Subject', 'Message'); END;
What would I replace ??? with to extend the procedure to the next line?
I know this sounds like a very elementary question, but I've yet to figure it out via queries on these forums or Google. Perhaps I'm not choosing the right words.
When creating the procedure, i was able to use || to extend the utl_smtp function parameters, but I get an error when using the same syntax during trigger creation.
I have some tables (below), but I'm having trouble entering multiple lines of data.Here are the tables I have created.
Quote: CUSTOMER table SQL> create table customer 2 (customer_no char(6) not null, 3 name varchar2(30) not null, 4 address varchar2(50) not null, 5 credit_limit number(6,2), 6 constraint customer_customer_no_pk primary key (customer_no));
Table created. SALESMAN table SQL> create table salesman 2 (salesman_id char(8), 3 name varchar2(20) not null, 4 address varchar2(50) not null, 5 emaill_address varchar2(30), 6 constraint salesman_salesman_id_pk primary key (salesman_id));
Table created. ITEM table SQL> create table item 2 (ISBN char(13) not null, 3 title varchar2(30) not null, 4 price number(4,2) not null, 5 constraint item_ISBN_pk primary key (ISBN));
Table created. INVOICE table SQL> create table invoice 2 (invoice_no char(1) not null, 3 invoice_date date not null, 4 salesman_id char(8),
[code]...
Table created. DELIVERY table SQL> create table delivery 2 (invoice_no char(1) not null, 3 ISBN char(13) not null,
[code]...
Table created. I can enter data into the customer, salesman and item table without any problems. However, I need to enter multiple lines of data using the same invoice_no (which is 1). The data is generally the same, except for 2 things need changing but I keep getting the following error...
i wanna copy data from a table to another one (same structure, only difference is that the second table is partitioned). the origin table consists of about millions of lines so i made an insert :
insert into table2 select * from table1. the insertion doesn't end correctly : TOAD crashes, any line was inserted in table2 : select * from ...etc.
However, the file system becomes full. i would if there is a way to purge something like cache ..., are these lines inserted somewhere : temporary table...,
I would like to write a query on USER_SOURCE that can display the number of code lines for each procedure/function in a package. Is it possible to write such a query? Maybe by using analytical functions?
for example in the following example i would like to count the lines between
"PROCEDURE proc1 IS" and "END proc1;" and between "PROCEDURE proc2 IS" and "END proc2;" SQL> select text from user_source where name='PKG_TEST' and type='PACKAGE BODY';
TEXT -------------------------------------------------- PACKAGE BODY PKG_TEST IS /************************************************* ****/ PROCEDURE proc1 IS BEGIN update t1 set EDITION_NAME = 'AAAAAAA'; commit; END proc1; [code]....
i need a chart with 4 lines and 2 y axis. 2 lines on the first y axis, 2 lines on the other y axis.First i tried to create 4 series and "multiple y axis", then i get the first line at one y axis, and all the other lines at the second y axis.
After that i have seen that it is possible to return more than one value within one series, so i create 2 series with 2 values each - i thought that then i will get the first 2 values at one y axis and the other 2 lines of the second serie at the second y axis... but sadly it is not like i expected - i get the same result as with 4 series.is it really necessary to create a custom xml for that?
here is my example: [URL] as you can see the blue and the red line are on different axis.the query of one serie looks like that (it gives the values of a day and the day before), the queries are ok.
select null link, to_char(a.ts,'DD.MM HH24:MI') label, sum(a.kw*100/12) over ( partition by trunc(a.ts) order by a.ts rows between unbounded preceding and current row ) "KWh", sum(b.kw*100/12) over ( partition by trunc(b.ts) order by b.ts rows between
I am creating a report which generates the data to a PDF/CSV based on users Input.When I create the report using Report Builder and execute the report,Delimited data appears in a PDF layout.
But if I add the below code in After Parameter Form trigger,the column headers keeps repeating with the data.
function AfterPForm return boolean is begin IF :P_DESTYPE = 'CSV' THEN :MODE :='DEFAULT'; :DESFORMAT:='DELIMITED'; Elsif :P_DESTYPE = 'PDF' THEN :DESTYPE := 'CACHE'; :DESFORMAT:='PDF'; END IF; return (TRUE); end; [code]....
But the PDF output seems fine where the header is shown only once.I could see many posts regarding this in Oracle reports 6i wherein delimited_hdr=no is used in Command Line but I would like to know the work around in Oracle reports 10g.
