APPS.CN_SCA_CREDITS_BATCH_PVT dependencies on CN_SCA_WINNERS

Line 106: FROM cn_sca_winners w,

102: SELECT MAX(w.sca_headers_interface_id) sca_headers_interface_id,
103: MAX(w.sca_credit_rule_id) sca_credit_rule_id,
104: DECODE(SUM(NVL(l.allocation_percentage,0)),100,'ALLOCATED','REV NOT 100')
105: process_status
106: FROM cn_sca_winners w,
107: cn_sca_lines_output l
108: WHERE w.sca_headers_interface_id = l.sca_headers_interface_id
109: AND w.sca_headers_interface_id BETWEEN l_start_id AND l_end_id
110: AND w.role_id = l.role_id

Line 196: -- Populate the data from cn_sca_matches to cn_sca_winners. During this

192: RETURN;
193: END IF;
194:
195: ----+
196: -- Populate the data from cn_sca_matches to cn_sca_winners. During this
197: -- process, eliminate duplicate records and identify the unique transaction
198: -- and rule combination. Get the resources, roles and allocation
199: -- for this credit rule.
200: ----+

Line 203: 'INSERT /*+ APPEND */ INTO cn_sca_winners( '||

199: -- for this credit rule.
200: ----+
201:
202: l_winners_sql :=
203: 'INSERT /*+ APPEND */ INTO cn_sca_winners( '||
204: ' sca_credit_rule_id, '||
205: ' sca_headers_interface_id, '||
206: ' process_date, '||
207: ' rank, '||

Line 293: debugmsg('Process Batch Rules: Records in cn_sca_winners :'||SQL%ROWCOUNT);

289: IN l_end_id,
290: IN p_org_id;
291:
292: debugmsg('Process Batch Rules: Executing Winners SQL ');
293: debugmsg('Process Batch Rules: Records in cn_sca_winners :'||SQL%ROWCOUNT);
294:
295: COMMIT WORK;
296:
297: EXCEPTION

Line 382: ' FROM cn_sca_winners a, '||

378: ' PARTITION BY a.sca_headers_interface_id, b.role_id, '||
379: ' c.revenue_type '||
380: ' ORDER BY b.resource_id) rn, '||
381: ' a.ORG_ID '||
382: ' FROM cn_sca_winners a, '||
383: ' cn_sca_lines_interface b, '||
384: ' (SELECT ''REVENUE'' revenue_type FROM dual '||
385: ' UNION ALL '||
386: ' SELECT ''NONREVENUE'' revenue_type FROM dual)c '||

Line 444: --+ CN_SCA_MATCHES table but not availble in CN_SCA_WINNERS table. We need

440: COMMIT WORK;
441:
442: --+
443: --+ Update STATUS flag to 'NOT ALLOCATED' if record is available in
444: --+ CN_SCA_MATCHES table but not availble in CN_SCA_WINNERS table. We need
445: --+ to use dynamic SQL since PL/SQL does not support RANK() function in
446: --+ 8.1.7
447: --+
448:

Line 473: ' FROM cn_sca_winners c '||

469: ' ) b '||
470: ' WHERE h.sca_headers_interface_id = b.sca_headers_interface_id '||
471: ' AND NOT EXISTS ( '||
472: ' SELECT ''X'' '||
473: ' FROM cn_sca_winners c '||
474: ' WHERE h.sca_headers_interface_id = c.sca_headers_interface_id)) '||
475: ' WHERE h.credit_rule_id IS NULL '||
476: ' AND h.process_status = ''SCA_UNPROCESSED'' '||
477: ' AND h.org_id = :p_org_id AND '|| -- added org_id here

Line 529: --+ cn_sca_matches table and cn_sca_winners table.

525: END;
526:
527: --+
528: --+ Delete the records corresponding to each physical batch from
529: --+ cn_sca_matches table and cn_sca_winners table.
530: --+
531:
532: debugmsg('Completed for the physical batch : ' || p_physical_batch_id);
533: