[Home] [Help]
972: /*Late charge changes for amount per tier Enhacement 6469663*/
973: CURSOR wrong_setup(fin_charge_date in date ) is
974: select substrb(party.party_name,1,50) name,lc_site.lc_cust_sites_id,lc_site.customer_id,lc_site.currency_code,lc_site.customer_site_use_id,
975: 'INTEREST' type
976: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
977: WHERE lc_site.interest_type = 'CHARGE_PER_TIER'
978: AND lc_site.interest_schedule_id = c_schdl.schedule_id
979: AND c_schdl.schedule_id = h_schdl.schedule_id
980: AND fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
983: AND cust_acct.party_id = party.party_id
984: UNION
985: select substrb(party.party_name,1,50) name,lc_site.lc_cust_sites_id,lc_site.customer_id,lc_site.currency_code,lc_site.customer_site_use_id,
986: 'PENALTY' type
987: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
988: WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
989: AND lc_site.penalty_schedule_id = c_schdl.schedule_id
990: AND c_schdl.schedule_id = h_schdl.schedule_id
991: AND fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
996:
997:
998:
999: l_org_id NUMBER;
1000: l_customer_name_from hz_parties.party_name%type;
1001: l_customer_name_to hz_parties.party_name%type;
1002: l_customer_number_from hz_cust_accounts.account_number%type;
1003: l_customer_number_to hz_cust_accounts.account_number%type;
1004: l_cust_site_use_id number;
997:
998:
999: l_org_id NUMBER;
1000: l_customer_name_from hz_parties.party_name%type;
1001: l_customer_name_to hz_parties.party_name%type;
1002: l_customer_number_from hz_cust_accounts.account_number%type;
1003: l_customer_number_to hz_cust_accounts.account_number%type;
1004: l_cust_site_use_id number;
1005: l_fin_charge_date date;
1230: from ar_payment_schedules ps,
1231: ar_transaction_history th,
1232: ra_customer_trx trx,
1233: hz_cust_accounts cust_acct,
1234: hz_parties party,
1235: hz_customer_profiles profiles,
1236: hz_cust_profile_amts prof_amts,
1237: hz_cust_site_uses site_use,
1238: ar_system_parameters sysparam
1355: /*Late Charge Changes for charge per tier Enhacement 6469663*/
1356: UPDATE ar_lc_cust_sites_t set penalty_type = NULL
1357: WHERE lc_cust_sites_id IN
1358: ( select lc_cust_sites_id
1359: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
1360: WHERE lc_site.penalty_type = 'CHARGE_PER_TIER'
1361: AND lc_site.penalty_schedule_id = c_schdl.schedule_id
1362: AND c_schdl.schedule_id = h_schdl.schedule_id
1363: AND p_fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
1369: delete from ar_lc_cust_sites_t
1370: where lc_cust_sites_id IN
1371: (
1372: select lc_cust_sites_id
1373: FROM ar_lc_cust_sites_t lc_site,hz_cust_accounts cust_acct,hz_parties party,ar_charge_schedules c_schdl,ar_charge_schedule_headers_v h_schdl
1374: WHERE lc_site.interest_type = 'CHARGE_PER_TIER'
1375: AND lc_site.interest_schedule_id = c_schdl.schedule_id
1376: AND c_schdl.schedule_id = h_schdl.schedule_id
1377: AND p_fin_charge_date between h_schdl.start_date and nvl(h_schdl.end_date,to_date('31-12-4712','DD-MM-YYYY'))
6579: p_master_request_id IN VARCHAR2) IS
6580:
6581: l_org_id number(15);
6582: l_mode varchar2(1);
6583: l_customer_name_from hz_parties.party_name%type;
6584: l_customer_name_to hz_parties.party_name%type;
6585: l_customer_number_from hz_cust_accounts.account_number%type;
6586: l_customer_number_to hz_cust_accounts.account_number%type;
6587: l_cust_site_use_id number;
6580:
6581: l_org_id number(15);
6582: l_mode varchar2(1);
6583: l_customer_name_from hz_parties.party_name%type;
6584: l_customer_name_to hz_parties.party_name%type;
6585: l_customer_number_from hz_cust_accounts.account_number%type;
6586: l_customer_number_to hz_cust_accounts.account_number%type;
6587: l_cust_site_use_id number;
6588: l_fin_charge_date date;
6856: l_worker_number NUMBER ;
6857: l_req_id NUMBER;
6858: l_rep_req_id NUMBER := 0;
6859: l_batch_name ar_interest_batches.batch_name%type;
6860: l_customer_name_from hz_parties.party_name%TYPE;
6861: l_customer_name_to hz_parties.party_name%TYPE;
6862: l_late_charge_batch VARCHAR2(100);
6863: l_fin_charge_date DATE;
6864: l_gl_date DATE;
6857: l_req_id NUMBER;
6858: l_rep_req_id NUMBER := 0;
6859: l_batch_name ar_interest_batches.batch_name%type;
6860: l_customer_name_from hz_parties.party_name%TYPE;
6861: l_customer_name_to hz_parties.party_name%TYPE;
6862: l_late_charge_batch VARCHAR2(100);
6863: l_fin_charge_date DATE;
6864: l_gl_date DATE;
6865: l_req_data VARCHAR2(2000);
6946: BEGIN
6947: IF p_customer_id_from IS NOT NULL THEN
6948: select hp.party_name
6949: into l_customer_name_from
6950: from hz_parties hp,
6951: hz_cust_accounts cust_acct
6952: where hp.party_id = cust_acct.party_id
6953: and cust_acct.cust_account_id = p_customer_id_from;
6954: ELSE
6963: BEGIN
6964: IF p_customer_id_to IS NOT NULL THEN
6965: select hp.party_name
6966: into l_customer_name_to
6967: from hz_parties hp,
6968: hz_cust_accounts cust_acct
6969: where hp.party_id = cust_acct.party_id
6970: and cust_acct.cust_account_id = p_customer_id_to;
6971: ELSE