Home » Developer & Programmer » Reports & Discoverer » Creating data link using a column only selected in one group
| Creating data link using a column only selected in one group [message #486255] |
Tue, 14 December 2010 08:44  |
davholla
Messages: 116
Registered: August 2009
Location: London
|
Senior Member |
|
|
I have a report with 2 groups Gheader and Glines.
The report looks at PO headers and lines. I want to create a data link from the the 2 queries based on the line id in po_lines_all.
However I only want to select this in the lines query so I do not get repeating records at the header query.
Does this make sense? If so, how do I do this?
|
|
|
|
|
|
| Re: Creating data link using a column only selected in one group [message #486265 is a reply to message #486256] |
Tue, 14 December 2010 09:45  |
davholla
Messages: 116
Registered: August 2009
Location: London
|
Senior Member |
|
|
cookiemonster wrote on Tue, 14 December 2010 14:51Not really.
Are you talking about oracle reports or oracle discoverer?
What's the relationship between the two tables? We do not have your tables.
Are they oracle apps tables?
You're going to have to flesh this out with more details if you want help.
Thanks for the help.
Oracle reports.
They are Oracle apps.
SELECT distinct decode(:P_sortby, 'PO NUMBER', null, poh.document_buyer_last_name)
, decode(:P_sortby, 'PO NUMBER', null, poh.document_buyer_first_name)
, decode(:po_num_type,'NUMERIC', null, poh.po_num)
, decode(:po_num_type,'NUMERIC', decode(rtrim(poh.po_num,'0123456789'),NULL,to_number(poh.po_num),-1), null)
, poh.po_type poh_po_type
, por.release_type poh_release_type
, poh.po_num || decode(poh.po_type,'RELEASE','-'|| por.release_num,null) poh_po_num
, poh.po_num security_poh_po_num
, por.release_num security_por_po_num
, poh.revision_num poh_revision_num
, poh.vendor_name poh_vendor_name
, poh.vendor_address_line1 poh_vendor_address_line1
, poh.vendor_address_line2 poh_vendor_address_line2
, poh.vendor_address_line3 poh_vendor_address_line3
, decode(poh.vendor_city,null,poh.vendor_state||' '|| poh.vendor_postal_code
, poh.vendor_city||', '||poh.vendor_state||' ' || poh.vendor_postal_code) poh_vendor_adr_info
, poh.vendor_country poh_vendor_country
, poh.customer_num poh_customer
, poh.vendor_num poh_vendor_num
, poh.creation_date poh_creation_date
, poh.revised_date poh_revised_date
--For BUG#3516972.Added a substr for the poh_buyer,poh_archive_buyer (outermost substr) with --parameter equal to the length of the buyer_name field in the report layout for normal completion of --the report.
, substr(substr(poh.document_buyer_first_name,1,1) ||' '||poh.document_buyer_last_name,1,12) poh_buyer
, substr(trim(substr(poh.archive_buyer_first_name,1,1) ||' '||poh.archive_buyer_last_name) ,1,12) poh_archive_buyer
, poh.document_buyer_agent_id poh_agent_id
, poh.payment_terms poh_payment_terms
, poh.ship_via poh_ship_via
, poh.fob poh_fob
, poh.freight_terms poh_freight_terms
, substr(poh.vendor_contact_first_name,1,1)||' '||substr(poh.vendor_contact_last_name ,1,10) poh_vendor_contact_name
, poh.vendor_phone poh_vendor_phone
, poh.vendor_contact_phone poh_vendor_contact_phone
, poh.note_to_vendor poh_note_to_vendor
, poh.printed_date poh_printed_date
, poh.amount_agreed poh_amount_agreed
, poh.cancel_flag poh_cancel_flag
, poh.confirming_order_flag poh_confirming_order_flag
, poh.acceptance_required_flag poh_acceptance_req_flag
, poh.acceptance_due_date poh_acceptance_due_date
, poh.currency_code poh_currency_code
, poh.currency_code C_CURRENCY
, poh.currency_name poh_currency_name
, poh.currency_conversion_rate poh_currency_conversion_rate
, poh.bill_to_location_id poh_bill_to_location
, pla.ship_to_location_id poh_ship_to_location
--, poh.ship_to_location_id poh_ship_to_location
, poh.po_header_id poh_po_header_id
, poh.po_release_id poh_po_release_id
, poh.po_type poh_po_type
, poh.approved_flag poh_approved_flag
, poh.print_count poh_print_count
, poh.effective_date poh_effective_date
, poh.expiration_date poh_expiration_date
, nvl(poh.po_release_id,-1) poh_join_release_id
, poh.vendor_site_id poh_vendor_site_id
, poh.vendor_id poh_vendor_id
, poh.po_header_id poh_po_header_id
, pll.po_line_id poh_line_id
FROM po_headers_print poh
, po_releases por
,po_line_locations_all pla, po_lines_all pll
WHERE poh.po_release_id = por.po_release_id (+)
AND poh.po_header_id = pla.po_header_id
and pll.po_header_id = poh.po_header_id
and pll.po_line_id = pla.po_line_id
and not upper (pll.item_description) like '%POSTAGE%FEE%'
--
AND ( nvl(por.release_num,-1)
BETWEEN nvl(:P_release_num_from,nvl(por.release_num,-1))
AND nvl(:P_release_num_to,nvl(por.release_num,-1))
OR poh.po_type != 'RELEASE')
AND poh.document_buyer_agent_id = nvl(:P_agent_id,poh.document_buyer_agent_id)
AND ( poh.release_date is NULL
OR trunc(poh.release_date)
BETWEEN nvl(:P_date_from, trunc(poh.release_date))
AND nvl(:P_date_to, trunc(poh.release_date) + 1) )
AND nvl(poh.approved_flag,'N') = nvl(:P_approved_flag,nvl(poh.approved_flag,'N'))
AND ((nvl(:P_print_releases,'Y') = 'Y') or (nvl(:P_print_releases,'Y') = 'N' and poh.po_type != 'RELEASE'))
AND nvl(poh.consigned_consumption_flag, 'N') <> 'Y'
AND nvl(por.consigned_consumption_flag, 'N') <> 'Y'
&where_performance
&where_clause
&C_ec_where_clause
&p_language_where
ORDER BY 1,2,3,4
And the second query
SELECT distinct pol.line_num pol_line_num
, pol.po_item_id pol_po_item_id
, pol.revision_num pol_item_revision
, pol.vendor_product_num pol_vendor_product_num
, pol.item_description pol_item_description
, round(pol.quantity_to_print,:P_QTY_PRECISION) pol_quantity_to_print
, nvl(&p_uom_col_name, pol.unit_of_measure) pol_unit_of_measure
, pol.price_to_print pol_price_to_print
, pol.amount_to_print C_AMOUNT_POL
, round(pol.quantity_comitted,:P_QTY_PRECISION) pol_quantity_comitted
, pol.un_number_and_desc pol_un_number_and_desc
, pol.hazard_class pol_hazard_class
, pol.cancel_flag pol_cancel_flag
, pol.cancel_date pol_cancel_date
, pol.note_to_vendor pol_note_to_vendor
, pol.contract_num pol_contract_num
, pol.po_quote_num pol_po_quote_num
, pol.vendor_quote_num pol_vendor_quote_num
, pol.quotation_line pol_quotation_line
, pol.po_header_id pol_po_header_id
, pol.po_line_id pol_po_line_id
, rownum pol_row_num
, pol.line_type pol_line_type
, nvl(pol.po_release_id,-1) pol_join_release_id
, nvl(pol.src_ga_flag,'N') pol_src_ga_flag
, pol.from_header_id pol_from_header_id
, pol.from_line_id pol_from_line_id
, pla.ship_to_location_id poh_ship_to_location
, pll.po_line_id
FROM po_lines_print pol , po_lines_all pll
,po_line_locations_all pla
WHERE &cancel_where_clause
AND decode ( :poh_po_type, 'BLANKET', decode(:P_BLANKET_LINES, 'Y' , -1, -2),
'PLANNED', decode(:P_BLANKET_LINES, 'Y' , -1, -2),
'CONTRACT', decode(:P_BLANKET_LINES, 'Y' , -1, -2),
-1 ) = -1
---Added lines to get the correct line id
AND pla.po_header_id = pol.po_header_id
-- Get the correct values
AND pll.po_header_id= pol.po_header_id
AND pla.po_line_id= pol.po_line_id
/*
AND pla.po_line_id = pol.po_line_id
and pll.po_line_id = pla.po_line_id
*/
order by pol.line_num
Now I want to link them based on the field po_lines_all.po_line_id
However I do NOT want this to be selected in the first query
as it means that I get multiple rows in the report.
Does that make sense?
|
|
|
|
| Re: Creating data link using a column only selected in one group [message #486274 is a reply to message #486265] |
Tue, 14 December 2010 10:21 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
davholla wrote on Tue, 14 December 2010 15:45
Now I want to link them based on the field po_lines_all.po_line_id
Link what to what exactly?
I don't have the apps tables, nor do most of the experts on this site as most of don't work with apps. You need to either explan your data model clearly or post in the oracle apps forums where people already know this information.
|
|
|
|
| Re: Creating data link using a column only selected in one group [message #486276 is a reply to message #486274] |
Tue, 14 December 2010 10:43 |
davholla
Messages: 116
Registered: August 2009
Location: London
|
Senior Member |
|
|
I want to create a data link in the report based on 3 fields selected in the child query but I only want to select 2 of the fields in the parent query.
So the data model is select fields a,b,c etc of table1, table2 and link table1.fieldy to the child query.
I can not really explain it another way - don't worry about if you don't understand it.
|
|
|
|
|
|
| Re: Creating data link using a column only selected in one group [message #486315 is a reply to message #486294] |
Tue, 14 December 2010 12:52 |
davholla
Messages: 116
Registered: August 2009
Location: London
|
Senior Member |
|
|
cookiemonster wrote on Tue, 14 December 2010 17:48If I don't understand it's unlikely anyone else reading this will either.
Looks suspiciously like you're joining the header query to po_lines_all to ensure that only headers that have a particular type of child record (po_lines_all) are returned.
Sound right?
Thanks for the persistence.
Yes that is right.
The child query is joined by po_header_id and po_line_id.
However I do not want to select po_line_id at header level as then I get several rows (one for each line) but at the moment if I do not this I can not form that data link.
However without this data link the child query picks up too many rows.
But with selecting the po_line_id at header level the header has too many rows (one for each po_line_id).
[Updated on: Tue, 14 December 2010 12:54] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Creating data link using a column only selected in one group [message #486452 is a reply to message #486255] |
Wed, 15 December 2010 11:21 |
davholla
Messages: 116
Registered: August 2009
Location: London
|
Senior Member |
|
|
Let me explain it better.
Query 1 (Qheaders) is linked to Q2 (Qlines).
Here are some scenarios
Scenario 1) If I link it via the line id then if a PO has 5 lines Qlines gives 5 lines which is correct.
Scenario 2)If I don't do this and link it via the header I get a cartesian products with all possible links.
The problem is that to create the link I have to select the line id at header level. This causes the header to print 5 times if there are 5 lines but if I don't have the link I get the problem in scenario (2) again.
|
|
|
|
| Re: Creating data link using a column only selected in one group [message #486454 is a reply to message #486452] |
Wed, 15 December 2010 11:35 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Trouble is that makes no sense.
This sounds like a basic master detail scenario - table A (qheaders) links to 1 or more records in table B (qlines) via a set of common columns.
As long as you select the common columns in both and use them to do the link you will only get the revelant detail lines.
You should never need to select columns from the detail query in the master query.
So you're going to have to explain why your specific case deviates from this basic concept.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 04:32:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
