[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Cursor for Selecting Standing Charges |
| |
*------------------------------------------------------*/
SELECT DISTINCT
sc.standing_charge_id
, sc.set_of_books_id
, sc.comments
, sc.charge_reference
, sc.description desc_1
, sc.bill_to_customer_id
, sc.bill_to_site_use_id
, sc.bill_to_contact_id
, sc.ship_to_customer_id
, sc.ship_to_address_id
, sc.bill_to_address_id
, sc.ship_to_site_use_id
, sc.ship_to_contact_id
, sc.start_date
, sc.end_date
, sc.standing_charge_date
, sc.next_due_date
, sc.suppress_inv_print
, sc.cust_trx_type_id
, sc.receipt_method_id
, sc.batch_source_id
, sc.salesrep_id
, sc.advance_arrears_ind -- change here to do testing
, sc.bank_account_id
, sc.payment_trxn_extension_id /*Bug No 5905216 Payment Upgrade for R12*/
, sc.previous_due_date -- change here to do testing
, sc.creation_date
, sc.created_by
, sc.last_update_date
, sc.last_updated_by
, sc.last_update_login
, SYSDATE
, sc.period_name sc_period_name
, sc.rowid sc_rowid
, sc.default_invoicing_rule
, bs.name bs_name
, nvl(sc.term_id,4) term_id
, bs.rev_acc_allocation_rule
, sob.currency_code
/*5905216*/
, sc.org_id
, sc.legal_entity_id
FROM gl_sets_of_books sob
, igi_rpi_standing_charges sc
, ar_system_parameters sp
, ra_batch_sources bs
, ra_cust_trx_types ct
, hz_cust_accounts ca
WHERE sp.set_of_books_id = cp_sob_id
AND sp.set_of_books_id = sob.set_of_books_id
AND sp.set_of_books_id = sc.set_of_books_id
AND bs.batch_source_id = NVL(cp_batch_source_id,bs.batch_source_id)
AND nvl(bs.end_date,cp_run_date +1) >= cp_run_date
AND nvl(bs.start_date,cp_run_date-1) <= cp_run_date
AND sc.batch_source_id = bs.batch_source_id
AND sc.cust_trx_type_id = ct.cust_trx_type_id
AND nvl(ct.end_date,cp_run_date+1) >= cp_run_date
AND nvl(ct.start_date,cp_run_date-1) <= cp_run_date
AND nvl(sc.date_synchronized_flag,'Y') = 'Y'
AND sc.status = STANDING_CHARGE_STATUS
/*changed the following AND clause for bug 4436839*/
AND (
( nvl(sc.advance_arrears_ind,sc.default_invoicing_rule) = ARREARS_STATUS
AND nvl(sc.previous_due_date,sc.start_date) <= nvl(sc.end_date,sc.next_due_date)
)
OR
( nvl(sc.advance_arrears_ind,sc.default_invoicing_rule) = ADVANCE_STATUS
AND sc.next_due_date <= NVL(sc.end_date,sc.next_due_date)
)
)
AND cp_run_date >= sc.next_due_date
AND sc.bill_to_customer_id = ca.cust_account_id
AND ca.status = 'A'
ORDER BY sc.standing_charge_id;
| Select Cursor for Line Details based on the Selected Standing Charge
| cursor above
*---------------------------------------------------------------------------*/
SELECT NVL(ld.price,0) price
, NVL(ld.previous_price,0) previous_price
, NVL(ld.revised_price,0) revised_price
, ld.charge_item_number
, ld.revised_effective_date
, ld.current_effective_date
, ld.previous_effective_date
, ld.line_item_id
, ld.item_id
, ld.quantity
, ld.description desc_2
, ld.vat_tax_id
, ld.revenue_code_combination_id
, ld.receivable_code_combination_id
, ld.period_name ld_period_name
, ld.accounting_rule_id
, decode( ld.accounting_rule_id, null, null,
ld.start_date ) start_date
, decode( ld.accounting_rule_id, null, null,
ld.duration ) duration
, uom.uom_code uom_uom_code
, uom.unit_of_measure unit_of_measure
, vt.tax_rate_code
, vt.percentage_rate
, nvl(vt.allow_adhoc_tax_rate_flag,'N') validate_flag
, ld.rowid ld_rowid
/*5905216*/
, ld.legal_entity_id
FROM igi_rpi_line_details ld
, mtl_units_of_measure uom
, igi_rpi_component_periods rcp
, ZX_RATES_B vt /*Bug No 7606235*/
WHERE ld.standing_charge_id = cp_standing_charge_id
--AND nvl(uom.disable_date,SYSDATE) >= SYSDATE
AND uom.unit_of_measure = rcp.unit_of_measure
AND rcp.period_name = ld.period_name
AND ld.vat_tax_id = vt.tax_rate_id(+)
AND NVL(vt.effective_from, SYSDATE) <= SYSDATE
AND NVL(vt.effective_to, SYSDATE) >= SYSDATE ORDER BY ld.line_item_id ;
SELECT igiaso.rpi_header_context_code
, igiaso.rpi_line_context_code
FROM igi_ar_system_options igiaso
;
SELECT meaning
FROM igi_lookups
WHERE lookup_type = 'RPI_LABELS'
AND lookup_code = label_code
;
SELECT 'x'
FROM ar_system_parameters
WHERE salesrep_required_flag = 'Y'
;
select 'x'
from ra_account_defaults rad,
ra_account_default_segments rads
where rads.gl_default_id = rad.gl_default_id
and rads.table_name = 'RA_SALESREPS'
;
SELECT 'x'
FROM ra_batch_sources
WHERE allow_sales_credit_flag = 'Y'
AND batch_source_id = cp_source_id
;
SELECT rule_id
FROM ra_rules
WHERE rule_id IN (-2,-3)
AND type = 'I'
AND UPPER(name) LIKE UPPER(fp_name)||'%'; -- Bug 2413794 vgadde added UPPER both sides
SELECT date1, date2, date3, date4
, pp_cur_next_due_date old_date
FROM igi_rpi_period_schedules
WHERE schedule_id = pp_sched_id
AND period_name = pp_period_name
AND nvl(enabled_flag,'Y') = 'Y';
SELECT DECODE(component
,'DAY' ,TO_NUMBER(factor)*1 + pp_curr_next_due_date
,'WEEK' ,TO_NUMBER(factor)*7 + pp_curr_next_due_date
,'MONTH' ,ADD_MONTHS(pp_curr_next_due_date,TO_NUMBER(factor))
,'YEAR' ,ADD_MONTHS(pp_curr_next_due_date,TO_NUMBER(factor)*12)
) new_next_due_date
, DECODE(component,'DAY' ,TO_NUMBER(factor)* -1 + pp_curr_next_due_date
,'WEEK' ,TO_NUMBER(factor)* -7 + pp_curr_next_due_date
,'MONTH' ,ADD_MONTHS(pp_curr_next_due_date,TO_NUMBER(factor)* -1)
,'YEAR' ,ADD_MONTHS(pp_curr_next_due_date,TO_NUMBER(factor)* -12)
) new_prev_due_date
, nvl( schedule_id,0) schedule_id
, period_name
, use_schedules_flag
, factor
, component
FROM igi_rpi_component_periods
WHERE period_name = pp_period_name
AND nvl(enabled_flag,'Y') = 'Y' ;
pp_raid_table ( pp_curr_rec_idx ).last_updated_by := pp_sc.last_updated_by;
pp_raid_table ( pp_curr_rec_idx ).last_update_date := sysdate;
pp_raid_table ( pp_curr_rev_idx ).last_updated_by := pp_sc.last_updated_by;
pp_raid_table ( pp_curr_rev_idx ).last_update_date := sysdate;
SELECT ras.salesrep_id
, ras.salesrep_number
, ras.sales_credit_type_id
, sct.name sales_credit_type_name
FROM ra_salesreps ras
, so_sales_credit_types sct
WHERE ras.salesrep_id = cp_salesrep_id
;
SELECT 'x'
FROM ra_batch_sources
WHERE batch_source_id = fp_batch_source_id
AND upper(sales_credit_rule) = upper('Amount')
;
SELECT 'x'
FROM ra_batch_sources
WHERE batch_source_id = fp_batch_source_id
AND upper(sales_credit_type_rule) = upper('Value')
;
SELECT 'x'
FROM ra_batch_sources
WHERE batch_source_id = fp_batch_source_id
AND upper(salesperson_rule) = upper('Number')
;
SELECT 'x'
FROM ra_interface_salescredits
WHERE
pp_rail.interface_line_context = interface_line_context
AND pp_rail.interface_line_attribute1 = interface_line_attribute1
AND pp_rail.interface_line_attribute2 = interface_line_attribute2
AND pp_rail.interface_line_attribute3 = interface_line_attribute3
AND pp_rail.interface_line_attribute4 = interface_line_attribute4
;
INSERT INTO ra_interface_salescredits
( interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, interface_line_attribute3
, interface_line_attribute4
, sales_credit_amount_split
, sales_credit_percent_split
, sales_credit_type_name
, sales_credit_type_id
, salesrep_id
, salesrep_number
, created_by
, creation_date
, last_updated_by
, last_update_date
/*5905216*/
, org_id
)
VALUES ( pp_rail.interface_line_context
, pp_rail.interface_line_attribute1
, pp_rail.interface_line_attribute2
, pp_rail.interface_line_attribute3
, pp_rail.interface_line_attribute4
, pp_rail.amount
, 100
, l_sc.sales_credit_type_name
, l_sc.sales_credit_type_id
, l_sc.salesrep_id
, l_sc.salesrep_number
, pp_rail.created_by
, pp_rail.creation_date
, pp_rail.last_updated_by
, pp_rail.last_update_date
, pp_rail.org_id
);
INSERT INTO ra_interface_lines( batch_source_name -- Mandatory
, currency_code -- Mandatory
, line_type -- Mandatory
, set_of_books_id -- Mandatory
, description -- Mandatory
, conversion_type -- MandatorY
, tax_code
, tax_rate
, link_to_line_context
, conversion_rate
, cust_trx_type_id
, interface_line_attribute1
, interface_line_attribute2
, interface_line_attribute3
, interface_line_attribute4
, link_to_line_attribute1
, link_to_line_attribute2
, link_to_line_attribute3
, link_to_line_attribute4
, interface_line_context
, created_by
, creation_date
, last_updated_by
, last_update_date
/*5905216*/
, org_id
, legal_entity_id )
VALUES
( l_rail.batch_source_name -- Mandatory
, l_rail.currency_code -- Mandatory
, l_rail.line_type -- Mandatory
, l_rail.set_of_books_id -- Mandatory
, l_rail.description -- Mandatory
, l_rail.conversion_type -- MandatorY
, l_rail.tax_code
, l_rail.tax_rate
, l_rail.link_to_line_context
, l_rail.conversion_rate
, l_rail.cust_trx_type_id
, l_rail.interface_line_attribute1
, l_rail.interface_line_attribute2
, l_rail.interface_line_attribute3
, l_line_number
, l_rail.interface_line_attribute1
, l_rail.interface_line_attribute2
, l_rail.interface_line_attribute3
, pp_line_number
, l_rail.interface_line_context
, l_rail.created_by
, l_rail.creation_date
, l_rail.last_updated_by
, l_rail.last_update_date
, l_rail.org_id
, l_rail.legal_entity_id
);
SELECT C.PRECISION, C.MINIMUM_ACCOUNTABLE_UNIT
INTO v_precision, v_min_acc_unit
FROM FND_CURRENCIES C
WHERE C.CURRENCY_CODE = l_rail.currency_code;
/** Insert normal LINE for this ITEM **/
INSERT INTO ra_interface_lines_all ( accounting_rule_id
, amount
, batch_source_name -- Mandatory
, comments
, description -- Mandatory
, currency_code -- Mandatory
, conversion_rate
, conversion_type -- Mandatory
, customer_bank_account_id
, PAYMENT_TRXN_EXTENSION_ID /*Bug No 5905216 Payment Upgrade - for R12*/
, cust_trx_type_id
, interface_line_attribute1
, interface_line_attribute2
, interface_line_attribute3
, interface_line_attribute4
, interface_line_context
, tax_code
, tax_rate
, link_to_line_context
, invoicing_rule_id
, line_number
, line_type -- Mandatory
, orig_system_bill_customer_id
, orig_system_bill_address_id
, orig_system_bill_contact_id
, orig_system_ship_customer_id
, orig_system_ship_address_id
, orig_system_ship_contact_id
, primary_salesrep_id
, printing_option
, quantity
, receipt_method_id
, set_of_books_id -- Mandatory
, trx_date
, uom_name
, uom_code
, unit_selling_price
, created_by
, creation_date
, last_updated_by
, last_update_date
, accounting_rule_duration
, rule_start_date
, gl_date
, term_id
/*5905216*/
, org_id
, legal_entity_id
, TAX_RATE_CODE /*Bug No 7606235*/
, TAXABLE_AMOUNT /*Bug No 7606235*/ )
VALUES
( l_rail.accounting_rule_id
, l_rail.amount
, l_rail.batch_source_name -- Mandatory
, l_rail.comments
, l_rail.description -- Mandatory
, l_rail.currency_code -- Mandatory
, l_rail.conversion_rate
, l_rail.conversion_type -- Mandatory
, l_rail.customer_bank_account_id
, l_rail.payment_trxn_extension_id /*Bug No 5905216 Payment Upgrade for R12*/
, l_rail.cust_trx_type_id
, l_rail.interface_line_attribute1
, l_rail.interface_line_attribute2
, l_rail.interface_line_attribute3
, l_rail.interface_line_attribute4
, l_rail.interface_line_context
, l_rail.tax_code
, l_rail.tax_rate
, l_rail.link_to_line_context
, l_rail.invoicing_rule_id
, l_rail.line_number
, l_rail.line_type -- Mandatory
, l_rail.orig_system_bill_customer_id
, l_rail.orig_system_bill_address_id
, l_rail.orig_system_bill_contact_id
, l_rail.orig_system_ship_customer_id
, l_rail.orig_system_ship_address_id
, l_rail.orig_system_ship_contact_id
, l_rail.primary_salesrep_id
, l_rail.printing_option
, l_rail.quantity
, l_rail.receipt_method_id
, l_rail.set_of_books_id -- Mandatory
, l_rail.trx_date
, l_rail.uom_name
, l_rail.uom_code
, l_rail.unit_selling_price
, l_rail.created_by
, l_rail.creation_date
, l_rail.last_updated_by
, l_rail.last_update_date
, l_rail.accounting_rule_duration
, l_rail.rule_start_date
, l_rail.gl_date
, l_rail.term_id
, l_rail.org_id
, l_rail.legal_entity_id
, l_rail.tax_code /*Bug No 7606235*/
, l_rail.amount /*Bug No 7606235*/ );
INSERT INTO ra_interface_distributions( account_class -- Mandatory
, interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, interface_line_attribute3
, interface_line_attribute4
, amount
, percent
, code_combination_id
, created_by
, creation_date
, last_updated_by
, last_update_date
/*5905216*/
, org_id
)
VALUES
( pp_raid_table ( idx ).account_class -- Mandatory
, pp_raid_table ( idx ).interface_line_context
, pp_raid_table ( idx ).interface_line_attribute1
, pp_raid_table ( idx ).interface_line_attribute2
, pp_raid_table ( idx ).interface_line_attribute3
, pp_raid_table ( idx ).interface_line_attribute4
, pp_raid_table ( idx ).amount
, pp_raid_table ( idx ).percent
, pp_raid_table ( idx ).code_combination_id
, pp_raid_table ( idx ).created_by
, pp_raid_table ( idx ).creation_date
, pp_raid_table ( idx ).last_updated_by
, pp_raid_table ( idx ).last_update_date
, pp_raid_table ( idx ).org_id
);
pp_raid_table.delete( idx );
pp_raid_table.delete( idx );
INSERT INTO ra_interface_distributions( account_class -- Mandatory
, interface_line_context
, interface_line_attribute1
, interface_line_attribute2
, interface_line_attribute3
, interface_line_attribute4
, amount
, percent
, code_combination_id
, created_by
, creation_date
, last_updated_by
, last_update_date
/*5905216*/
, org_id
)
VALUES
( pp_raid_table ( rev_idx ).account_class -- Mandatory
, pp_raid_table ( rev_idx ).interface_line_context
, pp_raid_table ( rev_idx ).interface_line_attribute1
, pp_raid_table ( rev_idx ).interface_line_attribute2
, pp_raid_table ( rev_idx ).interface_line_attribute3
, pp_raid_table ( rev_idx ).interface_line_attribute4
, pp_raid_table ( rev_idx ).amount
, pp_raid_table ( rev_idx ).percent
, pp_raid_table ( rev_idx ).code_combination_id
, pp_raid_table ( rev_idx ).created_by
, pp_raid_table ( rev_idx ).creation_date
, pp_raid_table ( rev_idx ).last_updated_by
, pp_raid_table ( rev_idx ).last_update_date
, pp_raid_table ( rev_idx ).org_id
);
select 'x'
from ra_rules
where rule_id = fp_rule_id
and type = 'ACC_DUR'
;
SELECT decode(pp_sc.suppress_inv_print,'Y','PRI','NOT')
INTO l_rail.printing_option
FROM sys.dual;
l_rail.last_updated_by := pp_sc.created_by;
l_rail.last_update_date := pp_sc.sysdate;
select ( to_date(to_char(pp_end_date,DEF_DATE_FORMAT)||END_DATE_TIME,RPI_DATE_FORMAT)
- to_date(to_char(pp_start_date,DEF_DATE_FORMAT)||BEGIN_DATE_TIME,RPI_DATE_FORMAT)
)
/ ( to_date(to_char(pp_end_date,DEF_DATE_FORMAT)||END_DATE_TIME,RPI_DATE_FORMAT)
- to_date(to_char(pp_actual_start_date,DEF_DATE_FORMAT)||BEGIN_DATE_TIME,RPI_DATE_FORMAT)
)
into l_factor
from sys.dual
;
SELECT igi_rpi_generate_s.nextval
INTO l_run_sequence
FROM sys.dual ;
l_date_range_table.delete;
Update igi_rpi_standing_charges
set generate_sequence = l_run_sequence
, date_synchronized_flag = 'N'
where standing_charge_id = std_rec.standing_charge_id
and set_of_books_id = std_rec.set_of_books_id;