[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT org_id
FROM ar_system_parameters_all
WHERE org_id = p_org_id;
PROCEDURE update_dates_for_trx_event
(p_source_id_int_1 IN NUMBER,
p_trx_number IN VARCHAR2,
p_legal_entity_id IN NUMBER,
p_ledger_id IN NUMBER,
p_org_id IN NUMBER,
p_event_id IN NUMBER,
p_valuation_method IN VARCHAR2,
p_entity_type_code IN VARCHAR2,
p_event_type_code IN VARCHAR2,
p_curr_event_date IN DATE,
p_event_date IN DATE,
p_status IN VARCHAR2,
p_action IN VARCHAR2,
p_curr_trx_date IN DATE,
p_transaction_date IN DATE,
x_event_id OUT NOCOPY NUMBER)
IS
CURSOR c IS
SELECT gld.cust_trx_line_gl_dist_id dist_ctlgd_id,
gld.gl_date dist_gl_date,
gld.account_set_flag dist_account_set_flag,
trx.customer_trx_id trx_trx_id,
trx.complete_flag trx_complete_flag,
trx.trx_date trx_trx_date,
trx.invoicing_rule_id trx_invoicing_rule_id,
ctt.post_to_gl trx_post_to_gl,
xet.entity_id ent_entity_id,
ev.event_id trx_event_id,
ev.event_date trx_event_date,
ev.event_status_code trx_event_status,
ev.transaction_date trx_ev_trx_date,
gld.event_id dist_event_id,
distev.event_status_code dist_event_status,
distev.event_date dist_event_date,
distev.transaction_date dist_ev_trx_date
FROM ra_customer_trx trx,
ra_cust_trx_line_gl_dist gld,
ra_cust_trx_types ctt,
xla_transaction_entities_upg xet,
xla_events ev,
xla_events distev
WHERE trx.customer_trx_id = p_source_id_int_1
AND trx.customer_trx_id = gld.customer_trx_id
AND gld.account_class = 'REC'
AND gld.posting_control_id = -3
AND gld.latest_rec_flag = 'Y'
AND ctt.cust_trx_type_id = trx.cust_trx_type_id
AND trx.SET_OF_BOOKS_ID = xet.LEDGER_ID
AND xet.application_id = 222
AND nvl(xet.source_id_int_1, -99) = trx.customer_trx_id
AND xet.entity_code = 'TRANSACTIONS'
AND xet.entity_id = ev.entity_id
AND ev.application_id = 222
AND ev.event_date = gld.gl_date(+)
AND distev.application_id(+) = 222
AND gld.event_id = distev.event_id(+)
ORDER BY DECODE(gld.account_set_flag,'N',1,2) asc;
arp_standard.debug(' call update event with at transaction level');
xla_events_pub_pkg.update_event
(p_event_source_info => l_event_source_info,
p_event_id => l_event_id,
p_event_date => p_event_date,
p_valuation_method => p_valuation_method,
p_transaction_date => p_transaction_date,
p_security_context => l_security);
arp_standard.debug(' EXCEPTION ent_ev_no_exist - no event update required');
arp_standard.debug(' EXCEPTION no_event_found - no event update required');
arp_standard.debug(' EXCEPTION more_than_one_event can not update');
INSERT INTO ar_detect_gt(gl_date ,
source_int_id ,
entity_code ,
event_id ,
from_application)
SELECT DISTINCT a.gl_date ,
a.trx_id ,
p_entity_code ,
a.event_id ,
'AR'
FROM
(SELECT d.gl_date gl_date,
d.customer_trx_id trx_id,
d.event_id event_id
FROM ra_cust_trx_line_gl_dist d
WHERE customer_trx_id = p_source_int_id
AND account_set_flag = 'N'
AND gl_date IS NOT NULL
UNION ALL
SELECT ra.gl_date gl_date,
ra.customer_trx_id trx_id,
ra.event_id event_id
FROM ra_customer_trx trx,
ra_cust_trx_types ctt,
ar_receivable_applications ra
WHERE trx.customer_trx_id = p_source_int_id
AND ctt.cust_trx_type_id= trx.cust_trx_type_id
AND ctt.org_id = trx.org_id
AND ctt.type = 'CM'
AND trx.customer_trx_id = ra.customer_trx_id
AND ra.status = 'APP') a;
INSERT INTO ar_detect_gt(gl_date ,
source_int_id ,
entity_code ,
event_id ,
from_application)
SELECT DISTINCT gl_date,
cash_receipt_id,
p_entity_code,
event_id,
'AR'
FROM
(SELECT gl_date gl_date,
cash_receipt_id cash_receipt_id,
event_id event_id
FROM ar_cash_receipt_history crh
WHERE cash_receipt_id = p_source_int_id
UNION ALL
SELECT ra.gl_date gl_date,
ra.cash_receipt_id cash_receipt_id,
ra.event_id event_id
FROM ar_receivable_applications ra
WHERE ra.cash_receipt_id = p_source_int_id
AND ra.status = 'APP');
INSERT INTO ar_detect_gt(gl_date ,
source_int_id ,
entity_code ,
event_id ,
from_application)
SELECT gl_date,
adjustment_id,
p_entity_code,
event_id,
'AR'
FROM ar_adjustments crh
WHERE adjustment_id = p_source_int_id;
INSERT INTO ar_detect_gt(gl_date ,
source_int_id ,
entity_code ,
event_id ,
from_application)
SELECT DISTINCT gl_date,
customer_trx_id,
p_entity_code,
event_id,
'AR'
FROM ar_transaction_history crh
WHERE customer_trx_id = p_source_int_id;
INSERT INTO ar_detect_gt(gl_date ,
source_int_id ,
entity_code ,
event_id ,
from_application)
SELECT e.event_date,
t.source_id_int_1,
t.entity_code,
e.event_id,
'XLA'
FROM xla_events e,
xla_transaction_entities_upg t
WHERE t.application_id = 222
AND t.ledger_id = arp_global.set_of_books_id
AND t.entity_code = p_entity_code
AND nvl(t.source_id_int_1,-99) = p_source_int_id
AND t.entity_id = e.entity_id
AND e.application_id = 222;
SELECT gl_date
FROM ar_detect_gt
WHERE source_int_id = p_source_int_id
AND entity_code = p_entity_code
AND from_application = 'AR'
AND event_id IS NULL;
SELECT a1.gl_date
FROM ar_detect_gt a1
WHERE a1.source_int_id = p_source_int_id
AND a1.entity_code = p_entity_code
AND a1.from_application = 'AR'
AND NOT EXISTS
(SELECT a2.gl_date
FROM ar_detect_gt a2
WHERE a2.source_int_id = a1.source_int_id
AND a2.entity_code = a1.entity_code
AND a2.from_application = 'XLA'
AND a2.gl_date = a1.gl_date);
DELETE FROM ar_detect_gt
WHERE entity_code = p_entity_code
AND source_int_id = p_source_int_id;
SELECT adj.adjustment_id
FROM ar_distributions_all ard,
ar_adjustments_all adj
WHERE ard.source_table = 'ADJ'
AND ard.source_id = adj.adjustment_id
AND adj.gl_date BETWEEN p_start_date AND p_end_date
AND adj.posting_control_id = -3
AND NVL(p_org_id,adj.org_id) = adj.org_id
AND adj.status = 'A'
AND NOT EXISTS
(SELECT NULL
FROM xla_distribution_links lk,
xla_ae_lines ae
WHERE ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num);
FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
SELECT ctlgd.customer_trx_id
FROM ra_cust_trx_line_gl_dist_all ctlgd,
ra_customer_trx_all trx,
ra_cust_trx_types_all tty
WHERE ctlgd.gl_date BETWEEN p_start_date AND p_end_date
AND ctlgd.posting_control_id = -3
AND NVL(p_org_id,ctlgd.org_id) = p_org_id
AND ctlgd.account_set_flag = 'N'
AND ctlgd.customer_trx_id = trx.customer_trx_id
AND trx.cust_trx_type_id = tty.cust_trx_type_id
AND tty.org_id = trx.org_id
AND tty.post_to_gl = 'Y'
AND DECODE(p_type,
'ALL',tty.type,
'INVDEPGUAR',DECODE( tty.type,'INV','INV',
'DEP','DEP',
'GUAR','GUAR','EXCLUDE'),
p_type) = tty.type
AND NOT EXISTS
(SELECT NULL
FROM xla_distribution_links lk,
xla_ae_lines ae
WHERE ctlgd.cust_trx_line_gl_dist_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num);
FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
SELECT crh.cash_receipt_id
FROM ar_distributions_all ard,
ar_cash_receipt_history_all crh,
ar_cash_receipts_all cr
WHERE ard.source_table = 'CRH'
AND ard.source_id = crh.cash_receipt_history_id
AND crh.gl_date BETWEEN p_start_date AND p_end_date
AND crh.posting_control_id = -3
AND NVL(p_org_id,crh.org_id) = crh.org_id
AND crh.cash_receipt_id = cr.cash_receipt_id
AND DECODE(p_type,
'ALL',cr.type,
p_type) = cr.type
AND NOT EXISTS
(SELECT NULL
FROM xla_distribution_links lk,
xla_ae_lines ae
WHERE ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num);
FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
SELECT ra.receivable_application_id
FROM ar_distributions_all ard,
ar_receivable_applications_all ra
WHERE ard.source_table = 'RA'
AND ra.status = 'APP'
AND ard.source_id = ra.receivable_application_id
AND ra.gl_date BETWEEN p_start_date AND p_end_date
AND ra.posting_control_id = -3
AND NVL(p_org_id,ra.org_id) = ra.org_id
AND DECODE(p_type,'ALL',p_type,
DECODE(ra.cash_receipt_id,NULL,
DECODE(ra.customer_trx_id,NULL,NULL,'CMAPP'),
'APP')) = p_type
AND NOT EXISTS
(SELECT NULL
FROM xla_distribution_links lk,
xla_ae_lines ae
WHERE ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num);
FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
SELECT th.customer_trx_id
FROM ar_distributions_all ard,
ar_transaction_history_all th
WHERE ard.source_table = 'TH'
AND ard.source_id = th.transaction_history_id
AND th.gl_date BETWEEN p_start_date AND p_end_date
AND th.posting_control_id = -3
AND NVL(p_org_id,th.org_id) = th.org_id
AND NOT EXISTS
(SELECT NULL
FROM xla_distribution_links lk,
xla_ae_lines ae
WHERE ard.line_id = lk.source_distribution_id_num_1
AND lk.application_id = 222
AND lk.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'
AND ae.application_id = 222
AND lk.ae_header_id = ae.ae_header_id
AND lk.ae_line_num = ae.ae_line_num);
FND_MESSAGE.SET_TOKEN('ERROR' ,'Insert_Head_Row:'||SQLERRM);
PROCEDURE update_cr_dist
( p_ledger_id IN NUMBER
,p_source_id_int_1 IN NUMBER
,p_third_party_merge_date IN DATE
,p_original_third_party_id IN NUMBER
,p_original_site_id IN NUMBER
,p_new_third_party_id IN NUMBER
,p_new_site_id IN NUMBER
,p_create_update IN VARCHAR2 DEFAULT 'U'
,p_entity_code IN VARCHAR2 DEFAULT 'RECEIPTS'
,p_type_of_third_party_merge IN VARCHAR2 DEFAULT 'PARTIAL'
,p_mapping_flag IN VARCHAR2 DEFAULT 'N'
,p_execution_mode IN VARCHAR2 DEFAULT 'SYNC'
,p_accounting_mode IN VARCHAR2 DEFAULT 'F'
,p_transfer_to_gl_flag IN VARCHAR2 DEFAULT 'Y'
,p_post_in_gl_flag IN VARCHAR2 DEFAULT 'Y'
,p_third_party_type IN VARCHAR2 DEFAULT 'C'
,x_errbuf OUT NOCOPY VARCHAR2
,x_retcode OUT NOCOPY VARCHAR2
,x_event_ids OUT NOCOPY xla_third_party_merge_pub.t_event_ids
,x_request_id OUT NOCOPY NUMBER)
IS
creation_mode EXCEPTION;
arp_standard.debug('update_cr_dist +');
arp_standard.debug(' p_create_update :'||p_create_update);
IF p_create_update = 'C' THEN
arp_standard.debug('Creation mode');
INSERT INTO xla_events_gt
(APPLICATION_ID
,LEDGER_ID
,ENTITY_CODE
,SOURCE_ID_INT_1
,VALUATION_METHOD)
VALUES
(222 --APPLICATION_ID
,p_ledger_id --LEDGER_ID
,p_entity_code --ENTITY_CODE
,p_source_id_int_1 --p_cash_receipt_id --SOURCE_ID_INT_1
,NULL); --VALUATION_METHOD
UPDATE ar_distributions
SET third_party_id = p_new_third_party_id
,third_party_sub_id = p_new_site_id
WHERE
( SOURCE_TABLE, SOURCE_ID ) IN
( SELECT 'CRH', CASH_RECEIPT_HISTORY_ID
FROM AR_CASH_RECEIPT_HISTORY
WHERE CASH_RECEIPT_ID = p_source_id_int_1
UNION ALL
SELECT 'RA', RECEIVABLE_APPLICATION_ID
FROM AR_RECEIVABLE_APPLICATIONS
WHERE CASH_RECEIPT_ID = p_source_id_int_1 )
AND source_type NOT IN ('UNID');
arp_standard.debug('update_cr_dist -');
arp_standard.debug('update_cr_dist -');
arp_standard.debug('update_cr_dist -');
SELECT e.event_date
FROM xla_events e,
xla_transaction_entities_upg t
WHERE e.application_id = 222
AND e.entity_id = p_entity_id
AND t.application_id = 222
AND t.entity_id = e.entity_id
-- AND e.process_status_code = 'U'
AND e.event_status_code IN ('U','I')
AND NOT EXISTS
(SELECT 'Y'
FROM gl_period_statuses glp
WHERE glp.application_id = 222
AND e.event_date BETWEEN glp.start_date AND glp.end_date
AND glp.set_of_books_id = t.ledger_id
AND glp.closing_status IN ('O','F'));