[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION UPDATE_SALES_FACT RETURN NUMBER IS
l_isc_schema VARCHAR2(30);
/* Insert into ISC_TMP_BOOK_SUM2 all the orders lines for orders having at least 1 line that is present in ISC_TMP_BOOK_SUM2 */
FII_UTIL.Start_Timer;
INSERT INTO isc_tmp_book_sum2 (pk1)
SELECT f.line_id
FROM isc_book_sum2_f f
WHERE f.header_id IN (SELECT fact.header_id
FROM isc_sales_credits_f fact,
isc_tmp_book_sum2 tmp
WHERE fact.line_id = tmp.pk1
)
AND NOT EXISTS (SELECT 1 FROM isc_tmp_book_sum2 a WHERE a.pk1 = f.line_id);
FII_UTIL.Print_Timer('Inserted '||sql%rowcount||' rows into isc_tmp_book_sum2 (updated lines) in');
/* Insert into ISC_TMP_BOOK_SUM2 order lines from ISC_SALES_CREDITS_F to be recollected because their sales credit have been deleted from OE_SALES_CREDITS */
FII_UTIL.Start_Timer;
INSERT INTO isc_tmp_book_sum2 (pk1)
SELECT f.line_id
FROM isc_book_sum2_f f
WHERE header_id IN (SELECT fact.header_id
FROM isc_sales_credits_f fact
WHERE NOT EXISTS (SELECT 1 FROM oe_sales_credits WHERE sales_credit_id = fact.sales_credit_id)
)
AND NOT EXISTS (SELECT 1 FROM isc_tmp_book_sum2 a WHERE a.pk1 = f.line_id);
FII_UTIL.Print_Timer('Inserted '||sql%rowcount||' rows into isc_tmp_book_sum2 (lines deleted from oe_sc..) in');
/* Insert into ISC_TMP_BOOK_SUM2 "SERVICE" order lines referencing parent service identified in #1 and #2 */
-- SERVICE rows whose ORDER or CUSTOMER_PRODUCT parent line_id has been updated
FII_UTIL.Start_Timer;
INSERT INTO ISC_TMP_BOOK_SUM2 (pk1)
SELECT f.line_id
FROM isc_book_sum2_f f
WHERE f.header_id IN
(SELECT fact.header_id
FROM isc_tmp_book_sum2 tmp,
isc_book_sum2_f fact
WHERE tmp.pk1 = fact.service_parent_line_id
AND EXISTS (SELECT 1 FROM isc_book_sum2_f WHERE line_id = fact.service_parent_line_id))
AND NOT EXISTS( SELECT 1 FROM isc_tmp_book_sum2 a WHERE a.pk1 = f.line_id);
FII_UTIL.Print_Timer('Inserted '||sql%rowcount||' rows into isc_tmp_book_sum2 (service_parent_line_id...) in');
DELETE FROM isc_sales_credits_f
WHERE line_id in (SELECT pk1 from ISC_TMP_BOOK_SUM2);
FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' rows from ISC_SALES_CREDITS_F in');
/* Insert into ISC_SALES_CREDITS_F */
BIS_COLLECTION_UTILITIES.put_line(' ');
BIS_COLLECTION_UTILITIES.put_line('Inserting data into sales fact table');
INSERT /*+ APPEND PARALLEL(f) */ INTO isc_sales_credits_f f
with s as (
select /*+ ordered use_hash(sc) parallel(sc) parallel(sr)
pq_distribute(sr,hash,hash) */
sc.sales_credit_id, sc.percent, sc.sales_credit_type_id,
sc.salesrep_id, sc.header_id, sc.line_id, sr.resource_id,
sr.org_id, sc.sales_group_id group_id, sc.created_by, sc.creation_date,
sc.last_updated_by, sc.last_update_date, sc.last_update_login
from oe_sales_credit_types sc_typ,
oe_sales_credits sc,
jtf_rs_salesreps sr
where sc.sales_group_id is not null
and sc.salesrep_id = sr.salesrep_id
and sc.sales_credit_type_id = sc_typ.sales_credit_type_id
and sc_typ.quota_flag = 'Y'
union all
select /*+ ordered use_hash(sc) parallel(sc) parallel(sg)
pq_distribute(sg,hash,hash) */
sc.sales_credit_id, sc.percent, sc.sales_credit_type_id,
sc.salesrep_id, sc.header_id, sc.line_id, sg.resource_id,
sg.org_id, sg.group_id, sc.created_by, sc.creation_date,
sc.last_updated_by, sc.last_update_date, sc.last_update_login
from oe_sales_credit_types sc_typ,
oe_sales_credits sc,
jtf_rs_srp_groups sg
where sc.sales_group_id is null
and sc.salesrep_id = sg.salesrep_id
and sc.last_update_date between sg.start_date and sg.end_date
and sc.sales_credit_type_id = sc_typ.sales_credit_type_id
and sc_typ.quota_flag = 'Y')
SELECT pk, sales_credit_id, resource_id, group_id, header_id, line_id,
percent, sales_credit_type_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login
FROM (SELECT pk, sales_credit_id, resource_id, group_id, header_id, line_id,
percent, sales_credit_type_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
rank() over (partition by line_id order by rnk) low_rnk
FROM (SELECT /*+ parallel(s) */
'DIRECT-'||s.sales_credit_id pk,
s.sales_credit_id, s.group_id, t5.header_id, t5.line_id,
1 rnk, s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t5, s
WHERE tmp.pk1 = t5.line_id
AND s.org_id = t5.org_ou_id
AND s.line_id = t5.line_id
UNION ALL
SELECT /*+ parallel(s) parallel(t7a) use_hash(s) pq_distribute(s,hash,hash) */
'SERVICE_PARENT-'||t7a.line_id||'-'||s.sales_credit_id pk,
s.sales_credit_id, s.group_id, t7a.header_id, t7a.line_id,
2 rnk, s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t7a, s
WHERE tmp.pk1 = t7a.line_id
AND s.org_id = t7a.org_ou_id
AND s.line_id = t7a.service_parent_line_id
AND t7a.item_type_code = 'SERVICE'
UNION ALL
SELECT /*+ parallel(s) parallel(t7b2) use_hash(s) pq_distribute(s,hash,hash)
parallel(t7b1) use_hash(t7b1) pq_distribute(t7b1,hash,hash) */
'SERVICE_PARENT_TOPMODEL-'||t7b2.line_id||'-'||s.sales_credit_id pk,
s.sales_credit_id, s.group_id group_id, t7b2.header_id, t7b2.line_id,
3 rnk, s.resource_id,
s.percent, s.sales_credit_type_id, s.created_by,
s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t7b1, isc_book_sum2_f t7b2, s
WHERE tmp.pk1 = t7b2.line_id
AND t7b2.item_type_code = 'SERVICE'
AND t7b1.line_id = t7b2.service_parent_line_id
AND s.line_id = t7b1.top_model_line_id
AND s.org_id = t7b1.org_ou_id
UNION ALL
SELECT /*+ ordered parallel(s) parallel(t7b1) use_hash(s) pq_distribute(s,hash,hash) */
'TOPMODEL-'||t7b1.line_id||'-'||s.sales_credit_id pk,
s.sales_credit_id, s.group_id, t7b1.header_id, t7b1.line_id,
4 rnk, s.resource_id,
s.percent, s.sales_credit_type_id, s.created_by,
s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t7b1, s
WHERE tmp.pk1 = t7b1.line_id
AND s.line_id = t7b1.top_model_line_id
AND s.org_id = t7b1.org_ou_id
UNION ALL
SELECT /*+ ordered parallel(s) parallel(t11) use_hash(s) pq_distribute(s,hash,hash) */
'HEADER-'||t11.line_id||'-'||s.sales_credit_id pk,
s.sales_credit_id, s.group_id, t11.header_id, t11.line_id,
5 rnk, s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
s.creation_date, s.last_updated_by, s.last_update_date, s.last_update_login
FROM isc_tmp_book_sum2 tmp, isc_book_sum2_f t11, s
WHERE tmp.pk1 = t11.line_id
AND s.line_id IS NULL
AND s.org_id = t11.org_ou_id
AND s.header_id = t11.header_id))
WHERE low_rnk = 1;
FII_UTIL.Print_Timer('Inserted '||sql%rowcount||' rows into the sales fact table in');
g_errbuf := 'Error in function Update Sales Fact: '||sqlerrm;
END update_sales_fact;
l_stmt := 'INSERT /*+ APPEND PARALLEL(F) */ '||
'INTO isc_tmp_book_sum2 F('||
'PK1,'||
'VIEW_TYPE,'||
'LOG_ROWID,'||
'BATCH_ID,'||
'CURR_CONV_DATE,'||
'FROM_CURRENCY,'||
'TO_CURRENCY1,'||
'RATE_TYPE, RATE1,'||
'TO_CURRENCY2, '||
'TO_CURRENCY3, '||
'TO_CURRENCY4, '||
'INV_OU_ID,'||
'MASTER_ORG_ID,'||
'INVENTORY_ITEM_ID,'||
'ITEM_INV_ORG_ID,'||
'TIME_BOOKED_DATE_ID,'||
'TIME_SHIPPED_DATE_ID,'||
'TIME_FULFILLED_DATE_ID,'||
'TIME_SCHEDULE_DATE_ID,'||
'TOP_MODEL_LINE_ID,'||
'ORDER_QUANTITY_UOM,'||
'INV_UOM_CODE,'||
'INV_UOM_RATE,'||
'ORDER_NUMBER,'||
'HEADER_ID,'||
'LINE_NUMBER,'||
'SERVICE_REFERENCE_TYPE_CODE,'||
'SOLD_TO_ORG_ID,'||
'SERVICE_REFERENCE_LINE_ID,'||
'FREIGHT_CHARGE,'||
'FREIGHT_COST)' ||
' SELECT /*+ USE_HASH(h,l,opa,aspa,gsb,hoi,ospa,gsb1,item) PARALLEL(h) PARALLEL(l) PARALLEL(opa) PARALLEL(aspa) PARALLEL(gsb) PARALLEL(hoi) PARALLEL(ospa) PARALLEL(gsb1) PARALLEL(item) */ '||
' l.line_id PK1,'||
' decode(l.top_model_line_id,'||
' null, 0,'||
' decode(ato_line_id,'||
' null, decode(item_type_code,'||
' ''MODEL'', 3,'||
' ''KIT'', 3, 4), 1)) VIEW_TYPE,'||
' null LOG_ROWID,'||
' null BATCH_ID,'||
' decode(upper(h.conversion_type_code),'||
' ''USER'', h.conversion_rate_date,'||
' h.booked_date) CURR_CONV_DATE,'||
' h.transactional_curr_code FROM_CURRENCY,'||
' gsb.currency_code TO_CURRENCY1,'||
' nvl(h.conversion_type_code,'''||
g_treasury_rate_type ||''') RATE_TYPE,'||
' decode(upper(h.conversion_type_code),'||
' ''USER'', h.conversion_rate,null) RATE1,'''||
g_global_currency || ''' TO_CURRENCY2,'||
' gsb1.currency_code TO_CURRENCY3,'''||
g_sec_global_currency || ''' TO_CURRENCY4,'||
' to_number(hoi.org_information3) INV_OU_ID,'||
' ospa.parameter_value MASTER_ORG_ID,'||
' l.inventory_item_id INVENTORY_ITEM_ID,'||
' nvl(l.ship_from_org_id,ospa.parameter_value) ITEM_INV_ORG_ID,'||
' trunc(nvl(l.order_firmed_date, h.booked_date)) TIME_BOOKED_DATE_ID,'||
' trunc(l.actual_shipment_date) TIME_SHIPPED_DATE_ID,'||
' trunc(nvl(l.actual_fulfillment_date, l.fulfillment_date)) TIME_FULFILLED_DATE_ID,'||
' trunc(l.schedule_ship_date) TIME_SCHEDULE_DATE_ID,'||
' l.top_model_line_id TOP_MODEL_LINE_ID,'||
' l.order_quantity_uom ORDER_QUANTITY_UOM,'||
' item.primary_uom_code INV_UOM_CODE,'||
' decode(l.order_quantity_uom, item.primary_uom_code,1,'||
' INV_CONVERT.inv_um_convert('||
' l.inventory_item_id,NULL,1,'||
' l.order_quantity_uom,'||
' item.primary_uom_code,'||
' NULL, NULL)) INV_UOM_RATE,'||
' h.order_number ORDER_NUMBER,'||
' h.header_id HEADER_ID,'||
' l.line_number ||''.''||'||
' l.shipment_number||decode(l.service_number,'''','||
' decode(l.component_number,'''','||
' decode(l.option_number,'''','''',''.''),''.''),''.'') ||'||
' l.option_number||decode(l.service_number,'''','||
' decode(l.component_number,'''','''',''.''),''.'') ||'||
' l.component_number||decode(l.service_number,'''','''',''.'')||'||
' l.service_number LINE_NUMBER,'||
' l.service_reference_type_code SERVICE_REFERENCE_TYPE_CODE,'||
' l.sold_to_org_id SOLD_TO_ORG_ID,'||
' l.service_reference_line_id SERVICE_REFERENCE_LINE_ID,'||
' nvl(opa.charge_adjamt,0)*l.ordered_quantity + nvl(opa.charge_operand,0) FREIGHT_CHARGE,'||
' opa.cost FREIGHT_COST'||
' FROM OE_ORDER_HEADERS_ALL h,'||
' OE_ORDER_LINES_ALL l,'||
' AR_SYSTEM_PARAMETERS_ALL aspa,'||
' GL_SETS_OF_BOOKS gsb,'||
' HR_ORGANIZATION_INFORMATION hoi,'||
' OE_SYS_PARAMETERS_ALL ospa,'||
' GL_SETS_OF_BOOKS gsb1,'||
' MTL_SYSTEM_ITEMS_B item, '||
' (select p.line_id, sum(decode(p.list_line_type_code, ''COST'', '||
' p.adjusted_amount, null)) cost, '||
' sum(decode(p.list_line_type_code, ''FREIGHT_CHARGE'', '||
' decode(nvl(p.applied_flag, ''Y''), ''Y'', '||
' decode(p.arithmetic_operator, ''LUMPSUM'', p.operand, null), '||
' null), null)) charge_operand, '||
' sum(decode(p.list_line_type_code, ''FREIGHT_CHARGE'', '||
' decode(nvl(p.applied_flag, ''Y''), ''Y'', '||
' decode(p.arithmetic_operator, ''LUMPSUM'', null, p.adjusted_amount), '||
' null), null)) charge_adjamt '||
' from oe_price_adjustments p '||
' where p.line_id is not null '||
' and p.charge_type_code in (''FTECHARGE'', ''FTEPRICE'') '||
' group by p.line_id) opa '||
' WHERE nvl(l.order_firmed_date, h.booked_date) >= to_date('''|| l_from_date || ''',''MM/DD/YYYY HH24:MI:SS'')'||
' AND l.header_id = h.header_id'||
' AND l.line_id = opa.line_id (+) '||
' AND h.org_id = aspa.org_id'||
' AND aspa.set_of_books_id = gsb.set_of_books_id'||
' AND h.booked_flag = ''Y'''||
' AND h.booked_date IS NOT NULL'||
' AND hoi.org_information_context =''Accounting Information'''||
' AND h.org_id = ospa.org_id'||
' AND ospa.parameter_code = ''MASTER_ORGANIZATION_ID'''||
' AND hoi.organization_id = nvl(l.ship_from_org_id, ospa.parameter_value)'||
' AND hoi.org_information1 = to_char(gsb1.set_of_books_id)'||
' AND l.inventory_item_id = item.inventory_item_id'||
' AND nvl(l.ship_from_org_id, ospa.parameter_value) = item.organization_id';
INSERT /*+ APPEND */
INTO isc_curr_book_sum2 F(
FROM_CURRENCY,
TO_CURRENCY1,
TO_CURRENCY3,
CONVERSION_DATE,
CONVERSION_TYPE,
RATE1,
RATE2,
RATE3,
RATE4)
SELECT from_currency, to_currency1, to_currency3, time_booked_date_id CONVERSION_DATE, rate_type CONVERSION_TYPE,
decode(from_currency, to_currency1, 1,
fii_currency.get_rate(from_currency, to_currency1, time_booked_date_id, rate_type)) RATE1,
decode(from_currency, g_global_currency, 1,
fii_currency.get_global_rate_primary(to_currency3, time_booked_date_id)) RATE2,
decode(from_currency, to_currency3, 1,
fii_currency.get_rate(from_currency, to_currency3, time_booked_date_id, g_global_rate_type)) RATE3,
decode(from_currency, g_sec_global_currency, 1,
fii_currency.get_global_rate_secondary(to_currency3, time_booked_date_id)) RATE4
FROM (SELECT /*+ PARALLEL(tmp) */ distinct from_currency, to_currency1, to_currency3, time_booked_date_id, rate_type
FROM isc_tmp_book_sum2 tmp);
INSERT /*+ APPEND */
INTO isc_service_book_sum2 F(
LINE_ID,
SERVICE_PARENT_LINE_ID)
SELECT pk1, ISC_DBI_BOOK_SUM2_F_C.get_cust_product_line_id(tmp.sold_to_org_id,tmp.service_reference_line_id)
FROM isc_tmp_book_sum2 tmp
WHERE service_reference_type_code = 'CUSTOMER_PRODUCT';
FUNCTION DELETE_DBI_BASE RETURN NUMBER IS
l_count NUMBER := 0;
DELETE FROM isc_book_sum2_f
WHERE line_id IN (select pk1
from isc_tmp_book_sum2
where view_type = -1)
AND fulfilled_flag = 'N';
FII_UTIL.Print_Timer('Deleted '||sql%rowcount||' changed records in');
/* Delete ont_dbi_change_log at the end */
-- BIS_COLLECTION_UTILITIES.put_line('Deleting obsolete records from OM log table');
DELETE FROM isc_tmp_book_sum2
WHERE view_type = -1;
FII_UTIL.Print_Timer('Deleted '||l_count||' changed records in');
g_errbuf := 'Error in Function DELETE_DBI_BASE : '||sqlerrm;
l_delete_count NUMBER := 0;
INSERT INTO isc_dbi_change_log (LINE_ID, HEADER_ID, LOG_ROWID, LAST_UPDATE_DATE)
SELECT line_id LINE_ID, header_id HEADER_ID, rowid LOG_ROWID, last_update_date LAST_UPDATE_DATE
FROM ont_dbi_change_log;
FII_UTIL.Print_Timer('Inserted '|| sql%rowcount || ' rows into ISC_DBI_CHANGE_LOG');
/* Make a copy of ont_dbi_change_log, no need to delete the duplication before the insert statement. */
-- DELETE FROM ont_dbi_change_log d1
-- WHERE EXISTS (SELECT 1 FROM ont_dbi_change_log d2
-- WHERE d2.rowid < d1.rowid
-- AND d2.last_update_date = d1.last_update_date
-- AND d2.line_id = d1.line_id);
INSERT INTO isc_tmp_book_sum2(pk1, view_type)
SELECT distinct line_id, -1
FROM isc_dbi_change_log log
WHERE NOT EXISTS (select '1'
from oe_order_lines_all l
where l.line_id = log.line_id);
l_delete_count := sql%rowcount;
FII_UTIL.Print_Timer('Identified '|| l_delete_count || ' deleted lines in');
IF l_delete_count > 0 THEN
BIS_COLLECTION_UTILITIES.Put_Line(' ');
l_delete_count := DELETE_DBI_BASE;
IF (l_delete_count = -1) THEN
return -1;
INSERT
INTO isc_tmp_book_sum2(
PK1,
VIEW_TYPE,
-- LOG_ROWID,
BATCH_ID,
CURR_CONV_DATE,
FROM_CURRENCY,
TO_CURRENCY1,
RATE_TYPE,
RATE1,
TO_CURRENCY2,
TO_CURRENCY3,
TO_CURRENCY4,
INV_OU_ID,
MASTER_ORG_ID,
INVENTORY_ITEM_ID,
ITEM_INV_ORG_ID,
TIME_BOOKED_DATE_ID,
TIME_SHIPPED_DATE_ID,
TIME_FULFILLED_DATE_ID,
TIME_SCHEDULE_DATE_ID,
TOP_MODEL_LINE_ID,
ORDER_QUANTITY_UOM,
INV_UOM_CODE,
INV_UOM_RATE,
ORDER_NUMBER,
HEADER_ID,
LINE_NUMBER,
SERVICE_REFERENCE_TYPE_CODE,
SOLD_TO_ORG_ID,
SERVICE_REFERENCE_LINE_ID,
FREIGHT_CHARGE,
FREIGHT_COST)
SELECT /*+ leading(log) use_hash(hoi,gsb,gsb1) */ l.line_id,
decode(l.top_model_line_id,
null, 0,
decode(ato_line_id,
null, decode(item_type_code,
'MODEL', 3, 'KIT', 3, 4),1)) VIEW_TYPE,
-- log.rowid LOG_ROWID,
null BATCH_ID,
decode(upper(h.conversion_type_code),
'USER', h.conversion_rate_date,
h.booked_date) CURR_CONV_DATE,
h.transactional_curr_code FROM_CURRENCY,
gsb.currency_code TO_CURRENCY1,
nvl(h.conversion_type_code,
g_treasury_rate_type) RATE_TYPE,
decode(upper(h.conversion_type_code),
'USER', h.conversion_rate,null) RATE1,
g_global_currency TO_CURRENCY2,
gsb1.currency_code TO_CURRENCY3,
g_sec_global_currency TO_CURRENCY4,
to_number(hoi.org_information3) INV_OU_ID,
ospa.parameter_value MASTER_ORG_ID,
l.inventory_item_id INVENTORY_ITEM_ID,
nvl(l.ship_from_org_id, ospa.parameter_value) ITEM_INV_ORG_ID,
trunc(nvl(l.order_firmed_date, h.booked_date)) TIME_BOOKED_DATE_ID,
trunc(l.actual_shipment_date) TIME_SHIPPED_DATE_ID,
trunc(nvl(l.actual_fulfillment_date, l.fulfillment_date)) TIME_FULFILLED_DATE_ID,
trunc(l.schedule_ship_date) TIME_SCHEDULE_DATE_ID,
l.top_model_line_id TOP_MODEL_LINE_ID,
l.order_quantity_uom ORDER_QUANTITY_UOM,
item.primary_uom_code INV_UOM_CODE,
decode(l.order_quantity_uom, item.primary_uom_code,1,
INV_CONVERT.inv_um_convert(
l.inventory_item_id,NULL,1,
l.order_quantity_uom,
item.primary_uom_code,
NULL, NULL)) INV_UOM_RATE,
h.order_number ORDER_NUMBER,
h.header_id HEADER_ID,
l.line_number ||'.'||
l.shipment_number||decode(l.service_number,'',
decode(l.component_number,'',
decode(l.option_number,'','','.'),'.'),'.') ||
l.option_number||decode(l.service_number,'',
decode(l.component_number,'','','.'),'.') ||
l.component_number||decode(l.service_number,'','','.')||
l.service_number LINE_NUMBER,
l.service_reference_type_code SERVICE_REFERENCE_TYPE_CODE,
l.sold_to_org_id SOLD_TO_ORG_ID,
l.service_reference_line_id SERVICE_REFERENCE_LINE_ID,
nvl(opa.charge_operand,0) + nvl(opa.charge_adjamt,0)*l.ordered_quantity FREIGHT_CHARGE,
opa.cost FREIGHT_COST
FROM (select p.line_id, sum(decode(p.list_line_type_code, 'COST',
p.adjusted_amount, null)) cost,
sum(decode(p.list_line_type_code, 'FREIGHT_CHARGE', decode(nvl(p.applied_flag, 'Y'), 'Y', decode(p.arithmetic_operator, 'LUMPSUM', p.operand, null), null), null)) charge_operand,
sum(decode(p.list_line_type_code, 'FREIGHT_CHARGE', decode(nvl(p.applied_flag, 'Y'), 'Y', decode(p.arithmetic_operator, 'LUMPSUM', null, p.adjusted_amount), null), null)) charge_adjamt
from oe_price_adjustments p,
(select /*+ no_merge cardinality (log, 1000)*/ distinct line_id from isc_dbi_change_log log) log1
where p.line_id = log1.line_id
and p.line_id is not null
and p.charge_type_code in ('FTEPRICE', 'FTECHARGE')
group by p.line_id) opa,
(select /*+ no_merge cardinality (ilog, 1000)*/ distinct line_id, header_id from isc_dbi_change_log ilog) log,
oe_order_lines_all l,
OE_ORDER_HEADERS_ALL h,
AR_SYSTEM_PARAMETERS_ALL aspa,
GL_SETS_OF_BOOKS gsb,
HR_ORGANIZATION_INFORMATION hoi,
OE_SYS_PARAMETERS_ALL ospa,
GL_SETS_OF_BOOKS gsb1,
MTL_SYSTEM_ITEMS_B item
WHERE log.line_id = l.line_id
-- AND log.last_update_date < g_incre_start_date
AND l.header_id = h.header_id
AND l.line_id = opa.line_id (+)
AND h.org_id = aspa.org_id
AND aspa.set_of_books_id = gsb.set_of_books_id
AND h.booked_flag = 'Y'
AND h.booked_date IS NOT NULL
AND nvl(l.order_firmed_date, h.booked_date) >= g_global_start_date
AND hoi.org_information_context ='Accounting Information'
AND h.org_id = ospa.org_id
AND ospa.parameter_code = 'MASTER_ORGANIZATION_ID'
AND hoi.organization_id = nvl(l.ship_from_org_id, ospa.parameter_value)
AND hoi.org_information1 = to_char(gsb1.set_of_books_id)
AND l.inventory_item_id = item.inventory_item_id
AND nvl(l.ship_from_org_id, ospa.parameter_value) = item.organization_id;
INSERT
INTO isc_curr_book_sum2 F(
FROM_CURRENCY,
TO_CURRENCY1,
TO_CURRENCY3,
CONVERSION_DATE,
CONVERSION_TYPE,
RATE1,
RATE2,
RATE3,
RATE4)
SELECT from_currency, to_currency1, to_currency3, time_booked_date_id CONVERSION_DATE, rate_type CONVERSION_TYPE,
decode(from_currency, to_currency1, 1,
fii_currency.get_rate(from_currency, to_currency1, time_booked_date_id, rate_type)) RATE1,
decode(from_currency, g_global_currency, 1,
fii_currency.get_global_rate_primary(to_currency3, time_booked_date_id)) RATE2,
decode(from_currency, to_currency3, 1,
fii_currency.get_rate(from_currency, to_currency3, time_booked_date_id, g_global_rate_type)) RATE3,
decode(from_currency, g_sec_global_currency, 1,
fii_currency.get_global_rate_secondary(to_currency3, time_booked_date_id)) RATE4
FROM (SELECT distinct from_currency, to_currency1, to_currency3, time_booked_date_id, rate_type
FROM isc_tmp_book_sum2);
INSERT
INTO isc_service_book_sum2 F(
LINE_ID,
SERVICE_PARENT_LINE_ID)
SELECT pk1, ISC_DBI_BOOK_SUM2_F_C.get_cust_product_line_id(tmp.sold_to_org_id,tmp.service_reference_line_id)
FROM isc_tmp_book_sum2 tmp
WHERE service_reference_type_code = 'CUSTOMER_PRODUCT';
UPDATE isc_tmp_book_sum2 SET batch_id = ceil(rownum/g_batch_size);
FII_UTIL.Print_Timer('Updated the batch id for '|| l_count || ' rows in');
SELECT order_number,
line_number,
header_id,
pk1 line_id,
to_char(time_booked_date_id, 'MM/DD/YYYY') time_booked_date_id,
to_char(time_fulfilled_date_id, 'MM/DD/YYYY') time_fulfilled_date_id,
to_char(time_shipped_date_id,'MM/DD/YYYY') time_shipped_date_id
FROM isc_tmp_book_sum2
WHERE (least(time_booked_date_id, nvl(time_fulfilled_date_id,time_booked_date_id), nvl(time_shipped_date_id,time_booked_date_id)) < l_time_min
OR greatest(time_booked_date_id, nvl(time_fulfilled_date_id,time_booked_date_id),nvl(time_shipped_date_id,time_booked_date_id)) > l_time_max);
SELECT order_number,
line_number,
header_id,
pk1 line_id,
to_char(time_schedule_date_id,'MM/DD/YYYY') time_schedule_date_id
FROM isc_tmp_book_sum2
WHERE (nvl(time_schedule_date_id, time_booked_date_id) < l_time_min
OR nvl(time_schedule_date_id, time_booked_date_id) > l_time_max);
SELECT /*+ PARALLEL(tmp) */ min(time_booked_date_id), max(time_booked_date_id),
min(time_shipped_date_id), max(time_shipped_date_id),
min(time_fulfilled_date_id), max(time_fulfilled_date_id),
min(time_schedule_date_id), max(time_schedule_date_id)
INTO l_min_booked_date, l_max_booked_date,
l_min_shipped_date, l_max_shipped_date,
l_min_ful_date, l_max_ful_date,
l_min_sche_date, l_max_sche_date
FROM isc_tmp_book_sum2 tmp;
SELECT min(time_booked_date_id), max(time_booked_date_id),
min(time_shipped_date_id), max(time_shipped_date_id),
min(time_fulfilled_date_id), max(time_fulfilled_date_id),
min(time_schedule_date_id), max(time_schedule_date_id)
INTO l_min_booked_date, l_max_booked_date,
l_min_shipped_date, l_max_shipped_date,
l_min_ful_date, l_max_ful_date,
l_min_sche_date, l_max_sche_date
FROM isc_tmp_book_sum2 tmp;
SELECT min(report_date), max(report_date)
INTO l_time_min, l_time_max
FROM fii_time_day;
SELECT min(report_date), max(report_date)
INTO l_time_min, l_time_max
FROM fii_time_day;
SELECT /*+ PARALLEL(tmp) PARALLEL(item) */ distinct tmp.inventory_item_id, tmp.item_inv_org_id
FROM isc_tmp_book_sum2 tmp,
eni_oltp_item_star item
WHERE tmp.inventory_item_id = item.inventory_item_id(+)
AND tmp.item_inv_org_id = item.organization_id(+)
AND item.organization_id IS NULL;
SELECT distinct tmp.inventory_item_id, tmp.item_inv_org_id
FROM isc_tmp_book_sum2 tmp,
eni_oltp_item_star item
WHERE tmp.inventory_item_id = item.inventory_item_id(+)
AND tmp.item_inv_org_id = item.organization_id(+)
AND item.organization_id IS NULL;
SELECT distinct decode(rate1, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
from_currency,
to_currency1 TO_CURRENCY,
conversion_type RATE_TYPE,
decode(rate1, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
FROM isc_curr_book_sum2 tmp
WHERE rate1 < 0
AND upper(conversion_type) <> 'USER'
UNION
SELECT distinct decode(rate2, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
to_currency3 FROM_CURRENCY,
g_global_currency TO_CURRENCY,
g_global_rate_type RATE_TYPE,
decode(rate2, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
FROM isc_curr_book_sum2 tmp
WHERE rate2 < 0
UNION
SELECT distinct decode(rate3, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
from_currency,
to_currency3,
g_global_rate_type RATE_TYPE,
decode(rate3, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
FROM isc_curr_book_sum2 tmp
WHERE rate3 < 0
UNION
SELECT distinct decode(rate4, -3, to_date('01/01/1999','MM/DD/RRRR'), conversion_date) CURR_CONV_DATE,
to_currency3 FROM_CURRENCY,
g_sec_global_currency TO_CURRENCY,
g_sec_global_rate_type RATE_TYPE,
decode(rate4, -1, 'RATE NOT AVAILABLE', -2, 'INVALID CURRENCY') STATUS
FROM isc_curr_book_sum2 tmp
WHERE rate4 < 0
AND l_sec_curr_def = 'Y';
SELECT distinct inventory_item_id,
order_quantity_uom from_unit,
inv_uom_code to_unit
FROM ISC_TMP_BOOK_SUM2
WHERE inv_uom_rate = -99999;
SELECT order_number,
line_number,
header_id,
pk1 line_id
FROM ISC_TMP_BOOK_SUM2
WHERE order_quantity_uom IS NULL;
FUNCTION INSERT_FACT RETURN NUMBER IS
l_total NUMBER;
INSERT /*+ APPEND PARALLEL(F) */ INTO ISC_BOOK_SUM2_F F
(line_id,
item_id,
inv_org_id,
inv_ou_id,
org_ou_id,
customer_id,
sales_channel_id,
return_reason_id,
order_category_id,
order_source_id,
order_type_id,
ship_to_org_id,
sold_to_org_id,
time_act_ship_date_id,
time_booked_date_id,
time_shipped_date_id,
time_fulfilled_date_id,
time_schedule_date_id,
time_ordered_date_id,
time_promise_date_id,
time_request_date_id,
currency_func_id,
curr_wh_func_id,
inventory_item_id,
item_inv_org_id,
top_model_item_id,
top_model_org_id,
actual_shipment_date,
booked_date,
shipped_date,
fulfilled_date,
schedule_ship_date,
ordered_date,
promise_date,
request_date,
ordered_quantity,
header_id,
h_marketing_source_code_id,
invoice_to_org_id,
marketing_source_code_id,
open_flag,
order_date_type_code,
order_number,
order_quantity_uom,
shippable_flag,
fulfilled_flag,
line_category_code,
line_item_type,
line_number,
item_type_code,
ato_line_id,
count_pdue_line,
count_ship_line,
flow_status_code,
inv_uom_code,
top_model_line_id,
unit_list_price,
unit_selling_price,
service_parent_line_id,
booked_amt_g,
invoiced_amt_g,
shipped_amt_g,
fulfilled_amt_g,
booked_list_amt_g,
booked_amt_f,
invoiced_amt_f,
shipped_amt_f,
fulfilled_amt_f,
booked_list_amt_f,
booked_amt_f1,
invoiced_amt_f1,
shipped_amt_f1,
fulfilled_amt_f1,
booked_list_amt_f1,
booked_qty_inv,
invoiced_qty_inv,
shipped_qty_inv,
fulfilled_qty_inv,
created_by,
last_update_login,
creation_date,
last_updated_by,
last_update_date,
ship_to_party_id,
booked_amt_g1,
invoiced_amt_g1,
shipped_amt_g1,
fulfilled_amt_g1,
booked_list_amt_g1,
freight_charge,
freight_charge_f,
freight_charge_g,
freight_charge_g1,
freight_cost,
freight_cost_f,
freight_cost_g,
freight_cost_g1,
charge_periodicity_code,
blanket_number,
blanket_line_number)
SELECT /*+ PARALLEL(v)*/ v.line_id,
v.item_id,
v.inv_org_id,
v.inv_ou_id,
v.org_ou_id,
v.customer_id,
v.sales_channel_id,
v.return_reason_id,
v.order_category_id,
v.order_source_id,
v.order_type_id,
v.ship_to_org_id,
v.sold_to_org_id,
v.time_act_ship_date_id,
v.time_booked_date_id,
v.time_shipped_date_id,
v.time_fulfilled_date_id,
v.time_schedule_date_id,
v.time_ordered_date_id,
v.time_promise_date_id,
v.time_request_date_id,
v.currency_func_id,
v.curr_wh_func_id,
v.inventory_item_id,
v.item_inv_org_id,
v.top_model_item_id,
v.top_model_org_id,
v.actual_shipment_date,
v.booked_date,
v.shipped_date,
v.fulfilled_date,
v.schedule_ship_date,
v.ordered_date,
v.promise_date,
v.request_date,
v.ordered_quantity,
v.header_id,
v.h_marketing_source_code_id,
v.invoice_to_org_id,
v.marketing_source_code_id,
v.open_flag,
v.order_date_type_code,
v.order_number,
v.order_quantity_uom,
v.shippable_flag,
v.fulfilled_flag,
v.line_category_code,
v.line_item_type,
v.line_number,
v.item_type_code,
v.ato_line_id,
v.count_pdue_line,
v.count_ship_line,
v.flow_status_code,
v.inv_uom_code,
v.top_model_line_id,
v.unit_list_price,
v.unit_selling_price,
v.service_parent_line_id,
v.booked_amt_g,
v.invoiced_amt_g,
v.shipped_amt_g,
v.fulfilled_amt_g,
v.booked_list_amt_g,
v.booked_amt_f,
v.invoiced_amt_f,
v.shipped_amt_f,
v.fulfilled_amt_f,
v.booked_list_amt_f,
v.booked_amt_f1,
v.invoiced_amt_f1,
v.shipped_amt_f1,
v.fulfilled_amt_f1,
v.booked_list_amt_f1,
v.booked_qty_inv,
v.invoiced_qty_inv,
v.shipped_qty_inv,
v.fulfilled_qty_inv,
v.created_by,
v.last_update_login,
v.creation_date,
v.last_updated_by,
v.last_update_date,
v.ship_to_party_id,
v.booked_amt_g1,
v.invoiced_amt_g1,
v.shipped_amt_g1,
v.fulfilled_amt_g1,
v.booked_list_amt_g1,
v.freight_charge,
v.freight_charge_f,
v.freight_charge_g,
v.freight_charge_g1,
v.freight_cost,
v.freight_cost_f,
v.freight_cost_g,
v.freight_cost_g1,
v.charge_periodicity_code,
v.blanket_number,
v.blanket_line_number
FROM ISCBV_BOOK_SUM2_FCV v;
g_errbuf := 'Error in Function INSERT_FACT : '||sqlerrm;
END insert_fact;
(select new.* from ISCBV_BOOK_SUM2_FCV new, ISC_BOOK_SUM2_F old
where new.line_id = old.line_id(+)
and new.batch_id = v_batch_id
and (old.line_id is null
or new.customer_id <> old.customer_id
or new.item_inv_org_id <> old.item_inv_org_id
or new.inv_ou_id <> old.inv_ou_id
or new.fulfilled_flag <> old.fulfilled_flag
or new.open_flag <> old.open_flag
or new.sales_channel_id <> old.sales_channel_id
or new.return_reason_id <> old.return_reason_id
or new.order_source_id <> old.order_source_id
or new.booked_amt_f <> old.booked_amt_f
or new.booked_amt_f1 <> old.booked_amt_f1
or new.booked_amt_g <> old.booked_amt_g
or new.booked_amt_g1 <> old.booked_amt_g1
or new.freight_charge <> old.freight_charge
or new.freight_charge_f <> old.freight_charge_f
or new.freight_charge_g <> old.freight_charge_g
or new.freight_charge_g1 <> old.freight_charge_g1
or new.freight_cost <> old.freight_cost
or new.freight_cost_f <> old.freight_cost_f
or new.freight_cost_g <> old.freight_cost_g
or new.freight_cost_g1 <> old.freight_cost_g1
or new.booked_qty_inv <> old.booked_qty_inv
or new.fulfilled_amt_f <> old.fulfilled_amt_f
or new.fulfilled_amt_f1 <> old.fulfilled_amt_f1
or new.fulfilled_amt_g <> old.fulfilled_amt_g
or new.fulfilled_amt_g1 <> old.fulfilled_amt_g1
or new.fulfilled_qty_inv <> old.fulfilled_qty_inv
or new.invoiced_amt_f <> old.invoiced_amt_f
or new.invoiced_amt_f1 <> old.invoiced_amt_f1
or new.invoiced_amt_g <> old.invoiced_amt_g
or new.invoiced_amt_g1 <> old.invoiced_amt_g1
or new.invoiced_qty_inv <> old.invoiced_qty_inv
or new.shipped_amt_f <> old.shipped_amt_f
or new.shipped_amt_f1 <> old.shipped_amt_f1
or new.shipped_amt_g <> old.shipped_amt_g
or new.shipped_amt_g1 <> old.shipped_amt_g1
or new.shipped_qty_inv <> old.shipped_qty_inv
or new.org_ou_id <> old.org_ou_id
or new.booked_date <> old.booked_date
or new.inventory_item_id <> old.inventory_item_id
or new.order_number <> old.order_number
or new.line_number <> old.line_number
or new.line_category_code <> old.line_category_code
or new.currency_func_id <> old.currency_func_id
or new.curr_wh_func_id <> old.curr_wh_func_id
or new.order_quantity_uom <> old.order_quantity_uom
or new.inv_uom_code <> old.inv_uom_code
or new.ship_to_party_id <> old.ship_to_party_id
or nvl(new.actual_shipment_date,l_date) <> nvl(old.actual_shipment_date, l_date)
or nvl(new.fulfilled_date, l_date) <> nvl(old.fulfilled_date, l_date)
or nvl(new.promise_date, l_date) <> nvl(old.promise_date, l_date)
or nvl(new.request_date, l_date) <> nvl(old.request_date, l_date)
or nvl(new.schedule_ship_date, l_date) <> nvl(old.schedule_ship_date, l_date)
or nvl(new.service_parent_line_id, -1) <> nvl(old.service_parent_line_id, -1)
or nvl(new.invoice_to_org_id, -1) <> nvl(old.invoice_to_org_id, -1)
or nvl(new.ordered_date, l_date) <> nvl(old.ordered_date, l_date)
or nvl(new.ordered_quantity, 0) <> nvl(old.ordered_quantity, 0)
or nvl(new.unit_selling_price, 0) <> nvl(old.unit_selling_price, 0)
or nvl(new.blanket_number,0) <> nvl(old.blanket_number, 0)
or nvl(new.blanket_line_number,0) <> nvl(old.blanket_line_number, 0)
or nvl(new.charge_periodicity_code,'na') <> nvl(old.charge_periodicity_code,'na')
or nvl(new.flow_status_code, 'na') <> nvl(old.flow_status_code, 'na')
or nvl(new.h_marketing_source_code_id, -1) <> nvl(old.h_marketing_source_code_id, -1)
or nvl(new.marketing_source_code_id, -1) <> nvl(old.marketing_source_code_id, -1)
or nvl(new.item_type_code, 'na') <> nvl(old.item_type_code, 'na')
or nvl(new.order_date_type_code, 'na') <> nvl(old.order_date_type_code, 'na')
or nvl(new.shippable_flag, 'na') <> nvl(old.shippable_flag, 'na')
or nvl(new.unit_list_price, 0) <> nvl(old.unit_list_price, 0)
or nvl(new.order_type_id, -1) <> nvl(old.order_type_id, -1)
or nvl(new.sold_to_org_id, -1) <> nvl(old.sold_to_org_id, -1)
or nvl(new.ship_to_org_id, -1) <> nvl(old.ship_to_org_id, -1)
or nvl(new.ato_line_id, -1) <> nvl(old.ato_line_id, -1)
or nvl(new.top_model_line_id, -1) <> nvl(old.top_model_line_id, -1)
or nvl(new.item_id, -1) <> nvl(old.item_id, -1)
or nvl(new.inv_org_id, -1) <> nvl(old.inv_org_id, -1)
or nvl(new.top_model_item_id, -1) <> nvl(old.top_model_item_id, -1)
or nvl(new.top_model_org_id, -1) <> nvl(old.top_model_org_id, -1))) v
ON (f.line_id = v.line_id)
WHEN MATCHED THEN UPDATE SET
f.item_id = v.item_id,
f.inv_org_id = v.inv_org_id,
f.inv_ou_id = v.inv_ou_id,
f.org_ou_id = v.org_ou_id,
f.customer_id = v.customer_id,
f.sales_channel_id = v.sales_channel_id,
f.return_reason_id = v.return_reason_id,
f.order_category_id = v.order_category_id,
f.order_source_id = v.order_source_id,
f.order_type_id = v.order_type_id,
f.ship_to_org_id = v.ship_to_org_id,
f.sold_to_org_id = v.sold_to_org_id,
f.time_act_ship_date_id = v.time_act_ship_date_id,
f.time_booked_date_id = v.time_booked_date_id,
f.time_shipped_date_id = v.time_shipped_date_id,
f.time_fulfilled_date_id = v.time_fulfilled_date_id,
f.time_schedule_date_id = v.time_schedule_date_id,
f.time_ordered_date_id = v.time_ordered_date_id,
f.time_promise_date_id = v.time_promise_date_id,
f.time_request_date_id = v.time_request_date_id,
f.currency_func_id = v.currency_func_id,
f.curr_wh_func_id = v.curr_wh_func_id,
f.inventory_item_id = v.inventory_item_id,
f.item_inv_org_id = v.item_inv_org_id,
f.top_model_item_id = v.top_model_item_id,
f.top_model_org_id = v.top_model_org_id,
f.actual_shipment_date = v.actual_shipment_date,
f.booked_date = v.booked_date,
f.shipped_date = v.shipped_date,
f.fulfilled_date = v.fulfilled_date,
f.schedule_ship_date = v.schedule_ship_date,
f.ordered_date = v.ordered_date,
f.promise_date = v.promise_date,
f.request_date = v.request_date,
f.ordered_quantity = v.ordered_quantity,
f.header_id = v.header_id,
f.h_marketing_source_code_id = v.h_marketing_source_code_id,
f.invoice_to_org_id = v.invoice_to_org_id,
f.marketing_source_code_id = v.marketing_source_code_id,
f.open_flag = v.open_flag,
f.order_date_type_code = v.order_date_type_code,
f.order_number = v.order_number,
f.order_quantity_uom = v.order_quantity_uom,
f.shippable_flag = v.shippable_flag,
f.fulfilled_flag = v.fulfilled_flag,
f.line_category_code = v.line_category_code,
f.line_item_type = v.line_item_type,
f.line_number = v.line_number,
f.item_type_code = v.item_type_code,
f.ato_line_id = v.ato_line_id,
f.count_pdue_line = v.count_pdue_line,
f.count_ship_line = v.count_ship_line,
f.flow_status_code = v.flow_status_code,
f.inv_uom_code = v.inv_uom_code,
f.top_model_line_id = v.top_model_line_id,
f.unit_list_price = v.unit_list_price,
f.unit_selling_price = v.unit_selling_price,
f.service_parent_line_id = v.service_parent_line_id,
f.booked_amt_g = v.booked_amt_g,
f.invoiced_amt_g = v.invoiced_amt_g,
f.shipped_amt_g = v.shipped_amt_g,
f.fulfilled_amt_g = v.fulfilled_amt_g,
f.booked_list_amt_g = v.booked_list_amt_g,
f.booked_amt_f= v.booked_amt_f,
f.invoiced_amt_f= v.invoiced_amt_f,
f.shipped_amt_f = v.shipped_amt_f,
f.fulfilled_amt_f = v.fulfilled_amt_f,
f.booked_list_amt_f = v.booked_list_amt_f,
f.booked_amt_f1= v.booked_amt_f1,
f.invoiced_amt_f1= v.invoiced_amt_f1,
f.shipped_amt_f1 = v.shipped_amt_f1,
f.fulfilled_amt_f1 = v.fulfilled_amt_f1,
f.booked_list_amt_f1 = v.booked_list_amt_f1,
f.booked_qty_inv = v.booked_qty_inv,
f.invoiced_qty_inv = v.invoiced_qty_inv,
f.shipped_qty_inv = v.shipped_qty_inv,
f.fulfilled_qty_inv = v.fulfilled_qty_inv,
f.created_by = v.created_by,
f.last_update_login = v.last_update_login,
f.creation_date = v.creation_date,
f.last_updated_by = v.last_updated_by,
f.last_update_date = v.last_update_date,
f.ship_to_party_id = v.ship_to_party_id,
f.booked_amt_g1 = v.booked_amt_g1,
f.invoiced_amt_g1 = v.invoiced_amt_g1,
f.shipped_amt_g1 = v.shipped_amt_g1,
f.fulfilled_amt_g1 = v.fulfilled_amt_g1,
f.booked_list_amt_g1 = v.booked_list_amt_g1,
f.freight_charge = v.freight_charge,
f.freight_charge_f = v.freight_charge_f,
f.freight_charge_g = v.freight_charge_g,
f.freight_charge_g1 = v.freight_charge_g1,
f.freight_cost = v.freight_cost,
f.freight_cost_f = v.freight_cost_f,
f.freight_cost_g = v.freight_cost_g,
f.freight_cost_g1 = v.freight_cost_g1,
f.charge_periodicity_code = v.charge_periodicity_code,
f.blanket_number = v.blanket_number,
f.blanket_line_number = v.blanket_line_number
WHEN NOT MATCHED THEN INSERT(
f.line_id,
f.item_id,
f.inv_org_id,
f.inv_ou_id,
f.org_ou_id,
f.customer_id,
f.sales_channel_id,
f.return_reason_id,
f.order_category_id,
f.order_source_id,
f.order_type_id,
f.ship_to_org_id,
f.sold_to_org_id,
f.time_act_ship_date_id,
f.time_booked_date_id,
f.time_shipped_date_id,
f.time_fulfilled_date_id,
f.time_schedule_date_id,
f.time_ordered_date_id,
f.time_promise_date_id,
f.time_request_date_id,
f.currency_func_id,
f.curr_wh_func_id,
f.inventory_item_id,
f.item_inv_org_id,
f.top_model_item_id,
f.top_model_org_id,
f.actual_shipment_date,
f.booked_date,
f.shipped_date,
f.fulfilled_date,
f.schedule_ship_date,
f.ordered_date,
f.promise_date,
f.request_date,
f.ordered_quantity,
f.header_id,
f.h_marketing_source_code_id,
f.invoice_to_org_id,
f.marketing_source_code_id,
f.open_flag,
f.order_date_type_code,
f.order_number,
f.order_quantity_uom,
f.shippable_flag,
f.fulfilled_flag,
f.line_category_code,
f.line_item_type,
f.line_number,
f.item_type_code,
f.ato_line_id,
f.count_pdue_line,
f.count_ship_line,
f.flow_status_code,
f.inv_uom_code,
f.top_model_line_id,
f.unit_list_price,
f.unit_selling_price,
f.service_parent_line_id,
f.booked_amt_g,
f.invoiced_amt_g,
f.shipped_amt_g,
f.fulfilled_amt_g,
f.booked_list_amt_g,
f.booked_amt_f,
f.invoiced_amt_f,
f.shipped_amt_f,
f.fulfilled_amt_f,
f.booked_list_amt_f,
f.booked_amt_f1,
f.invoiced_amt_f1,
f.shipped_amt_f1,
f.fulfilled_amt_f1,
f.booked_list_amt_f1,
f.booked_qty_inv,
f.invoiced_qty_inv,
f.shipped_qty_inv,
f.fulfilled_qty_inv,
f.created_by,
f.last_update_login,
f.creation_date,
f.last_updated_by,
f.last_update_date,
f.ship_to_party_id,
f.booked_amt_g1,
f.invoiced_amt_g1,
f.shipped_amt_g1,
f.fulfilled_amt_g1,
f.booked_list_amt_g1,
f.freight_charge,
f.freight_charge_f,
f.freight_charge_g,
f.freight_charge_g1,
f.freight_cost,
f.freight_cost_f,
f.freight_cost_g,
f.freight_cost_g1,
f.charge_periodicity_code,
f.blanket_number,
f.blanket_line_number)
VALUES (
v.line_id,
v.item_id,
v.inv_org_id,
v.inv_ou_id,
v.org_ou_id,
v.customer_id,
v.sales_channel_id,
v.return_reason_id,
v.order_category_id,
v.order_source_id,
v.order_type_id,
v.ship_to_org_id,
v.sold_to_org_id,
v.time_act_ship_date_id,
v.time_booked_date_id,
v.time_shipped_date_id,
v.time_fulfilled_date_id,
v.time_schedule_date_id,
v.time_ordered_date_id,
v.time_promise_date_id,
v.time_request_date_id,
v.currency_func_id,
v.curr_wh_func_id,
v.inventory_item_id,
v.item_inv_org_id,
v.top_model_item_id,
v.top_model_org_id,
v.actual_shipment_date,
v.booked_date,
v.shipped_date,
v.fulfilled_date,
v.schedule_ship_date,
v.ordered_date,
v.promise_date,
v.request_date,
v.ordered_quantity,
v.header_id,
v.h_marketing_source_code_id,
v.invoice_to_org_id,
v.marketing_source_code_id,
v.open_flag,
v.order_date_type_code,
v.order_number,
v.order_quantity_uom,
v.shippable_flag,
v.fulfilled_flag,
v.line_category_code,
v.line_item_type,
v.line_number,
v.item_type_code,
v.ato_line_id,
v.count_pdue_line,
v.count_ship_line,
v.flow_status_code,
v.inv_uom_code,
v.top_model_line_id,
v.unit_list_price,
v.unit_selling_price,
v.service_parent_line_id,
v.booked_amt_g,
v.invoiced_amt_g,
v.shipped_amt_g,
v.fulfilled_amt_g,
v.booked_list_amt_g,
v.booked_amt_f,
v.invoiced_amt_f,
v.shipped_amt_f,
v.fulfilled_amt_f,
v.booked_list_amt_f,
v.booked_amt_f1,
v.invoiced_amt_f1,
v.shipped_amt_f1,
v.fulfilled_amt_f1,
v.booked_list_amt_f1,
v.booked_qty_inv,
v.invoiced_qty_inv,
v.shipped_qty_inv,
v.fulfilled_qty_inv,
v.created_by,
v.last_update_login,
v.creation_date,
v.last_updated_by,
v.last_update_date,
v.ship_to_party_id,
v.booked_amt_g1,
v.invoiced_amt_g1,
v.shipped_amt_g1,
v.fulfilled_amt_g1,
v.booked_list_amt_g1,
v.freight_charge,
v.freight_charge_f,
v.freight_charge_g,
v.freight_charge_g1,
v.freight_cost,
v.freight_cost_f,
v.freight_cost_g,
v.freight_cost_g1,
v.charge_periodicity_code,
v.blanket_number,
v.blanket_line_number);
BIS_COLLECTION_UTILITIES.put_line('Inserting data into fact table');
g_row_count := Insert_fact;
FII_UTIL.Print_Timer('Inserted '||nvl(g_row_count,0)||' rows into the fact table in');
Procedure update_fact(errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2) IS
l_failure EXCEPTION;
l_delete_count NUMBER := 0;
BIS_COLLECTION_UTILITIES.put_line('Last updated date is '|| to_char(g_incre_start_date,'MM/DD/YYYY HH24:MI:SS'));
BIS_COLLECTION_UTILITIES.put_line('Entering function Update_Sales_Fact.');
SELECT nvl(implementation_flag,0)
INTO l_sc_page_implemented
FROM (SELECT sum(decode(implementation_flag,'Y',1,0)) implementation_flag
FROM bis_obj_properties
WHERE object_name IN (SELECT distinct bis.object_name
FROM BIS_OBJ_DEPENDENCY bis,
(SELECT object_name
FROM bis_obj_dependency
START WITH depend_object_name = 'ISC_SALES_CREDITS_F'
CONNECT BY PRIOR object_name = depend_object_name
ORDER BY 1) inline
WHERE bis.object_name = inline.object_name
AND bis.object_type = 'PAGE'));
IF (update_sales_fact = -1) -- call of the sc_f incremental collection function
THEN -- coll of sales credits fact errored out
g_row_count := -1;
BIS_COLLECTION_UTILITIES.put_line('Exiting function Update_Sales_Fact.');
DELETE FROM ONT_DBI_CHANGE_LOG
WHERE rowid IN (select log_rowid from isc_dbi_change_log)
AND last_update_date < g_incre_start_date;
FII_UTIL.Print_Timer('Deleted ' || sql%rowcount || ' rows from OM log table in');
END update_fact;
/* Insert into ISC_SALES_CREDITS_F */
BIS_COLLECTION_UTILITIES.put_line(' ');
BIS_COLLECTION_UTILITIES.put_line('Inserting data into sales fact table');
insert /*+ append parallel(f) */ into isc_sales_credits_f f
with s as (
select /*+ ordered use_hash(sc) parallel(sc) parallel(sr)
pq_distribute(sr,hash,hash) */
sc.sales_credit_id, sc.percent, sc.sales_credit_type_id,
sc.salesrep_id, sc.header_id, sc.line_id, sr.resource_id,
sr.org_id, sc.sales_group_id group_id, sc.created_by, sc.creation_date,
sc.last_updated_by, sc.last_update_date, sc.last_update_login
from oe_sales_credit_types sc_typ,
oe_sales_credits sc,
jtf_rs_salesreps sr
where sc.sales_group_id is not null
and sc.salesrep_id = sr.salesrep_id
and sc.sales_credit_type_id = sc_typ.sales_credit_type_id
and sc_typ.quota_flag = 'Y'
union all
select /*+ ordered use_hash(sc) parallel(sc) parallel(sg)
pq_distribute(sg,hash,hash) */
sc.sales_credit_id, sc.percent, sc.sales_credit_type_id,
sc.salesrep_id, sc.header_id, sc.line_id, sg.resource_id,
sg.org_id, sg.group_id, sc.created_by, sc.creation_date,
sc.last_updated_by, sc.last_update_date, sc.last_update_login
from oe_sales_credit_types sc_typ,
oe_sales_credits sc,
jtf_rs_srp_groups sg
where sc.sales_group_id is null
and sc.salesrep_id = sg.salesrep_id
and sc.last_update_date between sg.start_date and sg.end_date
and sc.sales_credit_type_id = sc_typ.sales_credit_type_id
and sc_typ.quota_flag = 'Y')
select pk, sales_credit_id, resource_id, group_id, header_id, line_id,
percent, sales_credit_type_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login
from (
select pk, sales_credit_id, resource_id, group_id, header_id, line_id,
percent, sales_credit_type_id, created_by, creation_date,
last_updated_by, last_update_date, last_update_login,
rank() over (partition by line_id order by rnk) low_rnk
from (
select /*+ parallel(s) */
'DIRECT-'||s.sales_credit_id pk, s.sales_credit_id, s.group_id,
t5.header_id, t5.line_id, 1 rnk, s.resource_id, s.percent,
s.sales_credit_type_id, s.created_by, s.creation_date,
s.last_updated_by, s.last_update_date, s.last_update_login
from isc_book_sum2_f t5, s
where s.line_id = t5.line_id
and s.org_id = t5.org_ou_id
union all
select /*+ parallel(s) parallel(t7a) use_hash(s) pq_distribute(s,hash,hash) */
'SERVICE_PARENT-'||t7a.line_id||'-'||s.sales_credit_id pk,
s.sales_credit_id, s.group_id, t7a.header_id, t7a.line_id, 2 rnk,
s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
s.creation_date, s.last_updated_by, s.last_update_date,
s.last_update_login
from isc_book_sum2_f t7a, s
where s.line_id = t7a.service_parent_line_id
and s.org_id = t7a.org_ou_id
and t7a.item_type_code = 'SERVICE'
union all
select /*+ parallel(s) parallel(t7b2) use_hash(s) pq_distribute(s,hash,hash)
parallel(t7b1) use_hash(t7b1) pq_distribute(t7b1,hash,hash) */
'SERVICE_PARENT_TOPMODEL-'||t7b2.line_id||'-'||s.sales_credit_id pk,
s.sales_credit_id, s.group_id, t7b2.header_id, t7b2.line_id, 3 rnk,
s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
s.creation_date, s.last_updated_by, s.last_update_date,
s.last_update_login
from isc_book_sum2_f t7b2, isc_book_sum2_f t7b1, s
where s.line_id = t7b1.top_model_line_id
and s.org_id = t7b1.org_ou_id
and t7b1.line_id = t7b2.service_parent_line_id
and t7b2.item_type_code = 'SERVICE'
union all
select /*+ ordered parallel(s) parallel(t7b1) use_hash(s) pq_distribute(s,hash,hash) */
'TOPMODEL-'||t7b1.line_id||'-'||s.sales_credit_id pk,
s.sales_credit_id, s.group_id, t7b1.header_id, t7b1.line_id, 4 rnk,
s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
s.creation_date, s.last_updated_by, s.last_update_date,
s.last_update_login
from isc_book_sum2_f t7b1, s
where s.line_id = t7b1.top_model_line_id
and s.org_id = t7b1.org_ou_id
union all
select /*+ ordered parallel(s) parallel(t11) use_hash(s) pq_distribute(s,hash,hash) */
'HEADER-'||t11.line_id||'-'||s.sales_credit_id pk,
s.sales_credit_id, s.group_id, t11.header_id, t11.line_id, 5 rnk,
s.resource_id, s.percent, s.sales_credit_type_id, s.created_by,
s.creation_date, s.last_updated_by, s.last_update_date,
s.last_update_login
from isc_book_sum2_f t11, s
where s.line_id is null
and s.org_id = t11.org_ou_id
and s.header_id = t11.header_id))
where low_rnk = 1;
FII_UTIL.Print_Timer('Inserted '||g_row_count||' rows into the sales fact table in');
Procedure update_sales_fact_dummy(errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2) IS
BEGIN
null;
END update_sales_fact_dummy;