[Home] [Help]
16: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
17: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
18: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
19: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
20: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
21: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE);
22:
23:
24: PROCEDURE from_conc_request_window(
57: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
58: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
59: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
60: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
61: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
62: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE);
63:
64: PROCEDURE arbr_cr_tmp_table;
65:
102: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
103: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
104: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
105: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
106: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
107: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
108: p_batch_id OUT NOCOPY RA_BATCHES.batch_id%TYPE,
109: p_selection_criteria_id OUT NOCOPY RA_BATCHES.selection_criteria_id%TYPE);
110:
128: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
129: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
130: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
131: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
132: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
133: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE);
134:
135: -- Bug 3922691 : added additional parameters so dynamic sql can be more fine-tuned
136: PROCEDURE construct_suffixe_select(
144: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
145: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
146: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
147: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
148: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
149: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
150: p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE);
151:
152: -- bug 3922691 : consolidate code used in 2 procedures
151:
152: -- bug 3922691 : consolidate code used in 2 procedures
153: PROCEDURE construct_hz(
154: p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
155: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
156: p_suffix_hz OUT NOCOPY varchar2);
157:
158: -- bug 3922691 : remove p_suffixe_select_statement, add p_lead_days
159: PROCEDURE select_DM_and_CM_IMM(
167: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
168: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
169: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
170: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
171: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
172: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
173: p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
174: p_batch_id IN RA_BATCHES.batch_id%TYPE,
175: p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
189: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
190: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
191: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
192: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
193: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
194: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
195: p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
196: p_batch_id IN RA_BATCHES.batch_id%TYPE,
197: p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
269: errorinv errorinvoicetabtyp;
270:
271: /* Bug 14146916 */
272: TYPE skipped_br_records IS RECORD (
273: customer_id HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
274: due_date AR_PAYMENT_SCHEDULES.due_date%TYPE,
275: site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE,
276: payment_schedule_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE);
277:
399: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE DEFAULT NULL,
400: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE DEFAULT NULL,
401: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE DEFAULT NULL,
402: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE DEFAULT NULL,
403: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE DEFAULT NULL,
404: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE DEFAULT NULL) IS
405:
406: -- Dates
407: l_batch_date DATE := NULL;
621: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
622: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
623: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
624: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
625: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
626: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE) IS
627:
628: l_request_id NUMBER;
629:
739: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
740: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
741: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
742: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
743: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
744: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE) IS
745:
746: l_batch_id RA_BATCHES.batch_id%TYPE;
747: l_selection_criteria_id RA_BATCHES.selection_criteria_id%TYPE;
904: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
905: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
906: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
907: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
908: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
909: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
910: p_batch_id OUT NOCOPY RA_BATCHES.batch_id%TYPE,
911: p_selection_criteria_id OUT NOCOPY
912: RA_BATCHES.selection_criteria_id%TYPE) IS
1176: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
1177: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
1178: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
1179: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
1180: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
1181: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE) IS
1182:
1183: /*
1184: Bug 1808967 : added trx.customer_bank_account_id in select statement,
1298:
1299: c_grouping cur_typ;
1300:
1301: -- 2nd break criteria
1302: l_customer_id HZ_CUST_ACCOUNTS.cust_account_id%TYPE;
1303: l_customer_id2 HZ_CUST_ACCOUNTS.cust_account_id%TYPE;
1304: l_due_date AR_PAYMENT_SCHEDULES.due_date%TYPE;
1305: l_site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE;
1306: l_customer_trx_id AR_PAYMENT_SCHEDULES.customer_trx_id%TYPE;
1299: c_grouping cur_typ;
1300:
1301: -- 2nd break criteria
1302: l_customer_id HZ_CUST_ACCOUNTS.cust_account_id%TYPE;
1303: l_customer_id2 HZ_CUST_ACCOUNTS.cust_account_id%TYPE;
1304: l_due_date AR_PAYMENT_SCHEDULES.due_date%TYPE;
1305: l_site_use_id AR_PAYMENT_SCHEDULES.customer_site_use_id%TYPE;
1306: l_customer_trx_id AR_PAYMENT_SCHEDULES.customer_trx_id%TYPE;
1307: l_payment_schedule_id AR_PAYMENT_SCHEDULES.payment_schedule_id%TYPE;
1912: +===========================================================================*/
1913:
1914: PROCEDURE construct_hz(
1915: p_receipt_creation_rule_code IN AR_RECEIPT_METHODS.receipt_creation_rule_code%TYPE,
1916: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
1917: p_suffix_hz OUT NOCOPY varchar2) IS
1918:
1919: l_trx_select_statement VARCHAR2(4000) := NULL;
1920:
2025: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
2026: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
2027: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
2028: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
2029: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
2030: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
2031: p_le_id IN RA_CUSTOMER_TRX.legal_entity_id%TYPE
2032: ) IS
2033:
2062: ar_payment_schedules ps,
2063: ra_customer_trx trx,
2064: ra_cust_trx_types type,
2065: fnd_currencies_vl cur,
2066: hz_cust_accounts cust,
2067: hz_parties party,
2068: iby_trxn_extensions_v extn
2069: WHERE trx.customer_trx_id = ps.customer_trx_id
2070: ';
2255: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
2256: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
2257: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
2258: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
2259: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
2260: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
2261: p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
2262: p_batch_id IN RA_BATCHES.batch_id%TYPE,
2263: p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
2503: p_trx_number_low IN RA_CUSTOMER_TRX.trx_number%TYPE,
2504: p_trx_number_high IN RA_CUSTOMER_TRX.trx_number%TYPE,
2505: p_cust_class IN AR_LOOKUPS.lookup_code%TYPE,
2506: p_cust_category IN AR_LOOKUPS.lookup_code%TYPE,
2507: p_customer_id IN HZ_CUST_ACCOUNTS.cust_account_id%TYPE,
2508: p_site_use_id IN HZ_CUST_SITE_USES.site_use_id%TYPE,
2509: p_receipt_method_id IN AR_RECEIPT_METHODS.receipt_method_id%TYPE,
2510: p_batch_id IN RA_BATCHES.batch_id%TYPE,
2511: p_invoice_currency_code IN RA_CUSTOMER_TRX.invoice_currency_code%TYPE,
3300: SELECT site_uses.site_use_id,
3301: site_uses.contact_id,
3302: Null external_bank_account_id
3303: INTO l_drawee_site_use_id, l_drawee_contact_id, l_drawee_bank_account_id
3304: FROM hz_cust_accounts cust_acct,
3305: hz_cust_acct_sites acct_site,
3306: hz_cust_site_uses site_uses,
3307: hz_cust_account_roles acct_role
3308: WHERE cust_acct.cust_account_id = l_drawee_id
3320: SELECT site_uses.site_use_id,
3321: site_uses.contact_id,
3322: Null external_bank_account_id
3323: INTO l_drawee_site_use_id, l_drawee_contact_id, l_drawee_bank_account_id
3324: FROM hz_cust_accounts cust_acct,
3325: hz_cust_acct_sites acct_site,
3326: hz_cust_site_uses site_uses,
3327: hz_cust_account_roles acct_role,
3328: hz_party_sites party_site
3333: AND site_uses.status = 'A'
3334: AND acct_site.party_site_id = party_site.party_site_id
3335: AND party_site.location_id =
3336: (select bloc.location_id
3337: FROM hz_cust_accounts bcust_acct,
3338: hz_cust_acct_sites bacct_site,
3339: hz_cust_site_uses bsite_uses,
3340: hz_party_sites bparty_site,
3341: hz_locations bloc
3381: -- get site/contact information pertaining to PRIMARY drawee site
3382:
3383: SELECT site_uses.site_use_id, site_uses.contact_id, acct_site.cust_acct_site_id
3384: INTO l_drawee_site_use_id, l_drawee_contact_id, l_site_id
3385: FROM hz_cust_accounts cust_acct,
3386: hz_cust_acct_sites acct_site,
3387: hz_cust_site_uses site_uses,
3388: hz_cust_account_roles acct_role
3389: WHERE cust_acct.cust_account_id = l_drawee_id
3399: -- the bank accounts are linked to
3400:
3401: SELECT site_uses.site_use_id
3402: INTO l_bill_to_site_id
3403: FROM hz_cust_accounts cust_acct,
3404: hz_cust_acct_sites acct_site,
3405: hz_cust_site_uses site_uses
3406: WHERE cust_acct.cust_account_id = l_drawee_id
3407: AND cust_acct.cust_account_id = acct_site.cust_account_id
3459: SELECT site_uses.site_use_id,
3460: site_uses.contact_id,
3461: p_customer_bank_account_id
3462: INTO l_drawee_site_use_id, l_drawee_contact_id, l_drawee_bank_account_id
3463: FROM hz_cust_accounts cust_acct,
3464: hz_cust_acct_sites acct_site,
3465: hz_cust_site_uses site_uses,
3466: hz_cust_account_roles acct_role,
3467: hz_party_sites party_site
3473: -- following conditions ensure that this DRAWEE site is also a BILL TO site
3474: AND acct_site.party_site_id = party_site.party_site_id
3475: AND party_site.location_id =
3476: (select bloc.location_id
3477: FROM hz_cust_accounts bcust_acct,
3478: hz_cust_acct_sites bacct_site,
3479: hz_cust_site_uses bsite_uses,
3480: hz_party_sites bparty_site,
3481: hz_locations bloc