[Home] [Help]
1529: gl_sets_of_books sob,
1530: oe_system_parameters_all osp,
1531: ra_customer_trx_all trxf,
1532: ra_customer_trx_lines_all tlf,
1533: ra_cust_trx_line_gl_dist_all gldf,
1534: ar_payment_schedules_all psch
1535: WHERE gt.event_type_code IN ('CM_CREATE','CM_UPDATE' ) --BUG#3419926
1536: AND gt.application_id = p_application_id
1537: AND gt.event_id = app.event_id
1561: AND NVL(psch.terms_sequence_number,1) = 1
1562: AND dist.source_type in ('REC','DEFERRED_TAX','TAX','CURR_ROUND') /* Bug 6119725 Start Changes */
1563: AND (((dist.ref_cust_trx_line_gl_dist_id IS NOT NULL AND sign(dist.ref_cust_trx_line_gl_dist_id) = 1)
1564: AND dist.ref_cust_trx_line_gl_dist_id NOT IN (SELECT cust_trx_line_gl_dist_id
1565: FROM ra_cust_trx_line_gl_dist_all ctlgd
1566: WHERE ctlgd.customer_trx_id = app.applied_customer_trx_id)) -- Restrict To rows of Invoice
1567: OR (((dist.ref_cust_trx_line_gl_dist_id IS NULL OR sign(dist.ref_cust_trx_line_gl_dist_id) = -1)
1568: AND ((sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0)))*-1 = sign(nvl(dist.amount_cr,0) * -1+nvl(dist.amount_dr,0)) AND dist.source_type = 'DEFERRED_TAX' )/* Bug 8269394 Changes */
1569: OR ( sign((app.acctd_amount_applied_from+nvl(app.acctd_earned_discount_taken,0)+nvl(app.acctd_unearned_discount_taken,0)))*-1 = sign(nvl(dist.acctd_amount_dr,0) * -1+nvl(dist.acctd_amount_cr,0)) AND dist.source_type = 'CURR_ROUND')
1742: gl_sets_of_books sob,
1743: oe_system_parameters_all osp,
1744: ra_customer_trx_all trxf,
1745: ra_customer_trx_lines_all tlf,
1746: ra_cust_trx_line_gl_dist_all gldf,
1747: ar_payment_schedules_all psch
1748: WHERE gt.event_type_code IN ('CM_CREATE','CM_UPDATE' )
1749: AND gt.application_id = 222
1750: AND gt.event_id = app.event_id
1948: FROM ra_customer_trx_all trx,
1949: gl_sets_of_books sob,
1950: xla_events_gt gt,
1951: ar_payment_schedules_all psch,
1952: ra_cust_trx_line_gl_dist_all ctlgd
1953: WHERE gt.event_type_code IN ('INV_CREATE' , 'INV_UPDATE' ,
1954: 'CM_CREATE' , 'CM_UPDATE' ,
1955: 'DM_CREATE' , 'DM_UPDATE' ,
1956: 'DEP_CREATE' , 'DEP_UPDATE' ,
2156: FROM ar_adjustments_all adj,
2157: gl_sets_of_books sob,
2158: xla_events_gt gt,
2159: ra_customer_trx_all trxf,
2160: ra_cust_trx_line_gl_dist_all ctlgd
2161: WHERE gt.event_type_code = 'ADJ_CREATE'
2162: AND gt.application_id = p_application_id
2163: AND adj.adjustment_id = gt.source_id_int_1
2164: AND adj.set_of_books_id = sob.set_of_books_id
2846: ,li.tax_line_id --tax_line_id
2847: ,'N' --MFAR_ADDITIONAL_ENTRY
2848: --}
2849: FROM xla_events_gt gt,
2850: ra_cust_trx_line_gl_dist_all gld,
2851: ra_customer_trx_lines_all li,
2852: ra_customer_trx_all hd,
2853: gl_sets_of_books sob,
2854: oe_system_parameters_all osp
3067: ar_adjustments_all adj,
3068: gl_sets_of_books sob,
3069: ra_customer_trx_all trxt,
3070: ra_customer_trx_lines_all tlt,
3071: ra_cust_trx_line_gl_dist_all gldt,
3072: oe_system_parameters_all osp
3073: WHERE gt.event_type_code = 'ADJ_CREATE'
3074: AND gt.application_id = p_application_id
3075: AND adj.event_id = gt.event_id
3362: gl_sets_of_books sob,
3363: oe_system_parameters_all osp,
3364: ra_customer_trx_all trxt,
3365: ra_customer_trx_lines_all tlt,
3366: ra_cust_trx_line_gl_dist_all gldt,
3367: ar_payment_schedules_all psch,
3368: ar_cash_receipts_all cr
3369: WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE' ,
3370: 'RECP_RATE_ADJUST' ) -- Uptake XLA reversal, 'RECP_REVERSE')
3498: gl_sets_of_books sob,
3499: oe_system_parameters_all osp,
3500: ra_customer_trx_all trxt,
3501: ra_customer_trx_lines_all tlt,
3502: ra_cust_trx_line_gl_dist_all gldt,
3503: ar_payment_schedules_all psch
3504: WHERE gt.event_type_code IN ( 'CM_CREATE','CM_UPDATE' )
3505: AND gt.application_id = p_application_id
3506: AND gt.event_id = app.event_id
3524: -- AND dist.activity_bucket IS NOT NULL
3525: /* Bug 6119725 Begin Changes */
3526: AND ((( dist.ref_cust_trx_line_gl_dist_id IS NOT NULL AND sign(dist.ref_cust_trx_line_gl_dist_id) = 1)
3527: AND dist.ref_cust_trx_line_gl_dist_id IN (SELECT cust_trx_line_gl_dist_id
3528: FROM ra_cust_trx_line_gl_dist_all ctlgd
3529: WHERE ctlgd.customer_trx_id = app.applied_customer_trx_id)) -- Select only TO rows which belong to Invoice
3530: OR (((dist.ref_cust_trx_line_gl_dist_id IS NULL OR sign(dist.ref_cust_trx_line_gl_dist_id) = -1)
3531: AND ((sign((app.amount_applied+nvl(app.earned_discount_taken,0)+nvl(app.unearned_discount_taken,0))) = sign(nvl(dist.amount_cr,0) * -1+nvl(dist.amount_dr,0)) AND dist.source_type = 'DEFERRED_TAX' )
3532: OR (sign((app.acctd_amount_applied_to+nvl(app.acctd_earned_discount_taken,0)+nvl(app.acctd_unearned_discount_taken,0))) = sign(nvl(dist.acctd_amount_dr,0) * -1+nvl(dist.acctd_amount_cr,0)) AND dist.source_type = 'CURR_ROUND')
3803: gl_sets_of_books sob,
3804: oe_system_parameters_all osp,
3805: ra_customer_trx_all trxt,
3806: ra_customer_trx_lines_all tlt,
3807: ra_cust_trx_line_gl_dist_all gldt,
3808: ar_payment_schedules_all psch,
3809: ar_system_parameters_all ars
3810: WHERE gt.event_type_code IN ( 'RECP_CREATE' ,'RECP_UPDATE',
3811: 'RECP_RATE_ADJUST' ) --Uptake XLA Reversal, 'RECP_REVERSE')
3907: gl_sets_of_books sob,
3908: oe_system_parameters_all osp,
3909: ra_customer_trx_all trxt,
3910: ra_customer_trx_lines_all tlt,
3911: ra_cust_trx_line_gl_dist_all gldt,
3912: ar_payment_schedules_all psch,
3913: ar_system_parameters_all ars
3914: WHERE gt.event_type_code IN ( 'CM_CREATE' ,'CM_UPDATE' )
3915: AND gt.application_id = p_application_id
3926: AND ars.org_id = app.org_id
3927: AND ars.ACCOUNTING_METHOD = 'CASH'
3928: AND acb.ref_cust_trx_line_gl_dist_id NOT IN
3929: (SELECT cust_trx_line_gl_dist_id
3930: FROM ra_cust_trx_line_gl_dist_all
3931: WHERE customer_trx_id = gt.source_id_int_1); --Excluding the receivable distribution of the CM
3932:
3933:
3934:
5490: AND ext.cm_app_to_trx_line_id IS NULL
5491: AND ard.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS','EDISC','UNEDISC','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX','DEFERRED_TAX','TAX')
5492: AND ( ard.ref_cust_trx_line_gl_dist_id IS NOT NULL
5493: AND ard.ref_cust_trx_line_gl_dist_id IN (SELECT cust_trx_line_gl_dist_id
5494: FROM ra_cust_trx_line_gl_dist_all ctlgd
5495: WHERE ctlgd.customer_trx_id = ra.customer_trx_id)) -- Select only FROM rows which belong to Credit Memo
5496: ORDER BY ra.receivable_application_id, ard.line_id;
5497:
5498:
5562: AND ctl.customer_trx_line_id = ard.ref_customer_trx_line_id
5563: AND ard.source_type NOT IN ('EXCH_GAIN','EXCH_LOSS','EDISC','UNEDISC','EDISC_NON_REC_TAX','UNEDISC_NON_REC_TAX','DEFERRED_TAX','TAX')
5564: AND ( ard.ref_cust_trx_line_gl_dist_id IS NOT NULL
5565: AND ard.ref_cust_trx_line_gl_dist_id IN (SELECT cust_trx_line_gl_dist_id
5566: FROM ra_cust_trx_line_gl_dist_all ctlgd
5567: WHERE ctlgd.customer_trx_id = ra.applied_customer_trx_id)) -- Select only TO rows which belong to Invoice
5568: ORDER BY ra.receivable_application_id, (NVL(ard.from_amount_cr,0)-NVL(ard.from_amount_dr,0)); -- Bug 14140214
5569:
5570: begin
5917: ,ar_cash_receipts_all cr
5918: ,ar_receivable_applications_all ra
5919: ,ar_distributions_all ard
5920: ,ra_customer_trx_all ct
5921: ,ra_cust_trx_line_gl_dist_all ctlgd
5922: ,ra_customer_trx_lines_all ctl
5923: ,(SELECT entity_id,
5924: source_id_int_1
5925: FROM xla_events_gt
6477: ar_receivable_applications_all ra,
6478: ar_cash_receipt_history_all crh,
6479: ar_distributions_all ard,
6480: ra_customer_trx_all trx,
6481: ra_cust_trx_line_gl_dist_all ctlgd,
6482: ra_customer_trx_lines_all ctl,
6483: ar_cash_receipts_all cr,
6484: gl_ledgers lg,
6485: ar_cash_receipt_history_all crhlatest,
6594: ar_receivable_applications_all ra,
6595: ar_cash_receipt_history_all crh,
6596: ar_distributions_all ard,
6597: ra_customer_trx_all trx,
6598: ra_cust_trx_line_gl_dist_all ctlgd,
6599: ra_customer_trx_lines_all ctl,
6600: ar_cash_receipts_all cr,
6601: gl_ledgers lg,
6602: (SELECT source_id,
6707: ar_receivable_applications_all ra,
6708: ar_cash_receipt_history_all crh,
6709: ar_distributions_all ard,
6710: ra_customer_trx_all trx,
6711: ra_cust_trx_line_gl_dist_all ctlgd,
6712: ra_customer_trx_lines_all ctl,
6713: ar_cash_receipts_all cr,
6714: gl_ledgers lg,
6715: ar_cash_receipt_history_all crhlatest,
6827: ar_receivable_applications_all ra,
6828: ar_cash_receipt_history_all crh,
6829: ar_distributions_all ard,
6830: ra_customer_trx_all trx,
6831: ra_cust_trx_line_gl_dist_all ctlgd,
6832: ra_customer_trx_lines_all ctl,
6833: ar_cash_receipts_all cr,
6834: gl_ledgers lg,
6835: ar_crh_gt crhgt,
7113: gl_sets_of_books sob,
7114: oe_system_parameters_all osp,
7115: ra_customer_trx_all trxt,
7116: ra_customer_trx_lines_all tlt,
7117: ra_cust_trx_line_gl_dist_all gldt,
7118: ar_payment_schedules_all psch,
7119: ar_cash_receipts_all cr,
7120: ar_distributions_all currard
7121: WHERE gt.event_type_code IN ('RECP_CREATE' ,'RECP_UPDATE', 'RECP_RATE_ADJUST' )
7621: FROM xla_events_gt gt,
7622: ar_transaction_history_all th,
7623: ra_customer_trx_all trx,
7624: ra_customer_trx_lines_all tl,
7625: ra_cust_trx_line_gl_dist_all gld,
7626: oe_system_parameters_all osp,
7627: ar_distributions_all dist,
7628: gl_sets_of_books sob,
7629: ar_payment_schedules_all psch
8002: -- AR_CASH_RECEIPT_HISTORY_ALL A Those 4 A entities will post together
8003: -- AR_MC_CASH_RECEIPT_HIST A (Misc) Cash Receipt
8004: -- AR_MISC_CASH_DISTRIBUTIONS_ALL A
8005: -- AR_MC_MISC_CASH_DISTS A
8006: -- RA_CUST_TRX_LINE_GL_DIST_ALL B Those 2 B entities will post tpgether
8007: -- RA_MC_TRX_LINE_GL_DIST B (Transactions)
8008: -- AR_ADJUSTMENTS_ALL C Those 2 C entities will post together
8009: -- AR_MC_ADJUSTMENTS C (Adjustments)
8010: -- AR_RECEIVABLE_APPLICATIONS_ALL D Those 2 D entities will post together
8115:
8116: --
8117: -- CTLGD
8118: -- GL Posting entiting are
8119: -- RA_CUST_TRX_LINE_GL_DIST_ALL,
8120: --
8121: UPDATE /*+ INDEX(ra_cust_trx_line_gl_dist_all ra_cust_trx_line_gl_dist_n6) */
8122: ra_cust_trx_line_gl_dist_all
8123: SET posting_control_id = l_pst_id,
8117: -- CTLGD
8118: -- GL Posting entiting are
8119: -- RA_CUST_TRX_LINE_GL_DIST_ALL,
8120: --
8121: UPDATE /*+ INDEX(ra_cust_trx_line_gl_dist_all ra_cust_trx_line_gl_dist_n6) */
8122: ra_cust_trx_line_gl_dist_all
8123: SET posting_control_id = l_pst_id,
8124: gl_posted_date = l_date,
8125: last_updated_by = fnd_global.user_id,
8118: -- GL Posting entiting are
8119: -- RA_CUST_TRX_LINE_GL_DIST_ALL,
8120: --
8121: UPDATE /*+ INDEX(ra_cust_trx_line_gl_dist_all ra_cust_trx_line_gl_dist_n6) */
8122: ra_cust_trx_line_gl_dist_all
8123: SET posting_control_id = l_pst_id,
8124: gl_posted_date = l_date,
8125: last_updated_by = fnd_global.user_id,
8126: last_update_date = sysdate,
8311: -- CTLGD
8312: SELECT 'lock'
8313: BULK COLLECT INTO l_lock
8314: FROM xla_entity_events_v eve,
8315: ra_cust_trx_line_gl_dist_all ctlgd
8316: WHERE eve.request_id = p_report_request_id
8317: AND eve.application_id = p_application_id
8318: AND eve.entity_code = 'TRANSACTIONS'
8319: AND eve.event_id = ctlgd.event_id