[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select h.org_id, h.header_id, 0 line_id, 0 line_number, 0 shipment_number, h.order_number, party.party_name account_name, tt.name order_type, h.transactional_curr_code
from oe_order_headers h,
hz_parties party,
hz_cust_accounts cust,
oe_transaction_types_tl tt
where h.open_flag = 'Y'
and h.order_number between nvl(p_order_number_low, h.order_number) and nvl(p_order_number_high, h.order_number)
and h.order_type_id = nvl(p_order_type_id, h.order_type_id)
and NVL(h.sold_to_org_id,-1) = nvl(p_customer_id, NVL(h.sold_to_org_id,-1))
and nvl(h.ship_to_org_id, -1) = nvl(p_ship_to_org_id, nvl(h.ship_to_org_id, -1))
and nvl(h.invoice_to_org_id, -1) = nvl(p_invoice_to_org_id, nvl(h.invoice_to_org_id, -1))
and nvl(h.salesrep_id, -1) = nvl(p_salesrep_id, nvl(h.salesrep_id, -1))
and h.ordered_date between nvl(l_order_date_low, h.ordered_date) and nvl(l_order_date_high, h.ordered_date)
and h.creation_date between nvl(l_order_creation_date_low, h.creation_date) and nvl(l_order_creation_date_high, h.creation_date)
and nvl(h.booked_date, sysdate) between nvl(l_booked_date_low, nvl(h.booked_date, sysdate)) and nvl(l_booked_date_high, nvl(h.booked_date, sysdate))
and h.booked_flag = decode(p_booked_orders, 'Y', 'Y', 'N', 'N', NULL, h.booked_flag)
and h.sold_to_org_id = cust.cust_account_id(+)
and cust.party_id = party.party_id (+)
and nvl(cust.customer_class_code, '-1') = nvl(p_customer_class_code, nvl(cust.customer_class_code, '-1'))
and NVL(h.price_list_id,-1) = nvl(p_price_list_id, NVL(h.price_list_id,-1))
and tt.transaction_type_id = h.order_type_id
and tt.language = userenv('LANG')
order by h.org_id, h.header_id;
select l.org_id, l.header_id, l.line_id, l.line_number, l.shipment_number, h.order_number, party.party_name account_name, tt.name order_type, h.transactional_curr_code
from oe_order_headers h,
oe_order_lines l,
mtl_item_categories ic,
mtl_default_category_sets cs,
hz_cust_accounts cust,
hz_parties party,
oe_transaction_types_tl tt
where h.open_flag = 'Y'
and h.order_number between nvl(p_order_number_low, h.order_number) and nvl(p_order_number_high, h.order_number)
and h.order_type_id = nvl(p_order_type_id, h.order_type_id)
and NVL(h.sold_to_org_id,-1) = nvl(p_customer_id, NVL(h.sold_to_org_id,-1))
and h.ordered_date between nvl(l_order_date_low, h.ordered_date) and nvl(l_order_date_high, h.ordered_date)
and h.creation_date between nvl(l_order_creation_date_low, h.creation_date) and nvl(l_order_creation_date_high, h.creation_date)
and nvl(h.booked_date, sysdate) between nvl(l_booked_date_low, nvl(h.booked_date, sysdate)) and nvl(l_booked_date_high, nvl(h.booked_date, sysdate))
and h.booked_flag = decode(p_booked_orders, 'Y', 'Y', 'N', 'N', NULL, h.booked_flag)
and l.header_id = h.header_id
and l.line_type_id = nvl(p_line_type_id, l.line_type_id)
and l.open_flag = 'Y'
and NVL(l.price_list_id,-1) = nvl(p_price_list_id, NVL(l.price_list_id,-1))
and nvl(l.ship_to_org_id, -1) = nvl(p_ship_to_org_id, nvl(l.ship_to_org_id, -1))
and nvl(l.invoice_to_org_id, -1) = nvl(p_invoice_to_org_id, nvl(l.invoice_to_org_id, -1))
and nvl(l.salesrep_id, -1) = nvl(p_salesrep_id, nvl(l.salesrep_id, -1))
and NVL(l.ship_from_org_id,-1) = nvl(p_ship_from_org_id, NVL(l.ship_from_org_id,-1))
and l.inventory_item_id = nvl(p_inventory_item_id, l.inventory_item_id)
and l.creation_date between nvl(l_line_creation_date_low, l.creation_date) and nvl(l_line_creation_date_high, l.creation_date)
and nvl(l.pricing_date, sysdate) between nvl(l_pricing_date_low, nvl(l.pricing_date, sysdate)) and nvl(l_pricing_date_high, nvl(l.pricing_date, sysdate))
and nvl(l.schedule_ship_date, sysdate) between nvl(l_schedule_ship_date_low, nvl(l.schedule_ship_date, sysdate)) and nvl(l_schedule_ship_date_high, nvl(l.schedule_ship_date, sysdate))
and ic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID',l.org_id)
and ic.inventory_item_id = l.inventory_item_id
and ic.category_set_id = cs.category_set_id
and ic.category_id = nvl(p_item_category_id, ic.category_id)
and cs.functional_area_id = 7
and nvl(cust.customer_class_code, '-1') = nvl(p_customer_class_code, nvl(cust.customer_class_code, '-1'))
and h.sold_to_org_id = cust.cust_account_id(+)
and cust.party_id = party.party_id (+)
and tt.transaction_type_id = h.order_type_id
and tt.language = userenv('LANG')
order by l.org_id, l.header_id, l.line_number, l.shipment_number, l.option_number;
select FND_DATE.Canonical_To_Date(p_order_date_low),
FND_DATE.Canonical_To_Date(p_order_date_high),
FND_DATE.Canonical_To_Date(p_order_creation_date_low),
FND_DATE.Canonical_To_Date(p_order_creation_date_high),
FND_DATE.CHARDT_TO_DATE(p_line_creation_date_low),
FND_DATE.CHARDT_TO_DATE(p_line_creation_date_high),
FND_DATE.Canonical_To_Date(p_booked_date_low),
FND_DATE.Canonical_To_Date(p_booked_date_high),
FND_DATE.CHARDT_TO_DATE(p_pricing_date_low),
FND_DATE.CHARDT_TO_DATE(p_pricing_date_high),
FND_DATE.CHARDT_TO_DATE(p_schedule_ship_date_low),
FND_DATE.CHARDT_TO_DATE(p_schedule_ship_date_high)
into l_order_date_low,
l_order_date_high,
l_order_creation_date_low,
l_order_creation_date_high,
l_line_creation_date_low,
l_line_creation_date_high,
l_booked_date_low,
l_booked_date_high,
l_pricing_date_low,
l_pricing_date_high,
l_schedule_ship_date_low,
l_schedule_ship_date_high
from dual;