[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT concatenated_segments
INTO l_inventory_item
FROM MTL_SYSTEM_ITEMS_kfv
WHERE INVENTORY_ITEM_ID = p_inventory_item_id
AND ORGANIZATION_ID = l_validation_org_id;
SELECT ATTRIBUTE_DISPLAY_NAME,
ATTRIBUTE_CODE
INTO l_display_name,
l_attribute_code
FROM OE_PC_ATTRIBUTES_V
WHERE ATTRIBUTE_ID = p_attribute_id
AND APPLICATION_ID=660;
SELECT NAME
INTO l_attribute_display_value
FROM RA_SALESREPS
WHERE salesrep_id = attribute_value and org_id = p_org_id;
SELECT LOCATION
INTO l_attribute_display_value
FROM HZ_CUST_SITE_USES_ALL
WHERE site_use_id = attribute_value
AND SITE_USE_CODE = 'DELIVER_TO'
AND ROWNUM = 1;
SELECT LOCATION
INTO l_attribute_display_value
FROM HZ_CUST_SITE_USES_ALL
WHERE site_use_id = attribute_value
AND SITE_USE_CODE = 'BILL_TO'
AND ROWNUM = 1;
SELECT name
INTO l_attribute_display_value
FROM HR_ORGANIZATION_UNITS
WHERE organization_id = attribute_value
AND ROWNUM = 1;
SELECT LOCATION
INTO l_attribute_display_value
FROM HZ_CUST_SITE_USES_ALL
WHERE site_use_id = attribute_value
AND SITE_USE_CODE = 'SHIP_TO'
AND ROWNUM = 1;
SELECT SUBSTR(HZP.PARTY_NAME,1,50) name
INTO l_attribute_display_value
FROM HZ_PARTIES HZP, HZ_CUST_ACCOUNTS HZC
WHERE HZC.cust_account_id = attribute_value
AND HZP.PARTY_ID = HZC.PARTY_ID;
SELECT meaning
INTO l_attribute_display_value
FROM OE_LOOKUPS
WHERE LOOKUP_TYPE = 'CALCULATE_PRICE_FLAG'
AND LOOKUP_CODE = attribute_value;
SELECT name
INTO l_attribute_display_value
FROM OE_DISCOUNTS_V
WHERE discount_id = attribute_value;
'UPDATED_FLAG',
'INCLUDE_ON_RETURNS_FLAG',
'ESTIMATED_FLAG',
'APPLIED_FLAG') THEN
IF attribute_value in ('Y','N') THEN
BEGIN
SELECT MEANING
INTO l_attribute_display_value
FROM OE_LOOKUPS
WHERE LOOKUP_TYPE = 'YES_NO'
AND LOOKUP_CODE = attribute_value;
select user_name
into l_attribute_display_value
from fnd_user
where user_id = attribute_value;
select responsibility_name
into l_attribute_display_value
from fnd_responsibility_tl fr
where fr.responsibility_id = attribute_value
and fr.application_id = FND_GLOBAL.RESP_APPL_ID
and fr.language = userenv('LANG');
select meaning
into l_attribute_display_value
from oe_lookups oel
where oel.lookup_type = 'CANCEL_CODE'
and oel.lookup_code = attribute_value;
SELECT set_name
INTO l_attribute_display_value
FROM OE_SETS_HISTORY
WHERE set_id = attribute_value
and rownum<2;
SELECT set_name
INTO l_attribute_display_value
FROM OE_SETS
WHERE set_id = attribute_value;
SELECT ENTITY_ID
,decode(ENTITY_ID, 1, 'oe_order_header_history',
2, 'oe_order_lines_history',
5, 'oe_sales_credit_history',
6, 'oe_price_adjs_history',
7, 'oe_sales_credit_history',
8, 'oe_price_adjs_history') entity_name
,ENTITY_DISPLAY_NAME
FROM OE_PC_ENTITIES_V
WHERE APPLICATION_ID = 660
AND ENTITY_ID < 1000
ORDER BY ENTITY_ID;
/* select all constrainable attributes*/
CURSOR C_ALL_ATTRIBUTES (ent_id number) IS
SELECT OEV.ATTRIBUTE_ID
,OEV.ATTRIBUTE_CODE
,OEV.COLUMN_NAME
,OEV.ATTRIBUTE_DISPLAY_NAME
FROM OE_PC_ATTRIBUTES_V OEV
WHERE OEV.ENTITY_ID = ent_id
AND OEV.APPLICATION_ID = 660
AND OEV.CONSTRAINTS_ENABLED_FLAG = 'Y';
/* Select currently constrained attributes only */
CURSOR C_CONST_ATTRIBUTES (ent_id number) IS
SELECT OEV.ATTRIBUTE_ID
,OEV.ATTRIBUTE_CODE
,OEV.COLUMN_NAME
,OEV.ATTRIBUTE_DISPLAY_NAME
FROM OE_PC_ATTRIBUTES_V OEV
WHERE OEV.ENTITY_ID = ent_id
AND OEV.APPLICATION_ID = 660
AND OEV.CONSTRAINTS_ENABLED_FLAG = 'Y'
AND EXISTS ( SELECT 'CONSTRAINT EXISTS FOR THIS COLUMN'
FROM OE_PC_CONSTRAINTS OEP
WHERE OEP.COLUMN_NAME = OEV.COLUMN_NAME
--14797876 start
--AND OEP.ON_OPERATION_ACTION IN (1,2)
AND OEP.ON_OPERATION_ACTION IN (1,2,0.5)
--14797876 end
AND OEP.ENTITY_ID = OEV.ENTITY_ID)
ORDER BY OEV.ATTRIBUTE_ID;
/* Check Whether the entity is constrained for update */
CURSOR IS_ENTITY_CONSTRAINED (v_ent_id NUMBER) IS
SELECT 'Y' -- There is an entity level constraint'
FROM OE_PC_CONSTRAINTS
WHERE ENTITY_ID = v_ent_id
AND COLUMN_NAME IS NULL
AND ON_OPERATION_ACTION IN (1,2)
AND CONSTRAINED_OPERATION <> 'X';
SELECT nvl(fnd_date.canonical_to_date(start_date),to_date('01/01/1950','MM/DD/RRRR')),
nvl(fnd_date.canonical_to_date(end_date), sysdate)
INTO min_hist_creation_date, max_hist_creation_date
FROM dual;
SELECT HEADER_ID
INTO l_header_id_from
FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = order_number_from;
SELECT HEADER_ID
INTO l_header_id_to
FROM OE_ORDER_HEADERS_ALL
WHERE ORDER_NUMBER = order_number_to;
l_header_id_stmt:= ' (SELECT header_id from oe_order_headers where order_number >='||order_number_from||' and org_id = '||l_input_org_id||')'; --8265428
l_header_id_stmt:= ' (SELECT header_id from oe_order_headers where order_number >='||order_number_from||')'; --8265428
l_header_id_stmt:= ' (SELECT Header_id from oe_order_headers where order_number <='||order_number_to||' and org_id = '||l_input_org_id||')'; --8265428
l_header_id_stmt:= ' (SELECT Header_id from oe_order_headers where order_number <='||order_number_to||')'; --8265428
l_header_id_stmt:= ' (SELECT Header_id from oe_order_headers where order_number between '||order_number_from||' and '||order_number_to||' and org_id = '||l_input_org_id||')'; --8265428
l_header_id_stmt:= ' (SELECT Header_id from oe_order_headers where order_number between '||order_number_from||' and '||order_number_to||')'; --8265428
l_cnt_stmt := 'select count(*) from ' || c_ent_rec.entity_name || ' where hist_creation_date between
to_date('''||To_Char(min_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and
to_date('''||To_Char(max_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and rownum=1 and '; --8265428
l_org_id_stmt := ' header_id IN (SELECT header_id from oe_order_headers where org_id = '||l_input_org_id||')'; --8265428
C_ATTR_TBL.delete;
l_id_stmt := ' select distinct header_id, header_id entity_number '||
' from oe_order_header_history where nvl(audit_flag, ''Y'') = ''Y'' and hist_creation_date between :m and :n and ';
l_id_stmt := ' select distinct header_id, line_id entity_number '
||' from oe_order_lines_history where nvl(audit_flag, ''Y'') = ''Y'' and hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') and hist_creation_date between :m and :n and ';
l_id_stmt := ' select distinct header_id, sales_credit_id entity_number ' ||
' from oe_sales_credit_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is null and hist_creation_date between :m and :n and ';
l_id_stmt := ' select distinct header_id, price_adjustment_id entity_number '||
' from oe_price_adjs_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is null and hist_creation_date between :m and :n and ';
l_id_stmt := ' select distinct header_id, sales_credit_id entity_number ' ||
' from oe_sales_credit_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is not null and hist_creation_date between :m and :n and ';
l_id_stmt := ' select distinct header_id, price_adjustment_id entity_number '||
' from oe_price_adjs_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is not null and hist_creation_date between :m and :n and ';
l_id_stmt := ' select distinct header_id, header_id entity_number '||
' from oe_order_header_history where nvl(audit_flag, ''Y'') = ''Y'' and hist_creation_date between
to_date('''||To_Char(min_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and
to_date('''||To_Char(max_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and ';
l_id_stmt := ' select distinct header_id, line_id entity_number '
||' from oe_order_lines_history where nvl(audit_flag, ''Y'') = ''Y'' and hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'')
and hist_creation_date between
to_date('''||To_Char(min_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and
to_date('''||To_Char(max_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and ';
l_id_stmt := ' select distinct header_id, sales_credit_id entity_number ' ||
' from oe_sales_credit_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is null and hist_creation_date between
to_date('''||To_Char(min_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and
to_date('''||To_Char(max_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and ';
l_id_stmt := ' select distinct header_id, price_adjustment_id entity_number '||
' from oe_price_adjs_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is null and hist_creation_date between
to_date('''||To_Char(min_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and
to_date('''||To_Char(max_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and ';
l_id_stmt := ' select distinct header_id, sales_credit_id entity_number ' ||
' from oe_sales_credit_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is not null and hist_creation_date between
to_date('''||To_Char(min_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and
to_date('''||To_Char(max_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and ';
l_id_stmt := ' select distinct header_id, price_adjustment_id entity_number '||
' from oe_price_adjs_history where nvl(audit_flag, ''Y'') = ''Y'' and line_id is not null and hist_creation_date between
to_date('''||To_Char(min_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and
to_date('''||To_Char(max_hist_creation_date,'DD-MON-RRRR HH24 MI SS')||''',''DD-MON-RRRR HH24 MI SS'') and ';
SELECT ORDER_NUMBER,SOLD_TO_ORG_ID,ORDER_TYPE_ID,ORG_ID
INTO l_order_number,l_sold_to_org_id,l_order_type_id,l_org_id
FROM OE_ORDER_HEADERS_ALL --bug14285026
WHERE HEADER_ID = l_header_id;
l_sql_stmt := 'select nvl(oer.reason_code, '
|| ' hist.reason_code) , hist_creation_date, '
|| ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
|| ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist '
|| ' , oe_reasons oer '
-- || ' where hist_type_code = '||''''||'UPDATE'||''''||' and '
|| ' where hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') and '
|| ' trunc(hist_creation_date) between :x and :z '
|| ' and hist.header_id = :y '
|| ' and hist.reason_id = oer.reason_id(+) '
|| l_ent_stmt;
l_sql_stmt := 'select nvl(oer.reason_code, '
|| ' hist.change_reason_code), hist_creation_date, '
|| ' hist_created_by , responsibility_id, nvl(oer.comments,change_reason_text) hist_comments, '
|| ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist '
|| ' , oe_reasons oer '
|| ' where '
|| ' trunc(hist_creation_date) between :x and :z '
|| ' and hist.header_id = :y '
|| ' and hist.reason_id = oer.reason_id(+) '
|| l_ent_stmt;
l_sql_stmt := 'select nvl(oer.reason_code, '
|| ' hist.reason_code), hist_creation_date, '
|| ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
|| ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist '
|| ' , oe_reasons oer '
|| ' where '
|| ' trunc(hist_creation_date) between :x and :z '
|| ' and hist.header_id = :y '
|| ' and hist.reason_id = oer.reason_id(+) '
|| ' and (hist.credit_card_number IS NOT NULL OR (hist.credit_card_number IS NULL AND hist.credit_card_holder_name IS NULL AND hist.credit_card_code IS NULL AND hist.credit_card_expiration_date IS NULL))'
|| l_ent_stmt;
l_sql_stmt := 'select nvl(oer.reason_code, '
|| ' hist.reason_code), hist_creation_date, '
|| ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
|| ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist '
|| ' , oe_reasons oer '
|| ' where '
|| ' trunc(hist_creation_date) between :x and :z '
|| ' and hist.header_id = :y '
|| ' and hist.reason_id = oer.reason_id(+) '
|| ' and (hist.credit_card_code IS NOT NULL OR (hist.credit_card_number IS NULL AND hist.credit_card_holder_name IS NULL AND hist.credit_card_code IS NULL AND hist.credit_card_expiration_date IS NULL))'
|| l_ent_stmt;
l_sql_stmt := 'select nvl(oer.reason_code, '
|| ' hist.reason_code), hist_creation_date, '
|| ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
|| ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist '
|| ' , oe_reasons oer '
|| ' where '
|| ' trunc(hist_creation_date) between :x and :z '
|| ' and hist.header_id = :y '
|| ' and hist.reason_id = oer.reason_id(+) '
|| ' and (hist.credit_card_holder_name IS NOT NULL OR (hist.credit_card_number IS NULL AND hist.credit_card_holder_name IS NULL AND hist.credit_card_code IS NULL AND hist.credit_card_expiration_date IS NULL))'
|| l_ent_stmt;
l_sql_stmt := 'select nvl(oer.reason_code, '
|| ' hist.reason_code), hist_creation_date, '
|| ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
|| ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist '
|| ' , oe_reasons oer '
|| ' where '
|| ' trunc(hist_creation_date) between :x and :z '
|| ' and hist.header_id = :y '
|| ' and hist.reason_id = oer.reason_id(+) '
|| ' and (hist.credit_card_expiration_date IS NOT NULL OR (hist.credit_card_number IS NULL AND hist.credit_card_holder_name IS NULL AND hist.credit_card_code IS NULL AND hist.credit_card_expiration_date IS NULL))'
|| l_ent_stmt;
l_sql_stmt := 'select nvl(oer.reason_code, '
|| ' hist.reason_code), hist_creation_date, '
|| ' hist_created_by , responsibility_id, nvl(oer.comments,hist_comments) hist_comments, '
|| ' hist.' || c_attr_tbl(j).column_name || ',hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist '
|| ' , oe_reasons oer '
|| ' where '
|| ' trunc(hist_creation_date) between :x and :z '
|| ' and hist.header_id = :y '
|| ' and hist.reason_id = oer.reason_id(+) '
|| l_ent_stmt;
will be updated with the new attribute value column */
l_old_db_rec_upd_flag := false;
SELECT count(*)
INTO l_count_hist
FROM oe_audit_attr_history
WHERE hist_creation_date = l_hist_creation_date
AND entity_number = l_entity_number
AND attribute_id = c_attr_tbl(j).attribute_id
AND entity_id = c_ent_rec.entity_id
and rownum = 1; -- Added for bug 7319059
select credit_card_code, credit_card_number
,credit_card_holder_name, credit_card_expiration_date
into l_credit_card_code, l_credit_card_number
,l_credit_card_holder_name, l_credit_card_expiration_date
from oe_order_header_history
where hist_creation_date = l_hist_creation_date
and header_id = l_header_id;
l_sql_stmt_last := 'select count(*) from '
|| c_ent_rec.entity_name || ' hist '
-- || ' where hist_type_code = '||''''||'UPDATE'||''''||' and '
|| ' where hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') and '
|| ' hist_creation_date > :x '
|| ' and header_id = :y '
|| l_ent_stmt;
l_sql_stmt_txn := 'select itev.instrument_id'||' ,ooh.Context '||
' from oe_order_headers_all ooh, oe_payments op, IBY_EXTN_INSTR_DETAILS_V itev '||
' where '||
' ooh.header_id = :y'||' and ooh.header_id = op.header_id and op.line_id is null and nvl(op.payment_collection_event,''PREPAY'') = ''INVOICE'' and op.trxn_extension_id = itev.trxn_extension_id';
l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||' ,Context '||
' from oe_order_headers_all '||
' where '||
' header_id = :y';
l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||' ,Context '||
' from oe_order_lines_all '||
' where '||
' line_id = :y';
l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||' ,Context '||
' from oe_sales_credits '||
' where '||
' sales_credit_id = :y';
l_sql_stmt_txn := 'select '
||c_attr_tbl(j).column_name || ',Context '
||' from oe_price_adjustments '
||' where '
||' price_adjustment_id = :y';
l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||',Context '||
' from oe_sales_credits '||
' where '||
' sales_credit_id = :y';
l_sql_stmt_txn := 'select '||c_attr_tbl(j).column_name||',Context '||
' from oe_price_adjustments'||
' where '||
' price_adjustment_id = :y';
'select hist_creation_date, nvl(oer.reason_code, change_reason_code), '
|| ' hist_created_by, responsibility_id, '
|| ' hist.' || c_attr_tbl(j).column_name || ', hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist, oe_reasons oer '
|| ' where hist_creation_date > :x '
|| ' and hist_type_code = '||''''||'UPDATE'||''''
|| ' and hist.header_id = :y '
|| ' and hist.reason_id = oer.reason_id(+) '
|| l_ent_stmt;
'select hist_creation_date, nvl(oer.reason_code, hist.reason_code), '
|| ' hist_created_by, responsibility_id, '
|| ' hist.' || c_attr_tbl(j).column_name || ', hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist, oe_reasons oer '
|| ' where hist_creation_date > :x '
-- || ' and hist_type_code = '||''''||'UPDATE'||''''
|| ' and hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') '
|| ' and hist.header_id = :y '
|| ' and hist.reason_id = oer.reason_id(+) '
|| l_ent_stmt;
'select hist_creation_date, nvl(oer.reason_code, hist.change_reason_code), '
|| ' hist_created_by, responsibility_id, '
|| ' hist.' || c_attr_tbl(j).column_name || ' ,hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist, oe_reasons oer '
|| ' where hist.reason_id = oer.reason_id(+) '
|| ' and hist.header_id = :y2 '
|| l_ent_stmt_subquery2
|| ' and hist.hist_creation_date = '
||' (Select hist_creation_date from (Select hist_creation_Date from '
||c_ent_rec.entity_name
|| ' where hist_creation_date > :x '
|| ' and hist_type_code = '||''''||'UPDATE'|| ''''
|| ' and header_id = :y '
|| l_ent_stmt_subquery
|| ' order by hist_creation_date asc) where rownum=1) ';
|| ' (select min(rowid) from '
|| c_ent_rec.entity_name
|| ' where hist_creation_date > :x '
|| ' and hist_type_code = '||''''||'UPDATE'|| ''''
|| ' and header_id = :y '
|| l_ent_stmt_subquery
|| ') ';*/
'select hist_creation_date, nvl(oer.reason_code, hist.reason_code), '
|| ' hist_created_by, responsibility_id, '
|| ' hist.' || c_attr_tbl(j).column_name || ' ,hist.context '
|| ' from '
|| c_ent_rec.entity_name || ' hist, oe_reasons oer '
|| ' where hist.reason_id = oer.reason_id(+) '
|| ' and hist.header_id = :y2 '
|| l_ent_stmt_subquery2
|| ' and hist.hist_creation_date = '
||' (Select hist_creation_date from (Select hist_creation_Date from '
||c_ent_rec.entity_name
|| ' where hist_creation_date > :x '
|| ' and hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') '
|| ' and header_id = :y '
|| l_ent_stmt_subquery
|| ' order by hist_creation_date asc) where rownum=1) ';
|| ' (select min(rowid) from '
|| c_ent_rec.entity_name
|| ' where hist_creation_date > :x '
-- || ' and hist_type_code = '||''''||'UPDATE' ||''''
|| ' and hist_type_code in (''UPDATE'',''CANCELLATION'',''SPLIT'') '
|| ' and header_id = :y '
|| l_ent_stmt_subquery
|| ') ';*/
select instrument_id
into l_last_instrument_id
from oe_order_header_history
where header_id = l_header_id
and hist_creation_date =
(select max(hist_creation_date)
from oe_order_header_history
where header_id = l_header_id);
oe_debug_pub.add('Inserting history records for order number => '||oe_pc_attr_tbl(i).order_number,1);
INSERT INTO OE_AUDIT_ATTR_HISTORY
(entity_id,
attribute_id,
reason_code,
hist_creation_date,
order_number,
user_id,
responsibility_id,
old_attribute_value,
new_attribute_value,
entity_number,
order_type_id,
org_id,
sold_to_org_id,
change_comments,
old_context_value,
new_context_value )
values
(oe_pc_attr_tbl(I).entity_id,
oe_pc_attr_tbl(I).attribute_id,
oe_pc_attr_tbl(I).reason_code,
oe_pc_attr_tbl(I).hist_creation_date,
oe_pc_attr_tbl(I).order_number,
oe_pc_attr_tbl(I).user_id,
oe_pc_attr_tbl(I).responsibility_id,
oe_pc_attr_tbl(I).old_attribute_value,
oe_pc_attr_tbl(I).new_attribute_value,
oe_pc_attr_tbl(I).entity_number,
oe_pc_attr_tbl(I).order_type_id,
oe_pc_attr_tbl(I).org_id,
oe_pc_attr_tbl(I).sold_to_org_id,
oe_pc_attr_tbl(I).change_comments,
oe_pc_attr_tbl(I).old_context_value, --Bug4324371
oe_pc_attr_tbl(I).new_context_Value );
OE_DEBUG_PUB.add(' In EXCEPTION : After Insert in OE_AUDIT_ATTR_HISTORY ', 5);
oe_pc_attr_tbl.delete(i);
oe_pc_attr_tbl.delete;
oe_pc_attr_tbl.delete; -- bug# 9067627 : Delete these tables when above INSERT encounters some error(s)
SELECT ATTRIBUTE_DISPLAY_NAME,COLUMN_NAME
INTO p_display_name,p_column_name
FROM OE_PC_ATTRIBUTES_V
WHERE ATTRIBUTE_ID = p_attribute_id;
SELECT NVL(FORM_LEFT_PROMPT,FORM_ABOVE_PROMPT)
INTO p_column_label
FROM FND_DESCR_FLEX_COL_USAGE_VL
WHERE APPLICATION_ID=660
AND APPLICATION_COLUMN_NAME=p_appl_column_name
AND DESCRIPTIVE_FLEXFIELD_NAME=p_flexfield_name
AND DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('Global Data Elements',p_context_value);
SELECT NVL(FORM_LEFT_PROMPT,FORM_ABOVE_PROMPT)
INTO p_column_label
FROM FND_DESCR_FLEX_COL_USAGE_VL
WHERE APPLICATION_ID=660
AND APPLICATION_COLUMN_NAME=p_appl_column_name
AND DESCRIPTIVE_FLEXFIELD_NAME=p_flexfield_name
AND DESCRIPTIVE_FLEX_CONTEXT_CODE = p_old_context_value;
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO v_context
FROM FND_DESCR_FLEX_COL_USAGE_VL
WHERE APPLICATION_ID=660
AND APPLICATION_COLUMN_NAME=p_appl_column_name
AND DESCRIPTIVE_FLEXFIELD_NAME=p_flexfield_name
AND DESCRIPTIVE_FLEX_CONTEXT_CODE IN ('Global Data Elements',p_context_value);
v_selectstmt VARCHAR2(2000) ; --dhgupta changed length from 500 to 2000 for bug # 1888160
v_selectstmt := 'SELECT '||v_cols||' FROM '||p_table_r.table_name||' '||v_where_clause;
oe_debug_pub.add('select stmt'||v_selectstmt);
DBMS_SQL.PARSE(v_cursor_id,v_selectstmt,DBMS_SQL.V7);
SELECT instrument_id, credit_card_expiration_date,credit_card_number,credit_card_code
INTO l_old_instrument_id,l_old_exp_date,l_old_cc_number, l_old_cc_code
FROM oe_order_header_history
WHERE header_id = p_header_id
AND hist_creation_date = p_old_hist_creation_date;
SELECT instrument_id,credit_card_expiration_date, credit_card_number,credit_card_code
INTO l_new_instrument_id,l_new_exp_date,l_new_cc_number, l_new_cc_code
FROM oe_order_header_history
WHERE header_id = p_header_id
AND hist_creation_date = p_new_hist_creation_date;
SELECT instrid
INTO l_old_instrument_id
FROM iby_creditcard_h
WHERE card_history_change_id = l_old_instrument_id;
SELECT instrid
INTO l_new_instrument_id
FROM iby_creditcard_h
WHERE card_history_change_id = l_new_instrument_id;
l_sql_stmt := 'select '
|| l_column_name
|| ' from iby_creditcard'
|| ' where instrid = :x ';
l_old_sql_stmt := 'select '
|| l_column_name
|| ' from iby_creditcard_h'
|| ' where card_history_change_id = :x ';
l_old_sql_stmt := 'select '
|| l_column_name
|| ' from iby_creditcard'
|| ' where instrid = :x ';
l_new_sql_stmt := 'select '
|| l_column_name
|| ' from iby_creditcard_h'
|| ' where card_history_change_id = :x ';
l_new_sql_stmt := 'select '
|| l_column_name
|| ' from iby_creditcard'
|| ' where instrid = :x ';
l_sql_stmt := 'select '
|| l_column_name
|| ' from iby_creditcard_h'
|| ' where card_history_change_id = :x ';
l_sql_stmt := 'select '
|| l_column_name
|| ' from iby_creditcard'
|| ' where instrid = :x ';
select set_name,set_type into l_set_name,l_set_type
from oe_sets
where set_id=p_set_id;
insert into OE_SETS_HISTORY
(set_id,
set_name,
set_type,
line_id,
header_id,
last_update_date,
last_updated_by,
creation_date,
created_by)
values(
p_set_id,
l_set_name,
l_set_type,
p_line_id,
p_header_id,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID );
oe_debug_pub.add('Error in inserting data in sets history');
PROCEDURE DELETE_SET_HISTORY(
p_line_id IN number,
x_return_status OUT NOCOPY varchar2 ) is
begin
delete from OE_SETS_HISTORY
where line_id=p_line_id;