[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE( M2A_DBLINK,
NULL, ' ',
'@'||M2A_DBLINK)
INTO v_dblink
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= arg_instance_id;
lv_sql_stmt_new := ' insert into MSC_SOURCING_TRANSACTIONS( '
||' INVENTORY_ITEM_ID, '
||' ORGANIZATION_ID, '
||' TRANSACTION_DATE, '
||' TRANSACTION_QTY, '
||' TRANSACTION_TYPE) '
||' SELECT inventory_item_id, organization_id, '
||' transaction_date, transaction_qty , 1 '
||' FROM MRP_AP_INNER_ORG_TRXS_V'||v_dblink||' x'
||' where x.inventory_item_id is not null '
|| lv_sql_stmt_app ;
lv_sql_stmt_new := ' insert into MSC_SOURCING_TRANSACTIONS( '
||' INVENTORY_ITEM_ID, '
||' ORGANIZATION_ID, '
||' SOURCE_ORG_ID, '
||' TRANSACTION_DATE, '
||' TRANSACTION_QTY, '
||' TRANSACTION_TYPE) '
||' SELECT inventory_item_id, organization_id, '
||' source_org_id, transaction_date, transaction_qty ,2 '
||' FROM MRP_AP_INTER_ORG_TRXS_V'||v_dblink||' x'
||' where x.inventory_item_id is not null '
|| lv_sql_stmt_app ;
lv_sql_stmt_new := ' insert into MSC_SOURCING_TRANSACTIONS( '
||' INVENTORY_ITEM_ID, '
||' SUPPLIER_ID, '
||' SUPPLIER_SITE_ID, '
||' TRANSACTION_DATE, '
||' TRANSACTION_QTY, '
||' TRANSACTION_TYPE) '
||' SELECT inventory_item_id, SUPPLIER_ID,nvl(SUPPLIER_SITE_ID,-1), '
||' transaction_date, transaction_qty ,3 '
||' FROM MRP_AP_PO_SUPPLIER_TRXS_V'||v_dblink||' x'
||' where x.inventory_item_id is not null '
|| lv_sql_stmt_app ;
SELECT DECODE( M2A_DBLINK,
NULL, ' ',
'@'||M2A_DBLINK)
INTO v_dblink
FROM MSC_APPS_INSTANCES
WHERE INSTANCE_ID= arg_instance_id;
lv_sql_stmt_new := ' insert into MSC_PO_RECEIPTS( '
||' RECEIPT_ID, '
||' SR_INSTANCE_ID, '
||' INVENTORY_ITEM_ID, '
||' ORGANIZATION_ID, '
||' SUPPLIER_ID, '
||' SUPPLIER_SITE_ID, '
||' TRANSACTION_DATE, '
||' TRANSACTION_QTY, '
||' LAST_UPDATE_DATE, '
||' LAST_UPDATED_BY, '
||' CREATION_DATE, '
||' CREATED_BY) '
||' SELECT x.receipt_id,:v_instance_id,item.inventory_item_id,x.organization_id, TP.TP_ID,nvl(TPS.TP_SITE_ID,-1), '
||' x.transaction_date, x.transaction_qty ,:v_current_date, :v_current_user, '
||' :v_current_date, :v_current_user'
||' FROM MRP_AP_PO_SUPPLIER_TRXS_V'||v_dblink||' x, MSC_TP_ID_LID tp, MSC_ITEM_ID_LID item, '
||' MSC_TP_SITE_ID_LID tps '
||' where x.inventory_item_id is not null and '
||' x.inventory_item_id = item.sr_inventory_item_id and '
||' item.sr_instance_id= :v_instance_id and '
||' x.supplier_id = tp.sr_tp_id and '
||' tp.sr_instance_id= :v_instance_id and '
||' x.supplier_site_id(+) = tps.sr_tp_site_id and '
||' tps.partner_type = 1 and '
||' tp.partner_type = 1 and '
||' tps.sr_instance_id= :v_instance_id and '
||' x.organization_id '|| arg_org_sub_str
|| lv_sql_stmt_app ;
SELECT
iil.sr_inventory_item_id,
iil.inventory_item_id,
sr_view.organization_id,
sr_view.source_org_id,
til.sr_tp_id,
til.tp_id,
nvl(tsil.sr_tp_site_id,-1) sr_tp_site_id,
tsil.tp_site_id,
sr_view.effective_date,
sr_view.sourcing_rule_id,
sr_view.sourcing_level,
msa.assignment_set_id
FROM msc_assignment_sets msa,
MSC_BOD_SOURCING_RULES_V sr_view,
MSC_ITEM_ID_LID iil,
MSC_TP_ID_LID til,
MSC_TP_SITE_ID_LID tsil
WHERE iil.inventory_item_id= sr_view.inventory_item_id
AND iil.sr_instance_id= sr_view.sr_instance_id
AND til.tp_id(+)= sr_view.supplier_id
AND til.sr_instance_id(+)= sr_view.sr_instance_id
AND til.partner_type(+)= 1
AND tsil.tp_site_id(+)= sr_view.supplier_site_id
AND tsil.sr_instance_id(+)= sr_view.sr_instance_id
AND tsil.partner_type(+)= 1
AND sr_view.effective_date <= v_current_date
AND NVL(sr_view.disable_date, TRUNC(v_current_date) + 1)
> TRUNC(v_current_date)
AND sr_view.assignment_set_id= msa.assignment_set_id
and msa.sr_instance_id = v_instance_id
ORDER BY
msa.assignment_set_id,
sr_view.organization_id,
sr_view.inventory_item_id,
sr_view.sourcing_level ASC;
SELECT TO_NUMBER(FND_PROFILE.VALUE('MSC_START_SOURCING_HISTORY'))
into start_date_offset
from dual;
then the table msc_sourcing_history would have been deleted and relcalculation will be
done from this new date */
IF (start_date_offset IS NOT NULL) THEN
IF start_date_new > lv_start_date THEN
lv_start_date := start_date_new - 1;
SELECT msh.ROWID,
msh.last_calculated_date,
msh.historical_allocation,
SYS_YES
INTO lv_rowid,
lv_start_date,
lv_historical_allocation,
lv_record_exists
FROM MSC_SOURCING_HISTORY msh
WHERE msh.inventory_item_id= arg_item_id
AND msh.organization_id= arg_org_id
AND msh.sr_instance_id= v_instance_id
AND msh.sourcing_rule_id= arg_sourcing_rule_id
AND NVL( msh.source_org_id,-1)= NVL( arg_source_org,-1)
AND NVL( msh.supplier_id,-1)= NVL ( arg_supplier_id,-1)
AND NVL( msh.supplier_site_id,-1)= NVL( arg_supplier_site_id,-1);
select GREATEST(NVL(SUM(transaction_qty),0),0)
into lv_total_alloc_qty
from MSC_SOURCING_TRANSACTIONS
where inventory_item_id = arg_sr_item_id
and organization_id = arg_org_id
and transaction_date > lv_start_date
and trunc(transaction_date) <= trunc(arg_end_date)
and transaction_type = 1;
select GREATEST(NVL(SUM(transaction_qty),0),0)
into lv_total_alloc_qty
from MSC_SOURCING_TRANSACTIONS
where inventory_item_id = arg_sr_item_id
and organization_id = arg_org_id
and SOURCE_ORG_ID = arg_source_org
and transaction_date > lv_start_date
and trunc(transaction_date) <= trunc(arg_end_date)
and transaction_type = 2;
select GREATEST(NVL(SUM(transaction_qty),0),0)
into lv_total_alloc_qty
from MSC_SOURCING_TRANSACTIONS
where inventory_item_id = arg_sr_item_id
and SUPPLIER_ID = arg_sr_supplier_id
and SUPPLIER_SITE_ID = arg_sr_supplier_site_id
and transaction_date > lv_start_date
and trunc(transaction_date) <= trunc(arg_end_date)
and transaction_type = 3;
UPDATE MSC_SOURCING_HISTORY
SET historical_allocation= lv_total_alloc_qty,
last_calculated_date = v_current_date,
LAST_UPDATED_BY = v_current_user,
LAST_UPDATE_DATE = v_current_date
WHERE rowid= lv_rowid;
INSERT INTO MSC_SOURCING_HISTORY
( inventory_item_id,
organization_id,
sourcing_rule_id,
source_org_id,
source_sr_instance_id,
supplier_id,
supplier_site_id,
historical_allocation,
refresh_number,
last_calculated_date,
sr_instance_id,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY)
VALUES
( arg_item_id,
arg_org_id,
arg_sourcing_rule_id,
arg_source_org,
v_instance_id,
arg_supplier_id,
arg_supplier_site_id,
lv_total_alloc_qty,
v_refresh_number,
v_current_date,
v_instance_id,
v_current_user,
v_current_date,
v_current_date,
v_current_user);
'SELECT GREATEST(NVL(SUM(transaction_qty),0),0)'
||' FROM MRP_AP_INNER_ORG_TRXS_V'||v_dblink||' x'
||' WHERE trunc(x.transaction_date) > trunc(:arg_start_date)'
||' AND trunc(x.transaction_date) <= trunc(:arg_end_date)'
||' AND x.inventory_item_id = :arg_inventory_item_id'
||' AND x.organization_id = :arg_organization_id';
' SELECT GREATEST(NVL(SUM(transaction_qty),0),0)'
||' FROM MRP_AP_INTER_ORG_TRXS_V'||v_dblink||' x'
||' WHERE trunc(x.transaction_date) > trunc(:arg_start_date)'
||' AND trunc(x.transaction_date) <= trunc(:arg_end_date)'
||' AND x.inventory_item_id = :arg_inventory_item_id'
||' AND x.organization_id = :arg_organization_id'
||' AND x.source_org_id = :arg_source_org_id';
' SELECT GREATEST(NVL(SUM(transaction_qty),0),0)'
||' FROM MRP_AP_PO_SUPPLIER_TRXS_V'||v_dblink||' x'
||' WHERE trunc(x.transaction_date) > trunc(:arg_start_date)'
||' AND trunc(x.transaction_date) <= trunc(:arg_end_date)'
||' AND x.inventory_item_id = :arg_inventory_item_id'
||' AND x.supplier_id = :arg_supplier_id'
||' AND NVL(x.supplier_site_id, -1)'
||' = NVL(:arg_supplier_site_id,-1)';