[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| INSERT_ROW - insert a row into PN_TERM_TEMPLATES.
| DELETE_ROW - deletes a row from PN_TERM_TEMPLATES.
| UPDATE_ROW - updates a row from PN_TERM_TEMPLATES.
| LOCKS_ROW - will check if a row has been modified since being queried by form.
|
|
| HISTORY
| 08-MAY-01 jbreyes o Created
| 13-DEC-01 Mrinal Misra o Added dbdrv command.
| 15-JAN-02 Mrinal Misra o In dbdrv command changed phase=pls to phase=plb.
| Added checkfile.Ref. Bug# 2184724.
| 05-JUN-02 Daniel Thota o Added org_id as a parameter to insert_row
| as part of Multi-Org
| 20-JUL-02 Mrinal Misra o Changed lock_row procedure for currency_code made
| null column in base table.
| 05-AUG-02 Mrinal Misra o Added x_location_id parameter for location_id
| column in table.
| 18-MAY-04 ftanudja o Add logic for term template type. 3539408.
| 15-JUL-2005 ftanudja o R12: add tax_classification_code. 4495054.
| 25-Jul-05 sdmahesh o overloaded delete_row proc to take PK as parameter
+===========================================================================*/
-------------------------------------------------------------------------------
-- PROCDURE : INSERT_ROW
-- INVOKED FROM : insert_row procedure
-- PURPOSE : inserts the row
-- HISTORY :
-- 13-JUL-05 sdmahesh o Bug 4284035 - Replaced pn_term_templates with _ALL
-- table.
-------------------------------------------------------------------------------
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2
,x_term_template_id IN OUT NOCOPY NUMBER
,x_name IN VARCHAR2
,x_set_of_books_id IN NUMBER
,x_currency_code IN VARCHAR2
,x_last_update_date IN DATE
,x_last_updated_by IN NUMBER
,x_creation_date IN DATE
,x_created_by IN NUMBER
,x_normalize IN VARCHAR2
,x_schedule_day IN NUMBER
,x_payment_purpose_code IN VARCHAR2
,x_payment_term_type_code IN VARCHAR2
,x_accrual_account_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_vendor_id IN NUMBER
,x_vendor_site_id IN NUMBER
,x_customer_id IN NUMBER
,x_customer_site_use_id IN NUMBER
,x_cust_ship_site_id IN NUMBER
,x_ap_ar_term_id IN NUMBER
,x_cust_trx_type_id IN NUMBER
,x_tax_group_id IN NUMBER
,x_tax_code_id IN NUMBER
,x_tax_classification_code 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_tax_included IN VARCHAR2
,x_po_header_id IN NUMBER
,x_cust_po_number IN VARCHAR2
,x_receipt_method_id IN NUMBER
,x_location_id IN NUMBER
,x_active IN VARCHAR2
,x_description IN VARCHAR2
,x_term_template_type IN VARCHAR2
,x_last_update_login 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_org_id NUMBER
)
IS
CURSOR c IS
SELECT ROWID
FROM pn_term_templates_all
WHERE term_template_id = x_term_template_id;
INSERT INTO pn_term_templates_all
(
term_template_id
,name
,set_of_books_id
,currency_code
,last_update_date
,last_updated_by
,creation_date
,created_by
,normalize
,schedule_day
,payment_purpose_code
,payment_term_type_code
,accrual_account_id
,project_id
,task_id
,organization_id
,expenditure_type
,expenditure_item_date
,vendor_id
,vendor_site_id
,customer_id
,customer_site_use_id
,cust_ship_site_id
,ap_ar_term_id
,cust_trx_type_id
,tax_group_id
,tax_code_id
,tax_classification_code
,distribution_set_id
,inv_rule_id
,account_rule_id
,salesrep_id
,tax_included
,po_header_id
,cust_po_number
,receipt_method_id
,location_id
,active
,description
,term_template_type
,last_update_login
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,org_id
)
VALUES
(
NVL(x_term_template_id,pn_term_templates_s.NEXTVAL)
,x_name
,x_set_of_books_id
,x_currency_code
,x_last_update_date
,x_last_updated_by
,x_creation_date
,x_created_by
,x_normalize
,x_schedule_day
,x_payment_purpose_code
,x_payment_term_type_code
,x_accrual_account_id
,x_project_id
,x_task_id
,x_organization_id
,x_expenditure_type
,x_expenditure_item_date
,x_vendor_id
,x_vendor_site_id
,x_customer_id
,x_customer_site_use_id
,x_cust_ship_site_id
,x_ap_ar_term_id
,x_cust_trx_type_id
,x_tax_group_id
,x_tax_code_id
,x_tax_classification_code
,x_distribution_set_id
,x_inv_rule_id
,x_account_rule_id
,x_salesrep_id
,x_tax_included
,x_po_header_id
,x_cust_po_number
,x_receipt_method_id
,x_location_id
,x_active
,x_description
,x_term_template_type
,x_last_update_login
,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_org_id
)
RETURNING term_template_id INTO x_term_template_id;
END insert_row;
PROCEDURE update_row (
x_rowid IN VARCHAR2
,x_term_template_id IN NUMBER
,x_name IN VARCHAR2
,x_set_of_books_id IN NUMBER
,x_currency_code IN VARCHAR2
,x_last_update_date IN DATE
,x_last_updated_by IN NUMBER
,x_normalize IN VARCHAR2
,x_schedule_day IN NUMBER
,x_payment_purpose_code IN VARCHAR2
,x_payment_term_type_code IN VARCHAR2
,x_accrual_account_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_vendor_id IN NUMBER
,x_vendor_site_id IN NUMBER
,x_customer_id IN NUMBER
,x_customer_site_use_id IN NUMBER
,x_cust_ship_site_id IN NUMBER
,x_ap_ar_term_id IN NUMBER
,x_cust_trx_type_id IN NUMBER
,x_tax_group_id IN NUMBER
,x_tax_code_id IN NUMBER
,x_tax_classification_code 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_tax_included IN VARCHAR2
,x_po_header_id IN NUMBER
,x_cust_po_number IN VARCHAR2
,x_receipt_method_id IN NUMBER
,x_location_id IN NUMBER
,x_active IN VARCHAR2
,x_description IN VARCHAR2
,x_term_template_type IN VARCHAR2
,x_last_update_login 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
)
IS
l_return_status VARCHAR2 (30) := NULL;
SELECT org_id
INTO l_org_id
FROM pn_term_templates_all
WHERE term_template_id = x_term_template_id;
UPDATE pn_term_templates_all
SET name = x_name
,set_of_books_id = x_set_of_books_id
,currency_code = x_currency_code
,last_update_date = x_last_update_date
,last_updated_by = x_last_updated_by
,normalize = x_normalize
,schedule_day = x_schedule_day
,payment_purpose_code = x_payment_purpose_code
,payment_term_type_code = x_payment_term_type_code
,accrual_account_id = x_accrual_account_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
,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
,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
,tax_group_id = x_tax_group_id
,tax_code_id = x_tax_code_id
,tax_classification_code = x_tax_classification_code
,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
,tax_included = x_tax_included
,po_header_id = x_po_header_id
,cust_po_number = x_cust_po_number
,receipt_method_id = x_receipt_method_id
,location_id = x_location_id
,active = x_active
,description = x_description
,term_template_type = x_term_template_type
,last_update_login = x_last_update_login
,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
WHERE term_template_id = x_term_template_id;
END update_row;
SELECT *
FROM pn_term_templates_all
WHERE term_template_id = x_term_template_id
FOR UPDATE OF term_template_id NOWAIT;
PROCEDURE delete_row
(
x_rowid IN VARCHAR2
)
IS
BEGIN
DELETE FROM pn_term_templates_all
WHERE ROWID = x_rowid;
END delete_row;
PROCEDURE delete_row
(
x_term_template_id IN NUMBER
)
IS
BEGIN
DELETE FROM pn_term_templates_all
WHERE term_template_id = x_term_template_id;
END delete_row;
SELECT 1
INTO l_dummy
FROM DUAL
WHERE NOT EXISTS ( SELECT 1
FROM pn_term_templates_all
WHERE name = x_name
AND ((x_term_template_id IS NULL)
OR (term_template_id <> x_term_template_id)
AND org_id = x_org_id
));