[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
x_hold_id in out nocopy number, --5128839
X_Invoice_Id NUMBER,
X_Line_Location_Id NUMBER,
X_Hold_Lookup_Code VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Held_By NUMBER,
X_Hold_Date DATE,
X_Hold_Reason VARCHAR2,
X_Release_Lookup_Code VARCHAR2,
X_Release_Reason VARCHAR2,
X_Status_Flag VARCHAR2,
X_Last_Update_Login NUMBER,
X_Creation_Date DATE,
X_Created_By NUMBER,
X_Responsibility_Id NUMBER,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Org_Id NUMBER,
X_calling_sequence IN VARCHAR2
) IS
CURSOR C IS SELECT rowid FROM AP_HOLDS
WHERE invoice_id = X_Invoice_Id
AND ( (line_location_id = X_Line_Location_Id)
or (line_location_id is NULL and X_Line_Location_Id is NULL))
AND hold_lookup_code = X_Hold_Lookup_Code;
current_calling_sequence := 'AP_HOLDS_PKG.INSERT_ROW<-' ||
X_calling_sequence;
select ap_holds_s.nextval
into x_hold_id
from dual;
debug_info := 'Insert into AP_HOLDS';
INSERT INTO AP_HOLDS(
hold_id,
invoice_id,
line_location_id,
hold_lookup_code,
last_update_date,
last_updated_by,
held_by,
hold_date,
hold_reason,
release_lookup_code,
release_reason,
status_flag,
last_update_login,
creation_date,
created_by,
responsibility_id,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category,
org_id
) VALUES (
x_hold_id,
X_Invoice_Id,
X_Line_Location_Id,
X_Hold_Lookup_Code,
X_Last_Update_Date,
X_Last_Updated_By,
X_Held_By,
X_Hold_Date,
X_Hold_Reason,
X_Release_Lookup_Code,
X_Release_Reason,
X_Status_Flag,
X_Last_Update_Login,
X_Creation_Date,
X_Created_By,
X_Responsibility_Id,
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_Attribute_Category,
X_Org_Id
);
SELECT nvl(user_releaseable_flag,'N'),
nvl(initiate_workflow_flag,'N')
INTO l_user_releaseable_flag,
l_initiate_workflow_flag
FROM ap_hold_codes
WHERE hold_lookup_code = X_Hold_Lookup_Code;
END Insert_Row;
SELECT *
FROM AP_HOLDS
WHERE rowid = X_Rowid
FOR UPDATE of Invoice_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Invoice_Id NUMBER,
X_Line_Location_Id NUMBER,
X_Hold_Lookup_Code VARCHAR2,
X_Last_Update_Date DATE,
X_Last_Updated_By NUMBER,
X_Held_By NUMBER,
X_Hold_Date DATE,
X_Hold_Reason VARCHAR2,
X_Release_Lookup_Code VARCHAR2,
X_Release_Reason VARCHAR2,
X_Status_Flag VARCHAR2,
X_Last_Update_Login NUMBER,
X_Responsibility_Id NUMBER,
X_Attribute1 VARCHAR2,
X_Attribute2 VARCHAR2,
X_Attribute3 VARCHAR2,
X_Attribute4 VARCHAR2,
X_Attribute5 VARCHAR2,
X_Attribute6 VARCHAR2,
X_Attribute7 VARCHAR2,
X_Attribute8 VARCHAR2,
X_Attribute9 VARCHAR2,
X_Attribute10 VARCHAR2,
X_Attribute11 VARCHAR2,
X_Attribute12 VARCHAR2,
X_Attribute13 VARCHAR2,
X_Attribute14 VARCHAR2,
X_Attribute15 VARCHAR2,
X_Attribute_Category VARCHAR2,
X_Wf_Status VARCHAR2,
X_calling_sequence IN VARCHAR2
) IS
l_invoice_amount AP_INVOICES_ALL.INVOICE_AMOUNT%TYPE;
current_calling_sequence := 'AP_HOLDS_PKG.UPDATE_ROW<-' ||
X_calling_sequence;
debug_info := 'Update AP_HOLDS';
UPDATE AP_HOLDS
SET
invoice_id = X_Invoice_Id,
line_location_id = X_Line_Location_Id,
hold_lookup_code = X_Hold_Lookup_Code,
last_update_date = X_Last_Update_Date,
last_updated_by = X_Last_Updated_By,
held_by = X_Held_By,
hold_date = X_Hold_Date,
hold_reason = X_Hold_Reason,
release_lookup_code = X_Release_Lookup_Code,
release_reason = X_Release_Reason,
status_flag = X_Status_Flag,
last_update_login = X_Last_Update_Login,
Responsibility_Id = X_Responsibility_Id,
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,
attribute_category = X_Attribute_Category,
/* bug 5206670. Hold Workflow */
wf_status = Decode(X_Wf_Status, 'STARTED', 'MANUALLYRELEASED',
X_WF_Status)
WHERE rowid = X_Rowid;
SELECT invoice_amount,
payment_status_flag,
invoice_type_lookup_code
INTO l_invoice_amount,
l_payment_status_flag,
l_invoice_type_lookup_code
FROM ap_invoices
WHERE invoice_id = x_invoice_id;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2,
X_calling_sequence IN VARCHAR2) IS
current_calling_sequence VARCHAR2(2000);
current_calling_sequence := 'AP_HOLDS_PKG.DELETE_ROW<-' ||
X_calling_sequence;
Select invoice_id
Into l_invoice_id
From ap_holds
Where rowid = X_Rowid;
debug_info := 'Delete from AP_HOLDS';
DELETE FROM AP_HOLDS
WHERE rowid = X_Rowid;
END Delete_Row;
PROCEDURE insert_single_hold (X_invoice_id IN number,
X_hold_lookup_code IN varchar2,
X_hold_type IN varchar2 DEFAULT NULL,
X_hold_reason IN varchar2 DEFAULT NULL,
X_held_by IN number DEFAULT NULL,
X_calling_sequence IN varchar2 DEFAULT NULL)
IS
current_calling_sequence VARCHAR2(2000);
select description
from ap_hold_codes
where hold_type = nvl(X_hold_type,hold_type)
and hold_lookup_code = X_hold_lookup_code;
'AP_HOLDS_PKG.insert_single_hold<-'||
X_calling_sequence;
l_api_name := 'Insert_Single_Hold';
debug_info := 'Select from AP_HOLD_CODES';
debug_info := 'Insert into AP_HOLDS';
SELECT ap_holds_s.nextval
INTO l_hold_id
FROM DUAL;
INSERT INTO AP_HOLDS
(INVOICE_ID, HOLD_LOOKUP_CODE, HOLD_DATE,
CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE, LAST_UPDATED_BY,
HELD_BY, HOLD_REASON, ORG_ID, HOLD_ID)
SELECT
X_invoice_id, X_hold_lookup_code, SYSDATE,
FND_GLOBAL.user_id, -- 7299826
SYSDATE,
FND_GLOBAL.LOGIN_ID, -- 7299826
SYSDATE,
FND_GLOBAL.user_id, -- 7299826
nvl(X_held_by,FND_GLOBAL.user_id), -- 7299826
l_hold_reason,
ORG_ID,L_HOLD_ID
FROM ap_invoices
WHERE invoice_id = X_invoice_id
AND not exists
(SELECT 'Already on this hold'
FROM ap_holds
WHERE invoice_id = X_invoice_id
AND hold_lookup_code = X_hold_lookup_code
AND release_lookup_code IS NULL);
END insert_single_hold;
l_last_updated_by ap_invoices.last_updated_by%TYPE;
l_last_update_login ap_invoices.last_update_login%TYPE;
select last_updated_by,
last_update_login
from ap_invoices
where invoice_id = X_invoice_id;
debug_info := 'Select from AP_INVOICES';
fetch invoice_who_cursor into l_last_updated_by, l_last_update_login;
debug_info := 'Update AP_HOLDS';
UPDATE ap_holds
SET release_lookup_code = X_release_lookup_code,
release_reason = (SELECT description
FROM ap_lookup_codes
WHERE lookup_type = 'HOLD CODE'
AND lookup_code = X_release_lookup_code),
last_updated_by = FND_GLOBAL.user_id, -- 7299826
last_update_date = SYSDATE,
last_update_login = FND_GLOBAL.login_id -- 7299826
WHERE invoice_id = X_invoice_id
AND held_by = nvl(X_held_by,held_by) -- 7299826
AND release_lookup_code IS NULL
AND hold_lookup_code = X_hold_lookup_code;
X_last_updated_by IN NUMBER,
X_last_update_date IN DATE,
X_holds_count IN OUT NOCOPY NUMBER,
X_approval_status_lookup_code IN OUT NOCOPY VARCHAR2,
X_calling_sequence IN VARCHAR2)
IS
l_success BOOLEAN := TRUE;
select ap_invoices_pkg.get_holds_count(invoice_id),
ap_invoices_pkg.get_approval_status(
invoice_id,
invoice_amount,
payment_status_flag,
invoice_type_lookup_code)
from ap_invoices
where invoice_id = X_invoice_id;
select hold_lookup_code
from ap_holds
where invoice_id = x_invoice_id
and hold_lookup_code IN ('TAX AMOUNT RANGE','TAX VARIANCE')
and release_lookup_code IS NULL;
debug_info := 'Update AP_INVOICE_DISTRIBUTIONS';
UPDATE ap_invoice_distributions D
SET final_match_flag = 'N'
WHERE D.invoice_id = X_invoice_id
AND ((X_hold_lookup_code = 'CANT CLOSE PO') OR
((X_hold_lookup_code = 'ALL')
AND EXISTS(SELECT 'X'
FROM AP_HOLDS H
WHERE H.invoice_id = X_invoice_id
AND H.hold_lookup_code = 'CANT CLOSE PO'
AND H.release_lookup_code IS NULL)));
debug_info := 'Update AP_HOLDS';
UPDATE ap_holds H
SET H.release_lookup_code = X_release_lookup_code,
H.release_reason = X_release_reason,
H.responsibility_id = X_responsibility_id,
H.last_update_date = X_last_update_date,
H.last_updated_by = X_last_updated_by
WHERE H.invoice_id = X_invoice_id
AND X_hold_lookup_code IN (H.hold_lookup_code, 'ALL')
AND H.hold_lookup_code not in ('DIST VARIANCE', 'NO RATE',
'CANT FUNDS CHECK', 'INSUFFICIENT FUNDS',
'FINAL MATCHING', 'FUTURE PERIOD', 'CANT TRY PO CLOSE',
'DIST ACCT INVALID', 'ERV ACCT INVALID', 'LIAB ACCT INVALID')
AND H.release_lookup_code is null;
||', X_last_updated_by= '||TO_CHAR(X_last_updated_by)
||', X_last_update_date= '||TO_CHAR(X_last_update_date)
||', X_holds_count= '||TO_CHAR(X_holds_count)
||', X_approval_status_lookup_code= '||
X_approval_status_lookup_code
);