[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'AR';
SELECT dcs.category_set_id,
cs.structure_id
FROM mtl_default_category_sets dcs,
mtl_category_sets cs,
mfg_lookups ml
WHERE ml.lookup_type = 'MTL_FUNCTIONAL_AREAS'
AND ml.lookup_code = dcs.functional_area_id
AND dcs.category_set_id = cs.category_set_id
AND ml.lookup_code = '1';
SELECT t.cust_trx_type_id
,t.invoice_currency_code
,t.exchange_rate
,NVL(c.precision,0) -- Bug 3480443
,t.trx_date
,t.invoicing_rule_id
FROM ra_customer_trx t
,fnd_currencies c
WHERE t.invoice_currency_code = c.currency_code
AND t.customer_trx_id = p_customer_trx_id;
SELECT t.customer_trx_id
FROM ra_customer_trx t
,ra_batch_sources bs
WHERE t.batch_source_id = bs.batch_source_id
AND t.trx_number = p_rev_adj_rec.trx_number
AND bs.name = NVL(p_rev_adj_rec.batch_source_name,bs.name)
AND NVL(t.invoicing_rule_id,0) <> -3
AND NOT EXISTS (SELECT 'X'
FROM ra_customer_trx_lines l
WHERE l.customer_trx_id = t.customer_trx_id
AND l.line_type = 'LINE'
AND autorule_complete_flag IS NOT NULL);
SELECT t.customer_trx_id
FROM ra_customer_trx t
WHERE t.customer_trx_id = p_rev_adj_rec.customer_trx_id
AND NVL(t.invoicing_rule_id,0) <> -3
AND NOT EXISTS (SELECT 'X'
FROM ra_customer_trx_lines l
WHERE l.customer_trx_id = t.customer_trx_id
AND l.line_type = 'LINE'
AND autorule_complete_flag IS NOT NULL);
SELECT tt.type,
t.previous_customer_trx_id
FROM ra_cust_trx_types tt,
ra_customer_trx t
WHERE tt.cust_trx_type_id = t.cust_trx_type_id
AND t.customer_trx_id = g_customer_trx_id;
SELECT SUM(l.extended_amount)
FROM ra_customer_trx_lines l
WHERE l.customer_trx_id = g_customer_trx_id
AND l.line_type = 'LINE';
SELECT sum(l.extended_amount)
FROM ra_customer_trx_lines l,
ra_cust_trx_types tt,
ra_customer_trx cm
WHERE l.customer_trx_id = cm.customer_trx_id
AND cm.cust_trx_type_id = tt.cust_trx_type_id
AND l.line_type = 'LINE'
AND tt.type = 'CM'
AND cm.previous_customer_trx_id = g_customer_trx_id;
SELECT salesrep_id
FROM ra_salesreps
WHERE salesrep_number = p_salesrep_number
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE)
AND NVL(end_date_active,SYSDATE)
AND g_trx_date BETWEEN NVL(start_date_active,g_trx_date)
AND NVL(end_date_active,g_trx_date) ;
SELECT salesrep_id
FROM ra_salesreps
WHERE salesrep_id = p_salesrep_id
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE)
AND NVL(end_date_active,SYSDATE)
AND g_trx_date BETWEEN NVL(start_date_active,g_trx_date)
AND NVL(end_date_active,g_trx_date) ;
SELECT grp.group_id group_id
FROM jtf_rs_group_members mem, jtf_rs_groups_b grp,
jtf_rs_salesreps srp, jtf_rs_group_usages usg,
jtf_rs_role_relations rrl
WHERE srp.resource_id = mem.resource_id
AND mem.group_id = grp.group_id
AND mem.group_id = usg.group_id
AND usg.usage = 'SALES'
AND mem.delete_flag = 'N'
AND mem.group_member_id = rrl.role_resource_id
AND rrl.role_resource_type = 'RS_GROUP_MEMBER'
AND rrl.delete_flag = 'N'
AND nvl(rrl.end_date_active, to_date('01/01/4713','MM/DD/RRRR')) >= l_group_start_date
AND rrl.start_date_active <= l_group_end_date
AND srp.salesrep_id = g_to_salesrep_id
AND nvl(srp.org_id, -99) = nvl(arp_standard.sysparm.org_id, -99)
AND l_group_end_date BETWEEN grp.start_date_active AND nvl(grp.end_date_active, to_date('01/01/4713','MM/DD/RRRR'))
AND grp.group_id = p_salesgroup_id
UNION ALL
SELECT group_id
FROM jtf_rs_groups_b
WHERE group_id = -1
AND group_id = p_salesgroup_id;
SELECT DECODE(p_rev_adj_rec.sales_credit_type,'N',
SUM(non_revenue_percent_split), SUM(revenue_percent_split))
--begin anuj
/* Multi-Org Access Control Changes for SSA;Begin;anukumar;11/01/2002*/
SELECT customer_trx_line_id
FROM ra_customer_trx_lines
WHERE line_number = p_line_number
AND customer_trx_id = g_customer_trx_id
AND line_type = 'LINE';
SELECT customer_trx_line_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_line_id
AND line_type = 'LINE';
SELECT category_id
FROM mtl_categories_vl
WHERE NVL(segment1,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment1,FND_API.G_MISS_CHAR)
AND NVL(segment2,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment2,FND_API.G_MISS_CHAR)
AND NVL(segment3,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment3,FND_API.G_MISS_CHAR)
AND NVL(segment4,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment4,FND_API.G_MISS_CHAR)
AND NVL(segment5,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment5,FND_API.G_MISS_CHAR)
AND NVL(segment6,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment6,FND_API.G_MISS_CHAR)
AND NVL(segment7,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment7,FND_API.G_MISS_CHAR)
AND NVL(segment8,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment8,FND_API.G_MISS_CHAR)
AND NVL(segment9,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment9,FND_API.G_MISS_CHAR)
AND NVL(segment10,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment10,FND_API.G_MISS_CHAR)
AND NVL(segment11,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment11,FND_API.G_MISS_CHAR)
AND NVL(segment12,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment12,FND_API.G_MISS_CHAR)
AND NVL(segment13,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment13,FND_API.G_MISS_CHAR)
AND NVL(segment14,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment14,FND_API.G_MISS_CHAR)
AND NVL(segment15,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment15,FND_API.G_MISS_CHAR)
AND NVL(segment16,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment16,FND_API.G_MISS_CHAR)
AND NVL(segment17,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment17,FND_API.G_MISS_CHAR)
AND NVL(segment18,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment18,FND_API.G_MISS_CHAR)
AND NVL(segment19,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment19,FND_API.G_MISS_CHAR)
AND NVL(segment20,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment20,FND_API.G_MISS_CHAR)
AND structure_id = g_category_structure_id;
SELECT category_id
FROM mtl_categories_vl
WHERE category_id = p_category_id
AND structure_id = g_category_structure_id;
SELECT COUNT(*)
FROM mtl_item_categories c,
ra_customer_trx_lines l
WHERE c.inventory_item_id = l.inventory_item_id
AND l.customer_trx_id = g_customer_trx_id
AND c.category_id = p_category_id
AND l.line_type = 'LINE'
AND c.category_set_id = g_category_set_id
AND c.organization_id = g_inv_org_id;
ELSIF p_rev_adj_rec.line_selection_mode = 'C'
THEN
FND_MESSAGE.set_name (application => 'AR',
name => 'AR_RA_NO_FROM_CATEGORY');
p_rev_adj_rec.line_selection_mode = 'C'
THEN
IF p_rev_adj_rec.to_category_id IS NULL
THEN
IF (p_rev_adj_rec.to_category_segment1 IS NOT NULL OR
p_rev_adj_rec.to_category_segment2 IS NOT NULL OR
p_rev_adj_rec.to_category_segment3 IS NOT NULL OR
p_rev_adj_rec.to_category_segment4 IS NOT NULL OR
p_rev_adj_rec.to_category_segment5 IS NOT NULL OR
p_rev_adj_rec.to_category_segment6 IS NOT NULL OR
p_rev_adj_rec.to_category_segment7 IS NOT NULL OR
p_rev_adj_rec.to_category_segment8 IS NOT NULL OR
p_rev_adj_rec.to_category_segment9 IS NOT NULL OR
p_rev_adj_rec.to_category_segment10 IS NOT NULL OR
p_rev_adj_rec.to_category_segment11 IS NOT NULL OR
p_rev_adj_rec.to_category_segment12 IS NOT NULL OR
p_rev_adj_rec.to_category_segment13 IS NOT NULL OR
p_rev_adj_rec.to_category_segment14 IS NOT NULL OR
p_rev_adj_rec.to_category_segment15 IS NOT NULL OR
p_rev_adj_rec.to_category_segment16 IS NOT NULL OR
p_rev_adj_rec.to_category_segment17 IS NOT NULL OR
p_rev_adj_rec.to_category_segment18 IS NOT NULL OR
p_rev_adj_rec.to_category_segment19 IS NOT NULL OR
p_rev_adj_rec.to_category_segment20 IS NOT NULL)
THEN
l_segment_rec.segment1 := p_rev_adj_rec.to_category_segment1;
SELECT inventory_item_id
FROM mtl_system_items
WHERE NVL(segment1,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment1,FND_API.G_MISS_CHAR)
AND NVL(segment2,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment2,FND_API.G_MISS_CHAR)
AND NVL(segment3,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment3,FND_API.G_MISS_CHAR)
AND NVL(segment4,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment4,FND_API.G_MISS_CHAR)
AND NVL(segment5,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment5,FND_API.G_MISS_CHAR)
AND NVL(segment6,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment6,FND_API.G_MISS_CHAR)
AND NVL(segment7,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment7,FND_API.G_MISS_CHAR)
AND NVL(segment8,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment8,FND_API.G_MISS_CHAR)
AND NVL(segment9,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment9,FND_API.G_MISS_CHAR)
AND NVL(segment10,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment10,FND_API.G_MISS_CHAR)
AND NVL(segment11,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment11,FND_API.G_MISS_CHAR)
AND NVL(segment12,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment12,FND_API.G_MISS_CHAR)
AND NVL(segment13,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment13,FND_API.G_MISS_CHAR)
AND NVL(segment14,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment14,FND_API.G_MISS_CHAR)
AND NVL(segment15,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment15,FND_API.G_MISS_CHAR)
AND NVL(segment16,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment16,FND_API.G_MISS_CHAR)
AND NVL(segment17,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment17,FND_API.G_MISS_CHAR)
AND NVL(segment18,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment18,FND_API.G_MISS_CHAR)
AND NVL(segment19,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment19,FND_API.G_MISS_CHAR)
AND NVL(segment20,FND_API.G_MISS_CHAR) = NVL(p_segment_rec.segment20,FND_API.G_MISS_CHAR)
AND organization_id = g_inv_org_id;
SELECT inventory_item_id
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = g_inv_org_id;
SELECT COUNT(*)
FROM ra_customer_trx_lines
WHERE customer_trx_id = g_customer_trx_id
AND inventory_item_id = p_item_id
AND line_type = 'LINE';
ELSIF p_rev_adj_rec.line_selection_mode = 'I'
THEN
FND_MESSAGE.set_name (application => 'AR',
name => 'AR_RA_NO_FROM_ITEM');
p_rev_adj_rec.line_selection_mode = 'I'
THEN
IF p_rev_adj_rec.to_inventory_item_id IS NULL
THEN
IF (p_rev_adj_rec.to_item_segment1 IS NOT NULL OR
p_rev_adj_rec.to_item_segment2 IS NOT NULL OR
p_rev_adj_rec.to_item_segment3 IS NOT NULL OR
p_rev_adj_rec.to_item_segment4 IS NOT NULL OR
p_rev_adj_rec.to_item_segment5 IS NOT NULL OR
p_rev_adj_rec.to_item_segment6 IS NOT NULL OR
p_rev_adj_rec.to_item_segment7 IS NOT NULL OR
p_rev_adj_rec.to_item_segment8 IS NOT NULL OR
p_rev_adj_rec.to_item_segment9 IS NOT NULL OR
p_rev_adj_rec.to_item_segment10 IS NOT NULL OR
p_rev_adj_rec.to_item_segment11 IS NOT NULL OR
p_rev_adj_rec.to_item_segment12 IS NOT NULL OR
p_rev_adj_rec.to_item_segment13 IS NOT NULL OR
p_rev_adj_rec.to_item_segment14 IS NOT NULL OR
p_rev_adj_rec.to_item_segment15 IS NOT NULL OR
p_rev_adj_rec.to_item_segment16 IS NOT NULL OR
p_rev_adj_rec.to_item_segment17 IS NOT NULL OR
p_rev_adj_rec.to_item_segment18 IS NOT NULL OR
p_rev_adj_rec.to_item_segment19 IS NOT NULL OR
p_rev_adj_rec.to_item_segment20 IS NOT NULL)
THEN
l_segment_rec.segment1 := p_rev_adj_rec.to_item_segment1;
SELECT customer_trx_line_id
FROM ra_customer_trx_lines
WHERE line_number = p_line_number
AND customer_trx_id = g_customer_trx_id
AND line_type = 'LINE';
SELECT customer_trx_line_id
FROM ra_customer_trx_lines
WHERE customer_trx_line_id = p_line_id
AND customer_trx_id = g_customer_trx_id
AND line_type = 'LINE';
ELSIF p_rev_adj_rec.line_selection_mode = 'L'
THEN
FND_MESSAGE.set_name (application => 'AR',
name => 'AR_RA_NO_FROM_LINE');
IF p_rev_adj_rec.line_selection_mode NOT IN ('A','C','I','S')
THEN
FND_MESSAGE.set_name (application => 'AR',
name => 'AR_RA_INVALID_LINE_MODE');
FND_MESSAGE.set_token('LINE_MODE', p_rev_adj_rec.line_selection_mode);
IF AR_Revenue_Adjustment_PVT.g_update_db_flag = 'Y'
THEN
/* Bug 4304865 - separate lookup for sales credit adjustments */
IF p_rev_adj_rec.adjustment_type IN ('SA','NR') THEN
l_meaning := ARPT_SQL_FUNC_UTIL.get_lookup_meaning
(p_lookup_type => 'SALESCRED_ADJ_REASON'
,p_lookup_code => p_rev_adj_rec.reason_code);
SELECT get_cost_ctr(gl_id_rev)
FROM ra_salesreps
WHERE salesrep_id = p_salesrep_id;
SELECT b.segment_num
FROM fnd_segment_attribute_values a ,
fnd_id_flex_segments b ,
gl_sets_of_books c
WHERE a.id_flex_num = c.chart_of_accounts_id
AND c.set_of_books_id = arp_global.sysparam.set_of_books_id
AND a.application_id = 101
AND a.id_flex_code = 'GL#'
AND a.attribute_value = 'Y'
AND a.segment_attribute_type = 'FA_COST_CTR'
AND a.application_id = b.application_id
AND a.id_flex_code = b.id_flex_code
AND a.id_flex_num = b.id_flex_num
AND a.application_column_name = b.application_column_name
AND a.id_flex_num = b.id_flex_num
AND b.enabled_flag = 'Y';
SELECT NVL(SUM(s.revenue_percent_split),0),
NVL(SUM(s.non_revenue_percent_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.customer_trx_id = p_customer_trx_id
AND l.line_type = 'LINE'
AND s.salesrep_id = NVL(p_salesrep_id,s.salesrep_id)
/* BEGIN bug 3067675 */
AND DECODE(p_sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)) =
NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N', NVL(s.non_revenue_salesgroup_id, -9999), NVL(s.revenue_salesgroup_id, -9999)))
/* END bug 3067675 */
AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(p_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = 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(+) = g_category_set_id;
FND_MESSAGE.set_name('AR','AR_RA_NO_SELECTED_SALESCRED');
FUNCTION Total_Selected_Line_Value
(p_customer_trx_line_id IN NUMBER
,p_customer_trx_id IN NUMBER
,p_item_id IN NUMBER
,p_category_id IN NUMBER
,p_salesrep_id IN NUMBER
,p_salesgroup_id IN NUMBER DEFAULT NULL -- bug 3067675
,p_sales_credit_type IN VARCHAR2)
RETURN NUMBER
IS
l_all_line_total NUMBER;
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 l.line_type = 'LINE'
AND l.customer_trx_id = p_customer_trx_id
AND d.account_class IN ('REV','UNEARN')
AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(p_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = 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(+) = g_category_set_id
AND ((p_salesrep_id IS NULL AND p_salesgroup_id IS NULL AND
p_sales_credit_type 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(p_salesrep_id,ls.salesrep_id)
AND DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)) =
NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)))
GROUP BY ls.salesrep_id
HAVING SUM(NVL(DECODE(p_sales_credit_type,'N',
ls.non_revenue_percent_split,ls.revenue_percent_split),0)) <> 0));
arp_util.debug('AR_RAAPI_UTIL.Total_Selected_Line_Value()+');
arp_util.debug('Total_Selected_Line_Value: ' || 'Unexpected error '||sqlerrm||
' at AR_RAAPI_UTIL.Total_Selected_Line_Value()+');
END Total_Selected_Line_Value ;
SELECT l.customer_trx_line_id,
lr.deferred_revenue_flag
FROM mtl_item_categories mic
,ra_customer_trx_lines l
,ra_rules lr
WHERE l.customer_trx_id = p_customer_trx_id
AND l.line_type = 'LINE'
AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,l.customer_trx_line_id)
AND l.autorule_complete_flag IS NULL
AND NVL(l.inventory_item_id,0) =
NVL(p_item_id,NVL(l.inventory_item_id,0))
AND DECODE(p_adjustment_type,'LL',
DECODE(p_category_id,NULL,
DECODE(p_item_id,NULL,
DECODE(p_customer_trx_line_id,NULL,
NVL(l.accounting_rule_duration,0),0),0),0),0) <= 1
AND mic.organization_id(+) = 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(+) = g_category_set_id
AND l.accounting_rule_id = lr.rule_id (+)
AND ((p_salesrep_id IS NULL AND p_salesgroup_id IS NULL AND
p_sales_credit_type 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(p_salesrep_id,ls.salesrep_id)
AND DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)) =
NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N',NVL(ls.non_revenue_salesgroup_id, -9999), NVL(ls.revenue_salesgroup_id, -9999)))
AND NVL(ls.revenue_adjustment_id,0) <>
NVL(p_revenue_adjustment_id,
NVL(ls.revenue_adjustment_id,0) + 1)
GROUP BY ls.salesrep_id
HAVING SUM(NVL(DECODE(p_sales_credit_type,'N',
ls.non_revenue_percent_split,ls.revenue_percent_split),0)) <> 0));
SELECT /*+ index(d ra_cust_trx_line_gl_dist_n1) push_pred(s)*/
NVL(SUM(d.amount),0) amount
,NVL(SUM(d.acctd_amount),0) acctd_amount
FROM ra_cust_trx_line_gl_dist d,
ra_cust_trx_line_salesreps s
WHERE d.customer_trx_line_id = p_cust_trx_line_id
AND d.customer_trx_id = p_customer_trx_id
AND d.account_class = DECODE(p_adjustment_type,'EA','UNEARN','REV')
AND NVL(d.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
NVL(d.revenue_adjustment_id,0) + 1)
AND d.customer_trx_line_id = s.customer_trx_line_id (+)
AND d.cust_trx_line_salesrep_id = s.cust_trx_line_salesrep_id (+)
AND NVL(s.salesrep_id,-9999) =
NVL(p_salesrep_id,
NVL(s.salesrep_id,-9999))
AND NVL(s.revenue_salesgroup_id, -9999) =
NVL(p_salesgroup_id /*group*/,
NVL(s.revenue_salesgroup_id, -9999));
SELECT /*+ index(d ra_cust_trx_line_gl_dist_n1) push_pred(s)*/
NVL(SUM(NVL(d.amount,0)),0) amount
,NVL(SUM(NVL(d.acctd_amount,0)),0) acctd_amount
FROM ra_cust_trx_line_gl_dist d
,ra_customer_trx_lines l
,ra_cust_trx_line_salesreps s
WHERE l.previous_customer_trx_line_id = p_cust_trx_line_id
AND d.customer_trx_id = l.customer_trx_id
AND d.customer_trx_line_id = l.customer_trx_line_id
AND d.account_class = DECODE(p_adjustment_type,'EA','UNEARN','REV')
AND NVL(d.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
NVL(d.revenue_adjustment_id,0) + 1)
AND d.customer_trx_line_id = s.customer_trx_line_id (+)
AND d.cust_trx_line_salesrep_id = s.cust_trx_line_salesrep_id (+)
AND NVL(s.salesrep_id,-9999) =
NVL(p_salesrep_id /* sr_id */,
NVL(s.salesrep_id,-9999))
AND NVL(s.revenue_salesgroup_id, -9999) =
NVL(p_salesgroup_id /*group*/,
NVL(s.revenue_salesgroup_id, -9999));
SELECT SUM(NVL(s.non_revenue_amount_split,0)) amount
FROM ra_cust_trx_line_salesreps s
WHERE s.customer_trx_line_id = p_cust_trx_line_id
AND s.salesrep_id = NVL(p_salesrep_id,s.salesrep_id)
AND NVL(s.non_revenue_salesgroup_id, -9999) =
NVL(p_salesgroup_id,
NVL(s.non_revenue_salesgroup_id, -9999))
AND NVL(s.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
NVL(s.revenue_adjustment_id,0) + 1);
SELECT NVL(SUM(NVL(s.non_revenue_amount_split,0)),0) amount
FROM ra_customer_trx_lines l
,ra_cust_trx_line_salesreps s
WHERE l.previous_customer_trx_line_id = p_cust_trx_line_id
AND l.customer_trx_line_id = s.customer_trx_line_id
AND s.salesrep_id = NVL(p_salesrep_id /* sr_id */,s.salesrep_id)
AND NVL(s.non_revenue_salesgroup_id, -9999) =
NVL(p_salesgroup_id /*group*/,
NVL(s.non_revenue_salesgroup_id, -9999));
SELECT cmt.customer_trx_id
FROM ra_customer_trx cmt
WHERE cmt.previous_customer_trx_id = p_target_trx
AND EXISTS ( SELECT 'Unrecognized CM'
FROM ra_customer_trx_lines cmtl
WHERE cmtl.customer_trx_id = cmt.customer_trx_id
AND cmtl.line_type = 'LINE'
AND cmtl.autorule_complete_flag = 'N');
select decode(previous_customer_trx_id, NULL,'N','Y')
into l_cm_flag
from ra_customer_trx
where customer_trx_id = p_customer_trx_id;
/* These cursors select only non-revenue salescredits
(which have no corresponding dist rows) for
non-revenue SC transfers */
OPEN c_line_nr_amount(l_line_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 IN ('REV','UNEARN')
AND NVL(d.revenue_adjustment_id,0) <> NVL(p_revenue_adjustment_id,
NVL(d.revenue_adjustment_id,0) + 1)
AND l.line_type = 'LINE'
AND l.customer_trx_id = p_customer_trx_id
AND l.customer_trx_line_id = NVL(p_customer_trx_line_id,
l.customer_trx_line_id)
AND NVL(l.inventory_item_id,0) =
NVL(p_item_id,NVL(l.inventory_item_id,0))
AND mic.organization_id(+) = 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(+) = g_category_set_id
AND DECODE(p_category_id,NULL,
DECODE(p_item_id,NULL,
DECODE(p_customer_trx_line_id,NULL,
DECODE(p_adjustment_type,'LL',
NVL(l.accounting_rule_duration,0),0),0),0),0) <= 1
AND ((p_salesrep_id IS NULL AND p_salesgroup_id IS NULL AND
p_sales_credit_type 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(p_salesrep_id,ls.salesrep_id)
AND DECODE(p_sales_credit_type,'N',
NVL(ls.non_revenue_salesgroup_id, -9999),
NVL(ls.revenue_salesgroup_id, -9999)) =
NVL(p_salesgroup_id, DECODE(p_sales_credit_type,'N',
NVL(ls.non_revenue_salesgroup_id, -9999),
NVL(ls.revenue_salesgroup_id, -9999)))
GROUP BY ls.salesrep_id
HAVING SUM(NVL(DECODE(p_sales_credit_type,'N',
ls.non_revenue_percent_split,ls.revenue_percent_split),0)) <> 0));
SELECT DISTINCT gl_date
FROM ra_cust_trx_line_gl_dist
WHERE revenue_adjustment_id = p_revenue_adjustment_id;
UPDATE ra_cust_trx_line_gl_dist
SET gl_date = l_gl_date
WHERE revenue_adjustment_id = p_revenue_adjustment_id
AND gl_date = c1.gl_date;
period type is being selected when more than 1 type exists
in a calendar. */
CURSOR c_start_period IS
SELECT p.start_date, p.new_period_num
FROM ar_periods p,
gl_sets_of_books sob,
ar_period_types tp
WHERE sob.period_set_name = p.period_set_name
AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
AND sob.accounted_period_type = p.period_type
AND sob.accounted_period_type = tp.period_type
AND p_start_date BETWEEN p.start_date AND p.end_date;
SELECT p.start_date, p.end_date
FROM ar_periods p,
gl_sets_of_books sob,
ar_period_types tp
WHERE sob.period_set_name = p.period_set_name
AND sob.set_of_books_id = arp_global.sysparam.set_of_books_id
AND sob.accounted_period_type = p.period_type
AND sob.accounted_period_type = tp.period_type
AND p.new_period_num = p_new_period_num;