Home » SQL & PL/SQL » SQL & PL/SQL » Passing parameter (11.5.10, 11i Bussiness suite,)
| Passing parameter [message #279123] |
Wed, 07 November 2007 01:13  |
alayam
Messages: 8
Registered: November 2006
Location: Hyderabad
|
Junior Member |
|
|
Hi! All,
I have developed a PL/SQL report ..
In Which I have given a paramter..
Like .. Where actual_ship_date = p_date
But I want to check 2 conditions here
like. . Where actual_ship_date = p_date
OR last_update_date = p_date
WHen i try this I'm getting outer join(+) will not allow you to use (IN OR )conditions.
So if i want to pass either of the paramter whats the procedure or syntax in where clause.
Your replys are appriciated and Thanks In advance.
Below is my original query.
SELECT p.party_name customer_name,
ca.account_number customer_number,
locs.country ship_to_country, locb.country bill_to_country,
TO_CHAR (ooha.order_number) order_number,
ooha.cust_po_number customer_po_number, otl.NAME order_type,
TO_CHAR (ooha.booked_date, 'MM/DD/YY') booked_date,
ooha.attribute1 end_customer,
oola.line_number
|| DECODE (oola.shipment_number,
NULL, NULL,
'.' || oola.shipment_number
)
|| DECODE (oola.option_number,
NULL, NULL,
'.' || oola.option_number
) line_number,
oola.flow_status_code order_line_status,
/*oe_line_status_pub.get_line_status
(oola.line_id,
oola.flow_status_code
) order_line_status,*/
msib.segment1 ordered_item, ood.organization_code warehouse,
TO_CHAR (oola.request_date, 'MM/DD/YY') request_date,
TO_CHAR (oola.schedule_ship_date,
'MM/DD/YY'
) schedule_ship_date,
TO_CHAR (oola.last_update_date,
'MM/DD/YY'
) last_update_date,
TO_CHAR (oola.actual_shipment_date,
'MM/DD/YY'
) actual_shipment_date,
oola.ordered_quantity
* DECODE (oola.line_category_code, 'ORDER', 1, -1)
order_line_quantity1,
abs(oola.ordered_quantity
* DECODE (oola.line_category_code, 'ORDER', 1, -1))
order_line_quantity,
abs(oola.tax_value
* DECODE (oola.line_category_code, 'ORDER', 1, -1)) tax,
oola.actual_shipment_date actual_shipment_date1 ,
oola.schedule_ship_date schedule_ship_date1,
abs(oola.unit_selling_price
* DECODE (oola.line_category_code, 'ORDER', 1, -1)
* oola.ordered_quantity) extended_price,
NVL (TO_NUMBER (oola.attribute2),
abs(oola.unit_selling_price
)) special_price,
NVL (TO_NUMBER (oola.attribute2),
abs(oola.unit_selling_price
)
* ( DECODE (oola.line_category_code, 'ORDER', 1, -1)
* oola.ordered_quantity
) )special_extended_price,
SUBSTR (jrs.NAME,
INSTR (jrs.NAME, ',') + 2,
LENGTH (jrs.NAME)
)
|| ' '
|| SUBSTR (jrs.NAME, 1, INSTR (jrs.NAME, ',') - 1)
salesperson,
oola.attribute1 quote_number,
abs(oola.unit_selling_price) unit_selling_price,
oola.split_by
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_cust_accounts ca,
hz_parties p,
hz_cust_site_uses_all csus,
hz_cust_acct_sites_all cass,
hz_party_sites pss,
hz_locations locs,
hz_cust_site_uses_all csub,
hz_cust_acct_sites_all casb,
hz_party_sites psb,
hz_locations locb,
oe_transaction_types_tl otl,
oe_transaction_types_tl otl2,
mtl_system_items_b msib,
hr_organization_units hou,
org_organization_definitions ood,
jtf_rs_salesreps jrs,
ra_territories rt,
mtl_categories mc,
mtl_item_categories mic,
mtl_category_sets mcs,
hr_operating_units hopu
WHERE TRUNC(oola.actual_shipment_date) = '04-SEP-2007'
OR TRUNC(oola.last_update_date) = '04-SEP-2007'
AND ooha.header_id = oola.header_id
AND ooha.sold_to_org_id = ca.cust_account_id
AND ca.party_id = p.party_id
AND oola.ship_to_org_id = csus.site_use_id
AND csus.cust_acct_site_id = cass.cust_acct_site_id
AND cass.party_site_id = pss.party_site_id
AND pss.location_id = locs.location_id
AND oola.invoice_to_org_id = csub.site_use_id
AND csub.cust_acct_site_id = casb.cust_acct_site_id
AND casb.party_site_id = psb.party_site_id
AND psb.location_id = locb.location_id
AND ooha.order_type_id = otl.transaction_type_id
AND otl.LANGUAGE = USERENV ('LANG')
AND oola.line_type_id = otl2.transaction_type_id
AND otl2.LANGUAGE = USERENV ('LANG')
AND oola.inventory_item_id = msib.inventory_item_id
AND oola.ship_from_org_id = msib.organization_id
AND oola.ship_from_org_id = ood.organization_id
AND oola.salesrep_id = jrs.salesrep_id
AND oola.org_id = jrs.org_id
AND csub.territory_id = rt.territory_id(+)
AND mic.inventory_item_id = oola.inventory_item_id
AND mic.organization_id = oola.ship_from_org_id
AND mcs.category_set_name = 'Inventory'
AND mcs.category_set_id = mic.category_set_id(+)
AND mic.category_id = mc.category_id(+)
AND ooha.booked_flag = 'Y'
-- AND oola.line_category_code = 'ORDER'
AND hopu.organization_id = ooha.org_id
--AND TRUNC(oola.actual_shipment_date) = /*'04-SEP-2007' */ fnd_date.canonical_to_date(p_date)
--'04-SEP-2007'
--AND oola.ordered_quantity < 0
--AND oola.actual_shipment_date IS NULL
--AND oola.cancelled_flag = 'y'
--AND oola.open_flag = 'Y'
--AND oola.flow_status_code IN ('CANCELLED', 'CLOSED')
AND hopu.organization_id =
DECODE ((SELECT 'VP'
FROM fnd_user_resp_groups furg,
fnd_responsibility_vl fr,
fnd_user_resp_groups_direct frd
WHERE furg.user_id =
fnd_profile.VALUE ('USER_ID')
AND furg.responsibility_id =
fr.responsibility_id
AND frd.responsibility_id =
fr.responsibility_id
AND furg.user_id = frd.user_id
AND TRUNC (SYSDATE) BETWEEN frd.start_date
AND NVL (frd.end_date,
TRUNC (SYSDATE)
)
AND fr.responsibility_key =
'OXSEMI_CUSTOM_REPORTS'),
'VP', hopu.organization_id,
fnd_profile.VALUE ('ORG_ID')
);
Thanks & regards,
Chandu
|
|
|
|
|
|
| Re: Passing parameter [message #279139 is a reply to message #279123] |
Wed, 07 November 2007 02:02  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Mon Dec 02 07:32:42 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
