[Home] [Help]
2132: --
2133:
2134: FUNCTION br_posted (p_customer_trx_id in NUMBER) RETURN VARCHAR2 IS
2135:
2136: customer_trx_id ra_customer_trx.customer_trx_id%TYPE;
2137:
2138:
2139: Cursor Cposted (CustomerTrxId NUMBER) IS SELECT
2140: customer_trx_id
2274: LINES.extended_amount,
2275: LINES.extended_acctd_amount,
2276: LINES.customer_trx_line_id
2277: FROM
2278: RA_CUSTOMER_TRX TRX ,
2279: RA_CUST_TRX_TYPES TYPES,
2280: AR_PAYMENT_SCHEDULES PAYS ,
2281: HZ_CUST_ACCOUNTS CUST_ACCT ,
2282: HZ_PARTIES PARTY,
2283: AR_RECEIPT_METHODS METH,
2284: CE_BANK_BRANCHES_V ABB,
2285: HZ_CUST_SITE_USES SITE,
2286: AP_BANK_ACCOUNTS ABA,
2287: RA_CUSTOMER_TRX_LINES LINES ,
2288: AR_CONS_INV_ALL CONS
2289: WHERE trx.legal_entity_id = ' || p_le_id ||
2290: ' and trx.bill_to_customer_id = cust_acct.cust_account_id
2291: and trx.cust_trx_type_id = types.cust_trx_type_id
2319: END LOOP;
2320: CLOSE AssignCur;
2321:
2322: /* bug 2201843 :
2323: 1) eliminate join to RA_CUSTOMER_TRX billsrec by storing BR's currency and
2324: trxdate of the BR in local variables br_currency and br_trxdate
2325: 2) replace TRX.drawee_id = cust_acct.cust_account_id OR
2326: TRX.bill_to_customer_id = cust_acct.cust_account_id
2327: with decode to avoid full scan on HZ_CUST_ACCOUNTS
2342: select '''' || invoice_currency_code ||'''',
2343: 'to_dat'||'e(''' ||trx_date||''')'
2344: into br_currency,
2345: br_trxdate
2346: from ra_customer_trx
2347: where customer_trx_id = br_trxid;
2348:
2349: /* get all non-BR transactions that can be assigned to this BR */
2350:
2396: PAYS.acctd_amount_due_remaining, /* Extended Acctd Amount */
2397: NULL /* Customer_trx_line_id */
2398: FROM
2399: RA_CUST_TRX_TYPES TYPES,
2400: RA_CUSTOMER_TRX TRX ,
2401: AR_PAYMENT_SCHEDULES PAYS ,
2402: HZ_CUST_ACCOUNTS CUST_ACCT ,
2403: HZ_PARTIES PARTY,
2404: AR_receipt_METHODS METH,
2418: and trx.receipt_method_id = meth.receipt_method_id (+)
2419: and PAYS.selected_for_receipt_batch_id is null
2420: and not exists
2421: (select 1
2422: from ra_customer_trx_lines sub,
2423: ar_payment_schedules ps
2424: where sub.br_ref_customer_trx_id = TRX.customer_trx_id
2425: and ps.customer_trx_id = trx.customer_trx_id
2426: and ( ( ps.amount_due_remaining = 0 and
2498: PAYS.acctd_amount_due_remaining, /* Extended Acctd Amount */
2499: NULL /* Customer_TRX_LINE_ID */
2500: FROM
2501: RA_CUST_TRX_TYPES TYPES,
2502: RA_CUSTOMER_TRX TRX ,
2503: AR_PAYMENT_SCHEDULES PAYS ,
2504: HZ_CUST_ACCOUNTS CUST_ACCT ,
2505: HZ_PARTIES PARTY,
2506: AR_receipt_METHODS METH,
2524: and H.current_record_flag = ''Y''
2525: and H.status = ''UNPAID''
2526: and not exists
2527: (select 1
2528: from ra_customer_trx_lines linesub,
2529: ar_payment_schedules paysub ,
2530: ra_customer_trx billsub
2531: where linesub.br_ref_customer_trx_id = TRX.customer_trx_id
2532: and linesub.customer_trx_id = billsub.customer_trx_id
2526: and not exists
2527: (select 1
2528: from ra_customer_trx_lines linesub,
2529: ar_payment_schedules paysub ,
2530: ra_customer_trx billsub
2531: where linesub.br_ref_customer_trx_id = TRX.customer_trx_id
2532: and linesub.customer_trx_id = billsub.customer_trx_id
2533: and billsub.customer_trx_id = paysub.customer_trx_id
2534: and (paysub.reserved_type is not null OR billsub.br_on_hold_flag = ''Y''))