[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO ISC_EDW_BACKLOGS_FSTG(
BACKLOGS_PK,
BASE_UOM_FK,
BILL_BKLG_COST_G,
BILL_BKLG_COST_T,
BILL_BKLG_MRG_G,
BILL_BKLG_MRG_T,
BILL_BKLG_REV_G,
BILL_BKLG_REV_T,
BILL_TO_CUST_FK,
BILL_TO_LOCATION_FK,
CUSTOMER_FK,
DATE_BALANCE_FK,
DEMAND_CLASS_FK,
DLQT_BKLG_COST_G,
DLQT_BKLG_COST_T,
DLQT_BKLG_MRG_G,
DLQT_BKLG_MRG_T,
DLQT_BKLG_REV_G,
DLQT_BKLG_REV_T,
GL_BOOK_FK,
INSTANCE,
INSTANCE_FK,
INV_ORG_FK,
ITEM_ORG_FK,
LAST_UPDATE_DATE,
OPERATING_UNIT_FK,
ORDER_CATEGORY_FK,
ORDER_SOURCE_FK,
ORDER_TYPE_FK,
QTY_BILL_BKLG_B,
QTY_DLQT_BKLG_B,
QTY_SHIP_BKLG_B,
QTY_UNBILL_SHIP_B,
SALES_CHANNEL_FK,
SALES_PERSON_FK,
SHIP_BKLG_COST_G,
SHIP_BKLG_COST_T,
SHIP_BKLG_MRG_G,
SHIP_BKLG_MRG_T,
SHIP_BKLG_REV_G,
SHIP_BKLG_REV_T,
SHIP_TO_CUST_FK,
SHIP_TO_LOCATION_FK,
TASK_FK,
TOP_MODEL_ITEM_FK,
TRX_CURRENCY_FK,
UNBILL_SHIP_COST_G,
UNBILL_SHIP_COST_T,
UNBILL_SHIP_MRG_G,
UNBILL_SHIP_MRG_T,
UNBILL_SHIP_REV_G,
UNBILL_SHIP_REV_T,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
USER_FK1,
USER_FK2,
USER_FK3,
USER_FK4,
USER_FK5,
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
OPERATION_CODE,
COLLECTION_STATUS)
SELECT BACKLOGS_PK,
BASE_UOM_FK,
BILL_BKLG_COST_G,
BILL_BKLG_COST_T,
BILL_BKLG_MRG_G,
BILL_BKLG_MRG_T,
BILL_BKLG_REV_G,
BILL_BKLG_REV_T,
BILL_TO_CUST_FK,
BILL_TO_LOCATION_FK,
CUSTOMER_FK,
DATE_BALANCE_FK,
DEMAND_CLASS_FK,
DLQT_BKLG_COST_G,
DLQT_BKLG_COST_T,
DLQT_BKLG_MRG_G,
DLQT_BKLG_MRG_T,
DLQT_BKLG_REV_G,
DLQT_BKLG_REV_T,
GL_BOOK_FK,
INSTANCE,
INSTANCE_FK,
INV_ORG_FK,
ITEM_ORG_FK,
LAST_UPDATE_DATE,
OPERATING_UNIT_FK,
ORDER_CATEGORY_FK,
ORDER_SOURCE_FK,
ORDER_TYPE_FK,
QTY_BILL_BKLG_B,
QTY_DLQT_BKLG_B,
QTY_SHIP_BKLG_B,
QTY_UNBILL_SHIP_B,
SALES_CHANNEL_FK,
SALES_PERSON_FK,
SHIP_BKLG_COST_G,
SHIP_BKLG_COST_T,
SHIP_BKLG_MRG_G,
SHIP_BKLG_MRG_T,
SHIP_BKLG_REV_G,
SHIP_BKLG_REV_T,
SHIP_TO_CUST_FK,
SHIP_TO_LOCATION_FK,
TASK_FK,
TOP_MODEL_ITEM_FK,
TRX_CURRENCY_FK,
UNBILL_SHIP_COST_G,
UNBILL_SHIP_COST_T,
UNBILL_SHIP_MRG_G,
UNBILL_SHIP_MRG_T,
UNBILL_SHIP_REV_G,
UNBILL_SHIP_REV_T,
USER_ATTRIBUTE1,
USER_ATTRIBUTE2,
USER_ATTRIBUTE3,
USER_ATTRIBUTE4,
USER_ATTRIBUTE5,
USER_ATTRIBUTE6,
USER_ATTRIBUTE7,
USER_ATTRIBUTE8,
USER_ATTRIBUTE9,
USER_ATTRIBUTE10,
USER_ATTRIBUTE11,
USER_ATTRIBUTE12,
USER_ATTRIBUTE13,
USER_ATTRIBUTE14,
USER_ATTRIBUTE15,
USER_ATTRIBUTE16,
USER_ATTRIBUTE17,
USER_ATTRIBUTE18,
USER_ATTRIBUTE19,
USER_ATTRIBUTE20,
USER_ATTRIBUTE21,
USER_ATTRIBUTE22,
USER_ATTRIBUTE23,
USER_ATTRIBUTE24,
USER_ATTRIBUTE25,
nvl(USER_FK1,'NA_EDW'),
nvl(USER_FK2,'NA_EDW'),
nvl(USER_FK3,'NA_EDW'),
nvl(USER_FK4,'NA_EDW'),
nvl(USER_FK5,'NA_EDW'),
USER_MEASURE1,
USER_MEASURE2,
USER_MEASURE3,
USER_MEASURE4,
USER_MEASURE5,
NULL, -- OPERATION_CODE
'LOCAL READY'
FROM ISC_EDW_BACKLOGS_F_FCV
WHERE seq_id = p_seq_id;
SELECT isc_tmp_back_s.nextval
INTO l_seq_id
FROM dual;
INSERT INTO isc_tmp_back(
SEQ_ID,
PK1)
SELECT /*+ PARALLEL(h) */
l_seq_id,
to_char(l.line_id)
FROM oe_order_headers_all h,
oe_order_lines_all l
WHERE h.last_update_date BETWEEN g_push_from_date AND g_push_to_date
AND l.header_id = h.header_id
AND nvl(l.ordered_quantity,0) > 0
AND nvl(l.source_document_type_id,0) <> 10
AND l.line_category_code = ('ORDER')
UNION
SELECT /*+ PARALLEL(l) */
l_seq_id,
to_char(l.line_id)
FROM oe_order_lines_all l
WHERE l.last_update_date BETWEEN g_push_from_date AND g_push_to_date
AND nvl(l.ordered_quantity,0) > 0
AND nvl(l.source_document_type_id,0) <> 10
AND l.line_category_code = ('ORDER');
UPDATE ISC_TMP_BACK
SET seq_id = l_seq_id
WHERE seq_id = -10
AND pk1 NOT IN ( SELECT pk1
FROM ISC_TMP_BACK
WHERE seq_id = l_seq_id) ;
DELETE ISC_TMP_BACK
WHERE seq_id = -10;
INSERT INTO ISC_EDW_BACK_MISSING_RATE(
ID,
PK1,
PK2,
CURR_CONV_DATE,
FROM_CURRENCY,
TO_CURRENCY,
RATE_TYPE,
FROM_UOM_CODE,
TO_UOM_CODE,
INVENTORY_ITEM_ID,
ITEM_NAME,
STATUS)
SELECT /* Reports Transaction to Base Conversion Currency Issue*/
g_seq_id_line ID,
ftp.pk1 PK1,
'' PK2,
decode( upper(h.conversion_type_code), 'USER',
h.conversion_rate_date,
h.booked_date) CURR_CONV_DATE,
h.transactional_curr_code FROM_CURRENCY,
gl.currency_code TO_CURRENCY,
nvl(h.conversion_type_code,
edw_param.rate_type) RATE_TYPE,
'' FROM_UOM_CODE,
'' TO_UOM_CODE,
'' INVENTORY_ITEM_ID,
'' ITEM_NAME,
decode( decode( upper(h.conversion_type_code), 'USER',
h.conversion_rate,
decode( h.transactional_curr_code, gl.currency_code,
1,
GL_CURRENCY_API.get_rate_sql(
h.transactional_curr_code,
gl.currency_code,
h.booked_date,
nvl(h.conversion_type_code, edw_param.rate_type)))),
-1,'RATE NOT AVAILABLE',
-2,'INVALID CURRENCY') STATUS
FROM EDW_LOCAL_INSTANCE inst,
EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
ISC_TMP_BACK ftp,
OE_ORDER_LINES_ALL l,
OE_ORDER_HEADERS_ALL h,
FINANCIALS_SYSTEM_PARAMS_ALL fspa,
GL_SETS_OF_BOOKS gl
WHERE ftp.pk1 = l.line_id
AND ftp.seq_id = g_seq_id_line
AND l.org_id = fspa.org_id
AND l.header_id = h.header_id
AND fspa.set_of_books_id = gl.set_of_books_id
AND h.booked_flag = 'Y'
AND h.booked_date IS NOT NULL
AND decode( upper(h.conversion_type_code), 'USER',
h.conversion_rate,
decode( h.transactional_curr_code, gl.currency_code,
1,
GL_CURRENCY_API.get_rate_sql(
h.transactional_curr_code,
gl.currency_code,
h.booked_date,
nvl(h.conversion_type_code, edw_param.rate_type)))) < 0
UNION
SELECT /* Reports Base to Global Conversion Currency Issue */
g_seq_id_line ID,
ftp.pk1 PK1,
'' PK2,
decode( upper(h.conversion_type_code), 'USER',
h.conversion_rate_date,
h.booked_date) CURR_CONV_DATE,
gl.currency_code FROM_CURRENCY,
edw_param.warehouse_currency_code TO_CURRENCY,
nvl(h.conversion_type_code,
edw_param.rate_type) RATE_TYPE,
'' FROM_UOM_CODE,
'' TO_UOM_CODE,
'' INVENTORY_ITEM_ID,
'' ITEM_NAME,
decode( EDW_CURRENCY.Get_Rate (gl.currency_code,h.booked_date),
-1,'RATE NOT AVAILABLE',
-2,'INVALID CURRENCY') STATUS
FROM EDW_LOCAL_INSTANCE inst,
EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
ISC_TMP_BACK ftp,
OE_ORDER_LINES_ALL l,
OE_ORDER_HEADERS_ALL h,
FINANCIALS_SYSTEM_PARAMS_ALL fspa,
GL_SETS_OF_BOOKS gl
WHERE ftp.pk1 = l.line_id
AND ftp.seq_id = g_seq_id_line
AND l.org_id = fspa.org_id
AND l.header_id = h.header_id
AND fspa.set_of_books_id = gl.set_of_books_id
AND h.booked_flag = 'Y'
AND h.booked_date IS NOT NULL
AND EDW_CURRENCY.Get_Rate (gl.currency_code,h.booked_date) < 0
UNION
SELECT /* Reports Base to Transaction Conversion Currency Issue */
g_seq_id_line ID,
ftp.pk1 PK1,
'' PK2,
decode( upper(h.conversion_type_code), 'USER',
h.conversion_rate_date,
h.booked_date) CURR_CONV_DATE,
gl.currency_code FROM_CURRENCY,
h.transactional_curr_code TO_CURRENCY,
nvl(h.conversion_type_code,
edw_param.rate_type) RATE_TYPE,
'' FROM_UOM_CODE,
'' TO_UOM_CODE,
'' INVENTORY_ITEM_ID,
'' ITEM_NAME,
decode( decode( upper(h.conversion_type_code),'USER',
1/ h.conversion_rate,
decode( h.transactional_curr_code, gl.currency_code,
1,
GL_CURRENCY_API.get_rate_sql (
gl.currency_code,
h.transactional_curr_code,
h.booked_date,
nvl(h.conversion_type_code, edw_param.rate_type)))),
-1,'RATE NOT AVAILABLE',
-2,'INVALID CURRENCY') STATUS
FROM EDW_LOCAL_INSTANCE inst,
EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
ISC_TMP_BACK ftp,
OE_ORDER_LINES_ALL l,
OE_ORDER_HEADERS_ALL h,
FINANCIALS_SYSTEM_PARAMS_ALL fspa,
GL_SETS_OF_BOOKS gl
WHERE ftp.pk1 = l.line_id
AND ftp.seq_id = g_seq_id_line
AND l.org_id = fspa.org_id
AND l.header_id = h.header_id
AND fspa.set_of_books_id = gl.set_of_books_id
AND h.booked_flag = 'Y'
AND h.booked_date IS NOT NULL
AND decode( upper(h.conversion_type_code),'USER',
1/ h.conversion_rate,
decode( h.transactional_curr_code, gl.currency_code,
1,
GL_CURRENCY_API.get_rate_sql (
gl.currency_code,
h.transactional_curr_code,
h.booked_date,
nvl(h.conversion_type_code, edw_param.rate_type)))) < 0
UNION
SELECT -- Reports "Ship from Org Base" to "Header Org Base" Conversion Currency Issue
g_seq_id_line ID,
ftp.pk1 PK1,
'' PK2,
h.booked_date CURR_CONV_DATE,
gl_cost.currency_code FROM_CURRENCY,
gl.currency_code TO_CURRENCY,
edw_param.rate_type RATE_TYPE,
'' FROM_UOM_CODE,
'' TO_UOM_CODE,
'' INVENTORY_ITEM_ID,
'' ITEM_NAME,
decode(GL_CURRENCY_API.get_rate_sql (
gl_cost.currency_code,
gl.currency_code,
h.booked_date,
edw_param.rate_type),
-1,'RATE NOT AVAILABLE',
-2,'INVALID CURRENCY') STATUS
FROM EDW_LOCAL_SYSTEM_PARAMETERS edw_param,
ISC_TMP_PK ftp,
OE_ORDER_LINES_ALL l,
OE_ORDER_HEADERS_ALL h,
FINANCIALS_SYSTEM_PARAMS_ALL fspa,
GL_SETS_OF_BOOKS gl,
GL_SETS_OF_BOOKS gl_cost,
HR_ORGANIZATION_INFORMATION hoi
WHERE ftp.pk1 = l.line_id
AND ftp.seq_id = g_seq_id_line
AND l.org_id = fspa.org_id
AND l.header_id = h.header_id
AND fspa.set_of_books_id = gl.set_of_books_id
AND h.booked_flag = 'Y'
AND h.booked_date IS NOT NULL
AND l.ship_from_org_id = hoi.organization_id -- if ship_from_org_id is null, don't include row in the missing rates
AND hoi.org_information_context = 'Accounting Information'
AND hoi.org_information1 = to_char(gl_cost.set_of_books_id)
AND GL_CURRENCY_API.get_rate_sql (
gl_cost.currency_code,
gl.currency_code,
h.booked_date,
edw_param.rate_type) < 0;
INSERT INTO ISC_EDW_BACK_MISSING_RATE(
ID,
PK1,
PK2,
CURR_CONV_DATE,
FROM_CURRENCY,
TO_CURRENCY,
RATE_TYPE,
FROM_UOM_CODE,
TO_UOM_CODE,
INVENTORY_ITEM_ID,
ITEM_NAME,
STATUS)
SELECT /* Reports UOM Conversion Issue */
g_seq_id_line ID,
ftp.pk1 PK1,
'' PK2,
to_date(NULL) CURR_CONV_DATE,
'' FROM_CURRENCY,
'' TO_CURRENCY,
'' RATE_TYPE,
l.order_quantity_uom FROM_UOM_CODE,
EDW_UTIL.Get_Edw_Base_Uom(
l.order_quantity_uom,
l.inventory_item_id) TO_UOM_CODE,
l.inventory_item_id INVENTORY_ITEM_ID,
nvl(mtl.segment1,'Item number unavailable')
||' : '||nvl(description,'Description unavailable')
ITEM_NAME,
'UOM ISSUE' STATUS
FROM EDW_LOCAL_INSTANCE inst,
ISC_TMP_BACK ftp,
OE_ORDER_LINES_ALL l,
OE_ORDER_HEADERS_ALL h,
MTL_SYSTEM_ITEMS_B mtl
WHERE ftp.pk1 = l.line_id
AND ftp.seq_id = g_seq_id_line
AND l.header_id = h.header_id
AND h.booked_flag = 'Y'
AND h.booked_date IS NOT NULL
AND l.ship_from_org_id = mtl.organization_id
AND l.inventory_item_id = mtl.inventory_item_id
AND EDW_UTIL.Get_Uom_Conv_Rate(l.order_quantity_uom,l.inventory_item_id) IS NULL;
FUNCTION Insert_Isc_Tmp_Back RETURN NUMBER IS
BEGIN
INSERT INTO ISC_TMP_BACK (
seq_id,
pk1)
SELECT -10 , ftp.pk1
FROM ISC_EDW_BACK_MISSING_RATE conv,
ISC_TMP_BACK ftp
WHERE conv.pk1 = ftp.pk1
AND ftp.seq_id = g_seq_id_line
AND conv.id = g_seq_id_line ;
g_errbuf := 'Error in Function Insert_Tmp_Back : '||sqlerrm;
FUNCTION Delete_Isc_Tmp_Back RETURN NUMBER IS
BEGIN
DELETE FROM ISC_TMP_BACK
WHERE pk1 IN ( SELECT pk1
FROM ISC_EDW_BACK_MISSING_RATE
WHERE id = g_seq_id_line )
AND seq_id >0 ;
g_errbuf := 'Error in Function Delete_Isc_Tmp_Back : '||sqlerrm;
SELECT DISTINCT trunc(curr_conv_date) curr_conv_date,
from_currency,
to_currency,
rate_type,
status
FROM ISC_EDW_BACK_MISSING_RATE
WHERE status NOT IN ('UOM ISSUE')
ORDER BY status,from_currency,trunc(curr_conv_date);
SELECT DISTINCT from_uom_code,
to_uom_code,
inventory_item_id item_id,
substr(item_name,0,50) item_name
FROM ISC_EDW_BACK_MISSING_RATE
WHERE status = 'UOM ISSUE'
ORDER BY item_name,from_uom_code;
FII_UTIL.Print_Timer('Inserted '||g_miss_conv||' rows into the ISC_EDW_BACK_MISSING_RATE table in ');
EDW_LOG.Put_Line('Inserting into ISC_TMP_BACK with < 0 seq_id the rows having missing conversion rates (currency and UoM)');
g_row_count := Insert_Isc_Tmp_Back;
FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows into the ISC_TMP_BACK table in ');
g_row_count := Delete_Isc_Tmp_Back ;
FII_UTIL.Print_Timer('Deleted '||g_row_count||' rows from the ISC_TMP_BACK table in ');
FII_UTIL.Print_Timer('Inserted '||g_rows_collected||' rows into the local staging table in ');
DELETE FROM isc_tmp_back
WHERE seq_id = g_seq_id_line;
DELETE FROM ISC_EDW_BACK_MISSING_RATE
WHERE id = g_seq_id_line;
EDW_LOG.Put_Line('Inserted '||g_rows_collected||' rows into the local staging table');