[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_PNT_ITEMS_AMT
(p_pnt_term_id IN pn_payment_items.payment_term_id%TYPE,
p_old_est_amt IN pn_payment_items.estimated_amount%TYPE,
p_new_est_amt IN pn_payment_items.estimated_amount%TYPE,
p_lease_id IN pn_payment_terms.lease_id%TYPE,
p_trm_str_dt IN DATE,
p_trm_end_dt IN DATE,
p_freq_code IN pn_payment_terms.frequency_code%TYPE);
PROCEDURE UPDATE_PNT_ITEMS_RATE(p_pnt_term_id pn_payment_items.payment_item_id%TYPE,
p_new_rate pn_payment_items.rate%TYPE);
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_PAYMENT_TERM_ID IN OUT NOCOPY NUMBER,
X_PAYMENT_PURPOSE_CODE IN VARCHAR2,
X_PAYMENT_TERM_TYPE_CODE IN VARCHAR2,
X_FREQUENCY_CODE IN VARCHAR2,
X_LEASE_ID IN NUMBER,
X_LEASE_CHANGE_ID IN NUMBER,
X_START_DATE IN DATE,
X_END_DATE IN DATE,
X_VENDOR_ID IN NUMBER,
X_VENDOR_SITE_ID IN NUMBER,
X_CUSTOMER_ID IN NUMBER,
X_CUSTOMER_SITE_USE_ID IN NUMBER,
X_TARGET_DATE IN DATE,
X_ACTUAL_AMOUNT IN NUMBER,
X_ESTIMATED_AMOUNT IN NUMBER,
X_SET_OF_BOOKS_ID IN NUMBER,
X_CURRENCY_CODE IN VARCHAR2,
X_RATE IN NUMBER,
X_NORMALIZE IN VARCHAR2,
X_LOCATION_ID IN NUMBER,
X_SCHEDULE_DAY IN NUMBER,
X_CUST_SHIP_SITE_ID IN NUMBER,
X_AP_AR_TERM_ID IN NUMBER,
X_CUST_TRX_TYPE_ID IN NUMBER,
X_PROJECT_ID IN NUMBER,
X_TASK_ID IN NUMBER,
X_ORGANIZATION_ID IN NUMBER,
X_EXPENDITURE_TYPE IN VARCHAR2,
X_EXPENDITURE_ITEM_DATE IN DATE,
X_TAX_GROUP_ID IN NUMBER,
X_TAX_CODE_ID IN NUMBER,
X_TAX_CLASSIFICATION_CODE IN VARCHAR2,
X_TAX_INCLUDED IN VARCHAR2,
X_DISTRIBUTION_SET_ID IN NUMBER,
X_INV_RULE_ID IN NUMBER,
X_ACCOUNT_RULE_ID IN NUMBER,
X_SALESREP_ID IN NUMBER,
X_APPROVED_BY IN NUMBER,
X_STATUS IN VARCHAR2,
X_INDEX_PERIOD_ID IN NUMBER,
X_INDEX_TERM_INDICATOR IN VARCHAR2,
X_PO_HEADER_ID IN NUMBER,
X_CUST_PO_NUMBER IN VARCHAR2,
X_RECEIPT_METHOD_ID IN NUMBER,
X_VAR_RENT_INV_ID IN NUMBER ,
X_VAR_RENT_TYPE IN VARCHAR2,
X_PERIOD_BILLREC_ID IN NUMBER,
X_REC_AGR_LINE_ID IN NUMBER,
X_AMOUNT_TYPE IN VARCHAR2,
X_CHANGED_FLAG IN VARCHAR2,
X_TERM_TEMPLATE_ID IN NUMBER,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_PROJECT_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_PROJECT_ATTRIBUTE1 IN VARCHAR2,
X_PROJECT_ATTRIBUTE2 IN VARCHAR2,
X_PROJECT_ATTRIBUTE3 IN VARCHAR2,
X_PROJECT_ATTRIBUTE4 IN VARCHAR2,
X_PROJECT_ATTRIBUTE5 IN VARCHAR2,
X_PROJECT_ATTRIBUTE6 IN VARCHAR2,
X_PROJECT_ATTRIBUTE7 IN VARCHAR2,
X_PROJECT_ATTRIBUTE8 IN VARCHAR2,
X_PROJECT_ATTRIBUTE9 IN VARCHAR2,
X_PROJECT_ATTRIBUTE10 IN VARCHAR2,
X_PROJECT_ATTRIBUTE11 IN VARCHAR2,
X_PROJECT_ATTRIBUTE12 IN VARCHAR2,
X_PROJECT_ATTRIBUTE13 IN VARCHAR2,
X_PROJECT_ATTRIBUTE14 IN VARCHAR2,
X_PROJECT_ATTRIBUTE15 IN VARCHAR2,
X_CREATION_DATE IN DATE,
X_CREATED_BY IN NUMBER,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
X_CALLING_FORM IN VARCHAR2 ,
x_org_id IN NUMBER,
x_lease_status IN VARCHAR2,
x_recoverable_flag IN VARCHAR2,
x_area_type_code IN VARCHAR2,
x_area IN NUMBER,
x_grouping_rule_id IN NUMBER,
x_term_altered_flag IN VARCHAR2,
x_source_code IN VARCHAR2,
x_term_comments IN VARCHAR2,
x_norm_start_date IN DATE,
x_parent_term_id IN NUMBER ,
x_index_norm_flag IN VARCHAR2,
x_include_in_var_rent IN VARCHAR2 DEFAULT NULL,
x_recur_bb_calc_date IN DATE,
x_opex_agr_id IN NUMBER,
x_opex_recon_id IN NUMBER,
x_opex_type IN VARCHAR2
)
IS
CURSOR c IS
SELECT ROWID
FROM pn_payment_terms_all
WHERE payment_term_id = x_payment_term_id;
SELECT org_id FROM pn_leases_all WHERE lease_id = X_LEASE_ID;
l_update_nbp_flag VARCHAR2(1);
SELECT index_lease_id
,lease_id
FROM pn_index_leases_all
WHERE lease_id = x_lease_id
AND GROSS_FLAG = 'Y';
pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.INSERT_ROW (+)');
l_update_nbp_flag := 'Y';
l_update_nbp_flag := NULL;
INSERT INTO pn_payment_terms_all
(
PAYMENT_TERM_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PAYMENT_PURPOSE_CODE,
PAYMENT_TERM_TYPE_CODE,
FREQUENCY_CODE,
LEASE_ID,
LEASE_CHANGE_ID,
START_DATE,
END_DATE,
VENDOR_ID,
VENDOR_SITE_ID,
CUSTOMER_ID,
CUSTOMER_SITE_USE_ID,
TARGET_DATE,
ACTUAL_AMOUNT,
ESTIMATED_AMOUNT,
SET_OF_BOOKS_ID,
CURRENCY_CODE,
RATE,
NORMALIZE,
LOCATION_ID,
SCHEDULE_DAY,
CUST_SHIP_SITE_ID,
AP_AR_TERM_ID,
CUST_TRX_TYPE_ID,
PROJECT_ID,
TASK_ID,
ORGANIZATION_ID,
EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE,
TAX_GROUP_ID,
TAX_CODE_ID,
TAX_CLASSIFICATION_CODE,
TAX_INCLUDED,
DISTRIBUTION_SET_ID,
INV_RULE_ID,
ACCOUNT_RULE_ID,
SALESREP_ID,
APPROVED_BY,
STATUS,
INDEX_PERIOD_ID,
INDEX_TERM_INDICATOR,
PO_HEADER_ID,
CUST_PO_NUMBER,
RECEIPT_METHOD_ID,
VAR_RENT_INV_ID ,
VAR_RENT_TYPE,
PERIOD_BILLREC_ID,
REC_AGR_LINE_ID,
AMOUNT_TYPE,
CHANGED_FLAG,
TERM_TEMPLATE_ID,
LEGAL_ENTITY_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
PROJECT_ATTRIBUTE_CATEGORY,
PROJECT_ATTRIBUTE1,
PROJECT_ATTRIBUTE2,
PROJECT_ATTRIBUTE3,
PROJECT_ATTRIBUTE4,
PROJECT_ATTRIBUTE5,
PROJECT_ATTRIBUTE6,
PROJECT_ATTRIBUTE7,
PROJECT_ATTRIBUTE8,
PROJECT_ATTRIBUTE9,
PROJECT_ATTRIBUTE10,
PROJECT_ATTRIBUTE11,
PROJECT_ATTRIBUTE12,
PROJECT_ATTRIBUTE13,
PROJECT_ATTRIBUTE14,
PROJECT_ATTRIBUTE15,
ORG_ID,
LEASE_STATUS,
RECOVERABLE_FLAG,
AREA_TYPE_CODE,
AREA,
GROUPING_RULE_ID,
TERM_ALTERED_FLAG,
SOURCE_CODE,
TERM_COMMENTS,
NORM_START_DATE,
PARENT_TERM_ID,
INDEX_NORM_FLAG,
INCLUDE_IN_VAR_RENT,
UPDATE_NBP_FLAG,
RECUR_BB_CALC_DATE,
OPEX_AGR_ID,
OPEX_RECON_ID,
OPEX_TYPE
)
VALUES
(
NVL(X_PAYMENT_TERM_ID,pn_payment_terms_s.NEXTVAL),
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_CREATION_DATE,
X_CREATED_BY,
X_LAST_UPDATE_LOGIN,
X_PAYMENT_PURPOSE_CODE,
X_PAYMENT_TERM_TYPE_CODE,
X_FREQUENCY_CODE,
X_LEASE_ID,
X_LEASE_CHANGE_ID,
X_START_DATE,
X_END_DATE,
X_VENDOR_ID,
X_VENDOR_SITE_ID,
X_CUSTOMER_ID,
X_CUSTOMER_SITE_USE_ID,
X_TARGET_DATE,
ROUND(X_ACTUAL_AMOUNT, l_precision),
ROUND(X_ESTIMATED_AMOUNT, l_precision),
X_SET_OF_BOOKS_ID,
X_CURRENCY_CODE,
X_RATE,
X_NORMALIZE,
X_LOCATION_ID,
X_SCHEDULE_DAY,
X_CUST_SHIP_SITE_ID,
X_AP_AR_TERM_ID,
X_CUST_TRX_TYPE_ID,
X_PROJECT_ID,
X_TASK_ID,
X_ORGANIZATION_ID,
X_EXPENDITURE_TYPE,
X_EXPENDITURE_ITEM_DATE,
X_TAX_GROUP_ID,
X_TAX_CODE_ID,
X_TAX_CLASSIFICATION_CODE,
X_TAX_INCLUDED,
X_DISTRIBUTION_SET_ID,
X_INV_RULE_ID,
X_ACCOUNT_RULE_ID,
X_SALESREP_ID,
X_APPROVED_BY,
X_STATUS,
X_INDEX_PERIOD_ID,
X_INDEX_TERM_INDICATOR,
X_PO_HEADER_ID,
X_CUST_PO_NUMBER,
X_RECEIPT_METHOD_ID,
X_VAR_RENT_INV_ID,
X_VAR_RENT_TYPE,
X_PERIOD_BILLREC_ID,
X_REC_AGR_LINE_ID,
X_AMOUNT_TYPE,
X_CHANGED_FLAG,
X_TERM_TEMPLATE_ID,
l_legal_entity_id,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_PROJECT_ATTRIBUTE_CATEGORY,
X_PROJECT_ATTRIBUTE1,
X_PROJECT_ATTRIBUTE2,
X_PROJECT_ATTRIBUTE3,
X_PROJECT_ATTRIBUTE4,
X_PROJECT_ATTRIBUTE5,
X_PROJECT_ATTRIBUTE6,
X_PROJECT_ATTRIBUTE7,
X_PROJECT_ATTRIBUTE8,
X_PROJECT_ATTRIBUTE9,
X_PROJECT_ATTRIBUTE10,
X_PROJECT_ATTRIBUTE11,
X_PROJECT_ATTRIBUTE12,
X_PROJECT_ATTRIBUTE13,
X_PROJECT_ATTRIBUTE14,
X_PROJECT_ATTRIBUTE15,
L_ORG_ID,
X_LEASE_STATUS,
X_RECOVERABLE_FLAG,
X_AREA_TYPE_CODE,
X_AREA,
X_GROUPING_RULE_ID,
X_TERM_ALTERED_FLAG,
X_SOURCE_CODE,
X_TERM_COMMENTS,
X_NORM_START_DATE,
X_PARENT_TERM_ID,
X_INDEX_NORM_FLAG,
X_INCLUDE_IN_VAR_RENT,
l_update_nbp_flag,
X_RECUR_BB_CALC_DATE,
X_OPEX_AGR_ID,
X_OPEX_RECON_ID,
X_OPEX_TYPE
)
RETURNING payment_term_id INTO X_PAYMENT_TERM_ID;
pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.INSERT_ROW (-)');
END insert_row;
SELECT *
FROM PN_PAYMENT_TERMS_ALL
WHERE PAYMENT_TERM_ID = X_PAYMENT_TERM_ID
FOR UPDATE OF payment_term_id NOWAIT;
PROCEDURE UPDATE_ROW (
X_PAYMENT_TERM_ID IN NUMBER,
X_PAYMENT_PURPOSE_CODE IN VARCHAR2,
X_PAYMENT_TERM_TYPE_CODE IN VARCHAR2,
X_FREQUENCY_CODE IN VARCHAR2,
X_LEASE_ID IN NUMBER,
X_LEASE_CHANGE_ID IN NUMBER,
X_START_DATE IN DATE,
X_END_DATE IN DATE,
X_VENDOR_ID IN NUMBER,
X_VENDOR_SITE_ID IN NUMBER,
X_CUSTOMER_ID IN NUMBER,
X_CUSTOMER_SITE_USE_ID IN NUMBER,
X_TARGET_DATE IN DATE,
X_ACTUAL_AMOUNT IN NUMBER,
X_ESTIMATED_AMOUNT IN NUMBER,
X_SET_OF_BOOKS_ID IN NUMBER,
X_CURRENCY_CODE IN VARCHAR2,
X_RATE IN NUMBER,
X_NORMALIZE IN VARCHAR2,
X_LOCATION_ID IN NUMBER,
X_SCHEDULE_DAY IN NUMBER,
X_CUST_SHIP_SITE_ID IN NUMBER,
X_AP_AR_TERM_ID IN NUMBER,
X_CUST_TRX_TYPE_ID IN NUMBER,
X_PROJECT_ID IN NUMBER,
X_TASK_ID IN NUMBER,
X_ORGANIZATION_ID IN NUMBER,
X_EXPENDITURE_TYPE IN VARCHAR2,
X_EXPENDITURE_ITEM_DATE IN DATE,
X_TAX_GROUP_ID IN NUMBER,
X_TAX_CODE_ID IN NUMBER,
X_TAX_CLASSIFICATION_CODE IN VARCHAR2,
X_TAX_INCLUDED IN VARCHAR2,
X_DISTRIBUTION_SET_ID IN NUMBER,
X_INV_RULE_ID IN NUMBER,
X_ACCOUNT_RULE_ID IN NUMBER,
X_SALESREP_ID IN NUMBER,
X_APPROVED_BY IN NUMBER,
X_STATUS IN VARCHAR2,
X_INDEX_PERIOD_ID IN NUMBER,
X_INDEX_TERM_INDICATOR IN VARCHAR2,
X_PO_HEADER_ID IN NUMBER,
X_CUST_PO_NUMBER IN VARCHAR2,
X_RECEIPT_METHOD_ID IN NUMBER,
X_VAR_RENT_INV_ID IN NUMBER,
X_VAR_RENT_TYPE IN VARCHAR2,
X_CHANGED_FLAG IN VARCHAR2,
X_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_ATTRIBUTE1 IN VARCHAR2,
X_ATTRIBUTE2 IN VARCHAR2,
X_ATTRIBUTE3 IN VARCHAR2,
X_ATTRIBUTE4 IN VARCHAR2,
X_ATTRIBUTE5 IN VARCHAR2,
X_ATTRIBUTE6 IN VARCHAR2,
X_ATTRIBUTE7 IN VARCHAR2,
X_ATTRIBUTE8 IN VARCHAR2,
X_ATTRIBUTE9 IN VARCHAR2,
X_ATTRIBUTE10 IN VARCHAR2,
X_ATTRIBUTE11 IN VARCHAR2,
X_ATTRIBUTE12 IN VARCHAR2,
X_ATTRIBUTE13 IN VARCHAR2,
X_ATTRIBUTE14 IN VARCHAR2,
X_ATTRIBUTE15 IN VARCHAR2,
X_PROJECT_ATTRIBUTE_CATEGORY IN VARCHAR2,
X_PROJECT_ATTRIBUTE1 IN VARCHAR2,
X_PROJECT_ATTRIBUTE2 IN VARCHAR2,
X_PROJECT_ATTRIBUTE3 IN VARCHAR2,
X_PROJECT_ATTRIBUTE4 IN VARCHAR2,
X_PROJECT_ATTRIBUTE5 IN VARCHAR2,
X_PROJECT_ATTRIBUTE6 IN VARCHAR2,
X_PROJECT_ATTRIBUTE7 IN VARCHAR2,
X_PROJECT_ATTRIBUTE8 IN VARCHAR2,
X_PROJECT_ATTRIBUTE9 IN VARCHAR2,
X_PROJECT_ATTRIBUTE10 IN VARCHAR2,
X_PROJECT_ATTRIBUTE11 IN VARCHAR2,
X_PROJECT_ATTRIBUTE12 IN VARCHAR2,
X_PROJECT_ATTRIBUTE13 IN VARCHAR2,
X_PROJECT_ATTRIBUTE14 IN VARCHAR2,
X_PROJECT_ATTRIBUTE15 IN VARCHAR2,
X_LAST_UPDATE_DATE IN DATE,
X_LAST_UPDATED_BY IN NUMBER,
X_LAST_UPDATE_LOGIN IN NUMBER,
x_recoverable_flag IN VARCHAR2,
x_area_type_code IN VARCHAR2,
x_area IN NUMBER,
x_grouping_rule_id IN NUMBER,
x_term_altered_flag IN VARCHAR2,
x_source_code IN VARCHAR2,
x_term_comments IN VARCHAR2,
X_TERM_TEMPLATE_ID IN NUMBER,
x_include_in_var_rent IN VARCHAR2 DEFAULT NULL,
X_RECUR_BB_CALC_DATE IN DATE,
x_opex_agr_id IN NUMBER,
x_opex_recon_id IN NUMBER,
x_opex_type IN VARCHAR2
)
IS
CURSOR has_distributions IS
SELECT 'Y'
FROM dual
WHERE EXISTS (SELECT 'Y' FROM pn_distributions_all
WHERE payment_term_id = x_payment_term_id);
l_update_nbp_flag VARCHAR2(1);
pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.UPDATE_ROW (+)');
l_update_nbp_flag := 'Y';
l_update_nbp_flag := NULL;
Please refer to PROCEDURE UPDATE_PNT_ITEMS_AMT
*/
IF nvl(X_NORMALIZE,'N') = 'N' THEN
l_context := 'SELECTing lease status';
SELECT status
INTO l_status
FROM pn_leases_all
WHERE lease_id = X_LEASE_ID;
l_context := 'SELECTing estimated amt';
SELECT estimated_amount
INTO l_old_est
FROM pn_payment_terms_all
WHERE payment_term_id = X_PAYMENT_TERM_ID;
UPDATE_PNT_ITEMS_AMT(X_PAYMENT_TERM_ID,
l_old_est,
X_ESTIMATED_AMOUNT,
X_LEASE_ID,
X_START_DATE,
X_END_DATE,
PN_SCHEDULES_ITEMS.get_frequency(X_FREQUENCY_CODE));
l_context := 'Selecting old rate and org_id';
SELECT rate, org_id,legal_entity_id
INTO l_rate, l_org_id,l_legal_entity_id
FROM pn_payment_terms_all
WHERE payment_term_id = X_PAYMENT_TERM_ID;
UPDATE_PNT_ITEMS_RATE(X_PAYMENT_TERM_ID, X_RATE);
PNT_PAYMENT_TERMS_PKG.UPDATE_VENDOR_AND_CUST (
X_PAYMENT_TERM_ID
,X_VENDOR_ID
,X_VENDOR_SITE_ID
,X_LAST_UPDATE_DATE
,X_LAST_UPDATED_BY
,X_LAST_UPDATE_LOGIN
,X_CUSTOMER_ID
,X_CUSTOMER_SITE_USE_ID
,X_CUST_SHIP_SITE_ID
);
UPDATE PN_PAYMENT_TERMS_ALL
SET PAYMENT_TERM_ID = X_PAYMENT_TERM_ID,
PAYMENT_PURPOSE_CODE = X_PAYMENT_PURPOSE_CODE,
PAYMENT_TERM_TYPE_CODE = X_PAYMENT_TERM_TYPE_CODE,
FREQUENCY_CODE = X_FREQUENCY_CODE,
LEASE_ID = X_LEASE_ID,
LEASE_CHANGE_ID = X_LEASE_CHANGE_ID,
START_DATE = X_START_DATE,
END_DATE = X_END_DATE,
VENDOR_ID = X_VENDOR_ID,
VENDOR_SITE_ID = X_VENDOR_SITE_ID,
CUSTOMER_ID = X_CUSTOMER_ID,
CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID,
TARGET_DATE = X_TARGET_DATE,
ACTUAL_AMOUNT = ROUND(X_ACTUAL_AMOUNT, l_precision),
ESTIMATED_AMOUNT = ROUND(X_ESTIMATED_AMOUNT, l_precision),
SET_OF_BOOKS_ID = X_SET_OF_BOOKS_ID,
CURRENCY_CODE = X_CURRENCY_CODE,
RATE = X_RATE,
NORMALIZE = X_NORMALIZE,
LOCATION_ID = X_LOCATION_ID,
SCHEDULE_DAY = X_SCHEDULE_DAY,
CUST_SHIP_SITE_ID = X_CUST_SHIP_SITE_ID,
AP_AR_TERM_ID = X_AP_AR_TERM_ID,
CUST_TRX_TYPE_ID = X_CUST_TRX_TYPE_ID,
PROJECT_ID = X_PROJECT_ID,
TASK_ID = X_TASK_ID,
ORGANIZATION_ID = X_ORGANIZATION_ID,
EXPENDITURE_TYPE = X_EXPENDITURE_TYPE,
EXPENDITURE_ITEM_DATE = X_EXPENDITURE_ITEM_DATE,
TAX_GROUP_ID = X_TAX_GROUP_ID,
TAX_CODE_ID = X_TAX_CODE_ID,
TAX_CLASSIFICATION_CODE = X_TAX_CLASSIFICATION_CODE,
TAX_INCLUDED = X_TAX_INCLUDED,
DISTRIBUTION_SET_ID = X_DISTRIBUTION_SET_ID,
INV_RULE_ID = X_INV_RULE_ID,
ACCOUNT_RULE_ID = X_ACCOUNT_RULE_ID,
SALESREP_ID = X_SALESREP_ID,
APPROVED_BY = X_APPROVED_BY,
STATUS = X_STATUS,
INDEX_PERIOD_ID = X_INDEX_PERIOD_ID,
INDEX_TERM_INDICATOR = X_INDEX_TERM_INDICATOR,
PO_HEADER_ID = X_PO_HEADER_ID,
CUST_PO_NUMBER = X_CUST_PO_NUMBER,
RECEIPT_METHOD_ID = X_RECEIPT_METHOD_ID,
VAR_RENT_INV_ID = X_VAR_RENT_INV_ID,
VAR_RENT_TYPE = X_VAR_RENT_TYPE,
CHANGED_FLAG = X_CHANGED_FLAG,
LEGAL_ENTITY_ID = L_LEGAL_ENTITY_ID,
ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
ATTRIBUTE1 = X_ATTRIBUTE1,
ATTRIBUTE2 = X_ATTRIBUTE2,
ATTRIBUTE3 = X_ATTRIBUTE3,
ATTRIBUTE4 = X_ATTRIBUTE4,
ATTRIBUTE5 = X_ATTRIBUTE5,
ATTRIBUTE6 = X_ATTRIBUTE6,
ATTRIBUTE7 = X_ATTRIBUTE7,
ATTRIBUTE8 = X_ATTRIBUTE8,
ATTRIBUTE9 = X_ATTRIBUTE9,
ATTRIBUTE10 = X_ATTRIBUTE10,
ATTRIBUTE11 = X_ATTRIBUTE11,
ATTRIBUTE12 = X_ATTRIBUTE12,
ATTRIBUTE13 = X_ATTRIBUTE13,
ATTRIBUTE14 = X_ATTRIBUTE14,
ATTRIBUTE15 = X_ATTRIBUTE15,
PROJECT_ATTRIBUTE_CATEGORY = X_PROJECT_ATTRIBUTE_CATEGORY,
PROJECT_ATTRIBUTE1 = X_PROJECT_ATTRIBUTE1,
PROJECT_ATTRIBUTE2 = X_PROJECT_ATTRIBUTE2,
PROJECT_ATTRIBUTE3 = X_PROJECT_ATTRIBUTE3,
PROJECT_ATTRIBUTE4 = X_PROJECT_ATTRIBUTE4,
PROJECT_ATTRIBUTE5 = X_PROJECT_ATTRIBUTE5,
PROJECT_ATTRIBUTE6 = X_PROJECT_ATTRIBUTE6,
PROJECT_ATTRIBUTE7 = X_PROJECT_ATTRIBUTE7,
PROJECT_ATTRIBUTE8 = X_PROJECT_ATTRIBUTE8,
PROJECT_ATTRIBUTE9 = X_PROJECT_ATTRIBUTE9,
PROJECT_ATTRIBUTE10 = X_PROJECT_ATTRIBUTE10,
PROJECT_ATTRIBUTE11 = X_PROJECT_ATTRIBUTE11,
PROJECT_ATTRIBUTE12 = X_PROJECT_ATTRIBUTE12,
PROJECT_ATTRIBUTE13 = X_PROJECT_ATTRIBUTE13,
PROJECT_ATTRIBUTE14 = X_PROJECT_ATTRIBUTE14,
PROJECT_ATTRIBUTE15 = X_PROJECT_ATTRIBUTE15,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
RECOVERABLE_FLAG = X_RECOVERABLE_FLAG,
AREA_TYPE_CODE = X_AREA_TYPE_CODE,
AREA = X_AREA,
GROUPING_RULE_ID = X_GROUPING_RULE_ID,
TERM_ALTERED_FLAG = X_TERM_ALTERED_FLAG,
SOURCE_CODE = X_SOURCE_CODE,
TERM_COMMENTS = X_TERM_COMMENTS,
TERM_TEMPLATE_ID = X_TERM_TEMPLATE_ID,
INCLUDE_IN_VAR_RENT = X_INCLUDE_IN_VAR_RENT,
UPDATE_NBP_FLAG = l_update_nbp_flag,
RECUR_BB_CALC_DATE = X_RECUR_BB_CALC_DATE,
OPEX_AGR_ID = X_OPEX_AGR_ID,
OPEX_RECON_ID = X_OPEX_RECON_ID,
OPEX_TYPE = X_OPEX_TYPE
WHERE PAYMENT_TERM_ID = X_PAYMENT_TERM_ID;
pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.UPDATE_ROW (-)');
END UPDATE_ROW;
PROCEDURE DELETE_ROW (
X_PAYMENT_TERM_ID IN NUMBER
)
IS
BEGIN
pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.DELETE_ROW (+)');
DELETE FROM pn_payment_terms_all
WHERE payment_term_id = x_payment_term_id;
pnp_debug_pkg.debug ('PNT_PAYMENT_TERMS_PKG.DELETE_ROW (-)');
END DELETE_ROW;
PROCEDURE UPDATE_VENDOR_AND_CUST
(
X_PAYMENT_TERM_ID IN NUMBER
,X_VENDOR_ID IN NUMBER
,X_VENDOR_SITE_ID IN NUMBER
,X_LAST_UPDATE_DATE IN DATE
,X_LAST_UPDATED_BY IN NUMBER
,X_LAST_UPDATE_LOGIN IN NUMBER
,X_CUSTOMER_ID IN NUMBER
,X_CUSTOMER_SITE_USE_ID IN NUMBER
,X_CUST_SHIP_SITE_ID IN NUMBER
)
IS
CURSOR c1 IS
SELECT ppi.payment_item_id
FROM pn_payment_items_all ppi
WHERE ppi.payment_term_id = X_PAYMENT_TERM_ID
AND ppi.transferred_to_ap_flag IS NULL
AND ppi.transferred_to_ar_flag IS NULL
FOR UPDATE OF ppi.payment_item_id NOWAIT;
pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_VENDOR_AND_CUST (+)');
UPDATE pn_payment_items_all
SET VENDOR_ID = X_VENDOR_ID
,VENDOR_SITE_ID = X_VENDOR_SITE_ID
,LAST_UPDATE_DATE = X_LAST_UPDATE_DATE
,LAST_UPDATED_BY = X_LAST_UPDATED_BY
,LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
,CUSTOMER_ID = X_CUSTOMER_ID
,CUSTOMER_SITE_USE_ID = X_CUSTOMER_SITE_USE_ID
,CUST_SHIP_SITE_ID = X_CUST_SHIP_SITE_ID
WHERE payment_item_id = eachItem.payment_item_id;
pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_VENDOR_AND_CUST (-)');
END UPDATE_VENDOR_AND_CUST;
SELECT lease_num
FROM pn_leases_all
WHERE lease_id = x_lease_id
AND EXISTS
(
SELECT pps.lease_id
FROM pn_payment_schedules_all pps
WHERE pps.lease_id = x_lease_id
AND pps.payment_status_lookup_code = 'APPROVED'
AND TO_CHAR(pps.schedule_date, 'DD') = x_schedule_day
AND pps.schedule_date BETWEEN l_startDate AND l_endDate
);
SELECT TRUNC(x_start_date, 'MM')
,LAST_DAY(x_end_date)
INTO l_startDate
,l_endDate
FROM DUAL;
PROCEDURE UPDATE_PNT_ITEMS_AMT (
p_pnt_term_id IN pn_payment_items.payment_term_id%TYPE,
p_old_est_amt IN pn_payment_items.estimated_amount%TYPE,
p_new_est_amt IN pn_payment_items.estimated_amount%TYPE,
p_lease_id IN pn_payment_terms.lease_id%TYPE,
p_trm_str_dt IN DATE,
p_trm_end_dt IN DATE,
p_freq_code IN pn_payment_terms.frequency_code%TYPE
)
IS
l_prorate_rule NUMBER;
SELECT ppi.payment_item_id
, pps.payment_schedule_id
, ppi.actual_amount
, ppi.estimated_amount
, pps.schedule_date
, ppi.currency_code
FROM pn_payment_items_all ppi,
pn_payment_schedules_all pps
WHERE ppi.payment_term_id = p_pnt_term_id
AND ppi.payment_item_type_lookup_code = 'CASH'
AND pps.payment_schedule_id = ppi.payment_schedule_id
AND pps.payment_status_lookup_code = 'DRAFT';
pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_AMT (+) TermId: '
||p_pnt_term_id||', OldEstAmt: '||p_old_est_amt
||', NewEstAmt: '||p_new_est_amt||', Freq: '||p_freq_code);
UPDATE pn_payment_items_all
SET estimated_amount = ROUND(l_est_amt, l_precision)
,actual_amount = ROUND(l_act_amt, l_precision)
,export_currency_amount = ROUND(l_act_amt, l_precision)
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
,last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE payment_item_id = pnt_rec.payment_item_id;
SELECT payment_term_proration_rule
INTO l_prorate_rule
FROM pn_leases_all
WHERE lease_id = p_lease_id;
l_context:= 'entering UPDATE loop';
/** updates estimated amount AND actual amount accordingly in the items table */
l_context := 'updates items table';
UPDATE pn_payment_items_all
SET estimated_amount = ROUND(l_est_amt, l_precision)
,actual_amount = ROUND(l_act_amt, l_precision)
,export_currency_amount = ROUND(l_act_amt, l_precision)
,last_update_date = SYSDATE
,last_updated_by = NVL(fnd_profile.value('USER_ID'),0)
,last_update_login= NVL(fnd_profile.value('LOGIN_ID'),0)
WHERE payment_item_id = pnt_rec.payment_item_id;
pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_AMT (-)');
pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_AMT (-)');
END UPDATE_PNT_ITEMS_AMT;
PROCEDURE UPDATE_PNT_ITEMS_RATE(p_pnt_term_id pn_payment_items.payment_item_id%TYPE,
p_new_rate pn_payment_items.rate%TYPE)
IS
l_context VARCHAR2(240);
SELECT payment_item_id
FROM pn_payment_items_all ppi
WHERE ppi.payment_term_id = p_pnt_term_id
AND ppi.export_to_ap_flag IS NULL
AND ppi.export_to_ar_flag IS NULL;
pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_RATE (+)');
UPDATE pn_payment_items_all
SET rate = p_new_rate
WHERE payment_item_id = pnt_rec.payment_item_id;
pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_RATE (-)');
pnp_debug_pkg.debug('PNT_PAYMENT_TERMS_PKG.UPDATE_PNT_ITEMS_RATE (-)');
END UPDATE_PNT_ITEMS_RATE;
l_last_update_date DATE;
l_last_updated_by NUMBER;
l_last_update_login NUMBER;
l_insert_dist_hist VARCHAR2(1);
SELECT *
FROM pn_payment_terms_all
WHERE payment_term_id = p_term_id;
SELECT *
FROM pn_distributions_all
WHERE payment_term_id = p_term_id;
SELECT MAX(pdh.distribution_change_id)
FROM pn_distributions_history pdh,
pn_distributions_all pd
WHERE pdh.distribution_id = pd.distribution_id
AND pd.payment_term_id = p_term_id;
SELECT MAX(term_history_id)
FROM pn_payment_terms_history
WHERE payment_term_id = p_term_id;
SELECT pn_distributions_history_s1.NEXTVAL
INTO l_dist_change_id
FROM DUAL;
pnp_debug_pkg.debug('create_hist_corr_upd : Inserting in Dist. Hist. for very first time');
INSERT INTO pn_distributions_history
(
DISTRIBUTION_HISTORY_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,DISTRIBUTION_CHANGE_ID
,DISTRIBUTION_ID
,ACCOUNT_ID
,ACCOUNT_CLASS
,PERCENTAGE
,LINE_NUMBER
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15)
VALUES
(
pn_distributions_history_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,l_dist_change_id
,p_hist_dist_tab(i).DISTRIBUTION_ID
,p_hist_dist_tab(i).ACCOUNT_ID
,p_hist_dist_tab(i).ACCOUNT_CLASS
,p_hist_dist_tab(i).PERCENTAGE
,p_hist_dist_tab(i).LINE_NUMBER
,p_hist_dist_tab(i).ATTRIBUTE_CATEGORY
,p_hist_dist_tab(i).ATTRIBUTE1
,p_hist_dist_tab(i).ATTRIBUTE2
,p_hist_dist_tab(i).ATTRIBUTE3
,p_hist_dist_tab(i).ATTRIBUTE4
,p_hist_dist_tab(i).ATTRIBUTE5
,p_hist_dist_tab(i).ATTRIBUTE6
,p_hist_dist_tab(i).ATTRIBUTE7
,p_hist_dist_tab(i).ATTRIBUTE8
,p_hist_dist_tab(i).ATTRIBUTE9
,p_hist_dist_tab(i).ATTRIBUTE10
,p_hist_dist_tab(i).ATTRIBUTE11
,p_hist_dist_tab(i).ATTRIBUTE12
,p_hist_dist_tab(i).ATTRIBUTE13
,p_hist_dist_tab(i).ATTRIBUTE14
,p_hist_dist_tab(i).ATTRIBUTE15
);
pnp_debug_pkg.debug('create_hist_corr_upd : Inserting in Term Hist. for very first time');
INSERT INTO pn_payment_terms_history
(
TERM_HISTORY_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PAYMENT_TERM_ID
,DISTRIBUTION_CHANGE_ID
,PREV_TERM_HISTORY_ID
,ADJUSTMENT_TYPE_CODE
,TOTAL_ADJ_AMOUNT
,PAYMENT_PURPOSE_CODE
,PAYMENT_TERM_TYPE_CODE
,FREQUENCY_CODE
,LEASE_ID
,LEASE_CHANGE_ID
,START_DATE
,END_DATE
,VENDOR_ID
,VENDOR_SITE_ID
,TARGET_DATE
,ACTUAL_AMOUNT
,ESTIMATED_AMOUNT
,SET_OF_BOOKS_ID
,CURRENCY_CODE
,RATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CUSTOMER_ID
,CUSTOMER_SITE_USE_ID
,NORMALIZE
,LOCATION_ID
,SCHEDULE_DAY
,CUST_SHIP_SITE_ID
,AP_AR_TERM_ID
,CUST_TRX_TYPE_ID
,PROJECT_ID
,TASK_ID
,ORGANIZATION_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ITEM_DATE
,TAX_GROUP_ID
,TAX_CODE_ID
,TAX_CLASSIFICATION_CODE
,TAX_INCLUDED
,DISTRIBUTION_SET_ID
,INV_RULE_ID
,ACCOUNT_RULE_ID
,SALESREP_ID
,APPROVED_BY
,STATUS
,INDEX_PERIOD_ID
,INDEX_TERM_INDICATOR
,PO_HEADER_ID
,CUST_PO_NUMBER
,RECEIPT_METHOD_ID
,LEGAL_ENTITY_ID
,PROJECT_ATTRIBUTE_CATEGORY
,PROJECT_ATTRIBUTE1
,PROJECT_ATTRIBUTE2
,PROJECT_ATTRIBUTE3
,PROJECT_ATTRIBUTE4
,PROJECT_ATTRIBUTE5
,PROJECT_ATTRIBUTE6
,PROJECT_ATTRIBUTE7
,PROJECT_ATTRIBUTE8
,PROJECT_ATTRIBUTE9
,PROJECT_ATTRIBUTE10
,PROJECT_ATTRIBUTE11
,PROJECT_ATTRIBUTE12
,PROJECT_ATTRIBUTE13
,PROJECT_ATTRIBUTE14
,PROJECT_ATTRIBUTE15
,VAR_RENT_INV_ID
,VAR_RENT_TYPE
,CHANGED_FLAG
,NORM_START_DATE
,TERM_TEMPLATE_ID
,EVENT_TYPE_CODE
,LEASE_STATUS
,NORM_END_DATE
,RECOVERABLE_FLAG
,PERIOD_BILLREC_ID
,AMOUNT_TYPE
,REC_AGR_LINE_ID
,GROUPING_RULE_ID
,AREA_TYPE_CODE
,AREA
,TERM_COMMENTS)
VALUES
(PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,tlinfo.PAYMENT_TERM_ID
,l_dist_change_id
,NULL -- PREV_TERM_HISTORY_ID
,NULL -- ADJUSTMENT_TYPE_CODE
,NULL -- TOTAL_ADJ_AMOUNT
,tlinfo.PAYMENT_PURPOSE_CODE
,tlinfo.PAYMENT_TERM_TYPE_CODE
,tlinfo.FREQUENCY_CODE
,tlinfo.LEASE_ID
,tlinfo.LEASE_CHANGE_ID
,tlinfo.START_DATE
,tlinfo.END_DATE
,tlinfo.VENDOR_ID
,tlinfo.VENDOR_SITE_ID
,tlinfo.TARGET_DATE
,tlinfo.ACTUAL_AMOUNT
,tlinfo.ESTIMATED_AMOUNT
,tlinfo.SET_OF_BOOKS_ID
,tlinfo.CURRENCY_CODE
,tlinfo.RATE
,tlinfo.ATTRIBUTE_CATEGORY
,tlinfo.ATTRIBUTE1
,tlinfo.ATTRIBUTE2
,tlinfo.ATTRIBUTE3
,tlinfo.ATTRIBUTE4
,tlinfo.ATTRIBUTE5
,tlinfo.ATTRIBUTE6
,tlinfo.ATTRIBUTE7
,tlinfo.ATTRIBUTE8
,tlinfo.ATTRIBUTE9
,tlinfo.ATTRIBUTE10
,tlinfo.ATTRIBUTE11
,tlinfo.ATTRIBUTE12
,tlinfo.ATTRIBUTE13
,tlinfo.ATTRIBUTE14
,tlinfo.ATTRIBUTE15
,tlinfo.CUSTOMER_ID
,tlinfo.CUSTOMER_SITE_USE_ID
,tlinfo.NORMALIZE
,tlinfo.LOCATION_ID
,tlinfo.SCHEDULE_DAY
,tlinfo.CUST_SHIP_SITE_ID
,tlinfo.AP_AR_TERM_ID
,tlinfo.CUST_TRX_TYPE_ID
,tlinfo.PROJECT_ID
,tlinfo.TASK_ID
,tlinfo.ORGANIZATION_ID
,tlinfo.EXPENDITURE_TYPE
,tlinfo.EXPENDITURE_ITEM_DATE
,tlinfo.TAX_GROUP_ID
,tlinfo.TAX_CODE_ID
,tlinfo.TAX_CLASSIFICATION_CODE
,tlinfo.TAX_INCLUDED
,tlinfo.DISTRIBUTION_SET_ID
,tlinfo.INV_RULE_ID
,tlinfo.ACCOUNT_RULE_ID
,tlinfo.SALESREP_ID
,tlinfo.APPROVED_BY
,tlinfo.STATUS
,tlinfo.INDEX_PERIOD_ID
,tlinfo.INDEX_TERM_INDICATOR
,tlinfo.PO_HEADER_ID
,tlinfo.CUST_PO_NUMBER
,tlinfo.RECEIPT_METHOD_ID
,tlinfo.LEGAL_ENTITY_ID
,tlinfo.PROJECT_ATTRIBUTE_CATEGORY
,tlinfo.PROJECT_ATTRIBUTE1
,tlinfo.PROJECT_ATTRIBUTE2
,tlinfo.PROJECT_ATTRIBUTE3
,tlinfo.PROJECT_ATTRIBUTE4
,tlinfo.PROJECT_ATTRIBUTE5
,tlinfo.PROJECT_ATTRIBUTE6
,tlinfo.PROJECT_ATTRIBUTE7
,tlinfo.PROJECT_ATTRIBUTE8
,tlinfo.PROJECT_ATTRIBUTE9
,tlinfo.PROJECT_ATTRIBUTE10
,tlinfo.PROJECT_ATTRIBUTE11
,tlinfo.PROJECT_ATTRIBUTE12
,tlinfo.PROJECT_ATTRIBUTE13
,tlinfo.PROJECT_ATTRIBUTE14
,tlinfo.PROJECT_ATTRIBUTE15
,tlinfo.VAR_RENT_INV_ID
,tlinfo.VAR_RENT_TYPE
,tlinfo.CHANGED_FLAG
,tlinfo.NORM_START_DATE
,tlinfo.TERM_TEMPLATE_ID
,tlinfo.EVENT_TYPE_CODE
,tlinfo.LEASE_STATUS
,tlinfo.NORM_END_DATE
,tlinfo.RECOVERABLE_FLAG
,tlinfo.PERIOD_BILLREC_ID
,tlinfo.AMOUNT_TYPE
,tlinfo.REC_AGR_LINE_ID
,tlinfo.GROUPING_RULE_ID
,tlinfo.AREA_TYPE_CODE
,tlinfo.AREA
,NULL);
UPDATE pn_payment_terms_all
SET term_altered_flag = 'Y'
WHERE payment_term_id = p_term_id;
SELECT pn_distributions_history_s1.NEXTVAL
INTO l_dist_change_id
FROM DUAL;
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. Hist.');
INSERT INTO pn_distributions_history
(
DISTRIBUTION_HISTORY_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,DISTRIBUTION_CHANGE_ID
,DISTRIBUTION_ID
,ACCOUNT_ID
,ACCOUNT_CLASS
,PERCENTAGE
,LINE_NUMBER
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15)
VALUES
(
pn_distributions_history_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,l_dist_change_id
,dist_rec.DISTRIBUTION_ID
,dist_rec.ACCOUNT_ID
,dist_rec.ACCOUNT_CLASS
,dist_rec.PERCENTAGE
,dist_rec.LINE_NUMBER
,dist_rec.ATTRIBUTE_CATEGORY
,dist_rec.ATTRIBUTE1
,dist_rec.ATTRIBUTE2
,dist_rec.ATTRIBUTE3
,dist_rec.ATTRIBUTE4
,dist_rec.ATTRIBUTE5
,dist_rec.ATTRIBUTE6
,dist_rec.ATTRIBUTE7
,dist_rec.ATTRIBUTE8
,dist_rec.ATTRIBUTE9
,dist_rec.ATTRIBUTE10
,dist_rec.ATTRIBUTE11
,dist_rec.ATTRIBUTE12
,dist_rec.ATTRIBUTE13
,dist_rec.ATTRIBUTE14
,dist_rec.ATTRIBUTE15);
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Term Hist.');
INSERT INTO pn_payment_terms_history
(TERM_HISTORY_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PAYMENT_TERM_ID
,DISTRIBUTION_CHANGE_ID
,PREV_TERM_HISTORY_ID
,ADJUSTMENT_TYPE_CODE
,TOTAL_ADJ_AMOUNT
,PAYMENT_PURPOSE_CODE
,PAYMENT_TERM_TYPE_CODE
,FREQUENCY_CODE
,LEASE_ID
,LEASE_CHANGE_ID
,START_DATE
,END_DATE
,VENDOR_ID
,VENDOR_SITE_ID
,TARGET_DATE
,ACTUAL_AMOUNT
,ESTIMATED_AMOUNT
,SET_OF_BOOKS_ID
,CURRENCY_CODE
,RATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CUSTOMER_ID
,CUSTOMER_SITE_USE_ID
,NORMALIZE
,LOCATION_ID
,SCHEDULE_DAY
,CUST_SHIP_SITE_ID
,AP_AR_TERM_ID
,CUST_TRX_TYPE_ID
,PROJECT_ID
,TASK_ID
,ORGANIZATION_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ITEM_DATE
,TAX_GROUP_ID
,TAX_CODE_ID
,TAX_CLASSIFICATION_CODE
,TAX_INCLUDED
,DISTRIBUTION_SET_ID
,INV_RULE_ID
,ACCOUNT_RULE_ID
,SALESREP_ID
,APPROVED_BY
,STATUS
,INDEX_PERIOD_ID
,INDEX_TERM_INDICATOR
,PO_HEADER_ID
,CUST_PO_NUMBER
,RECEIPT_METHOD_ID
,LEGAL_ENTITY_ID
,PROJECT_ATTRIBUTE_CATEGORY
,PROJECT_ATTRIBUTE1
,PROJECT_ATTRIBUTE2
,PROJECT_ATTRIBUTE3
,PROJECT_ATTRIBUTE4
,PROJECT_ATTRIBUTE5
,PROJECT_ATTRIBUTE6
,PROJECT_ATTRIBUTE7
,PROJECT_ATTRIBUTE8
,PROJECT_ATTRIBUTE9
,PROJECT_ATTRIBUTE10
,PROJECT_ATTRIBUTE11
,PROJECT_ATTRIBUTE12
,PROJECT_ATTRIBUTE13
,PROJECT_ATTRIBUTE14
,PROJECT_ATTRIBUTE15
,VAR_RENT_INV_ID
,VAR_RENT_TYPE
,CHANGED_FLAG
,NORM_START_DATE
,TERM_TEMPLATE_ID
,EVENT_TYPE_CODE
,LEASE_STATUS
,NORM_END_DATE
,RECOVERABLE_FLAG
,PERIOD_BILLREC_ID
,AMOUNT_TYPE
,REC_AGR_LINE_ID
,GROUPING_RULE_ID
,AREA_TYPE_CODE
,AREA
,TERM_COMMENTS)
VALUES
(PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,tlinfo.payment_term_id
,l_dist_change_id
,l_prev_term_hist_id
,l_adj_type_code
,NULL -- TOTAL_ADJ_AMOUNT
,term_rec.PAYMENT_PURPOSE_CODE
,term_rec.PAYMENT_TERM_TYPE_CODE
,term_rec.FREQUENCY_CODE
,term_rec.LEASE_ID
,term_rec.LEASE_CHANGE_ID
,term_rec.START_DATE
,term_rec.END_DATE
,term_rec.VENDOR_ID
,term_rec.VENDOR_SITE_ID
,term_rec.TARGET_DATE
,term_rec.ACTUAL_AMOUNT
,term_rec.ESTIMATED_AMOUNT
,term_rec.SET_OF_BOOKS_ID
,term_rec.CURRENCY_CODE
,term_rec.RATE
,term_rec.ATTRIBUTE_CATEGORY
,term_rec.ATTRIBUTE1
,term_rec.ATTRIBUTE2
,term_rec.ATTRIBUTE3
,term_rec.ATTRIBUTE4
,term_rec.ATTRIBUTE5
,term_rec.ATTRIBUTE6
,term_rec.ATTRIBUTE7
,term_rec.ATTRIBUTE8
,term_rec.ATTRIBUTE9
,term_rec.ATTRIBUTE10
,term_rec.ATTRIBUTE11
,term_rec.ATTRIBUTE12
,term_rec.ATTRIBUTE13
,term_rec.ATTRIBUTE14
,term_rec.ATTRIBUTE15
,term_rec.CUSTOMER_ID
,term_rec.CUSTOMER_SITE_USE_ID
,term_rec.NORMALIZE
,term_rec.LOCATION_ID
,term_rec.SCHEDULE_DAY
,term_rec.CUST_SHIP_SITE_ID
,term_rec.AP_AR_TERM_ID
,term_rec.CUST_TRX_TYPE_ID
,term_rec.PROJECT_ID
,term_rec.TASK_ID
,term_rec.ORGANIZATION_ID
,term_rec.EXPENDITURE_TYPE
,term_rec.EXPENDITURE_ITEM_DATE
,term_rec.TAX_GROUP_ID
,term_rec.TAX_CODE_ID
,term_rec.TAX_CLASSIFICATION_CODE
,term_rec.TAX_INCLUDED
,term_rec.DISTRIBUTION_SET_ID
,term_rec.INV_RULE_ID
,term_rec.ACCOUNT_RULE_ID
,term_rec.SALESREP_ID
,term_rec.APPROVED_BY
,term_rec.STATUS
,term_rec.INDEX_PERIOD_ID
,term_rec.INDEX_TERM_INDICATOR
,term_rec.PO_HEADER_ID
,term_rec.CUST_PO_NUMBER
,term_rec.RECEIPT_METHOD_ID
,term_rec.LEGAL_ENTITY_ID
,term_rec.PROJECT_ATTRIBUTE_CATEGORY
,term_rec.PROJECT_ATTRIBUTE1
,term_rec.PROJECT_ATTRIBUTE2
,term_rec.PROJECT_ATTRIBUTE3
,term_rec.PROJECT_ATTRIBUTE4
,term_rec.PROJECT_ATTRIBUTE5
,term_rec.PROJECT_ATTRIBUTE6
,term_rec.PROJECT_ATTRIBUTE7
,term_rec.PROJECT_ATTRIBUTE8
,term_rec.PROJECT_ATTRIBUTE9
,term_rec.PROJECT_ATTRIBUTE10
,term_rec.PROJECT_ATTRIBUTE11
,term_rec.PROJECT_ATTRIBUTE12
,term_rec.PROJECT_ATTRIBUTE13
,term_rec.PROJECT_ATTRIBUTE14
,term_rec.PROJECT_ATTRIBUTE15
,term_rec.VAR_RENT_INV_ID
,term_rec.VAR_RENT_TYPE
,term_rec.CHANGED_FLAG
,tlinfo.NORM_START_DATE
,tlinfo.TERM_TEMPLATE_ID
,tlinfo.EVENT_TYPE_CODE
,tlinfo.LEASE_STATUS
,tlinfo.NORM_END_DATE
,term_rec.RECOVERABLE_FLAG
,tlinfo.PERIOD_BILLREC_ID
,tlinfo.AMOUNT_TYPE
,tlinfo.REC_AGR_LINE_ID
,term_rec.GROUPING_RULE_ID
,term_rec.AREA_TYPE_CODE
,term_rec.AREA
,term_rec.TERM_COMMENTS);
IF p_change_mode = 'UPDATE' THEN
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- p_eff_str_dt : '||p_eff_str_dt);
SELECT pn_payment_terms_s.nextval
INTO l_new_term_id
FROM DUAL;
UPDATE pn_payment_terms_all
SET start_date = p_eff_str_dt,
end_date = NVL(p_eff_end_dt,term_rec.end_date),
term_altered_flag = 'Y',
changed_flag = 'N',
payment_term_id = l_new_term_id
WHERE payment_term_id = p_term_id;
SELECT pn_distributions_history_s1.NEXTVAL
INTO l_dist_change_id
FROM DUAL;
SELECT pn_distributions_s.nextval
INTO l_new_dist_id
FROM DUAL;
UPDATE pn_distributions_all
SET distribution_id = l_new_dist_id,
payment_term_id = l_new_term_id
WHERE distribution_id = dist_rec.distribution_id;
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. Hist.');
INSERT INTO pn_distributions_history
(DISTRIBUTION_HISTORY_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,DISTRIBUTION_CHANGE_ID
,DISTRIBUTION_ID
,ACCOUNT_ID
,ACCOUNT_CLASS
,PERCENTAGE
,LINE_NUMBER
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15)
VALUES
(pn_distributions_history_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,l_dist_change_id
,l_new_dist_id
,dist_rec.ACCOUNT_ID
,dist_rec.ACCOUNT_CLASS
,dist_rec.PERCENTAGE
,dist_rec.LINE_NUMBER
,dist_rec.ATTRIBUTE_CATEGORY
,dist_rec.ATTRIBUTE1
,dist_rec.ATTRIBUTE2
,dist_rec.ATTRIBUTE3
,dist_rec.ATTRIBUTE4
,dist_rec.ATTRIBUTE5
,dist_rec.ATTRIBUTE6
,dist_rec.ATTRIBUTE7
,dist_rec.ATTRIBUTE8
,dist_rec.ATTRIBUTE9
,dist_rec.ATTRIBUTE10
,dist_rec.ATTRIBUTE11
,dist_rec.ATTRIBUTE12
,dist_rec.ATTRIBUTE13
,dist_rec.ATTRIBUTE14
,dist_rec.ATTRIBUTE15);
UPDATE pn_payment_terms_all
SET end_date = NVL(p_eff_end_dt, term_rec.end_date),
term_altered_flag = 'Y',
changed_flag = 'Y'
WHERE payment_term_id = p_term_id;
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Term Hist.');
INSERT INTO pn_payment_terms_history
(TERM_HISTORY_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PAYMENT_TERM_ID
,DISTRIBUTION_CHANGE_ID
,PREV_TERM_HISTORY_ID
,ADJUSTMENT_TYPE_CODE
,TOTAL_ADJ_AMOUNT
,PAYMENT_PURPOSE_CODE
,PAYMENT_TERM_TYPE_CODE
,FREQUENCY_CODE
,LEASE_ID
,LEASE_CHANGE_ID
,START_DATE
,END_DATE
,VENDOR_ID
,VENDOR_SITE_ID
,TARGET_DATE
,ACTUAL_AMOUNT
,ESTIMATED_AMOUNT
,SET_OF_BOOKS_ID
,CURRENCY_CODE
,RATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CUSTOMER_ID
,CUSTOMER_SITE_USE_ID
,NORMALIZE
,LOCATION_ID
,SCHEDULE_DAY
,CUST_SHIP_SITE_ID
,AP_AR_TERM_ID
,CUST_TRX_TYPE_ID
,PROJECT_ID
,TASK_ID
,ORGANIZATION_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ITEM_DATE
,TAX_GROUP_ID
,TAX_CODE_ID
,TAX_CLASSIFICATION_CODE
,TAX_INCLUDED
,DISTRIBUTION_SET_ID
,INV_RULE_ID
,ACCOUNT_RULE_ID
,SALESREP_ID
,APPROVED_BY
,STATUS
,INDEX_PERIOD_ID
,INDEX_TERM_INDICATOR
,PO_HEADER_ID
,CUST_PO_NUMBER
,RECEIPT_METHOD_ID
,LEGAL_ENTITY_ID
,PROJECT_ATTRIBUTE_CATEGORY
,PROJECT_ATTRIBUTE1
,PROJECT_ATTRIBUTE2
,PROJECT_ATTRIBUTE3
,PROJECT_ATTRIBUTE4
,PROJECT_ATTRIBUTE5
,PROJECT_ATTRIBUTE6
,PROJECT_ATTRIBUTE7
,PROJECT_ATTRIBUTE8
,PROJECT_ATTRIBUTE9
,PROJECT_ATTRIBUTE10
,PROJECT_ATTRIBUTE11
,PROJECT_ATTRIBUTE12
,PROJECT_ATTRIBUTE13
,PROJECT_ATTRIBUTE14
,PROJECT_ATTRIBUTE15
,VAR_RENT_INV_ID
,VAR_RENT_TYPE
,CHANGED_FLAG
,NORM_START_DATE
,TERM_TEMPLATE_ID
,EVENT_TYPE_CODE
,LEASE_STATUS
,NORM_END_DATE
,RECOVERABLE_FLAG
,PERIOD_BILLREC_ID
,AMOUNT_TYPE
,REC_AGR_LINE_ID
,GROUPING_RULE_ID
,AREA_TYPE_CODE
,AREA
,TERM_COMMENTS)
VALUES
(PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,l_new_term_id
,l_dist_change_id
,l_prev_term_hist_id
,l_adj_type_code
,NULL -- TOTAL_ADJ_AMOUNT
,term_rec.PAYMENT_PURPOSE_CODE
,term_rec.PAYMENT_TERM_TYPE_CODE
,term_rec.FREQUENCY_CODE
,term_rec.LEASE_ID
,term_rec.LEASE_CHANGE_ID
,p_eff_str_dt
,NVL(p_eff_end_dt,term_rec.end_date)
,term_rec.VENDOR_ID
,term_rec.VENDOR_SITE_ID
,term_rec.TARGET_DATE
,term_rec.ACTUAL_AMOUNT
,term_rec.ESTIMATED_AMOUNT
,term_rec.SET_OF_BOOKS_ID
,term_rec.CURRENCY_CODE
,term_rec.RATE
,term_rec.ATTRIBUTE_CATEGORY
,term_rec.ATTRIBUTE1
,term_rec.ATTRIBUTE2
,term_rec.ATTRIBUTE3
,term_rec.ATTRIBUTE4
,term_rec.ATTRIBUTE5
,term_rec.ATTRIBUTE6
,term_rec.ATTRIBUTE7
,term_rec.ATTRIBUTE8
,term_rec.ATTRIBUTE9
,term_rec.ATTRIBUTE10
,term_rec.ATTRIBUTE11
,term_rec.ATTRIBUTE12
,term_rec.ATTRIBUTE13
,term_rec.ATTRIBUTE14
,term_rec.ATTRIBUTE15
,term_rec.CUSTOMER_ID
,term_rec.CUSTOMER_SITE_USE_ID
,term_rec.NORMALIZE
,term_rec.LOCATION_ID
,term_rec.SCHEDULE_DAY
,term_rec.CUST_SHIP_SITE_ID
,term_rec.AP_AR_TERM_ID
,term_rec.CUST_TRX_TYPE_ID
,term_rec.PROJECT_ID
,term_rec.TASK_ID
,term_rec.ORGANIZATION_ID
,term_rec.EXPENDITURE_TYPE
,term_rec.EXPENDITURE_ITEM_DATE
,term_rec.TAX_GROUP_ID
,term_rec.TAX_CODE_ID
,term_rec.TAX_CLASSIFICATION_CODE
,term_rec.TAX_INCLUDED
,term_rec.DISTRIBUTION_SET_ID
,term_rec.INV_RULE_ID
,term_rec.ACCOUNT_RULE_ID
,term_rec.SALESREP_ID
,term_rec.APPROVED_BY
,term_rec.STATUS
,term_rec.INDEX_PERIOD_ID
,term_rec.INDEX_TERM_INDICATOR
,term_rec.PO_HEADER_ID
,term_rec.CUST_PO_NUMBER
,term_rec.RECEIPT_METHOD_ID
,term_rec.LEGAL_ENTITY_ID
,term_rec.PROJECT_ATTRIBUTE_CATEGORY
,term_rec.PROJECT_ATTRIBUTE1
,term_rec.PROJECT_ATTRIBUTE2
,term_rec.PROJECT_ATTRIBUTE3
,term_rec.PROJECT_ATTRIBUTE4
,term_rec.PROJECT_ATTRIBUTE5
,term_rec.PROJECT_ATTRIBUTE6
,term_rec.PROJECT_ATTRIBUTE7
,term_rec.PROJECT_ATTRIBUTE8
,term_rec.PROJECT_ATTRIBUTE9
,term_rec.PROJECT_ATTRIBUTE10
,term_rec.PROJECT_ATTRIBUTE11
,term_rec.PROJECT_ATTRIBUTE12
,term_rec.PROJECT_ATTRIBUTE13
,term_rec.PROJECT_ATTRIBUTE14
,term_rec.PROJECT_ATTRIBUTE15
,term_rec.VAR_RENT_INV_ID
,term_rec.VAR_RENT_TYPE
,term_rec.CHANGED_FLAG
,tlinfo.NORM_START_DATE
,tlinfo.TERM_TEMPLATE_ID
,tlinfo.EVENT_TYPE_CODE
,tlinfo.LEASE_STATUS
,tlinfo.NORM_END_DATE
,term_rec.RECOVERABLE_FLAG
,tlinfo.PERIOD_BILLREC_ID
,tlinfo.AMOUNT_TYPE
,tlinfo.REC_AGR_LINE_ID
,term_rec.GROUPING_RULE_ID
,term_rec.AREA_TYPE_CODE
,term_rec.AREA
,l_term_comments);
l_last_update_date := SYSDATE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
SELECT pn_payment_terms_s.nextval
INTO l_term_id
FROM DUAL;
l_last_update_date := SYSDATE;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Terms table.');
INSERT INTO pn_payment_terms_all
(PAYMENT_TERM_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PAYMENT_PURPOSE_CODE
,PAYMENT_TERM_TYPE_CODE
,FREQUENCY_CODE
,LEASE_ID
,LEASE_CHANGE_ID
,START_DATE
,END_DATE
,VENDOR_ID
,VENDOR_SITE_ID
,CUSTOMER_ID
,CUSTOMER_SITE_USE_ID
,TARGET_DATE
,ACTUAL_AMOUNT
,ESTIMATED_AMOUNT
,SET_OF_BOOKS_ID
,CURRENCY_CODE
,RATE
,NORMALIZE
,LOCATION_ID
,SCHEDULE_DAY
,CUST_SHIP_SITE_ID
,AP_AR_TERM_ID
,CUST_TRX_TYPE_ID
,PROJECT_ID
,TASK_ID
,ORGANIZATION_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ITEM_DATE
,TAX_GROUP_ID
,TAX_CODE_ID
,TAX_CLASSIFICATION_CODE
,TAX_INCLUDED
,DISTRIBUTION_SET_ID
,INV_RULE_ID
,ACCOUNT_RULE_ID
,SALESREP_ID
,APPROVED_BY
,STATUS
,INDEX_PERIOD_ID
,INDEX_TERM_INDICATOR
,PO_HEADER_ID
,CUST_PO_NUMBER
,RECEIPT_METHOD_ID
,VAR_RENT_INV_ID
,VAR_RENT_TYPE
,PERIOD_BILLREC_ID
,REC_AGR_LINE_ID
,AMOUNT_TYPE
,CHANGED_FLAG
,TERM_TEMPLATE_ID
,LEGAL_ENTITY_ID
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,PROJECT_ATTRIBUTE_CATEGORY
,PROJECT_ATTRIBUTE1
,PROJECT_ATTRIBUTE2
,PROJECT_ATTRIBUTE3
,PROJECT_ATTRIBUTE4
,PROJECT_ATTRIBUTE5
,PROJECT_ATTRIBUTE6
,PROJECT_ATTRIBUTE7
,PROJECT_ATTRIBUTE8
,PROJECT_ATTRIBUTE9
,PROJECT_ATTRIBUTE10
,PROJECT_ATTRIBUTE11
,PROJECT_ATTRIBUTE12
,PROJECT_ATTRIBUTE13
,PROJECT_ATTRIBUTE14
,PROJECT_ATTRIBUTE15
,org_id
,lease_status
,recoverable_flag
,area_type_code
,area
,grouping_rule_id
,term_altered_flag
,source_code
,term_comments)
VALUES
(l_term_id
,l_last_update_date
,l_last_updated_by
,l_creation_date
,l_created_by
,l_last_update_login
,tlinfo.PAYMENT_PURPOSE_CODE
,tlinfo.PAYMENT_TERM_TYPE_CODE
,tlinfo.FREQUENCY_CODE
,tlinfo.LEASE_ID
,term_rec.LEASE_CHANGE_ID
,l_str_dt
,l_end_dt
,tlinfo.VENDOR_ID
,tlinfo.VENDOR_SITE_ID
,tlinfo.CUSTOMER_ID
,tlinfo.CUSTOMER_SITE_USE_ID
,tlinfo.TARGET_DATE
,tlinfo.ACTUAL_AMOUNT
,tlinfo.ESTIMATED_AMOUNT
,tlinfo.SET_OF_BOOKS_ID
,tlinfo.CURRENCY_CODE
,tlinfo.RATE
,tlinfo.NORMALIZE
,tlinfo.LOCATION_ID
,tlinfo.SCHEDULE_DAY
,tlinfo.CUST_SHIP_SITE_ID
,tlinfo.AP_AR_TERM_ID
,tlinfo.CUST_TRX_TYPE_ID
,tlinfo.PROJECT_ID
,tlinfo.TASK_ID
,tlinfo.ORGANIZATION_ID
,tlinfo.EXPENDITURE_TYPE
,tlinfo.EXPENDITURE_ITEM_DATE
,tlinfo.TAX_GROUP_ID
,tlinfo.TAX_CODE_ID
,tlinfo.TAX_CLASSIFICATION_CODE
,tlinfo.TAX_INCLUDED
,tlinfo.DISTRIBUTION_SET_ID
,tlinfo.INV_RULE_ID
,tlinfo.ACCOUNT_RULE_ID
,tlinfo.SALESREP_ID
,tlinfo.APPROVED_BY
,tlinfo.STATUS
,tlinfo.INDEX_PERIOD_ID
,tlinfo.INDEX_TERM_INDICATOR
,tlinfo.PO_HEADER_ID
,tlinfo.CUST_PO_NUMBER
,tlinfo.RECEIPT_METHOD_ID
,tlinfo.VAR_RENT_INV_ID
,tlinfo.VAR_RENT_TYPE
,tlinfo.PERIOD_BILLREC_ID
,tlinfo.REC_AGR_LINE_ID
,tlinfo.AMOUNT_TYPE
,l_changed_flag
,tlinfo.TERM_TEMPLATE_ID
,tlinfo.LEGAL_ENTITY_ID
,tlinfo.ATTRIBUTE_CATEGORY
,tlinfo.ATTRIBUTE1
,tlinfo.ATTRIBUTE2
,tlinfo.ATTRIBUTE3
,tlinfo.ATTRIBUTE4
,tlinfo.ATTRIBUTE5
,tlinfo.ATTRIBUTE6
,tlinfo.ATTRIBUTE7
,tlinfo.ATTRIBUTE8
,tlinfo.ATTRIBUTE9
,tlinfo.ATTRIBUTE10
,tlinfo.ATTRIBUTE11
,tlinfo.ATTRIBUTE12
,tlinfo.ATTRIBUTE13
,tlinfo.ATTRIBUTE14
,tlinfo.ATTRIBUTE15
,tlinfo.PROJECT_ATTRIBUTE_CATEGORY
,tlinfo.PROJECT_ATTRIBUTE1
,tlinfo.PROJECT_ATTRIBUTE2
,tlinfo.PROJECT_ATTRIBUTE3
,tlinfo.PROJECT_ATTRIBUTE4
,tlinfo.PROJECT_ATTRIBUTE5
,tlinfo.PROJECT_ATTRIBUTE6
,tlinfo.PROJECT_ATTRIBUTE7
,tlinfo.PROJECT_ATTRIBUTE8
,tlinfo.PROJECT_ATTRIBUTE9
,tlinfo.PROJECT_ATTRIBUTE10
,tlinfo.PROJECT_ATTRIBUTE11
,tlinfo.PROJECT_ATTRIBUTE12
,tlinfo.PROJECT_ATTRIBUTE13
,tlinfo.PROJECT_ATTRIBUTE14
,tlinfo.PROJECT_ATTRIBUTE15
,tlinfo.org_id
,tlinfo.lease_status
,tlinfo.recoverable_flag
,tlinfo.area_type_code
,tlinfo.area
,tlinfo.grouping_rule_id
,'Y'
,tlinfo.source_code
,tlinfo.term_comments);
SELECT pn_distributions_history_s1.NEXTVAL
INTO l_dist_change_id
FROM DUAL;
l_insert_dist_hist := 'N';
l_insert_dist_hist := 'N';
SELECT pn_distributions_s.NEXTVAL
INTO l_dist_id
FROM DUAL;
l_insert_dist_hist := 'Y';
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- l_insert_dist_hist: '||l_insert_dist_hist);
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. table.');
INSERT INTO pn_distributions_all
(distribution_id
,account_id
,payment_term_id
,term_template_id
,account_class
,percentage
,line_number
,last_update_date
,last_updated_by
,creation_date
,created_by
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,org_id)
VALUES
(l_dist_id
,p_hist_dist_tab(j).account_id
,l_term_id
,p_hist_dist_tab(j).term_template_id
,p_hist_dist_tab(j).account_class
,p_hist_dist_tab(j).percentage
,p_hist_dist_tab(j).line_number
,l_last_update_date
,l_last_updated_by
,l_creation_date
,l_created_by
,l_last_update_login
,p_hist_dist_tab(j).attribute_category
,p_hist_dist_tab(j).attribute1
,p_hist_dist_tab(j).attribute2
,p_hist_dist_tab(j).attribute3
,p_hist_dist_tab(j).attribute4
,p_hist_dist_tab(j).attribute5
,p_hist_dist_tab(j).attribute6
,p_hist_dist_tab(j).attribute7
,p_hist_dist_tab(j).attribute8
,p_hist_dist_tab(j).attribute9
,p_hist_dist_tab(j).attribute10
,p_hist_dist_tab(j).attribute11
,p_hist_dist_tab(j).attribute12
,p_hist_dist_tab(j).attribute13
,p_hist_dist_tab(j).attribute14
,p_hist_dist_tab(j).attribute15
,p_hist_dist_tab(j).org_id);
IF l_insert_dist_hist = 'Y' THEN
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Dist. Hist. table.');
INSERT INTO pn_distributions_history
(DISTRIBUTION_HISTORY_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,DISTRIBUTION_CHANGE_ID
,DISTRIBUTION_ID
,ACCOUNT_ID
,ACCOUNT_CLASS
,PERCENTAGE
,LINE_NUMBER
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15)
VALUES
(pn_distributions_history_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,l_dist_change_id
,l_dist_id
,p_hist_dist_tab(j).ACCOUNT_ID
,p_hist_dist_tab(j).ACCOUNT_CLASS
,p_hist_dist_tab(j).PERCENTAGE
,p_hist_dist_tab(j).LINE_NUMBER
,p_hist_dist_tab(j).ATTRIBUTE_CATEGORY
,p_hist_dist_tab(j).ATTRIBUTE1
,p_hist_dist_tab(j).ATTRIBUTE2
,p_hist_dist_tab(j).ATTRIBUTE3
,p_hist_dist_tab(j).ATTRIBUTE4
,p_hist_dist_tab(j).ATTRIBUTE5
,p_hist_dist_tab(j).ATTRIBUTE6
,p_hist_dist_tab(j).ATTRIBUTE7
,p_hist_dist_tab(j).ATTRIBUTE8
,p_hist_dist_tab(j).ATTRIBUTE9
,p_hist_dist_tab(j).ATTRIBUTE10
,p_hist_dist_tab(j).ATTRIBUTE11
,p_hist_dist_tab(j).ATTRIBUTE12
,p_hist_dist_tab(j).ATTRIBUTE13
,p_hist_dist_tab(j).ATTRIBUTE14
,p_hist_dist_tab(j).ATTRIBUTE15);
IF l_insert_dist_hist = 'N' THEN
OPEN get_max_dist_change_id_cur(p_term_id);
pnp_debug_pkg.debug('create_hist_corr_upd : '||p_change_mode|| '- Inserting into Term Hist. table.');
INSERT INTO pn_payment_terms_history
(TERM_HISTORY_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_LOGIN
,PAYMENT_TERM_ID
,DISTRIBUTION_CHANGE_ID
,PREV_TERM_HISTORY_ID
,ADJUSTMENT_TYPE_CODE
,TOTAL_ADJ_AMOUNT
,PAYMENT_PURPOSE_CODE
,PAYMENT_TERM_TYPE_CODE
,FREQUENCY_CODE
,LEASE_ID
,LEASE_CHANGE_ID
,START_DATE
,END_DATE
,VENDOR_ID
,VENDOR_SITE_ID
,TARGET_DATE
,ACTUAL_AMOUNT
,ESTIMATED_AMOUNT
,SET_OF_BOOKS_ID
,CURRENCY_CODE
,RATE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CUSTOMER_ID
,CUSTOMER_SITE_USE_ID
,NORMALIZE
,LOCATION_ID
,SCHEDULE_DAY
,CUST_SHIP_SITE_ID
,AP_AR_TERM_ID
,CUST_TRX_TYPE_ID
,PROJECT_ID
,TASK_ID
,ORGANIZATION_ID
,EXPENDITURE_TYPE
,EXPENDITURE_ITEM_DATE
,TAX_GROUP_ID
,TAX_CODE_ID
,TAX_CLASSIFICATION_CODE
,TAX_INCLUDED
,DISTRIBUTION_SET_ID
,INV_RULE_ID
,ACCOUNT_RULE_ID
,SALESREP_ID
,APPROVED_BY
,STATUS
,INDEX_PERIOD_ID
,INDEX_TERM_INDICATOR
,PO_HEADER_ID
,CUST_PO_NUMBER
,RECEIPT_METHOD_ID
,LEGAL_ENTITY_ID
,PROJECT_ATTRIBUTE_CATEGORY
,PROJECT_ATTRIBUTE1
,PROJECT_ATTRIBUTE2
,PROJECT_ATTRIBUTE3
,PROJECT_ATTRIBUTE4
,PROJECT_ATTRIBUTE5
,PROJECT_ATTRIBUTE6
,PROJECT_ATTRIBUTE7
,PROJECT_ATTRIBUTE8
,PROJECT_ATTRIBUTE9
,PROJECT_ATTRIBUTE10
,PROJECT_ATTRIBUTE11
,PROJECT_ATTRIBUTE12
,PROJECT_ATTRIBUTE13
,PROJECT_ATTRIBUTE14
,PROJECT_ATTRIBUTE15
,VAR_RENT_INV_ID
,VAR_RENT_TYPE
,CHANGED_FLAG
,NORM_START_DATE
,TERM_TEMPLATE_ID
,EVENT_TYPE_CODE
,LEASE_STATUS
,NORM_END_DATE
,RECOVERABLE_FLAG
,PERIOD_BILLREC_ID
,AMOUNT_TYPE
,REC_AGR_LINE_ID
,GROUPING_RULE_ID
,AREA_TYPE_CODE
,AREA
,TERM_COMMENTS)
VALUES
(PN_PAYMENT_TERMS_HISTORY_S.NEXTVAL
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.LOGIN_ID
,l_term_id
,l_dist_change_id
,l_prev_term_hist_id
,l_adj_type_code
,NULL -- TOTAL_ADJ_AMOUNT
,tlinfo.PAYMENT_PURPOSE_CODE
,tlinfo.PAYMENT_TERM_TYPE_CODE
,tlinfo.FREQUENCY_CODE
,tlinfo.LEASE_ID
,term_rec.LEASE_CHANGE_ID
,l_str_dt
,l_end_dt
,tlinfo.VENDOR_ID
,tlinfo.VENDOR_SITE_ID
,tlinfo.TARGET_DATE
,tlinfo.ACTUAL_AMOUNT
,tlinfo.ESTIMATED_AMOUNT
,tlinfo.SET_OF_BOOKS_ID
,tlinfo.CURRENCY_CODE
,tlinfo.RATE
,tlinfo.ATTRIBUTE_CATEGORY
,tlinfo.ATTRIBUTE1
,tlinfo.ATTRIBUTE2
,tlinfo.ATTRIBUTE3
,tlinfo.ATTRIBUTE4
,tlinfo.ATTRIBUTE5
,tlinfo.ATTRIBUTE6
,tlinfo.ATTRIBUTE7
,tlinfo.ATTRIBUTE8
,tlinfo.ATTRIBUTE9
,tlinfo.ATTRIBUTE10
,tlinfo.ATTRIBUTE11
,tlinfo.ATTRIBUTE12
,tlinfo.ATTRIBUTE13
,tlinfo.ATTRIBUTE14
,tlinfo.ATTRIBUTE15
,tlinfo.CUSTOMER_ID
,tlinfo.CUSTOMER_SITE_USE_ID
,tlinfo.NORMALIZE
,tlinfo.LOCATION_ID
,tlinfo.SCHEDULE_DAY
,tlinfo.CUST_SHIP_SITE_ID
,tlinfo.AP_AR_TERM_ID
,tlinfo.CUST_TRX_TYPE_ID
,tlinfo.PROJECT_ID
,tlinfo.TASK_ID
,tlinfo.ORGANIZATION_ID
,tlinfo.EXPENDITURE_TYPE
,tlinfo.EXPENDITURE_ITEM_DATE
,tlinfo.TAX_GROUP_ID
,tlinfo.TAX_CODE_ID
,tlinfo.TAX_CLASSIFICATION_CODE
,tlinfo.TAX_INCLUDED
,tlinfo.DISTRIBUTION_SET_ID
,tlinfo.INV_RULE_ID
,tlinfo.ACCOUNT_RULE_ID
,tlinfo.SALESREP_ID
,tlinfo.APPROVED_BY
,tlinfo.STATUS
,tlinfo.INDEX_PERIOD_ID
,tlinfo.INDEX_TERM_INDICATOR
,tlinfo.PO_HEADER_ID
,tlinfo.CUST_PO_NUMBER
,tlinfo.RECEIPT_METHOD_ID
,tlinfo.LEGAL_ENTITY_ID
,tlinfo.PROJECT_ATTRIBUTE_CATEGORY
,tlinfo.PROJECT_ATTRIBUTE1
,tlinfo.PROJECT_ATTRIBUTE2
,tlinfo.PROJECT_ATTRIBUTE3
,tlinfo.PROJECT_ATTRIBUTE4
,tlinfo.PROJECT_ATTRIBUTE5
,tlinfo.PROJECT_ATTRIBUTE6
,tlinfo.PROJECT_ATTRIBUTE7
,tlinfo.PROJECT_ATTRIBUTE8
,tlinfo.PROJECT_ATTRIBUTE9
,tlinfo.PROJECT_ATTRIBUTE10
,tlinfo.PROJECT_ATTRIBUTE11
,tlinfo.PROJECT_ATTRIBUTE12
,tlinfo.PROJECT_ATTRIBUTE13
,tlinfo.PROJECT_ATTRIBUTE14
,tlinfo.PROJECT_ATTRIBUTE15
,tlinfo.VAR_RENT_INV_ID
,tlinfo.VAR_RENT_TYPE
,tlinfo.CHANGED_FLAG
,tlinfo.NORM_START_DATE
,tlinfo.TERM_TEMPLATE_ID
,tlinfo.EVENT_TYPE_CODE
,tlinfo.LEASE_STATUS
,tlinfo.NORM_END_DATE
,tlinfo.RECOVERABLE_FLAG
,tlinfo.PERIOD_BILLREC_ID
,tlinfo.AMOUNT_TYPE
,tlinfo.REC_AGR_LINE_ID
,tlinfo.GROUPING_RULE_ID
,tlinfo.AREA_TYPE_CODE
,tlinfo.AREA
,l_term_comments);
SELECT opex_agr_id,index_period_id,var_rent_inv_id,rec_agr_line_id
FROM pn_payment_terms_all
where payment_term_id = p_payment_term_id;
SELECT pil.index_lease_number
INTO l_agreement_number
FROM pn_index_lease_periods_all pilp,
pn_index_leases_all pil
WHERE pilp.index_lease_id = pil.index_lease_id
AND pilp.index_period_id = rec1.index_period_id;
SELECT agr_num
INTO l_agreement_number
FROM pn_opex_agreements_all
WHERE agreement_id = rec1.opex_agr_id;
SELECT var.rent_num
INTO l_agreement_number
FROM pn_var_rents_all var,
pn_var_rent_inv_all inv
WHERE var.var_rent_id = inv.var_rent_id
AND inv.var_rent_inv_id = rec1.var_rent_inv_id;
SELECT rec.rec_agreement_num
INTO l_agreement_number
FROM pn_rec_agreements_all rec,
pn_rec_agr_lines_all rlines
WHERE rlines.rec_agreement_id = rec.rec_agreement_id
AND rlines.rec_agr_line_id = rec1.rec_agr_line_id;
SELECT NULL
INTO l_agreement_number
FROM DUAL;
SELECT 1 flag
FROM pn_opex_est_payments_all
WHERE (payment_term_id = p_payment_term_id or catch_up_term_id = p_payment_term_id)
AND agreement_id = p_opex_agr_id;
SELECT opex_agr_id,
opex_recon_id,
index_period_id,
var_rent_inv_id,
rec_agr_line_id,
source_code
FROM pn_payment_terms_all
where payment_term_id = p_payment_term_id;
UPDATE pn_payment_terms_all
SET changed_flag = x_changed_flag
WHERE payment_term_id = NVL(x_payment_term_id ,payment_term_id )
AND var_rent_inv_id = x_var_rent_inv_id;