[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'Y',
cust.cust_account_id
INTO p_out_IDfound ,
l_Custid
FROM HZ_CUST_ACCOUNTS_ALL cust ,
HZ_CUST_ACCT_SITES_ALL site,
hz_cust_site_uses_all siteuse
WHERE siteuse.site_use_id = p_in_site_id
AND site.cust_acct_site_id = siteuse.cust_acct_site_id
AND siteuse.site_use_code ='BILL_TO'
AND siteuse.status = 'A'
AND cust.cust_account_id =site.cust_account_id ;
g_hold_header_id.DELETE;
g_hold_line_id.DELETE;
g_hold_Source_Id.DELETE;
g_hold_ship_set.DELETE;
g_hold_arrival_set.DELETE;
g_hold_top_model_line_id.DELETE;
g_hold_activity_name.DELETE;
g_hold_source_rec.HOLD_ID.DELETE;
g_hold_source_rec.HOLD_SOURCE_ID.DELETE;
g_hold_source_rec.HOLD_ENTITY_CODE.DELETE;
g_hold_source_rec.HOLD_ENTITY_ID.DELETE;
SELECT HS.HOLD_SOURCE_ID,
HS.HOLD_ENTITY_ID,
HS.HOLD_ENTITY_CODE,
HS.HOLD_ENTITY_ID2,
HS.HOLD_ENTITY_CODE2,
HLD.ACTIVITY_NAME,
HS.HOLD_ID --ER# 3667551
FROM OE_HOLD_SOURCES HS,
OE_HOLD_DEFINITIONS HLD
WHERE --ER#7479609 HS.HOLD_ENTITY_CODE IN ('C','I','B','W','S')
HS.HOLD_ENTITY_CODE IN ('C','I','B','D','W','S','PL','OT','CD','SC','P','SM','EC','IC') --ER#7479609 --ER# 12571983 added ,'EC' --ER# 13331078 added 'IC'
AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
ROUND( SYSDATE )
AND HS.RELEASED_FLAG = 'N'
AND HLD.HOLD_ID = HS.HOLD_ID
AND SYSDATE
BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
ORDER BY HS.HOLD_ENTITY_CODE,HS.HOLD_ENTITY_CODE2,HS.HOLD_ENTITY_ID,
HS.HOLD_ENTITY_ID2;
g_hold_header_id.DELETE;
g_hold_line_id.DELETE;
g_hold_Source_Id.DELETE;
g_hold_ship_set.DELETE;
g_hold_arrival_set.DELETE;
g_hold_top_model_line_id.DELETE;
g_hold_activity_name.DELETE;
Customer_Pointer.DELETE;
Item_Pointer.DELETE;
ship_to_Pointer.DELETE;
bill_to_Pointer.DELETE;
Warehouse_Pointer.DELETE;
PriceList_Pointer.DELETE;
OrderType_Pointer.DELETE;
CreationDate_Pointer.DELETE;
SalesChannel_Pointer.DELETE;
PaymentType_Pointer.DELETE;
ShipMethod_Pointer.DELETE;
deliver_to_Pointer.DELETE;
Item_Customer_Pointer.DELETE;
Item_shipto_Pointer.DELETE;
Item_Billto_Pointer.DELETE;
Item_Warehouse_Pointer.DELETE;
Item_ShipMethod_Pointer.DELETE;
Item_Deliverto_Pointer.DELETE;
Item_PriceList_Pointer.DELETE;
Item_SourceType_Pointer.DELETE;
Item_LineType_Pointer.DELETE;
Warehouse_Customer_Pointer.DELETE;
Warehouse_shipto_Pointer.DELETE;
Warehouse_Billto_Pointer.DELETE;
Warehouse_LineType_Pointer.DELETE;
Warehouse_ShipMethod_Pointer.DELETE;
Warehouse_Deliverto_Pointer.DELETE;
Warehouse_SourceType_Pointer.DELETE;
Cust_SourceType_Pointer.DELETE;
Cust_Billto_Pointer.DELETE;
Cust_Shipto_Pointer.DELETE;
Cust_Deliverto_Pointer.DELETE;
Cust_PriceList_Pointer.DELETE;
Cust_LineType_Pointer.DELETE;
Cust_PayTerm_Pointer.DELETE;
Cust_OrderType_Pointer.DELETE;
Cust_PaymentType_Pointer.DELETE;
Cust_Curr_Pointer.DELETE;
Cust_SalesChannel_Pointer.DELETE;
PriceList_Curr_Pointer.DELETE;
OrderType_Curr_Pointer.DELETE;
OrderType_LineType_Pointer.DELETE;
CreDate_CreBy_Pointer.DELETE;
Customer_Hold.DELETE;
Item_Hold.DELETE;
ship_to_Hold.DELETE;
bill_to_Hold.DELETE;
Warehouse_Hold.DELETE;
PriceList_Hold.DELETE;
OrderType_Hold.DELETE;
CreationDate_Hold.DELETE;
SalesChannel_Hold.DELETE;
PaymentType_Hold.DELETE;
ShipMethod_Hold.DELETE;
deliver_to_Hold.DELETE;
Item_Customer_Hold.DELETE;
Item_shipto_Hold.DELETE;
Item_Billto_Hold.DELETE;
Item_Warehouse_Hold.DELETE;
Item_ShipMethod_Hold.DELETE;
Item_Deliverto_Hold.DELETE;
Item_PriceList_Hold.DELETE;
Item_SourceType_Hold.DELETE;
Item_LineType_Hold.DELETE;
Warehouse_Customer_Hold.DELETE;
Warehouse_shipto_Hold.DELETE;
Warehouse_Billto_Hold.DELETE;
Warehouse_LineType_Hold.DELETE;
Warehouse_ShipMethod_Hold.DELETE;
Warehouse_Deliverto_Hold.DELETE;
Warehouse_SourceType_Hold.DELETE;
Cust_SourceType_Hold.DELETE;
Cust_Billto_Hold.DELETE;
Cust_Shipto_Hold.DELETE;
Cust_Deliverto_Hold.DELETE;
Cust_PriceList_Hold.DELETE;
Cust_LineType_Hold.DELETE;
Cust_PayTerm_Hold.DELETE;
Cust_OrderType_Hold.DELETE;
Cust_PaymentType_Hold.DELETE;
Cust_Curr_Hold.DELETE;
Cust_SalesChannel_Hold.DELETE;
PriceList_Curr_Hold.DELETE;
OrderType_Curr_Hold.DELETE;
OrderType_LineType_Hold.DELETE;
CreDate_CreBy_Hold.DELETE;
EndCust_Pointer.Delete;
Item_EndCust_Pointer.Delete;
Warehouse_EndCust_Pointer.Delete;
EndCust_SourceType_Pointer.Delete;
EndCust_Billto_Pointer.Delete;
EndCust_Shipto_Pointer.Delete;
EndCust_Deliverto_Pointer.Delete;
EndCust_PriceList_Pointer.Delete;
EndCust_LineType_Pointer.Delete;
EndCust_PayTerm_Pointer.Delete;
EndCust_OrderType_Pointer.Delete;
EndCust_PaymentType_Pointer.Delete;
EndCust_Curr_Pointer.Delete;
EndCust_SalesChannel_Pointer.Delete;
EndCust_EndCustLoc_Pointer.Delete;
EndCust_Hold.Delete;
Item_EndCust_Hold.Delete;
Warehouse_EndCust_Hold.Delete;
EndCust_SourceType_Hold.Delete;
EndCust_Billto_Hold.Delete;
EndCust_Shipto_Hold.Delete;
EndCust_Deliverto_Hold.Delete;
EndCust_PriceList_Hold.Delete;
EndCust_LineType_Hold.Delete;
EndCust_PayTerm_Hold.Delete;
EndCust_OrderType_Hold.Delete;
EndCust_PaymentType_Hold.Delete;
EndCust_Curr_Hold.Delete;
EndCust_SalesChannel_Hold.Delete;
EndCust_EndCustLoc_Hold.Delete;
select mic.category_id
into l_itemcategory_id
from mtl_item_categories mic,
mtl_default_category_sets mdc
where mic.inventory_item_id = p_line_rec.inventory_item_id
and mic.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') --13653352
AND mdc.functional_area_id=7
AND mdc.category_set_id = mic.category_set_id;
INSERT INTO OE_ORDER_HOLDS
( ORDER_HOLD_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, HOLD_SOURCE_ID
, HEADER_ID
, LINE_ID
, RELEASED_FLAG
, ORG_ID
)
VALUES
( OE_ORDER_HOLDS_S.NEXTVAL
, SYSDATE
, NVL(FND_GLOBAL.USER_ID, -1)
, SYSDATE
, NVL(FND_GLOBAL.USER_ID, -1)
, NULL
, G_hold_source_id(i)
, G_Hold_header_id(i)
, G_Hold_line_id(i)
, 'N'
, l_org_id
);
g_hold_header_id.DELETE;
g_hold_line_id.DELETE;
g_hold_Source_Id.DELETE;
g_hold_ship_set.DELETE;
g_hold_arrival_set.DELETE;
g_hold_top_model_line_id.DELETE;
g_hold_activity_name.DELETE;
INSERT INTO OE_HOLD_SOURCES
( HOLD_SOURCE_ID
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, REQUEST_ID
, HOLD_ID
, HOLD_ENTITY_CODE
, HOLD_ENTITY_ID
, HOLD_UNTIL_DATE
, RELEASED_FLAG
, ORG_ID
)
VALUES
( g_hold_source_rec.hold_source_id(i)
, sysdate
, NVL(FND_GLOBAL.USER_ID, -1)
, sysdate
, NVL(FND_GLOBAL.USER_ID, -1)
, NULL
, NULL
, NULL
, NULL
, NULL
, g_hold_source_rec.HOLD_ID(i)
, g_hold_source_rec.HOLD_ENTITY_CODE(i)
, g_hold_source_rec.HOLD_ENTITY_ID(i)
, NULL
, 'N'
, l_org_id
);
g_hold_source_rec.HOLD_ID.DELETE;
g_hold_source_rec.HOLD_SOURCE_ID.DELETE;
g_hold_source_rec.HOLD_ENTITY_CODE.DELETE;
g_hold_source_rec.HOLD_ENTITY_ID.DELETE;
SELECT OE_HOLD_SOURCES_S.NEXTVAL
INTO g_hold_source_rec.HOLD_SOURCE_ID(i+1)
FROM DUAL;