[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
last_update_date DATE,
last_updated_by NUMBER,
creation_date DATE,
created_by NUMBER,
last_update_login NUMBER,
program_application_id NUMBER,
program_id NUMBER,
program_update_date DATE,
request_id NUMBER,
receipt_number VARCHAR2(100),
applied_flag VARCHAR2(100),
customer_id NUMBER,
customer_name VARCHAR2(100),
customer_number VARCHAR2(100),
trx_number VARCHAR2(100),
installment NUMBER,
amount_applied NUMBER,
amount_applied_from NUMBER,
trans_to_receipt_rate NUMBER,
discount NUMBER,
discounts_earned NUMBER,
discounts_unearned NUMBER,
discount_taken_earned NUMBER,
discount_taken_unearned NUMBER,
amount_due_remaining NUMBER,
due_date DATE,
status VARCHAR2(100),
term_id NUMBER,
trx_class_name VARCHAR2(100),
trx_class_code VARCHAR2(100),
trx_type_name VARCHAR2(100),
cust_trx_type_id NUMBER,
trx_date DATE,
location_name VARCHAR2(100),
bill_to_site_use_id NUMBER,
days_late NUMBER,
line_number NUMBER,
customer_trx_line_id NUMBER,
apply_date DATE,
gl_date DATE,
gl_posted_date DATE,
reversal_gl_date DATE,
exchange_rate NUMBER,
invoice_currency_code VARCHAR2(15),
amount_due_original NUMBER,
amount_in_dispute NUMBER,
amount_line_items_original NUMBER,
acctd_amount_due_remaining NUMBER,
acctd_amount_applied_to NUMBER,
acctd_amount_applied_from NUMBER,
exchange_gain_loss NUMBER,
discount_remaining NUMBER,
calc_discount_on_lines_flag VARCHAR2(100),
partial_discount_flag VARCHAR2(100),
allow_overapplication_flag VARCHAR2(100),
natural_application_only_flag VARCHAR2(100),
creation_sign VARCHAR2(100),
applied_payment_schedule_id NUMBER,
ussgl_transaction_code VARCHAR2(100),
ussgl_transaction_code_context VARCHAR2(100),
purchase_order VARCHAR2(50),
trx_doc_sequence_id NUMBER,
trx_doc_sequence_value VARCHAR2(100),
trx_batch_source_name VARCHAR2(100),
amount_adjusted NUMBER,
amount_adjusted_pending NUMBER,
amount_line_items_remaining NUMBER,
freight_original NUMBER,
freight_remaining NUMBER,
receivables_charges_remaining NUMBER,
tax_original NUMBER,
tax_remaining NUMBER,
selected_for_receipt_batch_id NUMBER,
receivable_application_id NUMBER,
attribute_category VARCHAR2(50),
attribute1 VARCHAR2(150),
attribute2 VARCHAR2(150),
attribute3 VARCHAR2(150),
attribute4 VARCHAR2(150),
attribute5 VARCHAR2(150),
attribute6 VARCHAR2(150),
attribute7 VARCHAR2(150),
attribute8 VARCHAR2(150),
attribute9 VARCHAR2(150),
attribute10 VARCHAR2(150),
attribute11 VARCHAR2(150),
attribute12 VARCHAR2(150),
attribute13 VARCHAR2(150),
attribute14 VARCHAR2(150),
attribute15 VARCHAR2(150),
trx_billing_number VARCHAR2(30),
global_attribute_category VARCHAR2(50),
global_attribute1 VARCHAR2(150),
global_attribute2 VARCHAR2(150),
global_attribute3 VARCHAR2(150),
global_attribute4 VARCHAR2(150),
global_attribute5 VARCHAR2(150),
global_attribute6 VARCHAR2(150),
global_attribute7 VARCHAR2(150),
global_attribute8 VARCHAR2(150),
global_attribute9 VARCHAR2(150),
global_attribute10 VARCHAR2(150),
global_attribute11 VARCHAR2(150),
global_attribute12 VARCHAR2(150),
global_attribute13 VARCHAR2(150),
global_attribute14 VARCHAR2(150),
global_attribute15 VARCHAR2(150),
global_attribute16 VARCHAR2(150),
global_attribute17 VARCHAR2(150),
global_attribute18 VARCHAR2(150),
global_attribute19 VARCHAR2(150),
global_attribute20 VARCHAR2(150),
transaction_category VARCHAR2(150),
trx_gl_date DATE,
comments VARCHAR2(240), -- bug 2662270
receivables_trx_id NUMBER, --
rec_activity_name VARCHAR2(50), --
application_ref_id NUMBER, -- CM refunds
application_ref_num VARCHAR2(150), --
application_ref_type VARCHAR2(30), --
application_ref_type_meaning VARCHAR2(80) --
);
L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('last_update_date'), '5');
L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('last_updated_by'), '6');
L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('last_update_login'), '9');
L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('program_update_date'), '12');
/* Bug2680500 Deleted
L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('amount_exch_rate_diff_base'), '56');
L_ORDER_BY :=REPLACE(UPPER(L_ORDER_BY), UPPER('selected_for_receipt_batch_id'), '77');
| ON_SELECT
|
| DESCRIPTION
|
| This procedure opens and executes the appropriate cursor.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
|
| ARGUMENTS : IN:
|
| OUT:
|
| RETURNS : NONE
|
| NOTES
|
| MODIFICATION HISTORY
| 25-AUG-97 Joan Zaman Created.
| 08-JAN-98 Karen Murphy See list of changes at the package level.
| 14-MAY-98 Guat Eng Tan Bug #655455. Added p_trx_bill_number_find
| parameter to on_select procedure. Added
| code to include the billing number restriction
| when selecting for open invoices.
| 24-Aug-01 H Yoshihara Bug 1930411: add p_related_cust_flag
| argument to ON_SELECT procedure.
|
+===========================================================================*/
PROCEDURE on_select (
p_select_type VARCHAR2
,p_apply_date DATE
,p_receipt_gl_date DATE
,p_customer_id NUMBER
,p_bill_to_site_use_id NUMBER
,p_receipt_currency VARCHAR2
,p_cm_customer_trx_id NUMBER
,p_trx_type_name_find VARCHAR2
,p_due_date_find VARCHAR2
,p_trx_date_find VARCHAR2
,p_amt_due_rem_find VARCHAR2
,p_trx_number_find VARCHAR2
,p_include_disputed VARCHAR2
,p_include_cross_curr VARCHAR2
,p_inv_class VARCHAR2
,p_chb_class VARCHAR2
,p_cm_class VARCHAR2
,p_dm_class VARCHAR2
,p_dep_class VARCHAR2
,p_status VARCHAR2
,p_order_by VARCHAR2
,p_trx_bill_number_find VARCHAR2
,p_purchase_order_find VARCHAR2 default NULL
,p_transaction_category_find VARCHAR2 default NULL
,p_br_class VARCHAR2 default NULL /* 01-JUN-2000 J Rautiainen BR Implementation */
,p_related_cust_flag VARCHAR2 /* bug1930411 */
) IS
open_trx_lng long;
arp_standard.debug( 'on_select()+' );
SELECT
/*+
FIRST_ROWS
INDEX(ps_inv AR_PAYMENT_SCHEDULES_N6)
INDEX(cust hz_cust_accounts_u1)
INDEX(bs ra_batch_sources_u2)
INDEX(ctt ra_cust_trx_types_u1)
INDEX(rcr hz_cust_acct_relate_n1)
INDEX(su hz_cust_site_uses_u1)
INDEX(t ra_terms_b_u1)
USE_NL(ps_inv)
USE_NL(ct)
USE_NL(ctt)
USE_NL(cust)
USE_NL(su)
USE_NL(bs)
USE_NL(t)
USE_NL(ci)
USE_NL(l_class)
PUSH_SUBQ ORDERED */
ps_inv.rowid row_id
, -1 cash_receipt_id
, ps_inv.customer_trx_id customer_trx_id
, NULL cm_customer_trx_id
, ps_inv.last_update_date last_update_date
, ps_inv.last_updated_by last_updated_by
, ps_inv.creation_date creation_date
, ps_inv.created_by created_by
, ps_inv.last_update_login last_update_login
, ps_inv.program_application_id program_application_id
, ps_inv.program_id program_id
, ps_inv.program_update_date program_update_date
, ps_inv.request_id request_id
, NULL receipt_number
, ''n'' applied_flag
, ps_inv.customer_id customer_id
, substrb(party.party_name,1, 50) customer_name
, cust.account_number customer_number
, ps_inv.trx_number trx_number
, ps_inv.terms_sequence_number installment
, NULL amount_applied
, NULL amount_applied_from
, NULL trans_to_receipt_rate
, NULL discount
, NULL discounts_earned
, NULL discounts_unearned
, ps_inv.discount_taken_earned discount_taken_earned
, ps_inv.discount_taken_unearned discount_taken_unearned
, ps_inv.amount_due_remaining amount_due_remaining
, ps_inv.due_date due_date
, ps_inv.status status
, ps_inv.term_id term_id
, l_class.meaning trx_class_name
, ps_inv.class trx_class_code
, ctt.name trx_type_name
, ctt.cust_trx_type_id cust_trx_type_id
, ct.trx_date trx_date
, su.location location_name
, ps_inv.customer_site_use_id bill_to_site_use_id
, NULL days_late
, NULL line_number
, NULL customer_trx_line_id
, :p_apply_date apply_date
, arp_view_constants.get_default_gl_date(
greatest(ps_inv.gl_date,
:p_receipt_gl_date,
DECODE(NVL(:l_app_gl_date_default, ''INV_REC_DT''),
''INV_REC_SYS_DT'', sysdate,
''INV_REC_DT'', ps_inv.gl_date,
ps_inv.gl_date))) gl_date
, NULL gl_posted_date
, NULL reversal_gl_date
, ps_inv.exchange_rate exchange_rate
, ps_inv.invoice_currency_code invoice_currency_code
, ps_inv.amount_due_original amount_due_original
, ps_inv.amount_in_dispute amount_in_dispute
, ps_inv.amount_line_items_original amount_line_items_original
, ps_inv.acctd_amount_due_remaining acctd_amount_due_remaining
, NULL acctd_amount_applied_to
, NULL acctd_amount_applied_from
, NULL exchange_gain_loss
, ps_inv.discount_remaining discount_remaining
, t.calc_discount_on_lines_flag calc_discount_on_lines_flag
, t.partial_discount_flag partial_discount_flag
, ctt.allow_overapplication_flag allow_overapplication_flag
, ctt.natural_application_only_flag natural_application_only_flag
, ctt.creation_sign creation_sign
, ps_inv.payment_schedule_id applied_payment_schedule_id
, ct.default_ussgl_transaction_code ussgl_transaction_code
, ct.default_ussgl_trx_code_context ussgl_transaction_code_context
, ct.purchase_order purchase_order
, ct.doc_sequence_id trx_doc_sequence_id
, ct.doc_sequence_value trx_doc_sequence_value
, bs.name trx_batch_source_name
, ps_inv.amount_adjusted amount_adjusted
, ps_inv.amount_adjusted_pending amount_adjusted_pending
, ps_inv.amount_line_items_remaining amount_line_items_remaining
, ps_inv.freight_original freight_original
, ps_inv.freight_remaining freight_remaining
, ps_inv.receivables_charges_remaining receivables_charges_remaining
, ps_inv.tax_original tax_original
, ps_inv.tax_remaining tax_remaining
, ps_inv.selected_for_receipt_batch_id selected_for_receipt_batch_id
, NULL receivable_application_id
, NULL attribute_category
, NULL attribute1
, NULL attribute2
, NULL attribute3
, NULL attribute4
, NULL attribute5
, NULL attribute6
, NULL attribute7
, NULL attribute8
, NULL attribute9
, NULL attribute10
, NULL attribute11
, NULL attribute12
, NULL attribute13
, NULL attribute14
, NULL attribute15
, ci.cons_billing_number trx_billing_number
, NULL global_attribute_CATEGORY
, NULL global_attribute1
, NULL global_attribute2
, NULL global_attribute3
, NULL global_attribute4
, NULL global_attribute5
, NULL global_attribute6
, NULL global_attribute7
, NULL global_attribute8
, NULL global_attribute9
, NULL global_attribute10
, NULL global_attribute11
, NULL global_attribute12
, NULL global_attribute13
, NULL global_attribute14
, NULL global_attribute15
, NULL global_attribute16
, NULL global_attribute17
, NULL global_attribute18
, NULL global_attribute19
, NULL global_attribute20
, ctt.attribute10 transaction_category -- ARTA
, ps_inv.gl_date trx_gl_date
FROM
ar_payment_schedules ps_inv
, ra_customer_trx ct
, ra_cust_trx_types ctt
, hz_cust_accounts cust
, hz_parties party
, hz_cust_site_uses su
, ra_batch_sources bs
, ra_terms_b t
, ar_cons_inv ci
, ar_lookups l_class
WHERE ps_inv.selected_for_receipt_batch_id IS NULL
/* 08-JUL-2000 J Rautiainen BR Implementation */
AND ps_inv.reserved_type IS NULL
AND ps_inv.reserved_value IS NULL
AND ps_inv.class||'''' NOT IN(''GUAR'',''PMT'')
AND ps_inv.class = l_class.lookup_code
AND l_class.lookup_type = ''INV/CM''
AND ps_inv.customer_trx_id = ct.customer_trx_id
AND ps_inv.customer_site_use_id = nvl(:p_bill_to_site_use_id, ps_inv.customer_site_use_id)
AND ps_inv.status = ''OP''
AND bs.batch_source_id = ct.batch_source_id
AND ps_inv.cust_trx_type_id = ctt.cust_trx_type_id
AND ps_inv.customer_id = cust.cust_account_id
AND cust.party_id = party.party_id
AND ps_inv.customer_site_use_id= su.site_use_id
AND ci.cons_inv_id(+) = ps_inv.cons_inv_id
-- Term id for credit memos is null
AND ps_inv.term_id = t.term_id(+)
AND ps_inv.customer_id IN
(SELECT rcr.RELATED_CUST_ACCOUNT_ID
FROM hz_cust_acct_relate RCR
WHERE RCR.CUST_ACCOUNT_ID = :p_customer_id
AND RCR.STATUS=''A''
AND RCR.BILL_TO_FLAG=''Y''
-- bug1930411 add flag whether or not related customers are selected
AND :p_related_cust_flag = ''Y''
UNION
SELECT :p_customer_id
FROM SYS.DUAL)
AND ps_inv.invoice_currency_code =
decode(:p_include_cross_curr,''Y'',
ps_inv.invoice_currency_code
,:p_receipt_currency)
AND ps_inv.status = NVL(:p_status,ps_inv.status)
/* 01-JUN-2000 J Rautiainen BR Implementation
* Added BR class */
AND ps_inv.class NOT IN (nvl(:p_inv_class,''XX''),nvl(:p_chb_class,''XX''),
nvl(:p_dep_class,''XX''),nvl(:p_cm_class,''XX''),
nvl(:p_dm_class,''XX''), nvl(:p_br_class,''XX''))
AND NVL(ps_inv.amount_in_dispute,0) = DECODE ( :p_include_disputed
, ''N'' , 0
, NVL(ps_inv.amount_in_dispute,0) )
';
SELECT
ps_inv.rowid row_id
, -1 cash_receipt_id
, ps_inv.customer_trx_id customer_trx_id
, ps_inv.last_update_date last_update_date
, ps_inv.last_updated_by last_updated_by
, ps_inv.creation_date creation_date
, ps_inv.created_by created_by
, ps_inv.last_update_login last_update_login
, ps_inv.program_application_id program_application_id
, ps_inv.program_id program_id
, ps_inv.program_update_date program_update_date
, ps_inv.request_id request_id
, ''n'' applied_flag
, -1 customer_id
, ps_inv.trx_number trx_number
, 0 discounts_earned
, 0 discounts_unearned
, 0 discount_taken_earned
, 0 discount_taken_unearned
, ps_inv.amount_due_remaining amount_due_remaining
, ps_inv.status status
, ps_inv.term_id term_id
, -1 bill_to_site_use_id
, arp_view_constants.get_apply_date apply_date
, arp_view_constants.get_default_gl_date(
greatest(ps_inv.gl_date,
:p_receipt_gl_date,
DECODE(NVL(:l_app_gl_date_default, ''INV_REC_DT''),
''INV_REC_SYS_DT'', sysdate,
''INV_REC_DT'', ps_inv.gl_date,
ps_inv.gl_date))) gl_date
, :p_receipt_currency invoice_currency_code
, ps_inv.amount_due_original amount_due_original
, ps_inv.amount_in_dispute amount_in_dispute
, ps_inv.amount_line_items_original amount_line_items_original
, ps_inv.discount_remaining discount_remaining
, ps_inv.payment_schedule_id applied_payment_schedule_id
FROM
ar_payment_schedules ps_inv
WHERE
ps_inv.payment_schedule_id = -1
';
SELECT
/*+
INDEX(app AR_RECEIVABLE_APPLICATIONS_N8 AR_RECEIVABLE_APPLICATIONS_N2)
INDEX(ps_inv ar_payment_schedules_u1 ar_payment_schedules_n2 ar_payment_schedules_n6)
INDEX(ps_cm ar_payment_schedules_n2)
INDEX(cust hz_cust_accounts_u1)
INDEX(ct ra_customer_trx_u1)
INDEX(bs ra_batch_sources_u2)
INDEX(ctt ra_cust_trx_types_u1)
INDEX(su hz_cust_site_uses_u1)
INDEX(t ra_terms_u1)
USE_NL(ps_cm)
USE_NL(app)
USE_NL(ps_inv)
USE_NL(cust)
USE_NL(ct)
USE_NL(ctl)
USE_NL(bs)
USE_NL(su)
USE_NL(ci)
USE_NL(l_class)
ORDERED PUSH_SUBQ */
app.rowid row_id
, -2 cash_receipt_id
, ps_inv.customer_trx_id customer_trx_id
, ps_cm.customer_trx_id cm_customer_trx_id
, app.last_update_date last_update_date
, app.last_updated_by last_updated_by
, app.creation_date creation_date
, app.created_by created_by
, app.last_update_login last_update_login
, app.program_application_id program_application_id
, app.program_id program_id
, app.program_update_date program_update_date
, app.request_id request_id
, ps_cm.trx_number receipt_number
, ''Y'' applied_flag
, ps_inv.customer_id customer_id
, substrb(party.party_name,1,50) customer_name
, cust.account_number customer_number
, ps_inv.trx_number trx_number
, DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL,ps_inv.terms_sequence_number) installment
, app.amount_applied amount_applied
, app.amount_applied amount_applied_from
, NULL trans_to_receipt_rate
, NULL discount
, NULL discounts_earned
, NULL discounts_unearned
, ps_inv.discount_taken_earned discount_taken_earned
, ps_inv.discount_taken_unearned discount_taken_unearned
, TO_NUMBER(DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL,ps_inv.amount_due_remaining)) amount_due_remaining
, DECODE(SIGN(ps_inv.payment_schedule_id),-1,TO_DATE(NULL), ps_inv.due_date) due_date
, ps_inv.status status
, ps_inv.term_id term_id
-- , l_class.meaning trx_class_name
, DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL, arpt_sql_func_util.get_lookup_meaning(''INV/CM'', ps_inv.CLASS)) trx_class_name
, ps_inv.class trx_class_code
, ctt.name trx_type_name
, ctt.cust_trx_type_id cust_trx_type_id
, ct.trx_date trx_date
, su.location location_name
, ct.bill_to_site_use_id bill_to_site_use_id
, DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL,to_number(app.apply_date-TRUNC(ps_inv.due_date))) days_late
, ctl.line_number line_number
, ctl.customer_trx_line_id customer_trx_line_id
, app.apply_date apply_date
, app.gl_date gl_date
, app.gl_posted_date gl_posted_date
, app.reversal_gl_date reversal_gl_date
, ps_inv.exchange_rate exchange_rate
, DECODE(SIGN(ps_inv.payment_schedule_id),-1,ps_cm.invoice_currency_code, ps_inv.invoice_currency_code) invoice_currency_code
, ps_inv.amount_due_original amount_due_original
, ps_inv.amount_in_dispute amount_in_dispute
, ps_inv.amount_line_items_original amount_line_items_original
, TO_NUMBER(DECODE(SIGN(ps_inv.payment_schedule_id),-1,NULL, ps_inv.acctd_amount_due_remaining)) acctd_amount_due_remaining
, app.acctd_amount_applied_to acctd_amount_applied_to
, app.acctd_amount_applied_from acctd_amount_applied_from
, app.acctd_amount_applied_from
- NVL(app.acctd_amount_applied_to,app.acctd_amount_applied_from)
exchange_gain_loss
, ps_inv.discount_remaining discount_remaining
, t.calc_discount_on_lines_flag calc_discount_on_lines_flag
, t.partial_discount_flag partial_discount_flag
, ctt.allow_overapplication_flag allow_overapplication_flag
, ctt.natural_application_only_flag natural_application_only_flag
, ctt.creation_sign creation_sign
, ps_inv.payment_schedule_id applied_payment_schedule_id
, app.ussgl_transaction_code ussgl_transaction_code
, app.ussgl_transaction_code_context ussgl_transaction_code_context
, ct.purchase_order purchase_order
, ct.doc_sequence_id trx_doc_sequence_id
, ct.doc_sequence_value trx_doc_sequence_value
, bs.name trx_batch_source_name
, ps_inv.amount_adjusted amount_adjusted
, ps_inv.amount_adjusted_pending amount_adjusted_pending
, ps_inv.amount_line_items_remaining amount_line_items_remaining
, ps_inv.freight_original freight_original
, ps_inv.freight_remaining freight_remaining
, ps_inv.receivables_charges_remaining receivables_charges_remaining
, ps_inv.tax_original tax_original
, ps_inv.tax_remaining tax_remaining
, ps_inv.selected_for_receipt_batch_id selected_for_receipt_batch_id
, app.receivable_application_id receivable_application_id
, app.attribute_category attribute_category
, app.attribute1 attribute1
, app.attribute2 attribute2
, app.attribute3 attribute3
, app.attribute4 attribute4
, app.attribute5 attribute5
, app.attribute6 attribute6
, app.attribute7 attribute7
, app.attribute8 attribute8
, app.attribute9 attribute9
, app.attribute10 attribute10
, app.attribute11 attribute11
, app.attribute12 attribute12
, app.attribute13 attribute13
, app.attribute14 attribute14
, app.attribute15 attribute15
, ci.cons_billing_number trx_billing_number
, app.global_attribute_category global_attribute_CATEGORY
, app.global_attribute1 global_attribute1
, app.global_attribute2 global_attribute2
, app.global_attribute3 global_attribute3
, app.global_attribute4 global_attribute4
, app.global_attribute5 global_attribute5
, app.global_attribute6 global_attribute6
, app.global_attribute7 global_attribute7
, app.global_attribute8 global_attribute8
, app.global_attribute9 global_attribute9
, app.global_attribute10 global_attribute10
, app.global_attribute11 global_attribute11
, app.global_attribute12 global_attribute12
, app.global_attribute13 global_attribute13
, app.global_attribute14 global_attribute14
, app.global_attribute15 global_attribute15
, app.global_attribute16 global_attribute16
, app.global_attribute17 global_attribute17
, app.global_attribute18 global_attribute18
, app.global_attribute19 global_attribute19
, app.global_attribute20 global_attribute20
, ctt.attribute10 transaction_category -- ARTA
, ps_cm.gl_date trx_gl_date
, app.comments comments -- bug 2662270
, app.receivables_trx_id receivables_trx_id -- cm refunds
, rt.name rec_activity_name
, app.application_ref_id application_ref_id
, app.application_ref_num application_ref_num
, app.application_ref_type application_ref_type
, arpt_sql_func_util.get_lookup_meaning(''APPLICATION_REF_TYPE'', app.application_ref_type) application_ref_type_meaning
FROM
ar_payment_schedules ps_cm
, ar_receivable_applications app
, ar_payment_schedules ps_inv
, hz_cust_accounts cust
, hz_parties party
, ra_customer_trx ct
, ra_customer_trx_lines ctl
, ra_batch_sources bs
, ar_receivables_trx rt
, ra_cust_trx_types ctt
, hz_cust_site_uses su
, ar_cons_inv ci
-- , ar_lookups l_class
, ra_terms t
WHERE
app.applied_payment_schedule_id = ps_inv.payment_schedule_id
AND app.display = ''Y''
-- This means we only get CM applications. We use index :)
AND app.customer_trx_id > -1
AND app.customer_trx_id = ps_cm.customer_trx_id
AND t.term_id(+) = ps_inv.term_id
AND ct.customer_trx_id(+) = ps_inv.customer_trx_id
AND bs.batch_source_id (+) = ct.batch_source_id
AND ctt.cust_trx_type_id(+) = ps_inv.cust_trx_type_id
AND cust.cust_account_id(+) = ps_inv.customer_id
AND cust.party_id = party.party_id(+)
AND su.site_use_id(+) = ct.bill_to_site_use_id
AND ctl.customer_trx_line_id(+) = app.applied_customer_trx_line_id
-- AND ps_inv.class||'''' = l_class.lookup_code
-- AND l_class.lookup_type = ''INV/CM''
AND ci.cons_inv_id(+) = ps_inv.cons_inv_id
AND rt.receivables_trx_id(+) = app.receivables_trx_id
' || l_cm_cust_trx_where ;
IF p_select_type = 'MASSAPPLY' THEN
--
-- Before opening the cursor query, we need to incoroprate
-- the query conditions (that were included in the Mass Apply
-- window) in the cursor select.
--
-- The query conditions were passed to the procedure in the
-- following variables.
--
l_trx_number_find := p_trx_number_find;
dbms_sql.define_column(c_open_trx,5,open_trx_row.last_update_date);
dbms_sql.define_column(c_open_trx,6,open_trx_row.last_updated_by);
dbms_sql.define_column(c_open_trx,9,open_trx_row.last_update_login);
dbms_sql.define_column(c_open_trx,12,open_trx_row.program_update_date);
dbms_sql.define_column(c_open_trx,77,open_trx_row.selected_for_receipt_batch_id);
dbms_sql.define_column(c_on_acct,4,on_acct_row.last_update_date);
dbms_sql.define_column(c_on_acct,5,on_acct_row.last_updated_by);
dbms_sql.define_column(c_on_acct,8,on_acct_row.last_update_login);
dbms_sql.define_column(c_on_acct,11,on_acct_row.program_update_date);
ELSIF p_select_type = 'CM' THEN
cm_apps_lng := cm_apps_lng || l_order_by;
dbms_sql.define_column(c_cm_apps,5,cm_apps_row.last_update_date);
dbms_sql.define_column(c_cm_apps,6,cm_apps_row.last_updated_by);
dbms_sql.define_column(c_cm_apps,9,cm_apps_row.last_update_login);
dbms_sql.define_column(c_cm_apps,12,cm_apps_row.program_update_date);
dbms_sql.define_column(c_cm_apps,77,cm_apps_row.selected_for_receipt_batch_id);
arp_standard.debug( 'on_select()-' );
arp_standard.debug('EXCEPTION: ar_add_fetch_select.on_select');
END on_select;
| This function selects a single row from the cursor
| and returns the record values to the form.
|
| SCOPE - PUBLIC
|
| EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE
|
| ARGUMENTS : IN:
|
| OUT:
|
| RETURNS : TRUE if a record has been selected
| FALSE if a record has not been selected
| NOTES
|
| MODIFICATION HISTORY
| 25-AUG-97 Joan Zaman Created.
| 08-JAN-98 Karen Murphy See list of changes at the package level.
|
+===========================================================================*/
function on_fetch (
p_select_type IN VARCHAR2,
row_id OUT NOCOPY VARCHAR2,
cash_receipt_id OUT NOCOPY NUMBER,
customer_trx_id OUT NOCOPY NUMBER,
cm_customer_trx_id OUT NOCOPY NUMBER,
last_update_date OUT NOCOPY DATE,
last_updated_by OUT NOCOPY NUMBER,
creation_date OUT NOCOPY DATE,
created_by OUT NOCOPY NUMBER,
last_update_login OUT NOCOPY NUMBER,
program_application_id OUT NOCOPY NUMBER,
program_id OUT NOCOPY NUMBER,
program_update_date OUT NOCOPY DATE,
request_id OUT NOCOPY NUMBER,
receipt_number OUT NOCOPY VARCHAR2,
applied_flag OUT NOCOPY VARCHAR2,
customer_id OUT NOCOPY NUMBER,
customer_name OUT NOCOPY VARCHAR2,
customer_number OUT NOCOPY VARCHAR2,
trx_number OUT NOCOPY VARCHAR2,
installment OUT NOCOPY NUMBER,
amount_applied OUT NOCOPY NUMBER,
amount_applied_from OUT NOCOPY NUMBER,
trans_to_receipt_rate OUT NOCOPY NUMBER,
discount OUT NOCOPY NUMBER,
discounts_earned OUT NOCOPY NUMBER,
discounts_unearned OUT NOCOPY NUMBER,
discount_taken_earned OUT NOCOPY NUMBER,
discount_taken_unearned OUT NOCOPY NUMBER,
amount_due_remaining OUT NOCOPY NUMBER,
due_date OUT NOCOPY DATE,
status OUT NOCOPY VARCHAR2,
term_id OUT NOCOPY NUMBER,
trx_class_name OUT NOCOPY VARCHAR2,
trx_class_code OUT NOCOPY VARCHAR2,
trx_type_name OUT NOCOPY VARCHAR2,
cust_trx_type_id OUT NOCOPY NUMBER,
trx_date OUT NOCOPY DATE,
location_name OUT NOCOPY VARCHAR2,
bill_to_site_use_id OUT NOCOPY NUMBER,
days_late OUT NOCOPY NUMBER,
line_number OUT NOCOPY NUMBER,
customer_trx_line_id OUT NOCOPY NUMBER,
apply_date OUT NOCOPY DATE,
gl_date OUT NOCOPY DATE,
gl_posted_date OUT NOCOPY DATE,
reversal_gl_date OUT NOCOPY DATE,
exchange_rate OUT NOCOPY NUMBER,
invoice_currency_code OUT NOCOPY VARCHAR2,
amount_due_original OUT NOCOPY NUMBER,
amount_in_dispute OUT NOCOPY NUMBER,
amount_line_items_original OUT NOCOPY NUMBER,
acctd_amount_due_remaining OUT NOCOPY NUMBER,
acctd_amount_applied_to OUT NOCOPY NUMBER,
acctd_amount_applied_from OUT NOCOPY NUMBER,
exchange_gain_loss OUT NOCOPY NUMBER,
discount_remaining OUT NOCOPY NUMBER,
calc_discount_on_lines_flag OUT NOCOPY VARCHAR2,
partial_discount_flag OUT NOCOPY VARCHAR2,
allow_overapplication_flag OUT NOCOPY VARCHAR2,
natural_application_only_flag OUT NOCOPY VARCHAR2,
creation_sign OUT NOCOPY VARCHAR2,
applied_payment_schedule_id OUT NOCOPY NUMBER,
ussgl_transaction_code OUT NOCOPY VARCHAR2,
ussgl_transaction_code_context OUT NOCOPY VARCHAR2,
purchase_order OUT NOCOPY VARCHAR2,
trx_doc_sequence_id OUT NOCOPY NUMBER,
trx_doc_sequence_value OUT NOCOPY VARCHAR2,
trx_batch_source_name OUT NOCOPY VARCHAR2,
amount_adjusted OUT NOCOPY NUMBER,
amount_adjusted_pending OUT NOCOPY NUMBER,
amount_line_items_remaining OUT NOCOPY NUMBER,
freight_original OUT NOCOPY NUMBER,
freight_remaining OUT NOCOPY NUMBER,
receivables_charges_remaining OUT NOCOPY NUMBER,
tax_original OUT NOCOPY NUMBER,
tax_remaining OUT NOCOPY NUMBER,
selected_for_receipt_batch_id OUT NOCOPY NUMBER,
receivable_application_id OUT NOCOPY NUMBER,
attribute_category OUT NOCOPY VARCHAR2,
attribute1 OUT NOCOPY VARCHAR2,
attribute2 OUT NOCOPY VARCHAR2,
attribute3 OUT NOCOPY VARCHAR2,
attribute4 OUT NOCOPY VARCHAR2,
attribute5 OUT NOCOPY VARCHAR2,
attribute6 OUT NOCOPY VARCHAR2,
attribute7 OUT NOCOPY VARCHAR2,
attribute8 OUT NOCOPY VARCHAR2,
attribute9 OUT NOCOPY VARCHAR2,
attribute10 OUT NOCOPY VARCHAR2,
attribute11 OUT NOCOPY VARCHAR2,
attribute12 OUT NOCOPY VARCHAR2,
attribute13 OUT NOCOPY VARCHAR2,
attribute14 OUT NOCOPY VARCHAR2,
attribute15 OUT NOCOPY VARCHAR2,
trx_billing_number OUT NOCOPY VARCHAR2,
global_attribute_category OUT NOCOPY VARCHAR2,
global_attribute1 OUT NOCOPY VARCHAR2,
global_attribute2 OUT NOCOPY VARCHAR2,
global_attribute3 OUT NOCOPY VARCHAR2,
global_attribute4 OUT NOCOPY VARCHAR2,
global_attribute5 OUT NOCOPY VARCHAR2,
global_attribute6 OUT NOCOPY VARCHAR2,
global_attribute7 OUT NOCOPY VARCHAR2,
global_attribute8 OUT NOCOPY VARCHAR2,
global_attribute9 OUT NOCOPY VARCHAR2,
global_attribute10 OUT NOCOPY VARCHAR2,
global_attribute11 OUT NOCOPY VARCHAR2,
global_attribute12 OUT NOCOPY VARCHAR2,
global_attribute13 OUT NOCOPY VARCHAR2,
global_attribute14 OUT NOCOPY VARCHAR2,
global_attribute15 OUT NOCOPY VARCHAR2,
global_attribute16 OUT NOCOPY VARCHAR2,
global_attribute17 OUT NOCOPY VARCHAR2,
global_attribute18 OUT NOCOPY VARCHAR2,
global_attribute19 OUT NOCOPY VARCHAR2,
global_attribute20 OUT NOCOPY VARCHAR2,
-- purchase_order OUT NOCOPY VARCHAR2,
transaction_category OUT NOCOPY VARCHAR2,
trx_gl_date OUT NOCOPY DATE,
comments OUT NOCOPY VARCHAR2, --- bug 2662270
receivables_trx_id OUT NOCOPY NUMBER,
rec_activity_name OUT NOCOPY VARCHAR,
application_ref_id OUT NOCOPY NUMBER,
application_ref_num OUT NOCOPY VARCHAR2,
application_ref_type OUT NOCOPY VARCHAR2,
application_ref_type_meaning OUT NOCOPY VARCHAR2
) return BOOLEAN IS
ignore number;
dbms_sql.column_value(c_open_trx,5,open_trx_row.last_update_date);
dbms_sql.column_value(c_open_trx,6,open_trx_row.last_updated_by);
dbms_sql.column_value(c_open_trx,9,open_trx_row.last_update_login);
dbms_sql.column_value(c_open_trx,12,open_trx_row.program_update_date);
dbms_sql.column_value(c_open_trx,77,open_trx_row.selected_for_receipt_batch_id);
last_update_date :=open_trx_row.last_update_date;
last_updated_by :=open_trx_row.last_updated_by;
last_update_login :=open_trx_row.last_update_login;
program_update_date :=open_trx_row.program_update_date;
selected_for_receipt_batch_id:=open_trx_row.selected_for_receipt_batch_id;
dbms_sql.column_value(c_on_acct,4,on_acct_row.last_update_date);
dbms_sql.column_value(c_on_acct,5,on_acct_row.last_updated_by);
dbms_sql.column_value(c_on_acct,8,on_acct_row.last_update_login);
dbms_sql.column_value(c_on_acct,11,on_acct_row.program_update_date);
last_update_date := on_acct_row.last_update_date;
last_updated_by := on_acct_row.last_updated_by;
last_update_login := on_acct_row.last_update_login;
program_update_date := on_acct_row.program_update_date;
dbms_sql.column_value(c_cm_apps,5,cm_apps_row.last_update_date);
dbms_sql.column_value(c_cm_apps,6,cm_apps_row.last_updated_by);
dbms_sql.column_value(c_cm_apps,9,cm_apps_row.last_update_login);
dbms_sql.column_value(c_cm_apps,12,cm_apps_row.program_update_date);
dbms_sql.column_value(c_cm_apps,77,cm_apps_row.selected_for_receipt_batch_id);
last_update_date :=cm_apps_row.last_update_date;
last_updated_by :=cm_apps_row.last_updated_by;
last_update_login :=cm_apps_row.last_update_login;
program_update_date :=cm_apps_row.program_update_date;
selected_for_receipt_batch_id:=cm_apps_row.selected_for_receipt_batch_id;
arp_standard.debug('EXCEPTION: ar_add_fetch_select.on_fetch');