Home » SQL & PL/SQL » SQL & PL/SQL » data in (insert into XXDSC_REVENUE_REPORT_DETAIL_MV) is not inserting
icon4.gif  data in (insert into XXDSC_REVENUE_REPORT_DETAIL_MV) is not inserting [message #248645] Fri, 29 June 2007 18:53 Go to next message
vamcs
Messages: 20
Registered: June 2007
Location: somerset
Junior Member

HI,
the table t1 contains millions of data, i need help in insertion with no(short) time.
The senario is before there is only the ledger columns and now the issue is they want to add the order columns and join them i actually wrote the query with the client spec but its taking hours so can any one help me out the information is below
insert into XXDSC_REVENUE_REPORT_DETAIL_MV
(
SOURCE,
bill_customer_name,
BILL_CUSTOMER_CLASS_CODE,
BILL_CUSTOMER_CATEGORY_CODE,
BILL_CUSTOMER_TYPE,
CUSTOMER,
GL_PERIOD, PERIOD_SET_NAME, PERIOD_YEAR, PERIOD_NUM,
COMPANY, SEGMENT, PRODUCT, ACCOUNT,
PROJECT, PROD_DESC, SEG_DESC, ACCT_DESC,
revenue_amount, set_of_books_id, je_category, je_source,
memo_or_gl_line_desc, gl_date, je_line_num, set_of_books,
code_combination_id, gl_abbrev,
-- adding the columns for order and line record
order_number ,Order_Header_ID,Header_ID,campaign_name,account_coordinator,
ordered_date,currency_code,closed_flag,purchase_order,sfa_order_id,order_source_id,
bill_to_site_use_id,ship_to_site_use_id,line_start_date,line_end_date,net_cpm,
revenue_rule,billing_rule,OM_SOURCE_REF,hard_end_date,line_ORIG_SYS_DOCUMENT_REF,
line_ORIG_SYS_LINE_REF,line_group_id,"Min$ Per Line Group", "Cap$ Per Month or Line Group",
languages,markets,billing_period,web_service_reference,tax_state_country,subscription_customer,
tax_value,tax_code,tax_date,open_quantity
)
SELECT 'GL' SOURCE,
SUBSTRB (NVL (hp.party_name, 'zManual Entry'),
1,
50
) bill_customer_name,
hca.customer_class_code bill_customer_class_code,
hp.category_code bill_customer_category_code,
hca.customer_type bill_customer_type ,
SUBSTRB (NVL (hp.party_name,
'zManual Entry'), 1, 50) customer,
period.period_name gl_period,
period.period_set_name period_set_name,
period.period_year period_year,
period.period_num period_num,
c.segment1 company,
c.segment2 SEGMENT,
c.segment3 product,
c.segment4 ACCOUNT,
c.segment5 project,
SUBSTRB (prod.description, 1, 150) prod_desc,
SUBSTRB (seg.description, 1, 150) seg_desc,
SUBSTRB (acct.description, 1, 150) acct_desc,
NVL (accounted_cr, 0) -
NVL (accounted_dr, 0) revenue_amount,
h.set_of_books_id set_of_books_id,
je_category je_category,
je_source je_source,
SUBSTRB (l.description, 1, 300) memo_or_gl_line_desc,
h.default_effective_date gl_date,
l.je_line_num je_line_num,
sob.NAME set_of_books,
c.code_combination_id code_combination_id,
SUBSTRB (( c.segment1
|| c.segment2
|| c.segment3
|| c.segment4
|| c.segment5
),
1,
40
) gl_abbrev,
f.ORDER_NUMBER order_number ,
f.header_id Order_Header_ID,
f.header_id Header_ID,
substrb(f.ATTRIBUTE5,1,150) campaign_name,
substrb(f.ATTRIBUTE6,1,50) account_coordinator,
f.ORDERED_DATE ordered_date,
f.transactional_curr_code currency_code,
decode(f.open_flag,'Y','N','Y') closed_flag,
f.cust_po_number purchase_order,
f.orig_sys_document_ref sfa_order_id,
f.order_source_id order_source_id,
f.INVOICE_TO_ORG_ID bill_to_site_use_id,
f.SHIP_TO_ORG_ID ship_to_site_use_id,
to_date(decode(length(ool.attribute1),
9,to_date(ool.attribute1,'DD-MON-RR'),
11,to_date(ool.attribute1,'DD-MON-RRRR'))
) line_start_date,
to_date(decode(length(ool.attribute2),
9,to_date(ool.attribute2,'DD-MON-RR'),
11,to_date(ool.attribute2,'DD-MON-RRRR'))
) line_end_date,
substrb(ool.ATTRIBUTE13,1,20) net_cpm,
substrb(ool.ATTRIBUTE3,1,50) revenue_rule,
substrb(ool.ATTRIBUTE4,1,50) billing_rule,
substrb(ool.ATTRIBUTE5,1,50) OM_SOURCE_REF,
substrb(ool.ATTRIBUTE6,1,20) hard_end_date,
substrb(ool.ORIG_SYS_DOCUMENT_REF,1,30) line_ORIG_SYS_DOCUMENT_REF,
substrb(ool.ORIG_SYS_LINE_REF,1,30) line_ORIG_SYS_LINE_REF,
substrb(ool.attribute7,1,50) line_group_id,
substrb(ool.attribute8,1,50) "Min$ Per Line Group",
substrb(ool.attribute9,1,50) "Cap$ Per Month or Line Group",
substrb(ool.ATTRIBUTE12,1,200) languages,
substrb(ool.ATTRIBUTE13,1,50) markets,
substrb(ool.attribute14,1,50) billing_period,
substrb(ool.attribute16,1,50) web_service_reference,
substrb(ool.attribute17,1,50) tax_state_country,
substrb(ool.attribute18,1,50) subscription_customer,
ool.tax_value tax_value,
ool.tax_code tax_code,
ool.tax_date tax_date,
ool.ORDERED_QUANTITY open_quantity
from
rep_admin.gl_periods period,
appsr.fnd_flex_values_vl prod,
appsr.fnd_flex_values_vl seg,
appsr.fnd_flex_values_vl acct,
rep_admin.gl_je_headers h,
rep_admin.gl_code_combinations c,
rep_admin.gl_sets_of_books sob,
--rep_admin.ra_customers cust,
rep_admin.hz_parties hp ,
rep_admin.hz_cust_accounts hca,
rep_admin.gl_je_lines l,
rep_admin.oe_order_headers_all f,
rep_admin.oe_order_lines_all ool,
(select f.ORDER_NUMBER order_number ,
f.header_id Order_Header_ID,
f.header_id Header_ID,
f.order_number||'-'||ool.line_number ord_det,
substrb(f.ATTRIBUTE5,1,150) campaign_name,
substrb(f.ATTRIBUTE6,1,50) account_coordinator,
f.ORDERED_DATE ordered_date,
f.transactional_curr_code currency_code,
decode(f.open_flag,'Y','N','Y') closed_flag,
f.cust_po_number purchase_order,
f.orig_sys_document_ref sfa_order_id,
f.order_source_id order_source_id,
f.INVOICE_TO_ORG_ID bill_to_site_use_id,
f.SHIP_TO_ORG_ID ship_to_site_use_id,
to_date(decode(length(ool.attribute1),
9,to_date(ool.attribute1,'DD-MON-RR'),
11,to_date(ool.attribute1,'DD-MON-RRRR'))
) line_start_date,
to_date(decode(length(ool.attribute2),
9,to_date(ool.attribute2,'DD-MON-RR'),
11,to_date(ool.attribute2,'DD-MON-RRRR'))
) line_end_date,
substrb(ool.ATTRIBUTE13,1,20) net_cpm,
substrb(ool.ATTRIBUTE3,1,50) revenue_rule,
substrb(ool.ATTRIBUTE4,1,50) billing_rule,
substrb(ool.ATTRIBUTE5,1,50) OM_SOURCE_REF,
substrb(ool.ATTRIBUTE6,1,20) hard_end_date,
substrb(ool.ORIG_SYS_DOCUMENT_REF,1,30) line_ORIG_SYS_DOCUMENT_REF,
substrb(ool.ORIG_SYS_LINE_REF,1,30) line_ORIG_SYS_LINE_REF,
substrb(ool.attribute7,1,50) line_group_id,
substrb(ool.attribute8,1,50) "Min$ Per Line Group",
substrb(ool.attribute9,1,50) "Cap$ Per Month or Line Group",
substrb(ool.ATTRIBUTE12,1,200) languages,
substrb(ool.ATTRIBUTE13,1,50) markets,
substrb(ool.attribute14,1,50) billing_period,
substrb(ool.attribute16,1,50) web_service_reference,
substrb(ool.attribute17,1,50) tax_state_country,
substrb(ool.attribute18,1,50) subscription_customer,
ool.tax_value tax_value,
ool.tax_code tax_code,
ool.tax_date tax_date,
ool.ORDERED_QUANTITY open_quantity from rep_admin.oe_order_headers_all f,
rep_admin.oe_order_lines_all ool where f.header_id=ool.header_id) ord
where h.je_header_id=l.je_header_id and
h.period_name=period.period_name and
l.code_combination_id=c.code_combination_id
and to_number(l.attribute11)=hca.account_number(+)
and hca.party_id = hp.party_id (+)
and c.segment3=prod.flex_value
and prod.flex_value_set_id=1002431 and
c.segment2=seg.flex_value and
seg.flex_value_set_id=1002434 and
c.segment4=acct.flex_value and
acct.flex_value_set_id=1002432 and
c.segment4 between 4000 and 4999 and
h.status='P' and
h.actual_flag='A' and
--and h.set_of_books_id=1
h.currency_code <> 'STAT' and
h.je_source not in ('Receivables','1','Consolidation','AR Translator') and
period.period_year >= 2004 and
nvl(accounted_cr,0)-nvl(accounted_dr,0) <> 0 and h.je_header_id not in (37841,37843,37845,37846,37849)
and h.set_of_books_id = sob.set_of_books_id and
l.attribute12 = ord.ord_det(+);
Re: data in (insert into XXDSC_REVENUE_REPORT_DETAIL_MV) is not inserting [message #248646 is a reply to message #248645] Fri, 29 June 2007 19:09 Go to previous message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>so can any one help me out
NO.
Do you honestly think anyone can tune SQL just by looking at it?
If this could be done, there would be a program to do it automagically.

Please read & FOLLOW ALL the posting guidelines at enumerated in the STICKY post at the top of this forum.

I am not about to waste any time on 180+ line UNFORMATTED SQL.
Previous Topic: CUBE/ROLLUP function
Next Topic: INSTR function (merged)
Goto Forum:
  


Current Time: Mon Dec 05 15:15:56 CST 2016

Total time taken to generate the page: 0.13234 seconds