[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_accounted_amount (p_schedule_id IN NUMBER,
p_functional_curr IN VARCHAR2,
p_conversion_type IN VARCHAR2,
p_item_currency OUT NOCOPY VARCHAR2)
IS
l_accounted_date DATE;
SELECT payment_item_id
,payment_term_id
,accounted_amount
,actual_amount
,currency_code
,due_date
,rate
FROM pn_payment_items_all
WHERE payment_schedule_id = p_schedule_id
AND payment_item_type_lookup_code = 'CASH';
SELECT pi.payment_item_id
,pi.accounted_amount
,pi.actual_amount
,pi.currency_code
FROM pn_payment_items_all pi
,pn_payment_items_all pi1
WHERE pi.payment_schedule_id = p_schedule_id
AND pi.payment_term_id = p_term_id
AND pi.payment_item_type_lookup_code = 'NORMALIZED'
AND pi1.payment_schedule_id = pi.payment_schedule_id
AND pi1.payment_term_id = pi.payment_term_id
AND pi1.payment_item_type_lookup_code = 'CASH'
AND pi1.payment_item_id = p_item_id ;
PNP_DEBUG_PKG.debug('pn_mass_appr_pkg.update_accounted_amount (+)');
UPDATE pn_payment_items
SET accounted_amount = l_accounted_amt,
accounted_date = l_accounted_date,
last_update_date = SYSDATE, --Bug#5389144
last_updated_by = NVL(fnd_profile.value('USER_ID'),0), --Bug#5389144
last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0) --Bug#5389144
WHERE payment_item_id = payment_item_rec.payment_item_id;
UPDATE pn_payment_items
SET accounted_amount = l_accounted_amt_norm,
accounted_date = l_accounted_date,
RATE = payment_item_rec.RATE,
CURRENCY_CODE = payment_item_rec.currency_code,
last_update_date = SYSDATE, --Bug#5389144
last_updated_by = NVL(fnd_profile.value('USER_ID'),0), --Bug#5389144
last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0) --Bug#5389144
WHERE payment_item_id = l_norm_payment_item_id;
PNP_DEBUG_PKG.debug('pn_mass_appr_pkg.update_accounted_amount (-)');
END update_accounted_amount;
SELECT DISTINCT
pps.payment_schedule_id s_payment_schedule_id,
pps.schedule_date s_schedule_date,
pps.period_name s_period_name,
pl.lease_class_code s_lease_class_code,
pl.lease_id s_lease_id,
pl.lease_num s_lease_number,
pl.name s_lease_name
FROM pn_payment_schedules_all pps,
pn_leases pl,
pn_lease_details_all pld,
pn_tenancies_all pt,
pn_locations_all ploc
WHERE pps.schedule_date BETWEEN NVL(p_schedule_from_date, pps.schedule_date)
AND NVL(p_schedule_to_date, pps.schedule_date)
AND pps.payment_status_lookup_code = 'DRAFT'
AND pl.lease_id = pps.lease_id
AND pl.lease_class_code = NVL(p_lease_class_code,pl.lease_class_code)
AND pl.lease_num >= NVL(p_lease_from_number, pl.lease_num)
AND pl.lease_num <= NVL(p_lease_to_number, pl.lease_num)
AND pld.lease_id = pps.lease_id
AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
AND pt.lease_id = pps.lease_id
AND ploc.location_id = pt.location_id
AND ploc.location_code >= NVL(p_location_from_code, ploc.location_code)
AND ploc.location_code <= NVL(p_location_to_code, ploc.location_code)
AND EXISTS
(SELECT NULL
FROM pn_payment_items_all item
WHERE item.payment_schedule_id = pps.payment_schedule_id
AND item.due_date BETWEEN NVL(p_trx_from_date, item.due_date)
AND NVL(p_trx_to_date, item.due_date)
)
ORDER BY pl.lease_id, pps.schedule_date;
SELECT DISTINCT
pps.payment_schedule_id s_payment_schedule_id,
pps.schedule_date s_schedule_date,
pps.period_name s_period_name,
pl.lease_class_code s_lease_class_code,
pl.lease_id s_lease_id,
pl.lease_num s_lease_number,
pl.name s_lease_name
FROM pn_payment_schedules_all pps,
pn_leases pl,
pn_lease_details_all pld
WHERE pps.schedule_date BETWEEN NVL(p_schedule_from_date, pps.schedule_date)
AND NVL(p_schedule_to_date, pps.schedule_date)
AND pps.payment_status_lookup_code = 'DRAFT'
AND pl.lease_id = pps.lease_id
AND pl.lease_class_code = NVL(p_lease_class_code,pl.lease_class_code)
AND pl.lease_num >= NVL(p_lease_from_number, pl.lease_num)
AND pl.lease_num <= NVL(p_lease_to_number, pl.lease_num)
AND pld.lease_id = pps.lease_id
AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
AND EXISTS
(SELECT NULL
FROM pn_payment_items_all item
WHERE item.payment_schedule_id = pps.payment_schedule_id
AND item.due_date BETWEEN NVL(p_trx_from_date, item.due_date)
AND NVL(p_trx_to_date, item.due_date)
)
ORDER BY pl.lease_id, pps.schedule_date;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (SELECT NULL
FROM pn_payment_terms_all ppt,
pn_payment_items_all ppi
WHERE ppi.payment_term_id = ppt.payment_term_id
AND ppi.payment_schedule_id = p_schedule_id
AND (ppt.customer_id IS NULL
OR ppt.customer_site_use_id IS NULL
OR (ppt.cust_ship_site_id IS NULL AND
p_ship_address_rule <> 'None'
)
OR ppt.ap_ar_term_id IS NULL
OR ppt.cust_trx_type_id IS NULL
)
);
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT NULL
FROM pn_payment_terms_all ppt,
pn_payment_items_all ppi
WHERE ppi.payment_term_id = ppt.payment_term_id
AND ppi.payment_schedule_id = p_schedule_id
AND (ppt.vendor_id IS NULL OR
ppt.vendor_site_id Is NULL
)
);
SELECT 'Y'
FROM dual
WHERE EXISTS(SELECT NULL
FROM pn_payment_items_all ppi
WHERE ppi.payment_schedule_id = p_schedule_id
AND ppi.actual_amount IS NULL);
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id
= TO_NUMBER(pn_mo_cache_utils.get_profile_value('PN_SET_OF_BOOKS_ID'
,pn_mo_cache_utils.get_current_org_id));
SELECT NVL(ship_address_rule,'None') AS ship_address_rule
FROM RA_BATCH_SOURCES
WHERE batch_source_id = 24;
/* variables to store the values selected in the main cursor */
l_payment_item_id PN_PAYMENT_ITEMS_ALL.payment_item_id%TYPE;
bad_currency_table.DELETE;
SAVEPOINT beforeupdate;
update_accounted_amount
( p_schedule_id => l_payment_schedule_id
,p_functional_curr => l_func_curr
,p_conversion_type => l_curr_conv_type
,p_item_currency => l_item_curr);
ROLLBACK TO beforeupdate;
ROLLBACK TO beforeupdate;
/* Update the export flags in the items */
UPDATE PN_PAYMENT_ITEMS
SET export_to_ap_flag = DECODE(l_lease_class_code,
'DIRECT','Y',
NULL),
export_to_ar_flag = DECODE(l_lease_class_code,
'THIRD_PARTY','Y',
'SUB_LEASE' ,'Y',
NULL),
last_update_date = SYSDATE, --Bug#5389144
last_updated_by = NVL(fnd_profile.value('USER_ID'),0), --Bug#5389144
last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0) --Bug#5389144
WHERE payment_item_type_lookup_code = 'CASH'
AND payment_schedule_id = l_payment_schedule_id;
UPDATE PN_PAYMENT_SCHEDULES
SET payment_status_lookup_code = 'APPROVED',
approved_by_user_id = fnd_profile.value('USER_ID'),
approval_date = SYSDATE,
period_name = l_period_name,
last_update_date = SYSDATE, --Bug#5389144
last_updated_by = NVL(fnd_profile.value('USER_ID'),0), --Bug#5389144
last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0) --Bug#5389144
WHERE payment_schedule_id = l_payment_schedule_id;
SELECT DISTINCT
pps.payment_schedule_id s_payment_schedule_id,
pps.schedule_date s_schedule_date,
pl.lease_id s_lease_id
FROM pn_payment_schedules_all pps,
pn_leases pl,
pn_lease_details_all pld,
pn_tenancies_all pt,
pn_locations_all ploc
WHERE pps.schedule_date BETWEEN NVL(p_schedule_from_date, pps.schedule_date)
AND NVL(p_schedule_to_date, pps.schedule_date)
AND pps.payment_status_lookup_code = 'APPROVED'
AND pl.lease_id = pps.lease_id
AND pl.lease_class_code = NVL(p_lease_class_code,pl.lease_class_code)
AND pl.lease_num >= NVL(p_lease_from_number, pl.lease_num)
AND pl.lease_num <= NVL(p_lease_to_number, pl.lease_num)
AND pld.lease_id = pps.lease_id
AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
AND pt.lease_id = pps.lease_id
AND ploc.location_id = pt.location_id
AND ploc.location_code >= NVL(p_location_from_code, ploc.location_code)
AND ploc.location_code <= NVL(p_location_to_code, ploc.location_code)
AND EXISTS(SELECT NULL
FROM pn_payment_items_all item
WHERE item.payment_schedule_id = pps.payment_schedule_id
AND item.due_date BETWEEN NVL(p_trx_from_date,item.due_date)
AND NVL(p_trx_to_date,item.due_date))
AND NOT EXISTS (SELECT NULL
FROM pn_payment_items_all pi
WHERE pi.payment_schedule_id = pps.payment_schedule_id
AND (pi.TRANSFERRED_TO_AP_FLAG = 'Y' OR
pi.TRANSFERRED_TO_AR_FLAG = 'Y')
)
ORDER BY pl.lease_id, pps.schedule_date;
SELECT DISTINCT
pps.payment_schedule_id s_payment_schedule_id,
pps.schedule_date s_schedule_date,
pl.lease_id s_lease_id
FROM pn_payment_schedules_all pps,
pn_leases pl,
pn_lease_details_all pld
WHERE pps.schedule_date BETWEEN NVL(p_schedule_from_date, pps.schedule_date)
AND NVL(p_schedule_to_date, pps.schedule_date)
AND pps.payment_status_lookup_code = 'APPROVED'
AND pl.lease_id = pps.lease_id
AND pl.lease_class_code = NVL(p_lease_class_code,pl.lease_class_code)
AND pl.lease_num >= NVL(p_lease_from_number, pl.lease_num)
AND pl.lease_num <= NVL(p_lease_to_number, pl.lease_num)
AND pld.lease_id = pps.lease_id
AND pld.responsible_user = NVL(p_responsible_user, pld.responsible_user)
AND EXISTS (SELECT NULL
FROM pn_payment_items_all item
WHERE item.payment_schedule_id = pps.payment_schedule_id
AND item.due_date BETWEEN NVL(p_trx_from_date,item.due_date)
AND NVL(p_trx_to_date,item.due_date))
AND NOT EXISTS (SELECT NULL
FROM pn_payment_items_all pi
WHERE pi.payment_schedule_id = pps.payment_schedule_id
AND (pi.TRANSFERRED_TO_AP_FLAG = 'Y' OR
pi.TRANSFERRED_TO_AR_FLAG = 'Y')
)
ORDER BY pl.lease_id, pps.schedule_date;
/* Update the export flags in the items to NULL */
UPDATE PN_PAYMENT_ITEMS
SET export_to_ap_flag = NULL,
export_to_ar_flag = NULL
WHERE payment_item_type_lookup_code = 'CASH'
AND payment_schedule_id = l_payment_schedule_id;
UPDATE PN_PAYMENT_SCHEDULES
SET payment_status_lookup_code = 'DRAFT',
approved_by_user_id = NULL,
approval_date = NULL,
period_name = NULL
WHERE payment_schedule_id = l_payment_schedule_id;