[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT user_name
INTO l_adjusted_by
FROM fnd_user
WHERE user_id = fnd_profile.value('USER_ID');
x_api_rec.last_updated_by := NULL;
x_api_rec.last_update_date := NULL;
x_api_rec.last_update_login := NULL;
SELECT
DECODE(p_newtx_rec.direct_salesrep_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).direct_salesrep_id,p_newtx_rec.direct_salesrep_id),
DECODE(p_newtx_rec.inventory_item_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).inventory_item_id,p_newtx_rec.inventory_item_id),
-- Bug fix 5349170
DECODE(nvl(p_newtx_rec.source_trx_id,FND_API.G_MISS_num), FND_API.G_MISS_NUM,
p_old_adj_tbl(1).source_trx_id,p_newtx_rec.source_trx_id),
DECODE(nvl(p_newtx_rec.source_trx_line_id,FND_API.G_MISS_num), FND_API.G_MISS_NUM,
p_old_adj_tbl(1).source_trx_line_id,p_newtx_rec.source_trx_line_id),
DECODE(nvl(p_newtx_rec.source_trx_sales_line_id,FND_API.G_MISS_num), FND_API.G_MISS_NUM,
p_old_adj_tbl(1).source_trx_sales_line_id,p_newtx_rec.source_trx_sales_line_id),
-- Bug fix 5349170
DECODE(p_newtx_rec.processed_date,FND_API.G_MISS_DATE,
p_old_adj_tbl(1).processed_date,p_newtx_rec.processed_date),
DECODE(p_newtx_rec.transaction_amount,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).transaction_amount,p_newtx_rec.transaction_amount),
DECODE(p_newtx_rec.trx_type,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).trx_type,p_newtx_rec.trx_type),
DECODE(p_newtx_rec.revenue_class_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).revenue_class_id,p_newtx_rec.revenue_class_id),
'UNLOADED',
DECODE(p_newtx_rec.attribute_category,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute_category,p_newtx_rec.attribute_category),
DECODE(p_newtx_rec.attribute1,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute1,p_newtx_rec.attribute1),
DECODE(p_newtx_rec.attribute2,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute2,p_newtx_rec.attribute2),
DECODE(p_newtx_rec.attribute3,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute3,p_newtx_rec.attribute3),
DECODE(p_newtx_rec.attribute4,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute4,p_newtx_rec.attribute4),
DECODE(p_newtx_rec.attribute5,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute5,p_newtx_rec.attribute5),
DECODE(p_newtx_rec.attribute6,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute6,p_newtx_rec.attribute6),
DECODE(p_newtx_rec.attribute7,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute7,p_newtx_rec.attribute7),
DECODE(p_newtx_rec.attribute8,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute8,p_newtx_rec.attribute8),
DECODE(p_newtx_rec.attribute9,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute9,p_newtx_rec.attribute9),
DECODE(p_newtx_rec.attribute10,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute10,p_newtx_rec.attribute10),
DECODE(p_newtx_rec.attribute11,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute11,p_newtx_rec.attribute11),
DECODE(p_newtx_rec.attribute12,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute12,p_newtx_rec.attribute12),
DECODE(p_newtx_rec.attribute13,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute13,p_newtx_rec.attribute13),
DECODE(p_newtx_rec.attribute14,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute14,p_newtx_rec.attribute14),
DECODE(p_newtx_rec.attribute15,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute15,p_newtx_rec.attribute15),
DECODE(p_newtx_rec.attribute16,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute16,p_newtx_rec.attribute16),
DECODE(p_newtx_rec.attribute17,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute17,p_newtx_rec.attribute17),
DECODE(p_newtx_rec.attribute18,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute18,p_newtx_rec.attribute18),
DECODE(p_newtx_rec.attribute19,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute19,p_newtx_rec.attribute19),
DECODE(p_newtx_rec.attribute20,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute20,p_newtx_rec.attribute20),
DECODE(p_newtx_rec.attribute21,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute21,p_newtx_rec.attribute21),
DECODE(p_newtx_rec.attribute22,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute22,p_newtx_rec.attribute22),
DECODE(p_newtx_rec.attribute23,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute23,p_newtx_rec.attribute23),
DECODE(p_newtx_rec.attribute24,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute24,p_newtx_rec.attribute24),
DECODE(p_newtx_rec.attribute25,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute25,p_newtx_rec.attribute25),
DECODE(p_newtx_rec.attribute26,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute26,p_newtx_rec.attribute26),
DECODE(p_newtx_rec.attribute27,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute27,p_newtx_rec.attribute27),
DECODE(p_newtx_rec.attribute28,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute28,p_newtx_rec.attribute28),
DECODE(p_newtx_rec.attribute29,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute29,p_newtx_rec.attribute29),
DECODE(p_newtx_rec.attribute30,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute30,p_newtx_rec.attribute30),
DECODE(p_newtx_rec.attribute31,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute31,p_newtx_rec.attribute31),
DECODE(p_newtx_rec.attribute32,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute32,p_newtx_rec.attribute32),
DECODE(p_newtx_rec.attribute33,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute33,p_newtx_rec.attribute33),
DECODE(p_newtx_rec.attribute34,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute34,p_newtx_rec.attribute34),
DECODE(p_newtx_rec.attribute35,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute35,p_newtx_rec.attribute35),
DECODE(p_newtx_rec.attribute36,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute36,p_newtx_rec.attribute36),
DECODE(p_newtx_rec.attribute37,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute37,p_newtx_rec.attribute37),
DECODE(p_newtx_rec.attribute38,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute38,p_newtx_rec.attribute38),
DECODE(p_newtx_rec.attribute39,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute39,p_newtx_rec.attribute39),
DECODE(p_newtx_rec.attribute40,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute40,p_newtx_rec.attribute40),
DECODE(p_newtx_rec.attribute41,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute41,p_newtx_rec.attribute41),
DECODE(p_newtx_rec.attribute42,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute42,p_newtx_rec.attribute42),
DECODE(p_newtx_rec.attribute43,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute43,p_newtx_rec.attribute43),
DECODE(p_newtx_rec.attribute44,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute44,p_newtx_rec.attribute44),
DECODE(p_newtx_rec.attribute45,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute45,p_newtx_rec.attribute45),
DECODE(p_newtx_rec.attribute46,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute46,p_newtx_rec.attribute46),
DECODE(p_newtx_rec.attribute47,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute47,p_newtx_rec.attribute47),
DECODE(p_newtx_rec.attribute48,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute48,p_newtx_rec.attribute48),
DECODE(p_newtx_rec.attribute49,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute49,p_newtx_rec.attribute49),
DECODE(p_newtx_rec.attribute50,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute50,p_newtx_rec.attribute50),
DECODE(p_newtx_rec.attribute51,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute51,p_newtx_rec.attribute51),
DECODE(p_newtx_rec.attribute52,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute52,p_newtx_rec.attribute52),
DECODE(p_newtx_rec.attribute53,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute53,p_newtx_rec.attribute53),
DECODE(p_newtx_rec.attribute54,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute54,p_newtx_rec.attribute54),
DECODE(p_newtx_rec.attribute55,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute55,p_newtx_rec.attribute55),
DECODE(p_newtx_rec.attribute56,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute56,p_newtx_rec.attribute56),
DECODE(p_newtx_rec.attribute57,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute57,p_newtx_rec.attribute57),
DECODE(p_newtx_rec.attribute58,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute58,p_newtx_rec.attribute58),
DECODE(p_newtx_rec.attribute59,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute59,p_newtx_rec.attribute59),
DECODE(p_newtx_rec.attribute60,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute60,p_newtx_rec.attribute60),
DECODE(p_newtx_rec.attribute61,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute61,p_newtx_rec.attribute61),
DECODE(p_newtx_rec.attribute62,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute62,p_newtx_rec.attribute62),
DECODE(p_newtx_rec.attribute63,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute63,p_newtx_rec.attribute63),
DECODE(p_newtx_rec.attribute64,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute64,p_newtx_rec.attribute64),
DECODE(p_newtx_rec.attribute65,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute65,p_newtx_rec.attribute65),
DECODE(p_newtx_rec.attribute66,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute66,p_newtx_rec.attribute66),
DECODE(p_newtx_rec.attribute67,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute67,p_newtx_rec.attribute67),
DECODE(p_newtx_rec.attribute68,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute68,p_newtx_rec.attribute68),
DECODE(p_newtx_rec.attribute69,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute69,p_newtx_rec.attribute69),
DECODE(p_newtx_rec.attribute70,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute70,p_newtx_rec.attribute70),
DECODE(p_newtx_rec.attribute71,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute71,p_newtx_rec.attribute71),
DECODE(p_newtx_rec.attribute72,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute72,p_newtx_rec.attribute72),
DECODE(p_newtx_rec.attribute73,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute73,p_newtx_rec.attribute73),
DECODE(p_newtx_rec.attribute74,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute74,p_newtx_rec.attribute74),
DECODE(p_newtx_rec.attribute75,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute75,p_newtx_rec.attribute75),
DECODE(p_newtx_rec.attribute76,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute76,p_newtx_rec.attribute76),
DECODE(p_newtx_rec.attribute77,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute77,p_newtx_rec.attribute77),
DECODE(p_newtx_rec.attribute78,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute78,p_newtx_rec.attribute78),
DECODE(p_newtx_rec.attribute79,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute79,p_newtx_rec.attribute79),
DECODE(p_newtx_rec.attribute80,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute80,p_newtx_rec.attribute80),
DECODE(p_newtx_rec.attribute81,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute81,p_newtx_rec.attribute81),
DECODE(p_newtx_rec.attribute82,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute82,p_newtx_rec.attribute82),
DECODE(p_newtx_rec.attribute83,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute83,p_newtx_rec.attribute83),
DECODE(p_newtx_rec.attribute84,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute84,p_newtx_rec.attribute84),
DECODE(p_newtx_rec.attribute85,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute85,p_newtx_rec.attribute85),
DECODE(p_newtx_rec.attribute86,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute86,p_newtx_rec.attribute86),
DECODE(p_newtx_rec.attribute87,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute87,p_newtx_rec.attribute87),
DECODE(p_newtx_rec.attribute88,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute88,p_newtx_rec.attribute88),
DECODE(p_newtx_rec.attribute89,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute89,p_newtx_rec.attribute89),
DECODE(p_newtx_rec.attribute90,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute90,p_newtx_rec.attribute90),
DECODE(p_newtx_rec.attribute91,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute91,p_newtx_rec.attribute91),
DECODE(p_newtx_rec.attribute92,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute92,p_newtx_rec.attribute92),
DECODE(p_newtx_rec.attribute93,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute93,p_newtx_rec.attribute93),
DECODE(p_newtx_rec.attribute94,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute94,p_newtx_rec.attribute94),
DECODE(p_newtx_rec.attribute95,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute95,p_newtx_rec.attribute95),
DECODE(p_newtx_rec.attribute96,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute96,p_newtx_rec.attribute96),
DECODE(p_newtx_rec.attribute97,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute97,p_newtx_rec.attribute97),
DECODE(p_newtx_rec.attribute98,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute98,p_newtx_rec.attribute98),
DECODE(p_newtx_rec.attribute99,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute99,p_newtx_rec.attribute99),
DECODE(p_newtx_rec.attribute100,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).attribute100,p_newtx_rec.attribute100),
DECODE(p_newtx_rec.rollup_date,FND_API.G_MISS_DATE,
p_old_adj_tbl(1).rollup_date,p_newtx_rec.rollup_date),
DECODE(p_newtx_rec.source_doc_type,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).source_doc_type,p_newtx_rec.source_doc_type),
DECODE(p_newtx_rec.orig_currency_code,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).orig_currency_code,
p_newtx_rec.orig_currency_code),
l_exchange_rate,
DECODE(p_newtx_rec.transaction_amount_orig,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).transaction_amount_orig,
p_newtx_rec.transaction_amount_orig),
-- Bug fix 5220393. Changed decode statement to just null value check.
decode(nvl(p_newtx_rec.trx_id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,
p_old_adj_tbl(1).trx_id, p_newtx_rec.trx_id),
decode(nvl(p_newtx_rec.trx_line_id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,
p_old_adj_tbl(1).trx_line_id, p_newtx_rec.trx_line_id),
decode(nvl(p_newtx_rec.trx_sales_line_id, FND_API.G_MISS_NUM),FND_API.G_MISS_NUM,
p_old_adj_tbl(1).trx_sales_line_id, p_newtx_rec.trx_sales_line_id),
-- Bug fix 5220393. Changed decode statement to just null value check.
DECODE(p_newtx_rec.quantity,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).quantity,p_newtx_rec.quantity),
DECODE(p_newtx_rec.source_trx_number,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).source_trx_number,p_newtx_rec.source_trx_number),
DECODE(p_newtx_rec.discount_percentage,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).discount_percentage,p_newtx_rec.discount_percentage),
DECODE(p_newtx_rec.margin_percentage,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).margin_percentage,p_newtx_rec.margin_percentage),
DECODE(p_newtx_rec.customer_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).customer_id,p_newtx_rec.customer_id),
DECODE(p_newtx_rec.order_number,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).order_number,p_newtx_rec.order_number),
p_newtx_rec.order_date,
DECODE(p_newtx_rec.invoice_number,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).invoice_number,p_newtx_rec.invoice_number),
p_newtx_rec.invoice_date,
SYSDATE,
DECODE(p_newtx_rec.revenue_type,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).revenue_type,p_newtx_rec.revenue_type),
DECODE(p_newtx_rec.adjust_comments,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).adjust_comments,p_newtx_rec.adjust_comments),
DECODE(p_newtx_rec.adjust_status,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).adjust_status,p_newtx_rec.adjust_status),
DECODE(p_newtx_rec.line_number,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).line_number,p_newtx_rec.line_number),
DECODE(p_newtx_rec.bill_to_address_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).bill_to_address_id,p_newtx_rec.bill_to_address_id),
DECODE(p_newtx_rec.ship_to_address_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).ship_to_address_id,p_newtx_rec.ship_to_address_id),
DECODE(p_newtx_rec.bill_to_contact_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).bill_to_contact_id,p_newtx_rec.bill_to_contact_id),
DECODE(p_newtx_rec.ship_to_contact_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).ship_to_contact_id,p_newtx_rec.ship_to_contact_id),
DECODE(p_newtx_rec.reason_code,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).reason_code,p_newtx_rec.reason_code),
DECODE(p_newtx_rec.quota_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).quota_id,p_newtx_rec.quota_id),
p_newtx_rec.comp_group_id,
DECODE(p_newtx_rec.direct_salesrep_number,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).direct_salesrep_number,
p_newtx_rec.direct_salesrep_number),
DECODE(p_newtx_rec.sales_channel,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).sales_channel,p_newtx_rec.sales_channel),
DECODE(p_newtx_rec.split_pct,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).split_pct,p_newtx_rec.split_pct),
DECODE(p_newtx_rec.split_status,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).split_status,p_newtx_rec.split_status),
DECODE(p_newtx_rec.commission_amount,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).commission_amount,p_newtx_rec.commission_amount),
DECODE(p_newtx_rec.role_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).role_id,p_newtx_rec.role_id),
DECODE(p_newtx_rec.pre_processed_code,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).pre_processed_code,p_newtx_rec.pre_processed_code),
DECODE(p_newtx_rec.org_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).org_id,p_newtx_rec.org_id),
/* Added for Crediting Bug */
DECODE(p_newtx_rec.terr_id,FND_API.G_MISS_NUM,
p_old_adj_tbl(1).terr_id,p_newtx_rec.terr_id),
DECODE(p_newtx_rec.terr_name,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).terr_name,p_newtx_rec.terr_name) ,
DECODE(p_newtx_rec.preserve_credit_override_flag,FND_API.G_MISS_CHAR,
p_old_adj_tbl(1).preserve_credit_override_flag,p_newtx_rec.preserve_credit_override_flag)
INTO x_final_trx_rec.direct_salesrep_id, x_final_trx_rec.inventory_item_id,
x_final_trx_rec.source_trx_id, x_final_trx_rec.source_trx_line_id,
x_final_trx_rec.source_trx_sales_line_id ,
x_final_trx_rec.processed_date,
x_final_trx_rec.transaction_amount, x_final_trx_rec.trx_type,
x_final_trx_rec.revenue_class_id, x_final_trx_rec.load_status,
x_final_trx_rec.attribute_category,
x_final_trx_rec.attribute1, x_final_trx_rec.attribute2,
x_final_trx_rec.attribute3, x_final_trx_rec.attribute4,
x_final_trx_rec.attribute5, x_final_trx_rec.attribute6,
x_final_trx_rec.attribute7, x_final_trx_rec.attribute8,
x_final_trx_rec.attribute9, x_final_trx_rec.attribute10,
x_final_trx_rec.attribute11, x_final_trx_rec.attribute12,
x_final_trx_rec.attribute13, x_final_trx_rec.attribute14,
x_final_trx_rec.attribute15, x_final_trx_rec.attribute16,
x_final_trx_rec.attribute17, x_final_trx_rec.attribute18,
x_final_trx_rec.attribute19, x_final_trx_rec.attribute20,
x_final_trx_rec.attribute21, x_final_trx_rec.attribute22,
x_final_trx_rec.attribute23, x_final_trx_rec.attribute24,
x_final_trx_rec.attribute25, x_final_trx_rec.attribute26,
x_final_trx_rec.attribute27, x_final_trx_rec.attribute28,
x_final_trx_rec.attribute29, x_final_trx_rec.attribute30,
x_final_trx_rec.attribute31, x_final_trx_rec.attribute32,
x_final_trx_rec.attribute33, x_final_trx_rec.attribute34,
x_final_trx_rec.attribute35, x_final_trx_rec.attribute36,
x_final_trx_rec.attribute37, x_final_trx_rec.attribute38,
x_final_trx_rec.attribute39, x_final_trx_rec.attribute40,
x_final_trx_rec.attribute41, x_final_trx_rec.attribute42,
x_final_trx_rec.attribute43, x_final_trx_rec.attribute44,
x_final_trx_rec.attribute45, x_final_trx_rec.attribute46,
x_final_trx_rec.attribute47, x_final_trx_rec.attribute48,
x_final_trx_rec.attribute49, x_final_trx_rec.attribute50,
x_final_trx_rec.attribute51, x_final_trx_rec.attribute52,
x_final_trx_rec.attribute53, x_final_trx_rec.attribute54,
x_final_trx_rec.attribute55, x_final_trx_rec.attribute56,
x_final_trx_rec.attribute57, x_final_trx_rec.attribute58,
x_final_trx_rec.attribute59, x_final_trx_rec.attribute60,
x_final_trx_rec.attribute61, x_final_trx_rec.attribute62,
x_final_trx_rec.attribute63, x_final_trx_rec.attribute64,
x_final_trx_rec.attribute65, x_final_trx_rec.attribute66,
x_final_trx_rec.attribute67, x_final_trx_rec.attribute68,
x_final_trx_rec.attribute69, x_final_trx_rec.attribute70,
x_final_trx_rec.attribute71, x_final_trx_rec.attribute72,
x_final_trx_rec.attribute73, x_final_trx_rec.attribute74,
x_final_trx_rec.attribute75, x_final_trx_rec.attribute76,
x_final_trx_rec.attribute77, x_final_trx_rec.attribute78,
x_final_trx_rec.attribute79, x_final_trx_rec.attribute80,
x_final_trx_rec.attribute81, x_final_trx_rec.attribute82,
x_final_trx_rec.attribute83, x_final_trx_rec.attribute84,
x_final_trx_rec.attribute85, x_final_trx_rec.attribute86,
x_final_trx_rec.attribute87, x_final_trx_rec.attribute88,
x_final_trx_rec.attribute89, x_final_trx_rec.attribute90,
x_final_trx_rec.attribute91, x_final_trx_rec.attribute92,
x_final_trx_rec.attribute93, x_final_trx_rec.attribute94,
x_final_trx_rec.attribute95, x_final_trx_rec.attribute96,
x_final_trx_rec.attribute97, x_final_trx_rec.attribute98,
x_final_trx_rec.attribute99, x_final_trx_rec.attribute100,
x_final_trx_rec.rollup_date, x_final_trx_rec.source_doc_type,
x_final_trx_rec.orig_currency_code, x_final_trx_rec.exchange_rate,
x_final_trx_rec.transaction_amount_orig, x_final_trx_rec.trx_id,
x_final_trx_rec.trx_line_id, x_final_trx_rec.trx_sales_line_id,
x_final_trx_rec.quantity, x_final_trx_rec.source_trx_number,
x_final_trx_rec.discount_percentage, x_final_trx_rec.margin_percentage,
x_final_trx_rec.customer_id, x_final_trx_rec.order_number,
x_final_trx_rec.order_date, x_final_trx_rec.invoice_number,
x_final_trx_rec.invoice_date, x_final_trx_rec.adjust_date,
x_final_trx_rec.revenue_type, x_final_trx_rec.adjust_comments,
x_final_trx_rec.adjust_status,
x_final_trx_rec.line_number, x_final_trx_rec.bill_to_address_id,
x_final_trx_rec.ship_to_address_id, x_final_trx_rec.bill_to_contact_id,
x_final_trx_rec.ship_to_contact_id, x_final_trx_rec.reason_code ,
x_final_trx_rec.quota_id, x_final_trx_rec.comp_group_id,
x_final_trx_rec.direct_salesrep_number, x_final_trx_rec.sales_channel,
x_final_trx_rec.split_pct, x_final_trx_rec.split_status,
x_final_trx_rec.commission_amount,x_final_trx_rec.role_id,
x_final_trx_rec.pre_processed_code, x_final_trx_rec.org_id,
x_final_trx_rec.terr_id, x_final_trx_rec.terr_name,
x_final_trx_rec.preserve_credit_override_flag
FROM dual;
PROCEDURE update_invoice_changes(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
p_validation_level IN VARCHAR2 := FND_API.G_VALID_LEVEL_FULL,
p_existing_data IN invoice_tbl,
p_new_data IN invoice_tbl,
p_exist_data_check IN VARCHAR2 DEFAULT NULL,
p_new_data_check IN VARCHAR2 DEFAULT NULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2) IS
--
l_api_name CONSTANT VARCHAR2(30) := 'update_invoice_changes';
l_insert_rec cn_invoice_changes_pkg.invoice_changes_all_rec_type;
SELECT invoice_change_id
FROM cn_invoice_changes
WHERE comm_lines_api_id = l_comm_lines_api_id;
SAVEPOINT update_invoice_changes;
x_loading_status := 'CN_INSERTED';
cn_invoice_changes_pkg.delete_row(c1_rec.invoice_change_id);
SELECT cn_invoice_change_s.NEXTVAL
INTO l_invoice_change_id
FROM dual;
l_insert_rec.invoice_change_id := l_invoice_change_id;
l_insert_rec.salesrep_id := p_new_data(i).salesrep_id;
l_insert_rec.invoice_number := p_new_data(i).invoice_number;
l_insert_rec.line_number := p_new_data(i).line_number;
l_insert_rec.revenue_type := p_new_data(i).revenue_type;
l_insert_rec.split_pct := p_new_data(i).split_pct;
l_insert_rec.comm_lines_api_id := p_new_data(i).comm_lines_api_id;
cn_invoice_changes_pkg.insert_row(
p_invoice_changes_all_rec => l_insert_rec);
ROLLBACK TO update_invoice_changes;
ROLLBACK TO update_invoice_changes;
ROLLBACK TO update_invoice_changes;
update_credit_memo logic:
Step 1: Create a cursor api_cur based on old split % data.
Step 2: Create a cursor header_cur based on old split % data.
Step 3: Open existing old split % PL/SQL table.
Step 4: For each record in the Step 3, open api_cur.
Step 5: Get the comm_lines_api from Step 4 and negate the record.
Step 6: Take the record info from api_cur and construct a adj_rec record type
Step 7: Open new split % PL/SQL.
Step 8: Complete constructing adj_rec type based on Step 6 and 7.
Step 9: Call cn_get_tx_data_pub.insert_api_record to create a new record.
-----------------------------------------------------------------------------*/
PROCEDURE update_credit_memo(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
p_validation_level IN VARCHAR2:= FND_API.G_VALID_LEVEL_FULL,
p_existing_data IN invoice_tbl,
p_new_data IN invoice_tbl,
p_to_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
p_to_salesrep_number IN VARCHAR2:= FND_API.G_MISS_CHAR,
p_called_from IN VARCHAR2,
p_adjust_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2) IS
--
l_api_name CONSTANT VARCHAR2(30) := 'update_credit_memo';
l_last_update_date DATE := sysdate;
l_last_updated_by NUMBER := fnd_global.user_id;
l_last_update_login NUMBER := fnd_global.login_id;
SELECT l.*
FROM cn_comm_lines_api l
WHERE l.salesrep_id = l_salesrep_id
AND l.invoice_number = l_invoice_number
AND l.line_number = l_line_number
AND l.revenue_type = l_revenue_type
AND l.split_pct = l_split_pct
AND l.trx_type IN ('CM','PMT')
AND l.load_status <> 'LOADED'
AND ((l.adjust_status NOT IN ('FROZEN','REVERSAL','SCA_PENDING')) OR
(l.adjust_status IS NULL))
AND ((l.split_status <> 'DELINKED') OR
(l.split_status IS NULL));
SELECT h.*, api.terr_id, api.terr_name, api.preserve_credit_override_flag
FROM cn_commission_headers h,
cn_comm_lines_api api
WHERE h.direct_salesrep_id = l_salesrep_id
AND h.invoice_number = l_invoice_number
AND h.line_number = l_line_number
AND h.revenue_type = l_revenue_type
AND h.split_pct = l_split_pct
AND h.trx_type IN ('CM','PMT')
AND ((h.adjust_status NOT IN ('FROZEN','REVERSAL')) OR
(h.adjust_status IS NULL))
AND ((h.split_status <> 'DELINKED') OR
(h.split_status IS NULL))
AND api.comm_lines_api_id = h.comm_lines_api_id
AND api.org_id = h.org_id;
SAVEPOINT update_credit_memo;
x_loading_status := 'CN_INSERTED';
-- Update this record with new split information.
IF (p_called_from = 'MASS') THEN
l_newtx_rec.direct_salesrep_number := p_to_salesrep_number;
cn_get_tx_data_pub.insert_api_record(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_action => 'UPDATE',
p_newtx_rec => l_newtx_rec,
x_api_id => l_comm_lines_api_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
cn_get_tx_data_pub.insert_api_record(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_action => 'UPDATE',
p_newtx_rec => l_newtx_rec,
x_api_id => l_comm_lines_api_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
/*cn_get_tx_data_pub.update_credit_credentials(
rec.comm_lines_api_id,
rec.terr_id,
rec.org_id,
rec.adjusted_by
);*/
-- Update this record with new split information.
IF (p_called_from = 'MASS') THEN
l_newtx_rec.direct_salesrep_number := p_to_salesrep_number;
cn_get_tx_data_pub.insert_api_record(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_action => 'UPDATE',
p_newtx_rec => l_newtx_rec,
x_api_id => l_comm_lines_api_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
cn_get_tx_data_pub.insert_api_record(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_validation_level => p_validation_level,
p_action => 'UPDATE',
p_newtx_rec => l_newtx_rec,
x_api_id => l_comm_lines_api_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
/*cn_get_tx_data_pub.update_credit_credentials(
rec.comm_lines_api_id,
rec.terr_id,
rec.org_id,
rec.adjusted_by
);*/
ROLLBACK TO update_credit_memo;
ROLLBACK TO update_credit_memo;
ROLLBACK TO update_credit_memo;
PROCEDURE update_mass_invoices (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE,
p_validation_level IN VARCHAR2:= FND_API.G_VALID_LEVEL_FULL,
p_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
p_pr_date_to IN DATE := FND_API.G_MISS_DATE,
p_pr_date_from IN DATE := FND_API.G_MISS_DATE,
p_calc_status IN VARCHAR2:= FND_API.G_MISS_CHAR,
p_invoice_num IN VARCHAR2:= FND_API.G_MISS_CHAR,
p_order_num IN NUMBER := FND_API.G_MISS_NUM,
p_srch_attr_rec IN cn_get_tx_data_pub.adj_rec_type,
p_to_salesrep_id IN NUMBER := FND_API.G_MISS_NUM,
p_to_salesrep_number IN VARCHAR2:= FND_API.G_MISS_CHAR,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_loading_status OUT NOCOPY VARCHAR2,
x_existing_data OUT NOCOPY invoice_tbl) IS
--
-- Local variables
--
l_api_name CONSTANT VARCHAR2(30) := 'update_mass_invoices';
SAVEPOINT update_mass_invoices;
x_loading_status := 'CN_INSERTED';
'SELECT '||
'CCH.direct_salesrep_id,CCH.invoice_number,CCH.line_number, '||
'CCH.revenue_type,CCH.split_pct,RSD.employee_number, CCH.comm_lines_api_id, '||
--Modified for Crediting Bug
'API.terr_id, API.preserve_credit_override_flag '||
'FROM cn_period_statuses CPSP, cn_salesreps RSD, cn_commission_headers CCH, '||
'cn_lookups CLT, cn_lookups CLR, cn_lookups CLS, cn_lookups CLRV, '||
'cn_lookups CLAD, cn_revenue_classes CNR, cn_quotas CQ, cn_trx_batches CTB, '||
-- Modified for Crediting Bug
'cn_comm_lines_api API '||
'WHERE CCH.direct_salesrep_id = RSD.salesrep_id '||
--Modified for Crediting Bug
'AND CCH.comm_lines_api_id = API.comm_lines_api_id(+)'||
'AND CCH.org_id = API.org_id(+)'||
'AND CCH.processed_period_id = CPSP.period_id '||
'AND CCH.status = CLS.lookup_code(+) '||
'AND CLS.lookup_type (+)= ''TRX_STATUS'' '||
'AND CCH.reason_code = CLR.lookup_code(+) '||
'AND CLR.lookup_type (+)= ''ADJUSTMENT_REASON'' '||
'AND CCH.trx_type = CLT.lookup_code(+) '||
'AND CLT.lookup_type (+)= ''TRX TYPES'' '||
'AND CCH.revenue_type = CLRV.lookup_code (+) '||
'AND CLRV.lookup_type (+) = ''REVENUE_TYPE'' '||
'AND CCH.adjust_status = CLAD.lookup_code (+) '||
'AND CLAD.lookup_type (+) = ''ADJUST_STATUS'' '||
'AND CCH.quota_id = CQ.quota_id(+) '||
'AND CCH.revenue_class_id = CNR.revenue_class_id(+) '||
'AND CCH.trx_batch_id = CTB.trx_batch_id(+) '||
'AND CCH.trx_type = ''INV'' '||
'AND ((CCH.adjust_status NOT IN (''REVERSAL'',''FROZEN'')) '||
'OR (CCH.adjust_status IS NULL)) ';
'SELECT CCLA.salesrep_id,CCLA.invoice_number,CCLA.line_number, '||
'CCLA.revenue_type,CCLA.split_pct,RSD.employee_number, CCLA.comm_lines_api_id, '||
--Modified for Crediting Bug
'CCLA.terr_id, CCLA.preserve_credit_override_flag '||
'FROM cn_comm_lines_api CCLA, '||
'cn_period_statuses CPSP, cn_salesreps RSD, '||
'cn_revenue_classes CNR, cn_lookups CLT, '||
'cn_lookups CLRV, cn_lookups CLAD,cn_lookups CLR '||
'WHERE RSD.salesrep_id = CCLA.salesrep_id '||
'AND CCLA.processed_period_id = CPSP.period_id '||
'AND CCLA.revenue_class_id = CNR.revenue_class_id(+) '||
'AND CCLA.reason_code = CLR.lookup_code(+) '||
'AND CLR.lookup_type (+)= ''ADJUSTMENT_REASON'' '||
'AND CCLA.trx_type = CLT.lookup_code '||
'AND CLT.lookup_type = ''TRX TYPES'' '||
'AND CCLA.revenue_type = CLRV.lookup_code(+) '||
'AND CLRV.lookup_type (+)= ''REVENUE_TYPE'' '||
'AND CCLA.adjust_status = CLAD.lookup_code(+) '||
'AND CLAD.lookup_type (+)= ''ADJUST_STATUS'' '||
'AND nvl(CCLA.load_status,''X'') <> ''LOADED'' '||
'AND CCLA.trx_type = ''INV'' '||
'AND ((CCLA.adjust_status NOT IN (''REVERSAL'',''FROZEN'',''SCA_PENDING'')) '||
'OR (CCLA.adjust_status IS NULL)) ';
-- Creating a table of to-be-deleted records.
l_existing_data(l_counter).salesrep_id := l_direct_salesrep_id;
cn_invoice_changes_pvt.update_invoice_changes(
p_api_version => l_api_version,
p_validation_level => p_validation_level,
p_existing_data => l_existing_data,
p_new_data => l_new_data,
p_exist_data_check => 'Y',
p_new_data_check => 'N',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_loading_status => x_loading_status);
ROLLBACK TO update_mass_invoices;
ROLLBACK TO update_mass_invoices;
ROLLBACK TO update_mass_invoices;
SELECT comm_lines_api_id,
invoice_number,
line_number,
revenue_type
FROM cn_comm_lines_api_all api
WHERE api.invoice_number = p_invoice_number
AND api.trx_type = p_trx_type
AND api.org_id = p_org_id
AND api.load_status NOT IN ( 'LOADED', 'FILTERED') -- vensrini Buf fix 4202682
AND (api.adjust_status NOT IN ('FROZEN','REVERSAL','SCA_PENDING') OR
api.adjust_status IS NULL)
UNION ALL
SELECT comm_lines_api_id,
invoice_number,
line_number,
revenue_type
FROM cn_commission_headers_all ch
WHERE ch.invoice_number = p_invoice_number
AND ch.trx_type = p_trx_type
AND ch.org_id = p_org_id
AND (ch.adjust_status NOT IN ('FROZEN','REVERSAL') OR
ch.adjust_status IS NULL);
SELECT invoice_change_id, revenue_type
FROM cn_invoice_changes
WHERE invoice_number = p_invoice_number
AND org_id = p_org_id; -- vensrini
l_insert_rec cn_invoice_changes_pkg.invoice_changes_all_rec_type;
x_loading_status := 'CN_INSERTED';
cn_invoice_changes_pkg.delete_row(c2_rec.invoice_change_id);
SELECT api.comm_lines_api_id,
api.salesrep_id,
api.invoice_number,
api.line_number,
api.revenue_type,
api.split_pct
FROM cn_comm_lines_api api
WHERE trx_type = 'INV'
AND load_status = 'UNLOADED'
AND adjust_status IS NULL;
SELECT api.invoice_number,
api.line_number,
api.revenue_type,
sum(api.transaction_amount) transaction_amount
FROM cn_comm_lines_api api
WHERE EXISTS (
SELECT 1
FROM cn_invoice_changes inv
WHERE api.invoice_number = inv.invoice_number
AND api.line_number = inv.line_number
AND api.revenue_type = inv.revenue_type)
AND (api.split_status NOT IN ('LINKED','DELINKED') OR
api.split_status IS NULL)
AND trx_type = l_trx_type
AND adjust_status IS NULL
GROUP BY api.invoice_number,
api.line_number,
api.revenue_type;
SELECT api.*
FROM cn_comm_lines_api api
WHERE api.invoice_number = l_invoice_number
AND api.line_number = l_line_number
AND api.revenue_type = l_revenue_type
AND (api.split_status NOT IN ('LINKED','DELINKED') OR
api.split_status IS NULL)
AND trx_type = l_trx_type;
SELECT inv.*,rep.employee_number
FROM cn_invoice_changes inv,
cn_salesreps rep
WHERE inv.salesrep_id = rep.salesrep_id
AND inv.invoice_number = l_invoice_number
AND inv.line_number = l_line_number
AND inv.revenue_type = l_revenue_type;
l_insert_rec cn_invoice_changes_pkg.invoice_changes_all_rec_type;
SELECT cn_invoice_change_s.NEXTVAL
INTO l_invoice_change_id
FROM dual;
l_insert_rec.invoice_change_id := l_invoice_change_id;
l_insert_rec.salesrep_id := c1_rec.salesrep_id;
l_insert_rec.invoice_number := c1_rec.invoice_number;
l_insert_rec.line_number := c1_rec.line_number;
l_insert_rec.revenue_type := c1_rec.revenue_type;
l_insert_rec.split_pct := c1_rec.split_pct;
l_insert_rec.comm_lines_api_id := c1_rec.comm_lines_api_id;
cn_invoice_changes_pkg.insert_row(
p_invoice_changes_all_rec => l_insert_rec);
UPDATE cn_comm_lines_api
SET adjust_status = 'INVLOAD'
WHERE comm_lines_api_id = c1_rec.comm_lines_api_id;
SELECT cn_comm_lines_api_s.NEXTVAL
INTO l_comm_lines_api_id
FROM sys.dual;
cn_comm_lines_api_pkg.insert_row(l_api_rec);