[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select primary_uom_code into l_uom
from mtl_system_items
where inventory_item_id = p_id
and rownum =1;
SELECT max(a.party_id) into l_party_id
FROM hz_cust_accounts a
WHERE a.cust_account_id = p_id;
SELECT max(a.party_site_id) into l_party_site_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b
WHERE b.site_use_id = p_id
AND b.cust_acct_site_id = a.cust_acct_site_id;
SELECT user_profile_option_name
FROM fnd_profile_options_vl
WHERE profile_option_name = l_profile_option_name;
SELECT distinct
transaction_date,
uom_code,
currency_code,
common_uom_code,
common_currency_code,
common_quantity,
common_amount
FROM ozf_sales_transactions_all
WHERE error_flag = 'Y'
AND SOURCE_CODE = 'OM';
UPDATE ozf_sales_transactions_all
SET common_amount = gl_currency_api.convert_amount_sql(currency_code,
common_currency_code,
transaction_date,
l_curr_conv_type,
amount) ,
common_quantity = inv_convert.inv_um_convert(inventory_item_id,
NULL,
quantity,
uom_code,
common_uom_code,
NULL,
NULL) ,
error_flag = DECODE(sign(inv_convert.inv_um_convert( inventory_item_id,
NULL,
quantity,
uom_code,
common_uom_code,
NULL,
NULL)
), -1, 'Y',
DECODE(
sign(gl_currency_api.convert_amount_sql(currency_code,
common_currency_code,
transaction_date,
l_curr_conv_type,
amount)
),-1,'Y','N'
)
)
WHERE source_code = 'OM'
AND error_flag = 'Y';
ozf_utility_pvt.write_conc_log(' -- Inserting New transaction ');
INSERT INTO ozf_sales_transactions_all(
SALES_TRANSACTION_ID ,
OBJECT_VERSION_NUMBER ,
LAST_UPDATE_DATE ,
LAST_UPDATED_BY ,
CREATION_DATE ,
REQUEST_ID ,
CREATED_BY ,
CREATED_FROM ,
LAST_UPDATE_LOGIN ,
PROGRAM_APPLICATION_ID ,
PROGRAM_UPDATE_DATE ,
PROGRAM_ID ,
SOLD_TO_CUST_ACCOUNT_ID ,
BILL_TO_SITE_USE_ID ,
SHIP_TO_SITE_USE_ID ,
TRANSACTION_DATE,
QUANTITY ,
UOM_CODE ,
AMOUNT ,
CURRENCY_CODE ,
INVENTORY_ITEM_ID ,
PRIMARY_QUANTITY ,
PRIMARY_UOM_CODE ,
AVAILABLE_PRIMARY_QUANTITY ,
COMMON_QUANTITY ,
COMMON_UOM_CODE ,
COMMON_CURRENCY_CODE ,
COMMON_AMOUNT ,
ERROR_FLAG,
HEADER_ID ,
LINE_ID ,
ORG_ID,
SOURCE_CODE,
TRANSFER_TYPE,
SOLD_TO_PARTY_ID,
SOLD_TO_PARTY_SITE_ID
)
SELECT ozf_sales_transactions_all_s.nextval,
1,
SYSDATE,
FND_GLOBAL.user_id,
SYSDATE,
-1,
FND_GLOBAL.user_id,
'OZFVRFSB',
-1,
NULL, --PROGRAM_APPLICATION_ID
NULL, --PROGRAM_UPDATE_DATE
NULL, --PROGRAM_ID
ln.sold_to_org_id, --SOLD_TO_CUST_ACCOUNT_ID ,
ln.invoice_to_org_id, --BILL_TO_SITE_USE_ID ,
ln.ship_to_org_id, --SHIP_TO_SITE_USE_ID ,
NVL(TRUNC(ln.actual_shipment_date),TRUNC(ln.request_date)),
/* 4590570
DECODE(ln.line_category_code,
'ORDER', TRUNC(ln.actual_shipment_date),
'RETURN', TRUNC(rln.actual_shipment_date)
), -- TRANSACTION_DATE
*/
NVL(ln.shipped_quantity,ln.ordered_quantity), --QUANTITY ,
ln.order_quantity_uom, --UOM ,
ln.unit_selling_price* NVL(ln.shipped_quantity,ln.ordered_quantity), --AMOUNT ,
hdr.transactional_curr_code, --CURRENCY_CODE ,
ln.inventory_item_id, --INVENTORY_ITEM_ID ,
inv_convert.inv_um_convert(ln.inventory_item_id,
NULL,
NVL(ln.shipped_quantity,ln.ordered_quantity),
ln.order_quantity_uom,
get_primary_uom(to_number(ln.inventory_item_id)),
NULL,
NULL),--PRIMARY_QUANTITY ,
get_primary_uom(to_number(ln.inventory_item_id)), --PRIMARY_UOM ,
inv_convert.inv_um_convert(ln.inventory_item_id,
NULL,
NVL(ln.shipped_quantity,ln.ordered_quantity),
ln.order_quantity_uom,
get_primary_uom(to_number(ln.inventory_item_id)),
NULL,
NULL), --AVAILABLE_PRIMARY_QUANTITY ,
inv_convert.inv_um_convert(ln.inventory_item_id,
NULL,
NVL(ln.shipped_quantity,ln.ordered_quantity),
ln.order_quantity_uom,
l_common_uom,
NULL,
NULL), --COMMON_QUANTITY ,
l_common_uom, --COMMON_UOM ,
l_common_currency_code, --COMMON_CURRENCY_CODE ,
gl_currency_api.convert_amount_sql(hdr.transactional_curr_code,
l_common_currency_code,
NVL(ln.actual_shipment_date,ln.request_date),
l_curr_conv_type,
ln.unit_selling_price*( NVL(ln.shipped_quantity,ln.ordered_quantity))
) , --COMMON_AMOUNT ,
DECODE(sign(inv_convert.inv_um_convert(ln.inventory_item_id,
NULL,
NVL(ln.shipped_quantity,ln.ordered_quantity),
ln.order_quantity_uom,
l_common_uom,
NULL,
NULL)
), -1, 'Y',
DECODE(
sign(gl_currency_api.convert_amount_sql(hdr.transactional_curr_code,
l_common_currency_code,
NVL(ln.actual_shipment_date,ln.request_date),
l_curr_conv_type,
(ln.unit_selling_price*NVL(ln.shipped_quantity,ln.ordered_quantity)))
),-1,'Y','N'
)
), -- ERROR_FLAG
ln.header_id, --HEADER_ID ,
ln.line_id, --LINE_ID ,
ln.org_id, --ORG_ID,
'OM', --SOURCE_CODE
DECODE(ln.line_category_code,
'ORDER', 'IN',
'RETURN', 'OUT')
, get_party_id(ln.sold_to_org_id) party_id
, get_party_site_id(ln.invoice_to_org_id) SOLD_TO_PARTY_SITE_ID
FROM oe_order_headers_all hdr,
oe_order_lines_all ln
WHERE ln.open_flag = 'N'
AND ln.cancelled_flag = 'N'
AND ln.header_id = hdr.header_id
AND NVL(ln.actual_shipment_date,ln.request_date) > l_global_start_date ;
AND NOT EXISTS ( SELECT 1
FROM ozf_sales_transactions_all trx
WHERE trx.line_id = ln.line_id
AND source_code = 'OM' );
DELETE FROM ozf_sales_transactions_all
WHERE source_code = 'OM';