[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_db_flag := 'N';
g_update_db_flag := 'Y';
SELECT l.customer_trx_line_id
,l.memo_line_id
,l.inventory_item_id
,l.accounting_rule_id
,l.accounting_rule_duration -- Bug 2168875
,NVL(l.override_auto_accounting_flag,'N')
override_auto_accounting_flag-- Bug 3879222
FROM mtl_item_categories mic
,ra_customer_trx_lines l
WHERE l.line_type = 'LINE'
AND l.autorule_complete_flag IS NULL
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id =
NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND ((AR_RAAPI_UTIL.g_from_salesrep_id IS NULL AND
AR_RAAPI_UTIL.g_from_salesgroup_id IS NULL)
OR EXISTS
(SELECT 'X'
FROM ra_cust_trx_line_salesreps ls
WHERE ls.customer_trx_line_id = l.customer_trx_line_id
AND ls.salesrep_id =
NVL(AR_RAAPI_UTIL.g_from_salesrep_id,ls.salesrep_id)
AND NVL(ls.revenue_salesgroup_id, -9999) =
NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,
NVL(ls.revenue_salesgroup_id, -9999))
GROUP BY ls.salesrep_id
HAVING SUM(NVL(ls.revenue_percent_split,0)) <> 0));
IF g_update_db_flag = 'Y'
THEN
create_adjustment
(p_rev_adj_rec => l_rev_adj_rec
,x_adjustment_id => x_adjustment_id
,x_adjustment_number => x_adjustment_number);
SELECT count(*)
INTO l_sr_count
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = l_line_id
AND NVL(revenue_percent_split,0) <> 0
AND customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id;
INSERT INTO AR_LINE_REV_ADJ_GT(
CUSTOMER_TRX_ID,
CUSTOMER_TRX_LINE_ID,
REVENUE_ADJUSTMENT_ID,
AMOUNT,
PERCENT)
VALUES
(p_rev_adj_rec.customer_trx_id,
c1.customer_trx_line_id,
x_adjustment_id,
l_revenue_amount_prorata,
p_rev_adj_rec.percent);
INSERT INTO AR_LINE_REV_ADJ_GT(
CUSTOMER_TRX_ID,
CUSTOMER_TRX_LINE_ID,
REVENUE_ADJUSTMENT_ID,
AMOUNT,
PERCENT)
VALUES
(p_rev_adj_rec.customer_trx_id,
c1.customer_trx_line_id,
x_adjustment_id,
l_revenue_amount_prorata,
p_rev_adj_rec.percent);
SELECT SUM(NVL(s.revenue_amount_split,0))
FROM ra_cust_trx_line_salesreps s
,mtl_item_categories mic
,ra_customer_trx_lines l
WHERE s.customer_trx_line_id = l.customer_trx_line_id
AND l.line_type = 'LINE'
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND EXISTS
(SELECT s1.salesrep_id
FROM ra_cust_trx_line_salesreps s1
WHERE s1.customer_trx_line_id = l.customer_trx_line_id
AND s1.salesrep_id =
NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s1.salesrep_id)
AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s1.non_revenue_salesgroup_id, -9999), NVL(s1.revenue_salesgroup_id, -9999)) =
NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s1.non_revenue_salesgroup_id, -9999), NVL(s1.revenue_salesgroup_id, -9999)))
GROUP BY s1.salesrep_id
HAVING DECODE(p_rev_adj_rec.sales_credit_type,'N',
SUM(NVL(s1.non_revenue_percent_split,0)),
SUM(NVL(s1.revenue_percent_split,0))) <> 0);
SELECT NVL(SUM(d.amount),0)
FROM ra_cust_trx_line_gl_dist d
,mtl_item_categories mic
,ra_customer_trx_lines l
WHERE d.customer_trx_line_id = l.customer_trx_line_id
AND d.account_class = 'REV'
AND l.line_type = 'LINE'
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND EXISTS
(SELECT s.salesrep_id
FROM ra_cust_trx_line_salesreps s
WHERE s.customer_trx_line_id = l.customer_trx_line_id
AND s.salesrep_id =
NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
GROUP BY s.salesrep_id
HAVING DECODE(p_rev_adj_rec.sales_credit_type,'N',
SUM(NVL(s.non_revenue_percent_split,0)),
SUM(NVL(s.revenue_percent_split,0))) <> 0);
SELECT SUM(NVL(s.non_revenue_amount_split,0))
FROM ra_customer_trx_lines l
,mtl_item_categories mic
,ra_cust_trx_line_salesreps s
WHERE l.customer_trx_line_id = s.customer_trx_line_id
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND NVL(s.non_revenue_percent_split,0) <> 0
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND s.salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
AND NVL(s.non_revenue_salesgroup_id,-9999) = NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(s.non_revenue_salesgroup_id,-9999));
SELECT COUNT(*)
FROM mtl_item_categories mic
,ra_customer_trx_lines l
WHERE l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND EXISTS
(SELECT s.salesrep_id
FROM ra_cust_trx_line_salesreps s
WHERE s.customer_trx_line_id = l.customer_trx_line_id
AND NVL(s.non_revenue_percent_split,0) <> 0
AND s.salesrep_id =
NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
AND NVL(s.non_revenue_salesgroup_id,-9999) =
NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(s.non_revenue_salesgroup_id,-9999))
GROUP BY s.customer_trx_line_id
HAVING SUM(NVL(s.non_revenue_percent_split,0)) <> 0);
SELECT SUM (s.revenue_amount_split) amount
,l.customer_trx_line_id
,l.accounting_rule_id
,l.accounting_rule_duration
,l.inventory_item_id
,l.memo_line_id
,l.warehouse_id
,l.line_number
FROM ra_cust_trx_line_salesreps s
,mtl_item_categories mic
,ra_customer_trx_lines l
WHERE l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id = s.customer_trx_line_id
AND l.customer_trx_line_id =
NVL (AR_RAAPI_UTIL.g_from_cust_trx_line_id, l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND l.line_type = 'LINE'
AND EXISTS
(SELECT s1.salesrep_id
FROM ra_cust_trx_line_salesreps s1
WHERE s1.customer_trx_line_id = l.customer_trx_line_id
AND s1.salesrep_id =
NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s1.salesrep_id)
AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s1.non_revenue_salesgroup_id, -9999), NVL(s1.revenue_salesgroup_id, -9999)) =
NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s1.non_revenue_salesgroup_id, -9999), NVL(s1.revenue_salesgroup_id, -9999)))
GROUP BY s1.salesrep_id
HAVING DECODE(p_rev_adj_rec.sales_credit_type,'N',
SUM(NVL(s1.non_revenue_percent_split,0)),
SUM(NVL(s1.revenue_percent_split,0))) <> 0)
GROUP BY l.customer_trx_line_id
,l.accounting_rule_id
,l.accounting_rule_duration
,l.inventory_item_id
,l.memo_line_id
,l.warehouse_id
/* Bug 2130207 - changed from l_warehouse_id */
,l.line_number
HAVING SUM(s.revenue_amount_split) <> 0;
SELECT NVL(SUM(revenue_amount_split),0)
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = l_cust_trx_line_id
and NVL(revenue_percent_split,0) <> 0
AND customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
and salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,salesrep_id)
and NVL(revenue_salesgroup_id, -9999) = NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(revenue_salesgroup_id, -9999));
SELECT NVL(SUM(d.amount),0)
FROM ra_cust_trx_line_gl_dist d
WHERE d.customer_trx_line_id = l_cust_trx_line_id
AND d.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND d.account_set_flag = 'N'
AND d.account_class = 'REV';
SELECT NVL(SUM(d.amount),0)
FROM ra_cust_trx_line_gl_dist d,
ra_customer_trx_lines l
WHERE d.customer_trx_line_id = l.customer_trx_line_id
AND d.customer_trx_id = l.customer_trx_id
AND l.previous_customer_trx_line_id = l_cust_trx_line_id
AND l.previous_customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND d.account_set_flag = 'N'
AND d.account_class = 'REV';
SELECT SUM(NVL(s.non_revenue_amount_split,0))
FROM ra_cust_trx_line_salesreps s
WHERE s.customer_trx_line_id = l_cust_trx_line_id
AND s.salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
and NVL(s.non_revenue_salesgroup_id, -9999) = NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(s.non_revenue_salesgroup_id, -9999));
SELECT SUM(NVL(s.revenue_amount_split,0)) revenue_amount_split,
SUM(NVL(s.revenue_percent_split,0)) revenue_percent_split,
SUM(NVL(s.non_revenue_amount_split,0)) nonrev_amount_split,
SUM(NVL(s.non_revenue_percent_split,0)) nonrev_percent_split
FROM ra_cust_trx_line_salesreps s,
ra_customer_trx_lines l
WHERE s.customer_trx_line_id = l_cust_trx_line_id
AND s.salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
AND l.line_type = 'LINE'
AND l.customer_trx_line_id = s.customer_trx_line_id
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id;
SELECT NVL(SUM(s.revenue_amount_split),0),
NVL(SUM(s.revenue_percent_split),0),
NVL(SUM(s.non_revenue_amount_split),0),
NVL(SUM(s.non_revenue_percent_split),0)
FROM ra_cust_trx_line_salesreps s,
ra_customer_trx_lines l
WHERE s.customer_trx_line_id = l_cust_trx_line_id
AND s.salesrep_id = AR_RAAPI_UTIL.g_to_salesrep_id
AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
NVL(AR_RAAPI_UTIL.g_to_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
AND l.line_type = 'LINE'
AND l.customer_trx_line_id = s.customer_trx_line_id
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id;
SELECT s.salesrep_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', s.non_revenue_salesgroup_id, s.revenue_salesgroup_id) salesgroup_id,
SUM(s.revenue_amount_split) revenue_amount_split,
SUM(s.revenue_percent_split) revenue_percent_split,
SUM(s.non_revenue_amount_split) nonrev_amount_split,
SUM(s.non_revenue_percent_split) nonrev_percent_split
FROM ra_cust_trx_line_salesreps s
WHERE s.customer_trx_line_id = l_cust_trx_line_id
AND s.salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,s.salesrep_id)
AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
NVL(AR_RAAPI_UTIL.g_from_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
AND NVL(s.revenue_adjustment_id,-99) <> x_adjustment_id -- bug 2543675
GROUP BY s.salesrep_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', s.non_revenue_salesgroup_id,s.revenue_salesgroup_id);
SELECT name
FROM ra_salesreps
WHERE salesrep_id = AR_RAAPI_UTIL.g_to_salesrep_id;
/* Bug 2543675 - insert target sales credit and get the credit ccid
before debiting the source sales reps */
IF l_line_amount <> 0 AND
p_rev_adj_rec.sales_credit_type IN ('R','B') AND
l_revenue_percent_split <> 0
THEN
insert_sales_credit( AR_RAAPI_UTIL.g_customer_trx_id,
AR_RAAPI_UTIL.g_to_salesrep_id,
AR_RAAPI_UTIL.g_to_salesgroup_id,
l_cust_trx_line_id,
l_line_amount,
l_line_percent,
'R',
l_credit_sales_credit_id,
x_adjustment_id,
l_rev_adj_rec.gl_date);
insert_sales_credit(AR_RAAPI_UTIL.g_customer_trx_id,
c2.salesrep_id,
c2.salesgroup_id,
l_cust_trx_line_id,
l_amount_prorata * -1,
l_percent_prorata * -1,
'R',
l_debit_sales_credit_id,
x_adjustment_id,
l_rev_adj_rec.gl_date);
/* Bug 2543675 - insert 1 credit for every debit */
transfer_salesrep_revenue
(c1.customer_trx_line_id
,AR_RAAPI_UTIL.g_customer_trx_id
,l_credit_sales_credit_id
,l_amount_prorata
,l_amount_prorata_acctd
,l_rev_adj_rec.gl_date
,l_credit_ccid
,l_last_salesrep_flag -- Bug 2477881
,l_line_amount -- Bug 2477881
,l_line_amount_acctd -- Bug 2477881
,x_adjustment_id);
insert_distribution(l_cust_trx_line_id,
l_debit_ccid,
l_percent_prorata * -1,
l_amount_prorata_acctd * -1, -- Bug 2143925
l_gl_date_valid, -- Bug 2146970
l_rev_adj_rec.gl_date,
'REV',
l_amount_prorata * -1,
l_debit_sales_credit_id,
AR_RAAPI_UTIL.g_customer_trx_id,
x_adjustment_id);
/* Bug 2543675 - insert 1 credit for every debit */
insert_distribution(l_cust_trx_line_id,
l_credit_ccid,
l_percent_prorata ,
l_amount_prorata_acctd,
l_gl_date_valid,
l_rev_adj_rec.gl_date,
'REV',
l_amount_prorata,
l_credit_sales_credit_id,
AR_RAAPI_UTIL.g_customer_trx_id,
x_adjustment_id);
insert_sales_credit(AR_RAAPI_UTIL.g_customer_trx_id,
c2.salesrep_id,
c2.salesgroup_id,
l_cust_trx_line_id,
l_nonrev_amount_prorata * -1,
l_nonrev_percent_prorata * -1,
'N',
l_debit_sales_credit_id,
x_adjustment_id,
NULL);
insert_sales_credit( AR_RAAPI_UTIL.g_customer_trx_id,
AR_RAAPI_UTIL.g_to_salesrep_id,
AR_RAAPI_UTIL.g_to_salesgroup_id,
l_cust_trx_line_id,
l_line_nonrev_amount,
l_line_nonrev_percent,
'N',
l_credit_sales_credit_id,
x_adjustment_id,
NULL);
SELECT SUM(NVL(s.revenue_amount_split,0))
FROM ra_cust_trx_line_salesreps s
,mtl_item_categories mic
,ra_customer_trx_lines l
WHERE s.customer_trx_line_id = l.customer_trx_line_id
AND l.line_type = 'LINE'
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id;
SELECT COUNT(*)
FROM mtl_item_categories mic
,ra_customer_trx_lines l
WHERE l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.line_type = 'LINE'
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id;
SELECT l.customer_trx_line_id
,l.line_number
,SUM(NVL(s.revenue_amount_split,0)) amount
FROM mtl_item_categories mic
,ra_cust_trx_line_salesreps s
,ra_customer_trx_lines l
WHERE l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id = s.customer_trx_line_id
AND l.customer_trx_line_id =
NVL (AR_RAAPI_UTIL.g_from_cust_trx_line_id, l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND l.line_type = 'LINE'
GROUP BY l.customer_trx_line_id
,l.line_number;
SELECT NVL(SUM(s.revenue_amount_split),0),
NVL(SUM(s.revenue_percent_split),0),
NVL(SUM(s.non_revenue_amount_split),0),
NVL(SUM(s.non_revenue_percent_split),0)
FROM ra_cust_trx_line_salesreps s,
ra_customer_trx_lines l
WHERE s.customer_trx_line_id = l_cust_trx_line_id
AND s.salesrep_id = AR_RAAPI_UTIL.g_to_salesrep_id
AND DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
NVL(AR_RAAPI_UTIL.g_to_salesgroup_id, DECODE(p_rev_adj_rec.sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
AND l.line_type = 'LINE'
AND l.customer_trx_line_id = s.customer_trx_line_id
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id;
SELECT name
FROM ra_salesreps
WHERE salesrep_id = AR_RAAPI_UTIL.g_to_salesrep_id;
insert_sales_credit( AR_RAAPI_UTIL.g_customer_trx_id,
AR_RAAPI_UTIL.g_to_salesrep_id,
AR_RAAPI_UTIL.g_to_salesgroup_id,
l_cust_trx_line_id,
l_line_amount,
l_line_percent,
'N',
l_sales_credit_id,
x_adjustment_id,
NULL);
SELECT SUM(ar.amount), count(*)
FROM ar_revenue_assignments ar,
gl_sets_of_books sob
WHERE customer_trx_line_id = p_customer_trx_line_id
AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
AND ar.period_set_name = sob.period_set_name
AND ar.account_class = 'REV';
SELECT (ar.gl_date + l_deferred_days) gl_date,
SUM(ar.amount) amount
FROM ar_revenue_assignments ar,
gl_sets_of_books sob
WHERE ar.customer_trx_line_id = p_customer_trx_line_id
AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
AND ar.period_set_name = sob.period_set_name
AND ar.account_class = 'REV'
GROUP BY ar.gl_date
ORDER BY (ar.gl_date + l_deferred_days);
SELECT l.customer_trx_line_id,
l.extended_amount,
l.accounting_rule_duration,
r.deferred_revenue_flag,
l.rule_start_date
FROM ra_customer_trx_lines l,
ra_rules r
WHERE l.accounting_rule_id = r.rule_id
AND l.customer_trx_line_id = p_customer_trx_line_id
AND l.customer_trx_id = p_customer_trx_id
AND l.line_type = 'LINE';
/* set local variable for second insert */
l_round_acctd_amount := p_revenue_acctd_amount - l_dist_acctd_tot;
insert_distribution ( p_customer_trx_line_id,
p_ccid,
l_dist_percent,
l_dist_acctd_amount,
l_gl_date,
c1.gl_date,
'REV',
l_dist_amount,
p_sales_credit_id,
p_customer_trx_id,
p_adjustment_id);
/* 6325023 - Need to insert a second dist for the acctd correction */
insert_distribution ( p_customer_trx_line_id,
p_ccid,
0,
l_round_acctd_amount,
l_gl_date,
c1.gl_date,
'REV',
0,
p_sales_credit_id,
p_customer_trx_id,
p_adjustment_id,
'Y');
SELECT
period_set_name,
precision,
minimum_accountable_unit,
asp.org_id,
asp.set_of_books_id
FROM
fnd_currencies fc,
gl_sets_of_books gsb,
ar_system_parameters asp
WHERE
gsb.set_of_books_id = asp.set_of_books_id
AND fc.currency_code = gsb.currency_code;
SELECT customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_customer_trx_line_id;
/* Insert using statement similar to one in
arp_auto_rule.create_assignments. Main diff is
that this one is by line rather than trx. */
INSERT INTO ra_cust_trx_line_gl_dist
(
customer_trx_line_id,
customer_trx_id,
code_combination_id,
set_of_books_id,
account_class,
account_set_flag,
percent,
amount,
acctd_amount,
gl_date,
cust_trx_line_salesrep_id,
request_id,
program_application_id,
program_id,
program_update_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
posting_control_id,
original_gl_date,
cust_trx_line_gl_dist_id,
revenue_adjustment_id,
user_generated_flag,
org_id
)
SELECT /*+ ORDERED push_pred(ra.lgd) index(dist ra_cust_trx_line_gl_dist_n1)*/
ra.customer_trx_line_id, /* customer_trx_line_id */
l.customer_trx_id, /* customer_trx_id */
dist.code_combination_id, /* code_combination_id */
arp_standard.sysparm.set_of_books_id, /* set_of_books_id */
ra.account_class, /* account_class */
'N', /* account_set_flag */
ROUND((((ra.amount * dist.percent
* p_revenue_amount)
/ l.extended_amount)
/ l.extended_amount),4), /* percent */
DECODE(fc.minimum_accountable_unit,
NULL, ROUND(((ra.amount * (dist.percent/100)
* p_revenue_amount)
/ l.extended_amount),
fc.precision),
ROUND((((ra.amount * (dist.percent/100)
* p_revenue_amount)
/ l.extended_amount)
/ fc.minimum_accountable_unit)
* fc.minimum_accountable_unit)), /* Amount */
DECODE(g_bmau,
NULL, ROUND((((ra.amount * (dist.percent/100)
* p_revenue_amount)
/ l.extended_amount)
* NVL(h.exchange_rate,1)),
g_base_precision),
ROUND(((((ra.amount * (dist.percent/100)
* p_revenue_amount)
/ l.extended_amount)
* NVL(h.exchange_rate,1))
/ g_bmau)
* g_bmau)), /* Acctd_amount */
DECODE(l.accounting_rule_duration, 1, p_gl_date,
arp_auto_rule.assign_gl_date(ra.gl_date)),/* Derived gl_date */
dist.cust_trx_line_salesrep_id, /* Srep ID */
arp_standard.profile.request_id,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
sysdate,
arp_standard.profile.user_id,
sysdate,
arp_standard.profile.user_id,
-3,
ra.gl_date, /* original_gl_date */
ra_cust_trx_line_gl_dist_s.NEXTVAL, /* cust_trx_line_gl_dist_id */
p_adjustment_id,
p_user_generated_flag,
arp_standard.sysparm.org_id
FROM
ra_customer_trx_lines l,
ra_customer_trx h,
fnd_currencies fc,
ra_cust_trx_line_gl_dist dist,
ar_revenue_assignments ra
WHERE
l.customer_trx_line_id = p_customer_trx_line_id
AND fc.currency_code = h.invoice_currency_code
AND l.customer_trx_id = h.customer_trx_id
AND ra.customer_trx_line_id = l.customer_trx_line_id
AND ra.period_set_name = g_period_set_name
AND dist.customer_trx_line_id= ra.customer_trx_line_id
AND dist.account_class = ra.account_class
AND dist.account_set_flag = 'Y'; /* model accounts */
/* Call MRC for inserts/updates if we have rows to
process and if MRC is enabled */
rows := sql%rowcount;
arp_standard.debug('Total lines inserted: ' || rows);
SELECT SUM(ar.amount), count(*)
FROM ar_revenue_assignments ar,
gl_sets_of_books sob
WHERE customer_trx_line_id = p_customer_trx_line_id
AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
AND ar.period_set_name = sob.period_set_name
AND ar.account_class = 'REV';
SELECT ar.gl_date
, SUM(ar.amount) amount
FROM ar_revenue_assignments ar,
gl_sets_of_books sob
WHERE ar.customer_trx_line_id = p_customer_trx_line_id
AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
AND ar.period_set_name = sob.period_set_name
AND ar.account_class = 'REV'
GROUP BY ar.gl_date
ORDER BY gl_date ASC;
SELECT COUNT(*)
FROM ra_salesreps
WHERE salesrep_id IN
(SELECT salesrep_id
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id
AND NVL(revenue_percent_split,0) <> 0
GROUP by salesrep_id
HAVING SUM(NVL(revenue_percent_split,0)) <> 0)
AND salesrep_id = NVL(p_salesrep_id,salesrep_id);
SELECT salesrep_id,
SUM(NVL(revenue_percent_split,0)) revenue_percent_split,
MAX(cust_trx_line_salesrep_id) max_id
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id
AND salesrep_id = NVL(p_salesrep_id,salesrep_id)
AND NVL(revenue_percent_split,0) <> 0
GROUP by salesrep_id
HAVING SUM(NVL(revenue_percent_split,0)) <> 0;
SELECT NVL(revenue_percent_split,0)
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id
AND cust_trx_line_salesrep_id = l_last_salescredit_id;
SELECT l.customer_trx_line_id,
l.line_number,
l.extended_amount,
l.accounting_rule_duration,
r.deferred_revenue_flag,
l.warehouse_id,
l.rule_start_date
FROM ra_customer_trx_lines l,
ra_rules r
WHERE l.accounting_rule_id = r.rule_id
AND l.customer_trx_line_id = p_customer_trx_line_id
AND l.customer_trx_id = p_customer_trx_id
AND l.line_type = 'LINE';
insert_distribution ( p_customer_trx_line_id,
l_debit_ccid,
l_dist_percent * -1,
l_dist_acctd_amount * -1,
l_gl_date_valid,
c1.gl_date,
'REV',
l_dist_amount * -1,
l_cust_trx_line_salesrep_id,
p_customer_trx_id,
p_adjustment_id,
p_user_generated_flag);
insert_distribution ( p_customer_trx_line_id,
l_credit_ccid,
l_dist_percent,
l_dist_acctd_amount,
l_gl_date_valid,
c1.gl_date,
l_revenue_type,
l_dist_amount,
l_cust_trx_line_salesrep_id,
p_customer_trx_id,
p_adjustment_id,
p_user_generated_flag);
SELECT COUNT(*)
FROM ra_salesreps
WHERE salesrep_id IN
(SELECT salesrep_id
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id
AND NVL(revenue_percent_split,0) <> 0
GROUP by salesrep_id
HAVING SUM(NVL(revenue_percent_split,0)) <> 0)
AND salesrep_id = NVL(p_salesrep_id,salesrep_id);
SELECT salesrep_id,
SUM(NVL(revenue_percent_split,0)) revenue_percent_split,
MAX(cust_trx_line_salesrep_id) max_id
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id
AND salesrep_id = NVL(p_salesrep_id,salesrep_id)
AND NVL(revenue_percent_split,0) <> 0
GROUP by salesrep_id
HAVING SUM(NVL(revenue_percent_split,0)) <> 0;
SELECT NVL(revenue_percent_split,0)
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id
AND cust_trx_line_salesrep_id = l_last_salescredit_id;
SELECT customer_trx_line_id,
line_number,
extended_amount,
warehouse_id,
accounting_rule_duration
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_customer_trx_line_id
AND customer_trx_id = p_customer_trx_id
AND line_type = 'LINE';
insert_distribution ( p_customer_trx_line_id,
l_debit_ccid,
l_dist_percent * -1,
l_dist_acctd_amount * -1,
p_gl_date,
p_gl_date,
'REV',
l_dist_amount * -1,
l_cust_trx_line_salesrep_id,
p_customer_trx_id,
p_adjustment_id,
p_user_generated_flag);
insert_distribution ( p_customer_trx_line_id,
l_credit_ccid,
l_dist_percent,
l_dist_acctd_amount,
p_gl_date,
p_gl_date,
l_revenue_type,
l_dist_amount,
l_cust_trx_line_salesrep_id,
p_customer_trx_id,
p_adjustment_id,
p_user_generated_flag);
/* 5644810 - Added NVL to sum(gl.amount), sum(gl.acctd_amount) and sum(gl.percent) in SELECT ststement */
CURSOR c_dist IS
SELECT l.customer_trx_line_id,
l.line_number,
l.extended_amount,
l.warehouse_id,
l.accounting_rule_duration,
gl.code_combination_id,
NVL(sum(gl.amount),0) amount,
NVL(sum(gl.acctd_amount),0) acctd_amount,
NVL(sum(gl.percent),0) percent
FROM ra_customer_trx_lines l,
ra_cust_trx_line_gl_dist gl
WHERE l.customer_trx_line_id = p_customer_trx_line_id
AND l.customer_trx_id = p_customer_trx_id
AND l.line_type = 'LINE'
AND l.customer_trx_line_id = gl.customer_trx_line_id (+)
AND gl.account_class (+) = 'REV'
AND gl.revenue_adjustment_id (+) IS NULL
GROUP BY l.customer_trx_line_id, l.line_number, l.extended_amount,
l.warehouse_id, l.accounting_rule_duration,
gl.code_combination_id;
insert_distribution ( p_customer_trx_line_id,
l_debit_ccid,
l_dist_percent,
l_dist_acctd_amount,
p_gl_date,
p_gl_date,
'REV',
l_dist_amount,
NULL, -- srep dist id
p_customer_trx_id,
p_adjustment_id,
p_user_generated_flag);
insert_distribution ( p_customer_trx_line_id,
l_credit_ccid,
l_dist_percent * -1,
l_dist_acctd_amount * -1,
p_gl_date,
p_gl_date,
l_revenue_type,
l_dist_amount * -1,
NULL, -- srep_dist_id
p_customer_trx_id,
p_adjustment_id,
p_user_generated_flag);
SELECT NVL(SUM(d.amount),0)
FROM ra_cust_trx_line_gl_dist d
,mtl_item_categories mic
,ra_customer_trx_lines l
WHERE d.customer_trx_line_id = l.customer_trx_line_id
AND d.account_class = 'REV'
AND l.line_type = 'LINE'
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND d.account_class IN ('REV','UNEARN','UNBILL')
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,
DECODE(AR_RAAPI_UTIL.g_from_inventory_item_id,NULL,
DECODE(AR_RAAPI_UTIL.g_from_cust_trx_line_id,NULL,
NVL(l.accounting_rule_duration,0),0),0),0) <= 1;
SELECT l.line_number
,l.customer_trx_line_id
,l.memo_line_id
,l.inventory_item_id
,l.accounting_rule_id
,NVL(SUM(d.amount),0) amount
FROM ra_cust_trx_line_gl_dist d
,mtl_item_categories mic
,ra_customer_trx_lines l
WHERE d.customer_trx_line_id = l.customer_trx_line_id
AND d.account_class = 'REV'
AND l.line_type = 'LINE'
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,
DECODE(AR_RAAPI_UTIL.g_from_inventory_item_id,NULL,
DECODE(AR_RAAPI_UTIL.g_from_cust_trx_line_id,NULL,
NVL(l.accounting_rule_duration,0),0),0),0) <= 1
GROUP BY l.line_number
,l.customer_trx_line_id
,l.memo_line_id
,l.inventory_item_id
,l.accounting_rule_id;
SELECT SUM(NVL(revenue_amount_split,0))
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = l_line_id
AND customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND salesrep_id = NVL(AR_RAAPI_UTIL.g_from_salesrep_id,salesrep_id)
AND NVL(revenue_salesgroup_id, -9999) = NVL(AR_RAAPI_UTIL.g_from_salesgroup_id,NVL(revenue_salesgroup_id, -9999));
SELECT NVL(COUNT(DISTINCT tax.item_exception_rate_id||
tax.tax_exemption_id|| tax.vat_tax_id||
tax.sales_tax_id|| tax.tax_rate|| tax.tax_precedence),0)
FROM ra_customer_trx_lines line
,mtl_item_categories mic
,ra_customer_trx_lines tax
WHERE line.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND tax.line_type = 'TAX'
AND line.customer_trx_line_id = tax.link_to_cust_trx_line_id
AND line.customer_trx_line_id IN
(NVL(AR_RAAPI_UTIL.g_from_cust_trx_line_id, line.customer_trx_line_id),
NVL(AR_RAAPI_UTIL.g_to_cust_trx_line_id, line.customer_trx_line_id))
AND NVL(line.inventory_item_id,0) IN
(NVL(AR_RAAPI_UTIL.g_from_inventory_item_id,NVL(line.inventory_item_id,0)),
NVL(AR_RAAPI_UTIL.g_to_inventory_item_id,NVL(line.inventory_item_id,0)))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND line.inventory_item_id = mic.inventory_item_id(+)
AND (NVL(AR_RAAPI_UTIL.g_from_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_from_category_id,NULL,0,mic.category_id) OR
NVL(AR_RAAPI_UTIL.g_to_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,0,mic.category_id))
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id;
SELECT NVL(SUM(d.amount),0) amount
FROM ra_cust_trx_line_gl_dist d
,mtl_item_categories mic
,ra_customer_trx_lines l
WHERE d.customer_trx_line_id = l.customer_trx_line_id
AND d.account_class = 'REV'
AND l.line_type = 'LINE'
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_to_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_to_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_to_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,
DECODE(AR_RAAPI_UTIL.g_to_inventory_item_id,NULL,
DECODE(AR_RAAPI_UTIL.g_to_cust_trx_line_id,NULL,
NVL(l.accounting_rule_duration,0),2),2),2) > 1;
SELECT COUNT(*)
FROM mtl_item_categories mic
,ra_customer_trx_lines l
WHERE l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_to_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_to_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_to_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND l.line_type = 'LINE'
AND DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,
DECODE(AR_RAAPI_UTIL.g_to_inventory_item_id,NULL,
DECODE(AR_RAAPI_UTIL.g_to_cust_trx_line_id,NULL,
NVL(l.accounting_rule_duration,0),2),2),2) > 1;
SELECT l.line_number
,l.customer_trx_line_id
,l.memo_line_id
,l.inventory_item_id
,l.accounting_rule_id
,l.accounting_rule_duration
,SUM(d.amount) amount
FROM ra_cust_trx_line_gl_dist d
,mtl_item_categories mic
,ra_customer_trx_lines l
WHERE d.customer_trx_line_id = l.customer_trx_line_id
AND d.account_class = 'REV'
AND l.line_type = 'LINE'
AND l.customer_trx_id = AR_RAAPI_UTIL.g_customer_trx_id
AND l.customer_trx_line_id = NVL(AR_RAAPI_UTIL.g_to_cust_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(AR_RAAPI_UTIL.g_to_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(AR_RAAPI_UTIL.g_to_category_id,0) =
DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND DECODE(AR_RAAPI_UTIL.g_to_category_id,NULL,
DECODE(AR_RAAPI_UTIL.g_to_inventory_item_id,NULL,
DECODE(AR_RAAPI_UTIL.g_to_cust_trx_line_id,NULL,
NVL(l.accounting_rule_duration,0),2),2),2) > 1
GROUP BY l.line_number
,l.customer_trx_line_id
,l.memo_line_id
,l.inventory_item_id
,l.accounting_rule_id
,l.accounting_rule_duration;
SELECT NVL(SUM(amount),0)
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_line_id = p_customer_trx_line_id
AND account_set_flag = 'N'
AND account_class IN ('REV','UNEARN')
AND NVL(amount,0) <> 0;
SELECT COUNT(*)
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_line_id = p_customer_trx_line_id
AND account_set_flag = 'N'
AND NVL(amount,0) <> 0;
SELECT rowid, amount
FROM ra_cust_trx_line_gl_dist
WHERE customer_trx_line_id = p_customer_trx_line_id
AND account_set_flag = 'N'
AND NVL(amount,0) <> 0;
UPDATE ra_cust_trx_line_gl_dist
SET percent = l_percent
WHERE rowid = c1.rowid;
SELECT ar_revenue_adjustments_s1.NEXTVAL
FROM dual;
SELECT ar_revenue_adjustments_s2.NEXTVAL
FROM dual;
INSERT INTO ar_revenue_adjustments
(revenue_adjustment_id
,revenue_adjustment_number
,customer_trx_id
,application_date
,from_salesrep_id
,to_salesrep_id
,from_salesgroup_id
,to_salesgroup_id
,type
,sales_credit_type
,amount_mode
,amount
,percent
,line_selection_mode
,from_category_id
,to_category_id
,from_inventory_item_id
,to_inventory_item_id
,from_cust_trx_line_id
,to_cust_trx_line_id
,gl_date
,reason_code
,comments
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,status
,creation_date
,created_by
,last_update_date
,last_updated_by
,org_id ) -- Bug 4607673
VALUES
(x_adjustment_id
,x_adjustment_number
,AR_RAAPI_UTIL.g_customer_trx_id
,SYSDATE
,AR_RAAPI_UTIL.g_from_salesrep_id
,AR_RAAPI_UTIL.g_to_salesrep_id
,AR_RAAPI_UTIL.g_from_salesgroup_id
,AR_RAAPI_UTIL.g_to_salesgroup_id
,p_rev_adj_rec.adjustment_type
,p_rev_adj_rec.sales_credit_type
,p_rev_adj_rec.amount_mode
,p_rev_adj_rec.amount
,p_rev_adj_rec.percent
,p_rev_adj_rec.line_selection_mode
,AR_RAAPI_UTIL.g_from_category_id
,AR_RAAPI_UTIL.g_to_category_id
,AR_RAAPI_UTIL.g_from_inventory_item_id
,AR_RAAPI_UTIL.g_to_inventory_item_id
,AR_RAAPI_UTIL.g_from_cust_trx_line_id
,AR_RAAPI_UTIL.g_to_cust_trx_line_id
/*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
,trunc(p_rev_adj_rec.gl_date)
/*,p_rev_adj_rec.gl_date*/
,p_rev_adj_rec.reason_code
,p_rev_adj_rec.comments
,p_rev_adj_rec.attribute_category
,p_rev_adj_rec.attribute1
,p_rev_adj_rec.attribute2
,p_rev_adj_rec.attribute3
,p_rev_adj_rec.attribute4
,p_rev_adj_rec.attribute5
,p_rev_adj_rec.attribute6
,p_rev_adj_rec.attribute7
,p_rev_adj_rec.attribute8
,p_rev_adj_rec.attribute9
,p_rev_adj_rec.attribute10
,p_rev_adj_rec.attribute11
,p_rev_adj_rec.attribute12
,p_rev_adj_rec.attribute13
,p_rev_adj_rec.attribute14
,p_rev_adj_rec.attribute15
,'A'
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,arp_standard.sysparm.org_id ); -- Bug 4607673
SELECT COUNT(*)
FROM ra_salesreps
WHERE salesrep_id IN
(SELECT salesrep_id
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id
AND NVL(revenue_percent_split,0) <> 0
GROUP by salesrep_id
HAVING SUM(NVL(revenue_percent_split,0)) <> 0);
SELECT extended_amount, warehouse_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_customer_trx_line_id;
SELECT salesrep_id,
SUM(NVL(revenue_percent_split,0)) revenue_percent_split,
MAX(cust_trx_line_salesrep_id) max_id
FROM ra_cust_trx_line_salesreps
WHERE customer_trx_line_id = p_customer_trx_line_id
AND NVL(revenue_percent_split,0) <> 0
GROUP by salesrep_id
HAVING SUM(NVL(revenue_percent_split,0)) <> 0;
insert_distribution ( p_customer_trx_line_id,
l_credit_ccid,
l_dist_percent,
l_dist_acctd_amount,
p_gl_date,
p_gl_date,
'UNEARN',
l_dist_amount,
NULL,
p_customer_trx_id,
p_adjustment_id);
insert_distribution ( p_customer_trx_line_id,
l_debit_ccid,
l_dist_percent * -1,
l_dist_acctd_amount * -1,
p_gl_date,
p_gl_date,
'SUSPENSE',
l_dist_amount * -1,
NULL,
p_customer_trx_id,
p_adjustment_id);
PROCEDURE insert_distribution(p_customer_trx_line_id IN NUMBER,
p_ccid IN NUMBER,
p_percent IN NUMBER,
p_acctd_amount IN NUMBER,
p_gl_date IN DATE,
p_orig_gl_date IN DATE,
p_account_class IN VARCHAR2,
p_amount IN NUMBER,
p_cust_trx_line_salesrep_id IN NUMBER,
p_customer_trx_id IN NUMBER,
p_adjustment_id IN NUMBER,
p_user_generated_flag IN VARCHAR2,
p_rounding_flag IN VARCHAR2
DEFAULT NULL)
IS
l_dist_id NUMBER;
SELECT customer_trx_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_customer_trx_line_id;
arp_util.debug('AR_Revenue_Adjustment_PVT.insert_distribution()+');
arp_util.debug(' g_update_db_flag = ' || g_update_db_flag);
IF g_update_db_flag = 'Y'
THEN
SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL
INTO l_dist_id
FROM dual;
INSERT INTO ra_cust_trx_line_gl_dist
(cust_trx_line_gl_dist_id
,customer_trx_line_id
,code_combination_id
,set_of_books_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,percent
,amount
,gl_date
,original_gl_date
,cust_trx_line_salesrep_id
,account_class
,customer_trx_id
,account_set_flag
,acctd_amount
,posting_control_id
,revenue_adjustment_id
,user_generated_flag
,org_id -- Bug 4607673
,rounding_correction_flag
) VALUES
(l_dist_id
,p_customer_trx_line_id
,p_ccid
,arp_global.sysparam.set_of_books_id
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,p_percent
,p_amount
/*Bug 6731185 JVARKEY Making sure GL date has no timestamp*/
,trunc(p_gl_date)
/*,p_gl_date*/
,p_orig_gl_date
,p_cust_trx_line_salesrep_id
,p_account_class
,p_customer_trx_id
,'N'
,p_acctd_amount
,-3
,p_adjustment_id
,p_user_generated_flag
,arp_standard.sysparm.org_id
,p_rounding_flag); -- Bug 4607673
g_ra_dist_tbl(g_dist_count).last_update_date := SYSDATE;
g_ra_dist_tbl(g_dist_count).last_updated_by := l_user_id;
arp_util.debug('AR_Revenue_Adjustment_PVT.insert_distribution()-');
('20001 error at AR_Revenue_Adjustment_PVT.insert_distribution()');
arp_util.debug('insert_distribution: ' || 'Unexpected error '||sqlerrm||
' at AR_Revenue_Adjustment_PVT.insert_distribution()+');
END insert_distribution;
/* 6615118 - insert model distributions if the srep is revenue
and the transaction has rules */
PROCEDURE insert_sales_credit (p_customer_trx_id IN NUMBER,
p_salesrep_id IN NUMBER,
p_salesgroup_id IN NUMBER,
p_cust_trx_line_id IN NUMBER,
p_amount IN NUMBER,
p_percent IN NUMBER,
p_type IN VARCHAR2,
p_sales_credit_id IN OUT NOCOPY NUMBER,
p_adjustment_id IN NUMBER,
p_gl_date IN DATE)
IS
CURSOR get_salesrep_line_id IS
SELECT ra_cust_trx_line_salesreps_s.NEXTVAL
FROM dual;
SELECT ra_cust_trx_line_gl_dist_s.NEXTVAL
FROM dual;
arp_util.debug('AR_Revenue_Adjustment_PVT.insert_sales_credit()+');
INSERT INTO ra_cust_trx_line_salesreps
(cust_trx_line_salesrep_id
,last_update_date
,last_updated_by
,creation_date
,created_by
,customer_trx_id
,salesrep_id
,revenue_salesgroup_id
,non_revenue_salesgroup_id
,customer_trx_line_id
,revenue_amount_split
,non_revenue_amount_split
,revenue_percent_split
,non_revenue_percent_split
,revenue_adjustment_id
,org_id)
VALUES (p_sales_credit_id
,SYSDATE
,l_user_id
,SYSDATE
,l_user_id
,p_customer_trx_id
,p_salesrep_id
,DECODE(p_type,'R',p_salesgroup_id,NULL)
,DECODE(p_type,'N',p_salesgroup_id,NULL)
,p_cust_trx_line_id
,DECODE(p_type,'R',p_amount,NULL)
,DECODE(p_type,'N',p_amount,NULL)
,DECODE(p_type,'R',p_percent,NULL)
,DECODE(p_type,'N',p_percent,NULL)
,p_adjustment_id
,arp_standard.sysparm.org_id);
arp_util.debug(' Inserted salescredit row w/ ID = ' ||
p_sales_credit_id);
SELECT inventory_item_id, memo_line_id, warehouse_id
INTO g_inventory_item_id, g_memo_line_id, g_warehouse_id
FROM RA_CUSTOMER_TRX_LINES
WHERE customer_trx_line_id = p_cust_trx_line_id;
INSERT INTO ra_cust_trx_line_gl_dist
(
customer_trx_line_id,
customer_trx_id,
code_combination_id,
set_of_books_id,
account_class,
account_set_flag,
percent,
amount,
acctd_amount,
gl_date,
cust_trx_line_salesrep_id,
request_id,
program_application_id,
program_id,
program_update_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
posting_control_id,
original_gl_date,
cust_trx_line_gl_dist_id,
revenue_adjustment_id,
user_generated_flag,
org_id
)
VALUES
( p_cust_trx_line_id,
p_customer_trx_id,
l_ccid,
arp_standard.sysparm.set_of_books_id,
l_account_class,
'Y',
p_percent,
NULL,
NULL,
NULL,
p_sales_credit_id,
arp_standard.profile.request_id,
arp_standard.application_id,
arp_standard.profile.program_id,
sysdate,
sysdate,
l_user_id,
sysdate,
l_user_id,
-3,
NULL,
l_gl_dist_id,
p_adjustment_id,
'Y',
arp_standard.sysparm.org_id
);
arp_util.debug(' Inserted ' || l_account_class ||
' model dist with gl_dist_id = ' || l_gl_dist_id ||
' ccid = ' || l_ccid);
arp_util.debug('AR_Revenue_Adjustment_PVT.insert_sales_credit()-');
('20001 error at AR_Revenue_Adjustment_PVT.insert_sales_credit()');
arp_util.debug('insert_sales_credit: ' || 'Unexpected error '||sqlerrm||
' at AR_Revenue_Adjustment_PVT.insert_sales_credit()+');
END insert_sales_credit;
SELECT l.customer_trx_line_id
FROM mtl_item_categories mic
,ra_rules r
,ra_customer_trx_lines l
WHERE l.customer_trx_id = p_customer_trx_id
AND l.accounting_rule_id = r.rule_id(+)
AND NVL(l.inventory_item_id,0) =
NVL(p_inventory_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = AR_RAAPI_UTIL.g_inv_org_id
AND l.inventory_item_id = mic.inventory_item_id(+)
AND NVL(p_category_id,0) =
DECODE(p_category_id,NULL,0,mic.category_id)
AND mic.category_set_id(+) = AR_RAAPI_UTIL.g_category_set_id
AND l.line_type = 'LINE'
AND NVL(r.deferred_revenue_flag,'N') <> 'Y';
select revenue_adjustment_id into x_first_rev_adj_id
FROM ar_revenue_adjustments
WHERE revenue_adjustment_number = l_first_adj_num;
select revenue_adjustment_id into x_last_rev_adj_id
FROM ar_revenue_adjustments
WHERE revenue_adjustment_number = l_real_last_adj_num;
select revenue_adjustment_id into x_first_rev_adj_id
FROM ar_revenue_adjustments
WHERE revenue_adjustment_number = l_first_adj_num;
select revenue_adjustment_id into x_last_rev_adj_id
FROM ar_revenue_adjustments
WHERE revenue_adjustment_number = l_last_adj_num;