[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
NOTE: We are doing a insert into bom_cto_order_lines_temp select .... using dyanamic sql
followed by a select ...bulk collect... from bom_cto_order_lines_temp. A more efficient
approach would be to do a direct select..bulk collect.. from oe_order_lines...
in the dyanamic sql instead of going via the GTT. However bulk collect with dyanamic sql
is supported 9i onwards. For 11.5.9, we need to make it compatible with 8i database as well.
This restriction may not be there in R12. Please keep this in mind while front porting.
*/
delete from bom_cto_order_lines_temp;
sql_stmt := 'INSERT INTO bom_cto_order_lines_temp (line_id, org_id, ship_from_org_id, schedule_ship_date, inventory_item_id) '||
'SELECT oel.line_id, oel.org_id, oel.ship_from_org_id, oel.schedule_ship_date, 1 '||
'from oe_order_lines_all oel, '||
' mtl_system_items msi, '||
' wf_item_activity_statuses was, '||
' wf_process_activities WPA '||
'where oel.inventory_item_id = msi.inventory_item_id '||
'and oel.ship_from_org_id = msi.organization_id '||
'and oel.source_type_code = ''EXTERNAL'' '||
'and msi.bom_item_type = 4 '||
'and oel.open_flag = ''Y'' '||
'and (oel.cancelled_flag is null '||
' or oel.cancelled_flag = ''N'') '||
'and oel.booked_flag = ''Y'' '||
'and oel.ordered_quantity > 0 '||
'and msi.replenish_to_order_flag = ''Y'' '||
'and msi.pick_components_flag = ''N'' '||
'and was.item_type = ''OEOL'' '||
'and was.activity_status = ''NOTIFIED'' '||
'and was.item_type = wpa.activity_item_type '||
'and was.process_activity = wpa.instance_id '||
'and wpa.activity_name in '||
'(''EXECUTECONCPROGAFAS'', ''CREATE_SUPPLY_ORDER_ELIGIBLE'', ''PURCHASE RELEASE ELIGIBLE'') ';
' (select oeh.header_id '||
' from oe_order_headers_all oeh, '||
' oe_transaction_types_tl oet, '||
' mtl_sales_orders mso '||
' where oeh.order_number = to_char( :p_sales_order) '||
' and oeh.order_type_id = oet.transaction_type_id '||
' and mso.segment1 = to_char(oeh.order_number) '||
' and mso.segment2 = oet.name '||
' and oet.language = (select language_code '||
' from fnd_languages'||
' where installed_flag = ''B'')' ||
' ) ' ;
sql_stmt := sql_stmt ||' and oel.line_id in (select oelc.line_id '||
'from oe_order_lines_all oelc '||
'where oelc.ato_line_id = :p_sales_order_line_id '||
'and (oelc.item_type_code = ''CONFIG'' '||
--Adding INCLUDED item type code for SUN ER#9793792
--' or (oelc.item_type_code in (''STANDARD'',''OPTION'') '||
' or (oelc.item_type_code in (''STANDARD'',''OPTION'',''INCLUDED'') '||
' and ato_line_id = line_id)) '||
') ';
'(select CAL.CALENDAR_DATE '||
' from bom_calendar_dates cal, '||
' mtl_parameters mp '||
' where mp.organization_id = oel.ship_from_org_id '||
' and cal.calendar_code = mp.calendar_code '||
' and cal.exception_set_id = mp.calendar_exception_set_id '||
' and cal.seq_num = '||
' (select cal2.prior_seq_num - '||
' (ceil(nvl(msi.fixed_lead_time,0) + '||
' nvl(msi.variable_lead_time,0) * '||
' INV_CONVERT.inv_um_convert '||
' (oel.inventory_item_id, '||
' null, '||
' oel.ordered_quantity , '||
' oel.order_quantity_uom, '||
' msi.primary_uom_code, '||
' null, '||
' null) '||
' )) '||
' from bom_calendar_dates cal2 '||
' where cal2.calendar_code = mp.calendar_code '||
' and cal2.exception_set_id = mp.calendar_exception_set_id '||
' and cal2.calendar_date =trunc(oel.schedule_ship_date) '||
' )) ';
we have selected. The drive_mark variable tells us which parameters
we are using, so we are sure to send the right ones to SQL.
*/
if (drive_mark = 0) then
-- No (optional) parameter is passed
EXECUTE IMMEDIATE sql_stmt;
select line_id, org_id, ship_from_org_id, schedule_ship_date
BULK COLLECT INTO line_id_arr, org_id_arr, ship_from_org_id_arr, schedule_ship_date_arr
from bom_cto_order_lines_temp;
select line_id into dummy
from oe_order_lines_all
where line_id = line_id_arr(i)
and source_type_code = 'EXTERNAL'
and open_flag = 'Y'
and booked_flag = 'Y'
and ordered_quantity > 0
and (cancelled_flag is null
or cancelled_flag = 'N')
and ship_from_org_id = ship_from_org_id_arr(i)
and schedule_ship_date = schedule_ship_date_arr(i)
FOR UPDATE NOWAIT;
update oe_order_lines_all
set program_id = l_program_id,
request_id = l_request_id
where line_id = line_id_arr(i);