[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select exchange_rate into l_exchange_rate
from ap_invoices
where invoice_id = l_invoice_id;
select exchange_rate into l_exchange_rate
from ra_customer_trx
where customer_trx_id = l_invoice_id;
select exchange_date into l_exchange_date
from ap_invoices
where invoice_id = l_invoice_id;
select exchange_date into l_exchange_date
from ra_customer_trx
where customer_trx_id = l_invoice_id;
select
conversion_rate into l_conversion_rate
from
mtl_uom_conversions_view muc,
mtl_movement_parameters mmp
where
muc.inventory_item_id = l_inventory_item_id
and muc.organization_id = l_organization_id
and muc.uom_code = mmp.weight_uom_code
and mmp.entity_org_id = P_LEGAL_ENTITY_ID;
select
conversion_rate into l_conversion_rate
from
mtl_uom_conversions_view
where
inventory_item_id = l_inventory_item_id
and organization_id = l_organization_id
and uom_code = l_transaction_uom_code;
select
conversion_rate into l_conversion_rate
from
mtl_uom_conversions_view muc,
mtl_movement_parameters mmp
where
muc.inventory_item_id = l_inventory_item_id
and muc.organization_id = l_organization_id
and muc.uom_code = mmp.weight_uom_code
and mmp.entity_org_id = P_LEGAL_ENTITY_ID;
procedure BEFORE_REPORT_UPDATES
(P_USER_ID in number,
P_CONC_LOGIN_ID in number,
P_PERIOD_NAME in varchar2,
P_CONC_REQUEST_ID in number,
P_CONC_APPLICATION_ID in number,
P_CONC_PROGRAM_ID in number,
P_REPORT_OPTION in varchar2,
P_MOVEMENT_TYPE in varchar2,
P_LEGAL_ENTITY_ID in number,
P_REPORT_REFERENCE in number,
P_FORMAT_TYPE in varchar2,
C_CONVERSION_TYPE in varchar2,
C_CONVERSION_OPTION in varchar2,
C_SET_OF_BOOKS_ID in number,
C_START_DATE in date,
C_END_DATE in date,
C_CURRENCY_CODE in varchar2)
is
--
begin
--
------------------------------------------------------
-- if REPORT OPTION is not Nullify Official/Summary --
------------------------------------------------------
if P_REPORT_OPTION <> 'NO/S'
then
begin
-------------------------------------------------------
-- Update of INVOICE_DATE_REFERENCE --
-- in MTL_MOVEMENT_STATISTICS table --
-- if DOCUMENT_SOURCE_TYPE is not Miscellaneous --
-- (Required for Where Clause of select and updates) --
-------------------------------------------------------
update mtl_movement_statistics mms
set invoice_date_reference = (select invoice_date
from ap_invoices
where invoice_id = mms.invoice_id)
where mms.movement_type = P_MOVEMENT_TYPE
and mms.entity_org_id = P_LEGAL_ENTITY_ID
and mms.movement_status = 'O'
and mms.document_source_type = 'PO'
and mms.invoice_id is not null;
update mtl_movement_statistics mms
set invoice_date_reference = (select trx_date
from ra_customer_trx
where customer_trx_id = mms.invoice_id)
where mms.movement_type = P_MOVEMENT_TYPE
and mms.entity_org_id = P_LEGAL_ENTITY_ID
and mms.movement_status = 'O'
and mms.document_source_type = 'SO'
and mms.invoice_id is not null;
update mtl_movement_statistics mms
set
last_update_date = sysdate,
last_updated_by = P_USER_ID,
last_update_login = P_CONC_LOGIN_ID,
period_name = P_PERIOD_NAME,
report_reference = P_REPORT_REFERENCE,
report_date = MTL_MOVEMENT_RPT_PKG.REPORT_DATE_CALC
(invoice_date_reference,
transaction_date)
where
mms.movement_type = P_MOVEMENT_TYPE
and mms.entity_org_id = P_LEGAL_ENTITY_ID
and ((decode(P_MOVEMENT_TYPE,
'D', mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code)
in (select territory_code from fnd_territories_vl)
and decode(P_MOVEMENT_TYPE,
'D',mms.dispatch_territory_code,
'DA', mms.dispatch_territory_code,
'A', mms.destination_territory_code,
'AA', mms.destination_territory_code) = P_FORMAT_TYPE
and decode(P_MOVEMENT_TYPE,
'D',mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
or P_FORMAT_TYPE = 'GEN')
and (mms.movement_type in ('AA','DA')
or
(mms.movement_type not in ('AA','DA')
and ((mms.invoice_id is null and mms.invoice_reference is null
and add_months(mms.transaction_date,1)
between C_START_DATE and C_END_DATE)
or
((mms.invoice_id is not null or mms.invoice_reference is not null)
and (mms.invoice_date_reference
between transaction_date
and add_months(mms.transaction_date,1)
or
mms.transaction_date between C_START_DATE and C_END_DATE)))))
and mms.movement_status = 'O';
update mtl_movement_statistics mms
set
currency_conversion_rate = MTL_MOVEMENT_RPT_PKG.EXCHANGE_RATE_CALC
(C_CONVERSION_OPTION,
C_CONVERSION_TYPE,
C_SET_OF_BOOKS_ID,
C_END_DATE,
C_CURRENCY_CODE,
currency_code,
transaction_date,
invoice_id,
document_source_type,
movement_type),
currency_conversion_type = C_CONVERSION_TYPE,
currency_conversion_date = MTL_MOVEMENT_RPT_PKG.CONVERSION_DATE_CALC
(C_END_DATE,
C_CONVERSION_OPTION,
C_CURRENCY_CODE,
currency_code,
transaction_date,
invoice_id,
document_source_type,
movement_type)
where
mms.movement_type = P_MOVEMENT_TYPE
and mms.entity_org_id = P_LEGAL_ENTITY_ID
and ((decode(P_MOVEMENT_TYPE,
'D', mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code)
in (select territory_code from fnd_territories_vl)
and decode(P_MOVEMENT_TYPE,
'D',mms.dispatch_territory_code,
'DA', mms.dispatch_territory_code,
'A', mms.destination_territory_code,
'AA', mms.destination_territory_code) = P_FORMAT_TYPE
and decode(P_MOVEMENT_TYPE,
'D',mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
or P_FORMAT_TYPE = 'GEN')
and (mms.movement_type in ('AA','DA')
or
(mms.movement_type not in ('AA','DA')
and ((mms.invoice_id is null and mms.invoice_reference is null
and add_months(mms.transaction_date,1)
between C_START_DATE and C_END_DATE)
or
((mms.invoice_id is not null or mms.invoice_reference is not null)
and (mms.invoice_date_reference
between transaction_date
and add_months(mms.transaction_date,1)
or
mms.transaction_date between C_START_DATE and C_END_DATE)))))
and mms.movement_status = 'O';
update mtl_movement_statistics mms
set
unit_weight = MTL_MOVEMENT_RPT_PKG.UNIT_WEIGHT_CALC
(inventory_item_id,
organization_id,
P_LEGAL_ENTITY_ID),
total_weight = MTL_MOVEMENT_RPT_PKG.WEIGHT_CALC
(total_weight,
inventory_item_id,
organization_id,
transaction_quantity,
transaction_uom_code,
P_LEGAL_ENTITY_ID,
P_FORMAT_TYPE)
where
mms.movement_type = P_MOVEMENT_TYPE
and mms.entity_org_id = P_LEGAL_ENTITY_ID
and ((decode(P_MOVEMENT_TYPE,
'D', mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code)
in (select territory_code from fnd_territories_vl)
and decode(P_MOVEMENT_TYPE,
'D',mms.dispatch_territory_code,
'DA', mms.dispatch_territory_code,
'A', mms.destination_territory_code,
'AA', mms.destination_territory_code) = P_FORMAT_TYPE
and decode(P_MOVEMENT_TYPE,
'D',mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
or P_FORMAT_TYPE = 'GEN')
and (mms.movement_type in ('AA','DA')
or
(mms.movement_type not in ('AA','DA')
and ((mms.invoice_id is null and mms.invoice_reference is null
and add_months(mms.transaction_date,1)
between C_START_DATE and C_END_DATE)
or
((mms.invoice_id is not null or mms.invoice_reference is not null)
and (mms.invoice_date_reference
between transaction_date
and add_months(mms.transaction_date,1)
or
mms.transaction_date between C_START_DATE and C_END_DATE)))))
and mms.movement_status = 'O'
and weight_method = 'S';
end BEFORE_REPORT_UPDATES;
procedure AFTER_REPORT_UPDATES
(P_USER_ID in number,
P_CONC_LOGIN_ID in number,
P_PERIOD_NAME in varchar2,
P_REPORT_OPTION in varchar2,
P_MOVEMENT_TYPE in varchar,
P_LEGAL_ENTITY_ID in number,
P_REPORT_REFERENCE in number,
P_FORMAT_TYPE in varchar2,
C_START_DATE in date,
C_END_DATE in date)
is
begin
--
------------------------------------------
-- if REPORT OPTION is Official/Summary --
--------------------------------------------
if P_REPORT_OPTION = 'O/S'
then
begin
------------------------------------------
-- Update mtl_movement_parameters table --
------------------------------------------
update mtl_movement_parameters
set
last_update_date = sysdate,
last_updated_by = P_USER_ID,
last_update_login = P_CONC_LOGIN_ID
where entity_org_id = P_LEGAL_ENTITY_ID;
update mtl_movement_parameters
set
last_arrival_id = P_REPORT_REFERENCE,
last_arrival_period = P_PERIOD_NAME
where entity_org_id = P_LEGAL_ENTITY_ID;
update mtl_movement_parameters
set
last_arrival_adj_id = P_REPORT_REFERENCE,
last_arrival_adj_period = P_PERIOD_NAME
where entity_org_id = P_LEGAL_ENTITY_ID;
update mtl_movement_parameters
set
last_dispatch_id = P_REPORT_REFERENCE,
last_dispatch_period = P_PERIOD_NAME
where entity_org_id = P_LEGAL_ENTITY_ID;
update mtl_movement_parameters
set
last_dispatch_adj_id = P_REPORT_REFERENCE,
last_dispatch_adj_period = P_PERIOD_NAME
where entity_org_id = P_LEGAL_ENTITY_ID;
update mtl_movement_statistics mms
set
movement_status = 'F'
where
mms.movement_type = P_MOVEMENT_TYPE
and mms.entity_org_id = P_LEGAL_ENTITY_ID
and ((decode(P_MOVEMENT_TYPE,
'D', mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code)
in (select territory_code from fnd_territories_vl)
and decode(P_MOVEMENT_TYPE,
'D',mms.dispatch_territory_code,
'DA', mms.dispatch_territory_code,
'A', mms.destination_territory_code,
'AA', mms.destination_territory_code) = P_FORMAT_TYPE
and decode(P_MOVEMENT_TYPE,
'D',mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
or P_FORMAT_TYPE = 'GEN')
and (mms.movement_type in ('AA','DA')
or
(mms.movement_type not in ('AA','DA')
and ((mms.invoice_id is null and mms.invoice_reference is null
and add_months(mms.transaction_date,1)
between C_START_DATE and C_END_DATE)
or
((mms.invoice_id is not null or mms.invoice_reference is not null)
and (mms.invoice_date_reference
between transaction_date
and add_months(mms.transaction_date,1)
or
mms.transaction_date between C_START_DATE and C_END_DATE)))))
and mms.movement_status = 'O';
update mtl_movement_statistics mms
set
movement_status = 'O',
last_update_date = sysdate,
last_updated_by = P_USER_ID,
last_update_login = P_CONC_LOGIN_ID,
period_name = null,
report_reference = null,
report_date = null
where
mms.movement_type = P_MOVEMENT_TYPE
and mms.entity_org_id = P_LEGAL_ENTITY_ID
and ((decode(P_MOVEMENT_TYPE,
'D', mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code)
in (select territory_code from fnd_territories_vl)
and decode(P_MOVEMENT_TYPE,
'D',mms.dispatch_territory_code,
'DA', mms.dispatch_territory_code,
'A', mms.destination_territory_code,
'AA', mms.destination_territory_code) = P_FORMAT_TYPE
and decode(P_MOVEMENT_TYPE,
'D',mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
or P_FORMAT_TYPE = 'GEN')
and (mms.movement_type in ('AA','DA')
or
(mms.movement_type not in ('AA','DA')
and ((mms.invoice_id is null and mms.invoice_reference is null
and add_months(mms.transaction_date,1)
between C_START_DATE and C_END_DATE)
or
((mms.invoice_id is not null or mms.invoice_reference is not null)
and (mms.invoice_date_reference
between transaction_date
and add_months(mms.transaction_date,1)
or
mms.transaction_date between C_START_DATE and C_END_DATE)))))
and mms.movement_status = 'F';
update mtl_movement_statistics mms
set
currency_conversion_rate = null,
currency_conversion_type = null,
currency_conversion_date = null
where
mms.movement_type = P_MOVEMENT_TYPE
and mms.entity_org_id = P_LEGAL_ENTITY_ID
and ((decode(P_MOVEMENT_TYPE,
'D', mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code)
in (select territory_code from fnd_territories_vl)
and decode(P_MOVEMENT_TYPE,
'D',mms.dispatch_territory_code,
'DA', mms.dispatch_territory_code,
'A', mms.destination_territory_code,
'AA', mms.destination_territory_code) = P_FORMAT_TYPE
and decode(P_MOVEMENT_TYPE,
'D',mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
or P_FORMAT_TYPE = 'GEN')
and (mms.movement_type in ('AA','DA')
or
(mms.movement_type not in ('AA','DA')
and ((mms.invoice_id is null and mms.invoice_reference is null
and add_months(mms.transaction_date,1)
between C_START_DATE and C_END_DATE)
or
((mms.invoice_id is not null or mms.invoice_reference is not null)
and (mms.invoice_date_reference
between transaction_date
and add_months(mms.transaction_date,1)
or
mms.transaction_date between C_START_DATE and C_END_DATE)))))
and mms.movement_status = 'F'
and mms.document_source_type <> 'MISC';
update mtl_movement_statistics mms
set
unit_weight = null,
total_weight = null
where
mms.movement_type = P_MOVEMENT_TYPE
and mms.entity_org_id = P_LEGAL_ENTITY_ID
and ((decode(P_MOVEMENT_TYPE,
'D', mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code)
in (select territory_code from fnd_territories_vl)
and decode(P_MOVEMENT_TYPE,
'D',mms.dispatch_territory_code,
'DA', mms.dispatch_territory_code,
'A', mms.destination_territory_code,
'AA', mms.destination_territory_code) = P_FORMAT_TYPE
and decode(P_MOVEMENT_TYPE,
'D',mms.destination_territory_code,
'DA', mms.destination_territory_code,
'A', mms.dispatch_territory_code,
'AA', mms.dispatch_territory_code) <> P_FORMAT_TYPE)
or P_FORMAT_TYPE = 'GEN')
and (mms.movement_type in ('AA','DA')
or
(mms.movement_type not in ('AA','DA')
and ((mms.invoice_id is null and mms.invoice_reference is null
and add_months(mms.transaction_date,1)
between C_START_DATE and C_END_DATE)
or
((mms.invoice_id is not null or mms.invoice_reference is not null)
and (mms.invoice_date_reference
between transaction_date
and add_months(mms.transaction_date,1)
or
mms.transaction_date between C_START_DATE and C_END_DATE)))))
and mms.movement_status = 'F'
and mms.weight_method <> 'M';
end AFTER_REPORT_UPDATES;