Server Utilities :: How To Associate Header Record To Its Corresponding Detail Record
May 16, 2012
I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the
LOAD DATA
APPEND
INTO TABLE post1.thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(file_type POSITION(1:5)CHAR,
[code]....
A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).
THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002
What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?
View 11 Replies
ADVERTISEMENT
Oct 13, 2011
I want to delete Master / Detail Data through cursor between date 01-02-2010 till 10-02-2010. Problem is in Detail I dont have date column in detail. But I have to delete Master and Detail record with desire date. I have made a cursor but it delete only detail record i want to delete master record too.
Master Table
M_NO CHAR (12) NOT NULL,
REMARKS VARCHAR2 (200),
CANCEL_YN CHAR (1) NOT NULL,
M_DATE DATE NOT NULL,
PRIMARY KEY ( M_NO ) ) ;
Detail Structure
M_SNO NUMBER NOT NULL,
ACCOUNT_CODE CHAR (19) NOT NULL,
CANCEL_YN CHAR (1) NOT NULL,
M_DESC VARCHAR2 (200),
DB_AMT NUMBER,
CR_AMT NUMBER,
M_NO CHAR (12) NOT NULL,
PRIMARY KEY ( M_SNO, M_NO ) ) ;
create or replace procedure test as
cursor md_cur is
select m_No from master where m_Date
between '01-02-2010' and '10-02-2010';
[code]./....
View 9 Replies
View Related
Feb 1, 2013
I have create Master block and a Detail block.When i am clicking on the Master block, then details records are displaying.But when i m moving from 1st record of detail block to 2nd record of master block, then detail block's data is not not displaying.It just firing the validation of detail block's first field.
when from Detail block (Cursor on the field Mapping code field) clicking on the 2nd records i.e Ord.No. =2 , at that time validation is getting fired.)
What i really wants that if click on the 2nd record of Master, automatically records should display in detail block.
View 3 Replies
View Related
Sep 21, 2011
The SQL loader somehow is loading only first record. The data file is a csv and the end of line character is a new line. Some text fields have multiple new lines.
Here is my control file
load data
infile '/home/devo/c0397105/RuleImport/testLoad/dummyLoad.csv'
Truncate
into table DUMMY_LOAD_TABLE
fields terminated by "," optionally enclosed by '"'
( ID "to_number(:ID)",REQUESTED_GROUP,PURPOSE,COMMENTS)
[code]........
We don't have Retail resource type as Dependent System now; the rule will be changed later when Dependent Systems can accept resource types other than application"
View 1 Replies
View Related
Feb 5, 2011
I have master detail block just consider Forms A as a master and Forms B as a Detail,when my cursor is in form A i try to save the record, it is saved Form A and putting Form B data as null, i have to put a validation to stop this activity, i have to inform the user "Provide values for form B text item",
I created a post block trigger in form A
Begin
If (:FORM-B.TEXT1 is null)l or( :FORM-B.TEXT2 is null)
then
Message( 'provide value for FORM-B');
Message( 'provide value for FORM-B');
raise form_trigger_failure;
ELSE
NULL;
END IF
END;
Im Getting error message and navigation got stopped in last item of form A itself,i need to show error message and cursor control should move to first item of form -B which trigger i have to use for this.
View 1 Replies
View Related
Apr 22, 2010
My form consist two datablock cust_mstr,cust_dtls, each customer code consists two or three contact details records, when i delete the single detail record instead of deleting single record it deletes all the details records from cust_detail table.
Here by i attaching my form for your reference pls find the code in delete button
View 12 Replies
View Related
Apr 16, 2013
I am using APEX 4.0. I have created a master detail page , the business logic is “ We can save master record only if there is at least one detail record exist.”
But current wizard developed page has functionality that I have to save master first then only I can add detail record. I want to save them together.
View 0 Replies
View Related
Aug 1, 2010
I have an excel table whose data i have to transfer in oracle.
I did the following steps:
1) converted excel data in CSV format.
2) Created a control file test.ctl
LOAD DATA
INFILE 'C:IDB Price List.csv'
BADFILE 'C:IDB Price List.bad'
DISCARDFILE 'C:IDB Price List.dsc'
INSERT INTO TABLE idb_price_list
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(group_no,type,description,amt,flag)
3) when i run the sql loader utility, i get an error "sql-524: partial record found at end of datafile"
View 5 Replies
View Related
Jun 30, 2011
When i create report in master detail. When detail record end on the page, the second record show on same page. Problem is that detail show on other page. I want that if record is completed on that page then ok. Otherwise it show on next page.
View 3 Replies
View Related
Aug 9, 2010
I'm having a multi-record detail block in my form and after querying the form, when I scroll through the scroll bar, it is hanging (freezing) just before reaching the last record.
I have noticed that it never hangs if I use arrow keys to scroll between the records. It only hangs when I use scroll bar or page down key.
Regardless of the number of records fetched, it only hangs when I click the scroll bar (or press page down key) to fetch the last record.
It happens to all my custom forms and I'm developing this for Oracle EBS Apps 11.5.10.2. Oracle JInitiator Version is 1.3.1.21 and my form builder is Forms 6.0.8.28.0.
View 9 Replies
View Related
Mar 4, 2012
MASTER-DETAIL FORM HAVING AN LOV IN THE MASTER .LOV SELECTS THE PRIMARY KEY FIELD FROM THE MASTER TABLE AND IN LOV PUSHBUTTON HAVING A TRIGGER WHEN BUTTON PRESSED
Declare
bool boolean;
begin
bool := show_LOV('PURCHASELOV');
[code]...
WHEN SELECTING THE ITEM THROUGH LOV IT EXECUTES ALL VALUES BUT AFTER CHANGING ANY DATAS THEN NEW RECORD IS CREATED IN THE MASTER TABLE AND NO CHANGES OCCURED TO THE CHANGED RECORD.
View 4 Replies
View Related
Nov 10, 2010
1. My detail form has part number as one of the fields and I want to make sure the user can enter a part number only once. How can I make sure user cannot enter it more than once?
2. I want to limit number of detail records for every parent record to 8. How can I accomplish this?
3. I have approval flags at both master and detail. If the user tries to approve the master with some/all detail records unapproved, system should display error message and make the user approve the detail before approving the master.
View 2 Replies
View Related
Oct 11, 2011
I have created a sql loader control file to load a delimeter file in a table.
The file gets loaded into the table properly, however the commit is happening for every 64 records.
My aim is to rise the commit count by 1000 records.
View 1 Replies
View Related
Oct 7, 2010
I need to load a file with fields separated by '|^|' and at end of each record has '||*||'.
So in my ctl file what do i mention ? fields terminated by '|^|' ? for the record termination wat should I say?
Should I still mention 'trailing null col' in my ctl file...?
Sample data file:
Name|^|Age|^|city||*||
john|^|33|^|||*||
james|^||^|nyc||*||
ken|^|44|^|
washington||*||
the fields are properly terminated with |^| and the records are terminated with ||*||. Is it true that a file with |^| as field terminator cannot be loaded with sqlldr?
View 3 Replies
View Related
Jul 26, 2012
My procedure proc_ex is in when_validate_item trigger
I have one Multi Record data block in my form with values in its items
I need to Populate multi record block values to one Record Group using
add_group_row,
add_group_column,
set_group_char_cell to populate values to record group
Let us suppose my multi record data block looks like
item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80
how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies
View 3 Replies
View Related
Aug 26, 2013
I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name
Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
now i have to select below 2 rows how can write select qurie for this?
1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
View 4 Replies
View Related
Sep 13, 2011
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
View 4 Replies
View Related
Apr 21, 2010
I have the following case to solve:
Example Table:
Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00
View 8 Replies
View Related
Jun 29, 2013
I create a data block on a table when I am inserting new record only one record have been saved. Last record no longer exist.
View 4 Replies
View Related
Oct 21, 2010
My issues is when I changes the data in Header block the detail block should corresponding data immediately related to header on the detail lines.
The form is based on master detail relationship.
View 7 Replies
View Related
Sep 10, 2012
I have one multi record block in that i have one LOV..If i choose lov in first record that won't be display in second record lov...
View 4 Replies
View Related
Nov 22, 2012
Sometimes during recovery I encountered the following message "
This error occurs when Backup And Restore Occur between different versions
View 4 Replies
View Related
Aug 5, 2010
I have requirement of loading a part of the flat file that contains many headers and lines info. The program has to load the lines whose header recrd_type is 05 using SQL*LOADER.
eg of flat file.
Header
trans_code comp date rec_type
------------------------------------------------------------
8 12800002 0729201005
transcode_line acct date refrence
------------------------------------------------------------
4424604001002738307272010 24427330207710017569675
4424604001002738307272010 24427330207710017569675
View 7 Replies
View Related
May 16, 2013
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53 24
2013 March loc1 55007 78 57 28
2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing
---------------------------------------------------------------------------------------------------------------------------------
2013 January loc1 48037 24 57
2013 February loc1 47960 3660 53
2013 March loc1 55007 78 57
2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
View 5 Replies
View Related
Feb 22, 2013
SQL> update t set a = 1 where b = 2; -- must have redo record
2 rows updated.
SQL> rollback;
the above redo record that uncommit changed must be written from redo buffer to the online redo logfile. why Oracle write the redo record that uncommit changed to the online redo logfile ? when it will be used?
View 10 Replies
View Related
Apr 20, 2013
Is there any way to pass table of record as parameter to the plsql server page? For instance, I can define a new type in package specification:
create or replace package tst_type
as
type tab_expr_sec is table of varchar2(50) index by pls_integer;
type rec_expr is record (
prim_expr varchar2(50),
expr tab_expr_sec
);
[code]....
View 5 Replies
View Related
Jul 19, 2012
I am trying to record audit info about sql statement run by user (only one audit entry per specific type of operation such as create table, or insert table). Such as if a user create three tables, but database record only one entry of create table type per session.
I am giving you all the statement I issued...
SQL> create user saimon identified by abc1;
User created.
SQL> grant connect, resource to saimon;
Grant succeeded.
SQL> audit table, insert table by saimon by session;
Audit succeeded.
SQL> show parameter audit
NAME TYPE VALUE
-------------------- ----------- -------------
audit_file_dest string /u01/app/oracle/admin/orcl/adum
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DBSQL>
[oracle@DBTEST ~]$ sqlplus saimon/abc1
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 19 21:45:09 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL>
SQL> create table TB1 (id number, name varchar2(20));
Table created.
SQL> create table TB3 (id number, name varchar2(20));
Table created.
SQL> select USERNAME, TERMINAL, OS_USERNAME, USERHOST, ACTION, action_name, OBJ_NAMe
2 from user_audit_trail;
USERNAME TERMINAL OS_USERNAM USERHOST ACTION ACTION_NAME OBJ_NAME
---------- ---------- ---------- ----------------- ----------------------- ----------
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB3
SAIMON pts/4 oracle DBTEST 1 CREATE TABLE TB1
conn / as sysdba
Now my question is I have enabled statement auditing for session not by access. So only one audit entry should have been recorded for two table creation. Why database is recording every create statement?
SQL> show user
USER is "SYS"
SQL> SELECT audit_option, failure, success, user_name
2 FROM dba_stmt_audit_opts;
AUDIT_OPTION FAILURE SUCCESS USER_NAME
----------------------------------- ---------- ---------- ------------------------------
TABLE BY SESSION BY SESSION SAIMON
INSERT TABLE BY SESSION BY SESSION SAIMON
View 2 Replies
View Related
Oct 17, 2012
[URL] ...... Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
I have the following table
CREATE TABLE "ALLOCATEASSOCIATES"
( "PROJID" VARCHAR2(30) NOT NULL ENABLE,
"ASSOCIATEID" NUMBER(*,0) NOT NULL ENABLE,
"ALLOCATIONSTARTDATE" DATE,
"ALLOCATIONPERCENT" NUMBER(*,0),
[code]...
Given that
1. An associate must be allocated to at-least and a maximum of 100% at any given point of time
2. User selects 2 dates between which inconsistency of allocation needs to be displayed
If the end user selects 1st Apr 2012 and July 31st 2012 between which reports needs to be generated, am looking for the following output
ASSOCIATE_ID FROM_DATE TO_DATE ALLOCATION_INCONSISTENCY
2 01-APR-12 15-APR-12 75
2 16-APR-12 15-JUN-12 25
2 16-JUN-12 30-JUN-12 50
The Allocation_Inconsistency denotes that the associate has a deficit of allocation between the 2 dates. The associate with ID 2 has a deficit of 75% of allocation from 1st Apr 2012 till 15th Apr 2012. Similarly 25% deficit between 16th Apr 2012 and 15th June 2012 and so on so forth. However, there is no allocation deficit for the month of July as he is allocated 100% for this month and hence is not appearing in the expected output.
View 5 Replies
View Related
Nov 12, 2006
I have the following questions :
1. After creating a view, how do I associate that view with certain level of security. As in, only a few users must be allowed to access that view.
2. How can I create a new user-login and password for my database application?
View 1 Replies
View Related
Jul 19, 2013
my report is based on two tables.master and detail. i am using GROUP ABOVE style.displaying master tables columns up and detail table columns down.
my problem is ...i want 3 columns from master table (i.e)RECEIVER ID,RECEIVER NAME and RECEIVE DATE to be printed at the bottom of the page.
and also i want only 4 records from detail table to be printed on each page.
View 1 Replies
View Related
