Forms :: Filter Records In A Data Block Based On Yes Or No Criteria
Aug 13, 2010
I need to filter records in a datablock based on Yes or No criteria.So i created a listitem(INCLUDE_ZERO_QTY_ITEMS) for that and i am using following code in WHEN-LIST-CHANGED trigger.
BEGIN
IF :XGEC_SW_ITEM_LOAD_ITEM_DETS_V.INCLUDE_ZERO_QTY_ITEMS = 'Y'
THEN
GO_BLOCK ('XGEC_SW_ITEM_LOAD_ITEM_DETS_V');
SET_BLOCK_PROPERTY ('XGEC_SW_ITEM_LOAD_ITEM_DETS_V',
default_where,
'1=1');
[code]....
I am able to filter the records but the list item is not displaying initial value and the value that i am selecting from poplist.
i have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
I have a complex requirement to be resolved, i have one table of quantities from where i want to show or select the data based on 2 criteria.
1) if the ps_qty is greater than 1000 then there should be two lines , like the qty should not be displayed greater than 1000, instead it should be lesser than 1000. 2) The weight should not be more than 50,000, it should be less than 50000 if its more it should be displayed as 2 lines.
The following is the test case.
CREATE TABLE OW_STAG_SHIP (PS_CODE VARCHAR2(12),PS_DESC VARCHAR2(30),PS_QTY NUMBER,PS_WT NUMBER); INSERT INTO OW_STAG_SHIP VALUES ('A','AAA',400,30000); -- this will be displayed as its because qty and wt are ok INSERT INTO OW_STAG_SHIP VALUES ('B','BBB',1100,4000); --Need to be displaed in two lines as qty is more than 1000 INSERT INTO OW_STAG_SHIP VALUES ('C','CCC',2500,6000); --Need to be displayed in three lines as qty is more INSERT INTO OW_STAG_SHIP VALUES ('D','DDD',600,60000); --Need to be displaed in two lines as wt is more. select * from ow_stag_ship
2. report parameters will be the bill_month & no_of_months
Based on above 2 parameters I need to scan through the data for BILL_MONTH <= '20-Jun-2012' and NO_OF_MONTHS <= 6 the other criteria being the AVG_IND in (1,2).
In brief the criteria will be to pick all BPRF_NO having AVG_ID in (1, 2) consecutively till a break (AVG_IND not in (1, 2) starting from the given BILL_MONTH and going below this period, that is BILL_MONTH <= '30-Jun-2012'.
For the below data, if my parameters are: BILL_MONTH <= '30-Jun-2012' and NO_OF_MONTHS <= 3, only the underscore added data should be picked (as they fulfill the criteria) and the report output will be like:
Here the NO_OF_TIMES is the count, that is no of times the BPRF_NO falls into the above mentioned criteria consecutively starting from the provided BILL_MONTH.
Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.
But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.
why it's failing to pull records if i try to query data in first case only.
I 've noticed transactional triggers, as a value for Query data block property, How can i use it, and is it really 's mode advantages than using from clause and procedure based with DMLs on non-based items?
I am trying to create a datablock based on a procedure , but im getting errors in compilation:
Errors are : 1) identifier 'HSM_WSH_DEL_UTIL.DEL_TBL' must be declared
2) PL/SQL ERROR 320 at line 7, column 27 the declaration of the type of this expression is incomplete or malformed
Heres my pkg spec and body for the data block:
CREATE OR REPLACE PACKAGE hsm_wsh_del_util IS TYPE del_record IS RECORD (delivery_id NUMBER); TYPE del_tbl IS TABLE OF del_record INDEX BY BINARY_INTEGER;
PROCEDURE do_query (p_del IN OUT del_tbl); END hsm_wsh_del_util; [Code] .....
I have a block in the form which is based of a table as data source and so when i query on the form using a execute_query inbuilt statement is fired and all the records in the table behind is retrived and displayed. Also if i need to update any record i can do in on the screen and use commit_form so that all the changes go into the underlying table. Now my problem is when i retrieving my records i want to filter those records based on some conditions to be displayed in the form and not all records to be retrieved. Is it possible to do it if I am using the execute_query inbuilt and my block is based of a table?
I am working on Oracle forms 10g (Windows 7 OS).I have got one requirement to show one LOV for COLOR table. Say i have three colors BLACK, RED, BLUE in color table. Suppose in a multi record block if a user selects BLACK from a lov for one record then for the next record lov it should show only RED and BLUE. How to filter the COLOR from a LOV record group query which are already selected?
Upon form load, TList will be populated with predefined item. The behavior i am trying to achieve is to have a text item so user could entered specific text which will then filter the values in TList .
i am looking to write a query that will count all NOT NULL queue values in a table that belongs to a certain group, but not sure how to write it. The following is what I have wrote, but just wanted to clarify how to implement count case based on 2 criterias.
select * count(case when queue is not null then 1 end) over(partition by group) as queue_orders from table_a;
For example if I have 10 jobs that belong to group 1, but I would like to count all group 1 jobs that have a queue value.
I have no data at the mo, just something that I am trying to start off.
I have the following query with analytic function but wrong results on the last column COUNT.
1)I am getting the output order by b.sequence_no column . This is a must. 2)COUNT Column :
I don't want the total count based on thor column hence there is no point in grouping by that column. The actual requirement to achieve COUNT is:
2a -If in the next row, if either the THOR and LOC combination changes to a new value, then COUNT=1 (In other words, if it is different from the following row)
2b-If the values of THOR and LOC repeats in the following row, then the count should be the total of all those same value rows until the rows become different. (In this case 2b-WHERE THE ROWS ARE SAME- also I only want to show these same rows only once. This is shown in the "MY REQUIRED OUTPUT) .
My present query: select r.name REGION , p.name PT, do.name DELOFF, ro.name ROUTE,
[code]...
My incorrect output[PART OF DATA]:Quote: REGIONPT DELOFF ROUTE THOR LOC SEQ COUNT NAASNAAS MAYNOOTHMAYNOOTHR010 DUBLINRD CEL 1 1 NAASNAAS MAYNOOTHMAYNOOTHR010 NEWTOWNRD CEL 2 1
[code]...
My required output[PART OF DATA]-:Quote: REGIONPT DELOFF ROUTE THOR LOC COUNT NAASNAAS MAYNOOTHMAYNOOTHR010 DUBLINRD CEL 1 NAASNAAS MAYNOOTHMAYNOOTHR010 NEWTOWNRD CEL 1 NAASNAAS MAYNOOTHMAYNOOTHR010 PRIMHILL CEL 1
[code]...
NOTE :Count as 1 is correctly coming.But where there is same rows and I want to take the total count on them, I am not getting.
I'm having difficulty trying to show a list of forms that I would like to present the user after they log on with SSO.
The funny thing is that I can show the user in a message box. However, I cannot filter records that the user should have on an "Application" menu. The application menu is designed to allow the user to launch the form by double clicking on the menu item. That works fine. It is just that I cannot seem to filter. It shows no records.
Here is my WNFI
DECLARE sso_user varchar2(40); nn number; v_vis VARCHAR2(20);
I want to start by saying I am brand new to SQL. I have an access database linked to my oracle and am trying to query a very specific set of data and I can't seem to narroe it down. I have 244,000 lines in the DB and I'm trying to find items on a specific trype of vendor agreement. I may have the same item on multiple agreements. ex 1-, 1a-, 2-,2a-,3-,3a-,4-,4a-,5-,5a-,6-,6a-,7-,7a-,8-,8a-.
I need to find a way to select just items that appear on a 4- or 4a- and no other agreement reference. The query I did so far pulls all of the 4- and 4a- agreements but will also pull items,like #233 in the example above, but not showing the 1a- agreement. I need it to overlook that item eventhough it does appear on the agreement I am looking for but also has an agreement I am not. the statement I am using right now is:
SELECT item, ven_item, ven_agrmt_ref, base_cost, vendor FROM "all items by agreement" WHERE ven_agrmt_ref >= '4' ORDER BY item
Is it possible to filter each row in a select statement based upon a list that you loop through. For instance in a where clause having an in statement but rather than selecting any value in the in statement looping the main select through each value in the "in" filter.
Eg:
CREATE TABLE TRANSAC( Item_id number , Transaction_Date DATE, Category_id number )
You could then write
SELECT MAX(Transaction_Date) from TRANSAC where category_id = '141'
and then where category_id = '142' then '143' etc.
I understand i could use an inline view instead of a where clause but the issue is that i already have the list of values I want to compare against.
Table 1Name Item DateJon Apples 06/11/2013 00:30:00 hrsSam OrangesNish Apples Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon Apples 8
I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar
I have 2 blocks, namely addsubject3rd and chosensubjects. I'm copying the selected records(this comes with checkboxes) from the 1st block to the second block. But what happened is that it copied all the records that are on the addsubject3rd. How can I copy only selected records(checkboxes) from addsubject3rd block to chosensubjects blocks?
i have a cursor which selects the codes based on the school records now i have a multi record block b1 the requirement is that if no of records selected in cusor is 10 then in block b1 10 records should be displayed and if it is 15 then 15 records should be displayed. Is there any way that i can set the no of records displayed in a block dynamically.The block b1 is non database block i am only using a create_record to populate the block.
I have datablock 'EMP', it's multi record block. I have a button 'CREATE', I have 'commit_form' in 'When button pressed' trigger, to commit records. It's working fine.
Now, i have a check box in the same block, i want to insert records which are checked only.
I am working in form 6i, EBS 11i. I have below requirement to achieve.
I have header data block A, line control block B. My requirement is, i know that in master detail block, if i press F11 in header block, it will clear the line block, like this, in my case if i press F11 in header data block A, i want to clear the line block B. Is there any way we can achieve this.
I have a Master block and a Detail Block. They are related using two columns, Document_ID and Page_no. I want to display all the records in master table with corresponding detail records beside them in single line. i.e., as shown below:
MasterItem1 DetailItem1 DetailItem2 DetailItem3
I created the relation between them and executed query. When I execute query in the form, I can find that all the Master Items are displayed in vertical records, but i can only see one record of detail fields. They are displayed as shown below:
Version information: Forms [32 Bit] Version 10.1.2.3.0 (Production) Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
When an user enters a value in a field of a Screen, I want to query up the next screen records based on that user input value in the previous screen.The previous screen is based on a control block i.e.
I'm using Oracle forms 10g. In the form i have this multiple records under one block. After performing the query, all records are cannot be updated. And i have this buttons at the right side that will enable the certain item that can only be updated. My problem is i cannot enable the item in the first record,instead of first record all records in that item was enable.
I am working in form 6i, EBS11i. I have a multi record data block, i am inserting checked records only using below logic.
ON-INSERT Trigger:
if checkbox_checked('block.checkbox') THEN insert_record; end if;
Requirement: Let us say, i have 4 records, i checked 2 records.. inserted them. Now if i want to insert other 2 unchecked records, it's not accepting, is it possible to insert records which are not checked after insertion.
I have made a travel booking system which comprises of 3 forms
1)Travel Booking form 2)Reservation Form 3)Cancellation Form
Under one booking number i can add multiple users in which they can have there multiple travels.
Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.
What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.
For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.
