Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT
- xadma.distrib_id
- , arra.receivable_application_id
- , arps.amount_due_original
- , (arra.amount_applied
- * COALESCE(arra.trans_to_receipt_rate, 1) )
- , arra.application_type
- , arra.customer_trx_id
- , CASE WHEN xactla.period_id IS NOT NULL
- OR ( xactla.creditor IS NOT NULL
- AND xadma.subtipo_item IN ( 'Renta'
- , 'Renta Interina'
- , 'Intereses'
- , 'Capital') ) THEN
- xadma.amount_line
- * ((arra.amount_applied
- * COALESCE(arra.trans_to_receipt_rate, 1) )
- /(SELECT
- SUM(tbl_amount.amount_line
- + tbl_amount.amount_line_iva) tot_invoice
- FROM XXTIP.XXTIP_AR_DISTRIB_MTOS_ALL tbl_amount
- WHERE tbl_amount.customer_trx_id = xadma.customer_trx_id))
- ELSE
- 0
- END mtocredi_siniva
- , CASE WHEN xactla.period_id IS NOT NULL
- OR (xactla.creditor IS NOT NULL
- AND xadma.subtipo_item IN ( 'Renta'
- , 'Renta Interina'
- , 'Intereses'
- , 'Capital')) THEN
- xadma.amount_line_iva
- * ((arra.amount_applied
- * COALESCE(arra.trans_to_receipt_rate, 1) )
- /(SELECT
- SUM(tbl_amount.amount_line
- + tbl_amount.amount_line_iva) tot_invoice
- FROM XXTIP.XXTIP_AR_DISTRIB_MTOS_ALL tbl_amount
- WHERE tbl_amount.customer_trx_id = xadma.customer_trx_id))
- ELSE
- 0
- END mtocredi_iva
- , CASE WHEN xactla.period_id IS NOT NULL
- OR (xactla.creditor IS NOT NULL
- AND xadma.subtipo_item IN ( 'Renta'
- , 'Renta Interina'
- , 'Intereses'
- , 'Capital')) THEN
- xactla.creditor
- ELSE
- ''
- END creditor
- , CASE WHEN (xactla.creditor IS NOT NULL
- AND xactla.period_id IS NULL
- AND (xadma.subtipo_item NOT IN ( 'Renta'
- , 'Renta Interina'
- , 'Intereses'
- , 'Capital')
- OR xadma.subtipo_item IS NULL ))
- OR xactla.creditor IS NULL THEN
- xadma.amount_line
- * ((arra.amount_applied
- * COALESCE(arra.trans_to_receipt_rate, 1) )
- /(SELECT
- SUM(tbl_amount.amount_line
- + tbl_amount.amount_line_iva) tot_invoice
- FROM XXTIP.XXTIP_AR_DISTRIB_MTOS_ALL tbl_amount
- WHERE tbl_amount.customer_trx_id = xadma.customer_trx_id))
- ELSE
- 0
- END mtotip_siniva
- , CASE WHEN (xactla.creditor IS NOT NULL
- AND xactla.period_id IS NULL
- AND (xadma.subtipo_item NOT IN ( 'Renta'
- , 'Renta Interina'
- , 'Intereses'
- , 'Capital')
- OR xadma.subtipo_item IS NULL ))
- OR xactla.creditor IS NULL THEN
- xadma.amount_line_iva
- * ((arra.amount_applied
- * COALESCE(arra.trans_to_receipt_rate, 1) )
- /(SELECT
- SUM(tbl_amount.amount_line
- + tbl_amount.amount_line_iva) tot_invoice
- FROM XXTIP.XXTIP_AR_DISTRIB_MTOS_ALL tbl_amount
- WHERE tbl_amount.customer_trx_id = xadma.customer_trx_id))
- ELSE
- 0
- END mtotip_iva
- , xadma.cia
- , arra.apply_date
- , arra.creation_date
- , REPLACE(acr.receipt_number, '|', '') receipt_number
- , acr.receipt_date
- , CASE WHEN cban.bank_account_name LIKE 'BBVA-00201258826-MXN_FID%' THEN
- 'BBVA-00201258826-MXN_FID'
- ELSE
- cban.bank_account_name
- END bank_account_name
- FROM xxtip.xxtip_ar_distrib_mtos_all xadma
- , ar_payment_schedules_all arps
- , ar_receivable_applications_all arra
- , ar_cash_receipts_all acr
- , ar_receipt_method_accounts_all arma
- , ce_bank_acct_uses_all ceba
- , ce_bank_accounts cban
- , xxtip.xxtip_ar_cust_trx_lines_all xactla
- WHERE arps.customer_trx_id = xadma.customer_trx_id
- AND arra.applied_payment_schedule_id = arps.payment_schedule_id
- AND acr.cash_receipt_id = arra.cash_receipt_id
- AND arma.remit_bank_acct_use_id = acr.remit_bank_acct_use_id
- AND arma.receipt_method_id = acr.receipt_method_id
- AND ceba.bank_acct_use_id = arma.remit_bank_acct_use_id
- AND cban.bank_account_id = ceba.bank_account_id
- AND xadma.customer_trx_line_id = xactla.customer_trx_line_id
- AND xadma.trx_number = xactla.trx_number
- AND xadma.org_id = CASE WHEN TO_NUMBER(xactla.org_id) = 84 THEN
- 85
- ELSE
- TO_NUMBER(xactla.org_id)
- END
- AND arra.amount_applied != 0
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement