[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(max(notification_iteration),0) + 1
INTO l_notif_iter
FROM AP_APINV_APPROVERS
WHERE Invoice_Key = p_invoice_key;
SELECT invoice_amount
INTO l_amount
FROM ap_invoices_all
WHERE invoice_id = l_invoice_id;
SELECT displayed_field
into l_tr_reason
FROM ap_lookup_codes
WHERE lookup_code = l_reason(i)
and lookup_type = 'NLS TRANSLATION';
l_h_hist.last_update_date := sysdate;
l_h_hist.last_updated_by :=
FND_PROFILE.VALUE('AP_IAW_USER');
l_h_hist.last_update_login := -1;
Insert_Header_History(
p_inv_aprvl_hist => l_h_hist);
UPDATE AP_INVOICES_ALL
SET WFApproval_Status = 'NOT REQUIRED'
WHERE Invoice_Id = l_invoice_id
AND WFApproval_Status = 'INITIATED';
UPDATE AP_INVOICE_LINES_ALL
SET WFApproval_Status = 'NOT REQUIRED'
WHERE Invoice_Id = l_invoice_id
AND WFApproval_Status = 'INITIATED';
SELECT l_tr_reason || ', ' || displayed_field
into l_tr_reason
FROM ap_lookup_codes
WHERE lookup_code = l_reason(i)
and lookup_type = 'NLS TRANSLATION';
l_h_hist.last_update_date := sysdate;
l_h_hist.last_updated_by :=
FND_PROFILE.VALUE('AP_IAW_USER');
l_h_hist.last_update_login := -1;
Insert_Header_History(
p_inv_aprvl_hist => l_h_hist);
UPDATE AP_INVOICES_ALL
SET WFApproval_Status = 'REJECTED'
WHERE Invoice_Id = l_invoice_id
AND WFApproval_Status = 'INITIATED';
UPDATE AP_INVOICE_LINES_ALL
SET WFApproval_Status = 'REJECTED'
WHERE Invoice_Id = l_invoice_id
AND WFApproval_Status = 'INITIATED';
SELECT line_number, amount
FROM ap_invoice_lines_all
WHERE po_header_id is not null
AND invoice_id = l_invoice_id
AND wfapproval_status = 'INITIATED';
SELECT displayed_field
into l_tr_reason
FROM ap_lookup_codes
WHERE lookup_code = l_reason(i)
and lookup_type = 'NLS TRANSLATION';
l_l_hist.last_updated_by :=
FND_PROFILE.VALUE('AP_IAW_USER');
l_l_hist.last_update_date := sysdate;
l_l_hist.last_update_login := -1;
Insert_Line_History(
p_line_aprvl_hist => l_l_hist);
UPDATE AP_INVOICE_LINES_ALL
SET WFApproval_Status = 'NOT REQUIRED'
WHERE Invoice_Id = l_invoice_id
AND PO_Header_Id IS NOT NULL
AND WFApproval_Status = 'INITIATED';
SELECT 1, Role_Name
INTO l_pend, l_name
FROM AP_APINV_APPROVERS
WHERE Notification_Status = 'PEND'
AND Invoice_Key = itemkey
AND rownum = 1;
SELECT nvl(max(notification_iteration),0) + 1
INTO l_not_iteration
FROM AP_APINV_APPROVERS
WHERE Invoice_Key = itemkey;
UPDATE AP_APINV_APPROVERS
SET Notification_Iteration = l_not_iteration
,Notification_Key = itemkey || '_' || l_not_iteration
WHERE Role_Name = l_name
AND Invoice_Key = itemkey;
SELECT sum(DECODE(Notification_Status, 'SENT', 1, 0)),
sum(DECODE(Notification_Status, 'COMP', 1, 0))
INTO l_sent, l_comp
FROM AP_APINV_APPROVERS
WHERE Invoice_Key = itemkey
GROUP BY Invoice_Key;
UPDATE AP_INVOICES_ALL
SET WFApproval_Status = 'NOT REQUIRED'
WHERE Invoice_Id = l_invoice_id
AND WFApproval_Status = 'INITIATED';
UPDATE AP_INVOICE_LINES_ALL
SET WFApproval_Status = 'NOT REQUIRED'
WHERE Invoice_Id = l_invoice_id
AND WFApproval_Status = 'INITIATED';
DELETE FROM AP_APINV_APPROVERS
WHERE Invoice_Id = l_invoice_id;
UPDATE AP_INVOICES_ALL
SET WFApproval_Status = 'WFAPPROVED'
WHERE WFApproval_Status = 'INITIATED'
AND Invoice_Id IN (SELECT DISTINCT Invoice_ID
FROM AP_APINV_APPROVERS
WHERE Invoice_Id = l_invoice_id
and Invoice_Iteration = l_iteration
AND Line_Number IS NULL);
--in the subselects, we do not need
--to filter by approval_status
-- since any rejection
--would have set the Line's
--wfapproval_status to 'Rejected'
--already, so the line will
-- not even be selected for update by the main
--part of the query.
UPDATE AP_INVOICE_LINES_ALL
SET WFApproval_Status = 'WFAPPROVED'
WHERE Invoice_Id = l_invoice_id
AND WFApproval_Status = 'INITIATED'
AND Line_Number IN (SELECT DISTINCT Line_Number
FROM AP_APINV_APPROVERS
WHERE invoice_id = l_invoice_id
and Invoice_Iteration = l_iteration);
UPDATE AP_INVOICES_ALL
SET WFApproval_Status = 'NOT REQUIRED'
WHERE Invoice_Id = l_invoice_id
AND WFApproval_Status = 'INITIATED';
UPDATE AP_INVOICE_LINES_ALL
SET WFApproval_Status = 'NOT REQUIRED'
WHERE Invoice_Id = l_invoice_id
AND WFApproval_Status = 'INITIATED';
DELETE FROM AP_APINV_APPROVERS
WHERE Invoice_Id = l_invoice_id;
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE project_id =l_ids(l_rec);
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE project_id =l_ids(l_rec);
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE invoice_distribution_id = l_ids(l_rec);
--Insert record into ap_apinv_approvers
INSERT INTO AP_APINV_APPROVERS(
INVOICE_ID,
INVOICE_ITERATION,
INVOICE_KEY,
LINE_NUMBER,
NOTIFICATION_STATUS,
ROLE_NAME,
ORIG_SYSTEM,
ORIG_SYSTEM_ID,
DISPLAY_NAME,
APPROVER_CATEGORY,
API_INSERTION,
AUTHORITY,
APPROVAL_STATUS,
ACTION_TYPE_ID,
GROUP_OR_CHAIN_ID,
OCCURRENCE,
SOURCE,
ITEM_CLASS,
ITEM_ID,
ITEM_CLASS_ORDER_NUMBER,
ITEM_ORDER_NUMBER,
SUB_LIST_ORDER_NUMBER,
ACTION_TYPE_ORDER_NUMBER,
GROUP_OR_CHAIN_ORDER_NUMBER,
MEMBER_ORDER_NUMBER,
APPROVER_ORDER_NUMBER,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID )
VALUES(
l_invoice_id,
l_iteration,
itemkey,
l_line_num,
'PEND',
l_next_approver.NAME,
l_next_approver.ORIG_SYSTEM,
l_next_approver.ORIG_SYSTEM_ID,
l_next_approver.DISPLAY_NAME,
l_next_approver.APPROVER_CATEGORY,
l_next_approver.API_INSERTION,
l_next_approver.AUTHORITY,
l_next_approver.APPROVAL_STATUS,
l_next_approver.ACTION_TYPE_ID,
l_next_approver.GROUP_OR_CHAIN_ID,
l_next_approver.OCCURRENCE,
l_next_approver.SOURCE,
l_class(l_rec),
l_ids(l_rec),
l_next_approver.ITEM_CLASS_ORDER_NUMBER,
l_next_approver.ITEM_ORDER_NUMBER,
l_next_approver.SUB_LIST_ORDER_NUMBER,
l_next_approver.ACTION_TYPE_ORDER_NUMBER,
l_next_approver.GROUP_OR_CHAIN_ORDER_NUMBER,
l_next_approver.MEMBER_ORDER_NUMBER,
l_next_approver.APPROVER_ORDER_NUMBER,
nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
sysdate,
nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
-1),
nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
sysdate,
200,
0,
sysdate,
0);
l_debug_info := 'after insert';
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE project_id = l_next_approver.item_id;
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE project_id = l_next_approver.item_id;
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE invoice_distribution_id = l_next_approver.item_id;
--Insert record into ap_apinv_approvers
INSERT INTO AP_APINV_APPROVERS(
INVOICE_ID,
INVOICE_ITERATION,
INVOICE_KEY,
LINE_NUMBER,
NOTIFICATION_STATUS,
ROLE_NAME,
ORIG_SYSTEM,
ORIG_SYSTEM_ID,
DISPLAY_NAME,
APPROVER_CATEGORY,
API_INSERTION,
AUTHORITY,
APPROVAL_STATUS,
ACTION_TYPE_ID,
GROUP_OR_CHAIN_ID,
OCCURRENCE,
SOURCE,
ITEM_CLASS,
ITEM_ID,
ITEM_CLASS_ORDER_NUMBER,
ITEM_ORDER_NUMBER,
SUB_LIST_ORDER_NUMBER,
ACTION_TYPE_ORDER_NUMBER,
GROUP_OR_CHAIN_ORDER_NUMBER,
MEMBER_ORDER_NUMBER,
APPROVER_ORDER_NUMBER,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
REQUEST_ID )
VALUES(
l_invoice_id,
l_iteration,
itemkey,
l_line_num,
'PEND',
l_next_approver.NAME,
l_next_approver.ORIG_SYSTEM,
l_next_approver.ORIG_SYSTEM_ID,
l_next_approver.DISPLAY_NAME,
l_next_approver.APPROVER_CATEGORY,
l_next_approver.API_INSERTION,
l_next_approver.AUTHORITY,
l_next_approver.APPROVAL_STATUS,
l_next_approver.ACTION_TYPE_ID,
l_next_approver.GROUP_OR_CHAIN_ID,
l_next_approver.OCCURRENCE,
l_next_approver.SOURCE,
l_next_approver.item_class,
l_next_approver.item_id,
l_next_approver.ITEM_CLASS_ORDER_NUMBER,
l_next_approver.ITEM_ORDER_NUMBER,
l_next_approver.SUB_LIST_ORDER_NUMBER,
l_next_approver.ACTION_TYPE_ORDER_NUMBER,
l_next_approver.GROUP_OR_CHAIN_ORDER_NUMBER,
l_next_approver.MEMBER_ORDER_NUMBER,
l_next_approver.APPROVER_ORDER_NUMBER,
nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
sysdate,
nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
-1),
nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1),
sysdate,
200,
0,
sysdate,
0);
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE project_id =l_ids(l_rec);
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE project_id =l_ids(l_rec);
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE invoice_distribution_id = l_ids(l_rec);
--Insert record into ap_approvers_list_gt
INSERT INTO AP_APPROVERS_LIST_GT(
LINE_NUMBER,
ROLE_NAME,
ORIG_SYSTEM,
ORIG_SYSTEM_ID,
DISPLAY_NAME,
APPROVER_CATEGORY,
API_INSERTION,
AUTHORITY,
APPROVAL_STATUS,
ITEM_CLASS,
ITEM_ID,
APPROVER_ORDER_NUMBER)
VALUES(
l_line_num,
l_next_approver.NAME,
l_next_approver.ORIG_SYSTEM,
l_next_approver.ORIG_SYSTEM_ID,
l_next_approver.DISPLAY_NAME,
l_next_approver.APPROVER_CATEGORY,
l_next_approver.API_INSERTION,
l_next_approver.AUTHORITY,
l_next_approver.APPROVAL_STATUS,
l_class(l_rec),
l_ids(l_rec),
l_next_approver.APPROVER_ORDER_NUMBER);
l_debug_info := 'after insert';
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE project_id = l_next_approver.item_id;
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE project_id = l_next_approver.item_id;
SELECT Invoice_Line_Number
INTO l_line_num
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE invoice_distribution_id = l_next_approver.item_id;
--Insert record into ap_approvers_list_gt
INSERT INTO AP_APINV_APPROVERS(
LINE_NUMBER,
ROLE_NAME,
ORIG_SYSTEM,
ORIG_SYSTEM_ID,
DISPLAY_NAME,
APPROVER_CATEGORY,
API_INSERTION,
AUTHORITY,
APPROVAL_STATUS,
ITEM_CLASS,
ITEM_ID,
APPROVER_ORDER_NUMBER)
VALUES(
l_line_num,
l_next_approver.NAME,
l_next_approver.ORIG_SYSTEM,
l_next_approver.ORIG_SYSTEM_ID,
l_next_approver.DISPLAY_NAME,
l_next_approver.APPROVER_CATEGORY,
l_next_approver.API_INSERTION,
l_next_approver.AUTHORITY,
l_next_approver.APPROVAL_STATUS,
l_next_approver.item_class,
l_next_approver.item_id,
l_next_approver.APPROVER_ORDER_NUMBER);
SELECT DECODE(nvl(Line_Number,''),'','HEADER','LINE'), invoice_key
INTO l_level, l_invoice_key
FROM AP_APINV_APPROVERS
WHERE Notification_Key = itemkey
AND rownum = 1;
--update approvers table
UPDATE AP_APINV_APPROVERS
SET Notification_Status = 'SENT'
WHERE Notification_Key = itemkey;
--update appropriate history table
IF l_level = 'HEADER' THEN
Insert_Header_History(itemtype, itemkey, p_type => 'NEW');
Insert_Line_History(itemtype, itemkey, p_type => 'NEW');
SELECT supervisor_id
INTO l_esc_approver_id
FROM per_employees_current_x
WHERE employee_id = l_approver_id;
l_esc_approver.api_insertion := ame_util.apiInsertion;
--update AME
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionTypeIn => 'APINV',
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.noResponseStatus,
approverNameIn => l_role,
itemClassIn => ame_util.headerItemClassName,
itemIdIn => to_char(l_invoice_id),
forwardeeIn => l_esc_approver);
--update history for non-responding approver
Update ap_inv_aprvl_hist_all
Set Response = 'ESCALATED'
,Last_Update_Date = sysdate
,Last_Updated_By = nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
,Last_Update_Login =
nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1)
Where invoice_id = l_invoice_id
AND iteration = l_iteration
AND approver_id = l_approver_id;
Insert_Header_History(itemtype, itemkey, p_type => 'ESC');
SELECT Item_Class, Item_Id
FROM AP_APINV_APPROVERS
WHERE Notification_Key = itemkey;
SELECT supervisor_id
INTO l_esc_approver_id
FROM per_employees_current_x
WHERE employee_id = l_approver_id;
l_esc_approver.api_insertion := ame_util.apiInsertion;
--update AME
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionTypeIn => 'APINV',
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.noResponseStatus,
approverNameIn => l_role,
itemClassIn => l_item_class,
itemIdIn => l_item_id,
forwardeeIn => l_esc_approver);
--update history for non-responding approver
Update ap_line_aprvl_hist_all
Set Response = 'ESCALATED'
,Last_Update_Date = sysdate
,Last_Updated_By = nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
,Last_Update_Login =
nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1)
Where invoice_id = l_invoice_id
AND approver_id = l_approver_id
AND notification_key = itemkey;
Insert_Line_History(itemtype, itemkey, p_type => 'ESC');
--but I want to avoid another select on the table
CURSOR Items_Cur(itemkey IN VARCHAR2) IS
SELECT Item_Class, Item_Id, Role_Name, Invoice_Key
FROM AP_APINV_APPROVERS
WHERE Notification_Key = itemkey;
--Update Approvers table
UPDATE AP_APINV_APPROVERS
SET Notification_status = 'COMP'
WHERE Notification_Key = itemkey;
SELECT DECODE(nvl(Line_Number,''),'','HEADER','LINE')
INTO l_level
FROM AP_APINV_APPROVERS
WHERE Notification_Key = itemkey
AND rownum = 1;
--update history at appropriate level
IF l_level = 'HEADER' THEN
update_header_history(itemtype,
actid,
itemkey);
update_line_history(itemtype,
actid,
itemkey);
--update AME status
--amy check with ame as to when updateApprovalStatuses will be available
--so I will not need to loop.
OPEN Items_Cur(itemkey);
--update AME with response
IF l_result = 'APPROVED' THEN
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverNameIn => l_name,
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id);
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.rejectStatus,
approverNameIn => l_name,
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id);
/*handles all the updates for an approvers response. This version called
* from the framework pages
*/
PROCEDURE Response_Handler(p_invoice_id IN NUMBER,
p_line_num IN NUMBER,
p_not_key IN VARCHAR2,
p_response IN VARCHAR2,
p_comments IN VARCHAR2 ) IS
--Define cursor for lines affected by notification
--Note that Invoice_Key s/b the same for all records in the cursor
--but I want to avoid another select on the table
CURSOR Items_Cur(l_not_key IN VARCHAR2, l_line_num IN NUMBER) IS
SELECT Item_Class, Item_Id, Role_Name, Invoice_Key
FROM AP_APINV_APPROVERS
WHERE Notification_Key = l_not_key
AND line_number = l_line_num;
--Update Approvers table
UPDATE AP_APINV_APPROVERS
SET Notification_status = 'COMP'
WHERE Notification_Key = p_not_key;
--update history at appropriate level
Update_Line_History(p_invoice_id,
p_line_num,
p_response,
p_comments);
--update AME status
--amy check with ame as to when updateApprovalStatuses will be available
--so I will not need to loop.
OPEN Items_Cur(p_not_key, p_line_num);
--update AME with response
IF l_result = 'WFAPPROVED' THEN
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(p_invoice_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverNameIn => l_name,
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id);
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(p_invoice_id),
approvalStatusIn => AME_UTIL.rejectStatus,
approverNameIn => l_name,
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id);
SELECT
PV.vendor_name,
AI.invoice_num,
AI.invoice_date,
AI.description,
decode(AI.source, 'ISP', u.user_name, null)
INTO
l_invoice_supplier_name,
l_invoice_number,
l_invoice_date,
l_invoice_description,
l_supplier_role
FROM
ap_invoices_all AI,
po_vendors PV,
po_vendor_sites_all PVS,
fnd_user u
WHERE
AI.invoice_id = l_invoice_id AND
AI.vendor_id = PV.vendor_id AND
AI.vendor_site_id = PVS.vendor_site_id(+) and
u.user_id = ai.created_by;
l_parameter_list.delete;
SELECT Invoice_Id, Invoice_Iteration, Notification_Key,
Notification_Iteration
INTO l_invoice_id, l_iteration, l_not_key, l_not_it
FROM AP_APINV_APPROVERS
WHERE Invoice_Key = itemkey
AND Notification_Status = 'PEND'
AND ROWNUM = 1;
SELECT Invoice_Id, Invoice_Iteration, Notification_Key,
Notification_Iteration, Role_Name, orig_system_id
INTO l_invoice_id, l_iteration, l_not_key, l_not_it
, l_role, l_orig_id
FROM AP_APINV_APPROVERS
WHERE Notification_Key = itemkey
AND ROWNUM = 1;
SELECT
PV.vendor_name,
PVS.vendor_site_code,
AI.invoice_num,
AI.invoice_date,
AI.description,
NVL(AI.invoice_amount, 0),
AI.invoice_currency_code
INTO
l_invoice_supplier_name,
l_invoice_supplier_site,
l_invoice_number,
l_invoice_date,
l_invoice_description,
l_invoice_total,
l_invoice_currency_code
FROM
ap_invoices_all AI,
po_vendors PV,
po_vendor_sites_all PVS
WHERE
AI.invoice_id = l_invoice_id AND
AI.vendor_id = PV.vendor_id AND
AI.vendor_site_id = PVS.vendor_site_id(+);
PROCEDURE Insert_Header_History(
p_inv_aprvl_hist IN ap_iaw_pkg.r_inv_aprvl_hist) IS
l_api_name CONSTANT VARCHAR2(200) := 'Insert_Header_History';
SELECT AP_INV_APRVL_HIST_S.nextval
INTO l_hist_id
FROM dual;
SELECT max(notification_order) + 1
INTO l_not_cnt
FROM ap_inv_aprvl_hist_all
WHERE invoice_id = p_inv_aprvl_hist.invoice_id
AND iteration = p_inv_aprvl_hist.iteration;
--insert into the history table
INSERT INTO AP_INV_APRVL_HIST_ALL
(APPROVAL_HISTORY_ID
,INVOICE_ID
,ITERATION
,RESPONSE
,APPROVER_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID
,AMOUNT_APPROVED
,NOTIFICATION_ORDER)
VALUES (
l_hist_id
,p_inv_aprvl_hist.invoice_id
,p_inv_aprvl_hist.iteration
,p_inv_aprvl_hist.response
,p_inv_aprvl_hist.approver_id
,p_inv_aprvl_hist.created_by
,p_inv_aprvl_hist.creation_date
,p_inv_aprvl_hist.last_update_date
,p_inv_aprvl_hist.last_updated_by
,p_inv_aprvl_hist.last_update_login
,p_inv_aprvl_hist.org_id
,p_inv_aprvl_hist.amount_approved
,l_not_cnt);
WF_CORE.CONTEXT('APINVLDP','Insert_Header_History');
END Insert_Header_History;
PROCEDURE Insert_Header_History(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
p_type IN VARCHAR2 ) IS
l_approver_id NUMBER;
l_api_name CONSTANT VARCHAR2(200) := 'Insert_Header_History';
SELECT AP_INV_APRVL_HIST_S.nextval
INTO l_hist_id
FROM dual;
SELECT max(nvl(notification_order,0)) + 1
INTO l_not_cnt
FROM ap_inv_aprvl_hist_all
WHERE invoice_id = l_invoice_id
AND iteration = l_iteration;
--insert into the history table
INSERT INTO AP_INV_APRVL_HIST_ALL
(APPROVAL_HISTORY_ID
,INVOICE_ID
,ITERATION
,RESPONSE
,APPROVER_ID
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID
,AMOUNT_APPROVED
,NOTIFICATION_ORDER)
VALUES (
l_hist_id
,l_invoice_id
,l_iteration
,'PENDING'
,l_approver_id
,-1 --nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
,sysdate
,sysdate
,-1 --nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
,-1 --nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1)
,l_org_id
,l_amount
,l_not_cnt);
WF_CORE.CONTEXT('APINVLDP','Insert_Header_History',itemtype,
itemkey);
END Insert_Header_History;
PROCEDURE Insert_Line_History(
p_line_aprvl_hist IN ap_iaw_pkg.r_line_aprvl_hist) IS
l_api_name CONSTANT VARCHAR2(200) := 'Insert_Line_History';
SELECT AP_INV_APRVL_HIST_S.nextval
INTO l_hist_id
FROM dual;
SELECT max(notification_order) + 1
INTO l_not_cnt
FROM ap_line_aprvl_hist
WHERE invoice_id = p_line_aprvl_hist.invoice_id
AND iteration = p_line_aprvl_hist.iteration
AND line_number = p_line_aprvl_hist.line_number;
INSERT INTO AP_LINE_APRVL_HIST
(LINE_APRVL_HISTORY_ID
,LINE_NUMBER
,INVOICE_ID
,ITERATION
,RESPONSE
,APPROVER_ID
--,NOTIFICATION_KEY
,LINE_AMOUNT_APPROVED
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID
,ITEM_CLASS
,ITEM_ID
,NOTIFICATION_ORDER)
VALUES (
l_hist_id
,p_line_aprvl_hist.line_number
,p_line_aprvl_hist.invoice_id
,p_line_aprvl_hist.iteration
,'PENDING'
,p_line_aprvl_hist.approver_id
--,p_line_aprvl_hist.notification_key
,p_line_aprvl_hist.line_amount_approved
,p_line_aprvl_hist.created_by
,p_line_aprvl_hist.creation_date
,p_line_aprvl_hist.last_update_date
,p_line_aprvl_hist.last_updated_by
,p_line_aprvl_hist.last_update_login
,p_line_aprvl_hist.org_id
,p_line_aprvl_hist.item_class
,p_line_aprvl_hist.item_id
,l_not_cnt);
WF_CORE.CONTEXT('APINVLDP','Insert_Line_History');
END Insert_Line_History;
PROCEDURE Insert_Line_History(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
P_type IN VARCHAR2 ) IS
l_approver_id NUMBER;
l_api_name CONSTANT VARCHAR2(200) := 'Insert_Line_History';
SELECT Line_Number, item_class, item_id
FROM AP_APINV_APPROVERS
WHERE Notification_Key = itemkey
GROUP BY Line_Number, item_class, item_id;
SELECT AP_INV_APRVL_HIST_S.nextval
INTO l_hist_id
FROM dual;
SELECT max(nvl(notification_order,0)) + 1
INTO l_not_cnt
FROM ap_line_aprvl_hist
WHERE invoice_id = l_invoice_id
AND iteration = l_iteration
AND line_number = l_line_num;
--insert into the history table
INSERT INTO AP_LINE_APRVL_HIST
(LINE_APRVL_HISTORY_ID
,LINE_NUMBER
,INVOICE_ID
,ITERATION
,RESPONSE
,APPROVER_ID
,NOTIFICATION_KEY
,LINE_AMOUNT_APPROVED
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID
,ITEM_CLASS
,ITEM_ID
,NOTIFICATION_ORDER)
VALUES (
l_hist_id
,l_line_num
,l_invoice_id
,l_iteration
,'PENDING'
,l_approver_id
,itemkey
,l_line_amt
,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
,sysdate
,sysdate
,nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1)
,nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1)
,l_org_id
,l_item_class
,l_item_id
,l_not_cnt);
WF_CORE.CONTEXT('APINVLDP','Insert_Line_History',itemtype, itemkey);
END Insert_Line_History;
/*This procedure updates the pending record in the history table with the
result values returned by the notification. It will also set the line status
to Rejected, if that is the approver's response.*/
PROCEDURE Update_Header_History(itemtype IN VARCHAR2,
actid IN NUMBER,
itemkey IN VARCHAR2) IS
l_invoice_id NUMBER(15);
l_api_name CONSTANT VARCHAR2(200) := 'Update_Header_History';
--update history table status
--If this is an escalation approver, need condition
--to avoid clobbering original approvers record.
Update ap_inv_aprvl_hist_all
Set Response = l_result
,Approver_Comments = l_comments
,Last_Update_Date = sysdate
,Last_Updated_By = l_user_id
,Last_Update_Login = l_login_id
Where invoice_id = l_invoice_id
AND iteration = l_iteration
And Response = 'PENDING';
UPDATE AP_INVOICES
SET wfapproval_status = l_result
,Last_Update_Date = sysdate
,Last_Updated_By = l_user_id
,Last_Update_Login = l_login_id
WHERE invoice_id = l_invoice_id
AND wfapproval_status = 'INITIATED';
WF_CORE.CONTEXT('APINVLPN','Update_Header_History',itemtype, itemkey,
to_char(actid));
End Update_Header_History;
/*This procedure updates the pending record in the history table with the
result values returned by the notification. It will also set the line status
to Rejected, if that is the approver's response.*/
PROCEDURE Update_Line_History(
p_invoice_id IN NUMBER,
p_line_num IN NUMBER,
p_response IN VARCHAR2,
p_comments IN VARCHAR2) IS
l_api_name CONSTANT VARCHAR2(200) := 'Update_Line_History';
--update line history table status
--If this is an escalation approver, need
--condition to avoid clobbering original approvers record.
Update ap_line_aprvl_hist_all
Set Response = p_response
,Approver_Comments = p_comments
,Last_Update_Date = sysdate
,Last_Updated_By = -1
,Last_Update_Login = -1
Where invoice_id = p_invoice_id
AND line_number = p_line_num
And Response = 'PENDING';
UPDATE AP_INVOICE_LINES
SET wfapproval_status = p_response
,Last_Update_Date = sysdate
,Last_Updated_By = -1
,Last_Update_Login = -1
WHERE invoice_id = p_invoice_id
AND wfapproval_status <> 'MANUALLY APPROVED'
AND line_number = p_line_num;
WF_CORE.CONTEXT('APINVLPN','Update_Line_History');
END Update_Line_History;
PROCEDURE Update_Line_History(itemtype IN VARCHAR2,
actid IN NUMBER,
itemkey IN VARCHAR2) IS
l_invoice_id NUMBER(15);
l_api_name CONSTANT VARCHAR2(200) := 'Update_Line_History';
--update line history table status
--If this is an escalation approver, need
--condition to avoid clobbering original approvers record.
Update ap_line_aprvl_hist_all
Set Response = l_result
,Approver_Comments = l_comments
,Last_Update_Date = sysdate
,Last_Updated_By = l_user_id
,Last_Update_Login = l_login_id
Where Notification_key = itemkey
And Response = 'PENDING';
UPDATE AP_INVOICE_LINES
SET wfapproval_status = l_result
,Last_Update_Date = sysdate
,Last_Updated_By = l_user_id
,Last_Update_Login = l_login_id
WHERE invoice_id = l_invoice_id
AND wfapproval_status <> 'MANUALLY APPROVED'
AND line_number in (SELECT line_number
FROM ap_apinv_approvers
WHERE notification_key = itemkey);
WF_CORE.CONTEXT('APINVLPN','Update_Line_History',itemtype, itemkey,
to_char(actid));
END Update_Line_History;
SELECT Item_Class, Item_Id
FROM AP_LINE_APRVL_HIST_ALL
WHERE Invoice_ID = p_invoice_id
AND Line_Number = p_line_num
GROUP BY Item_Class, Item_Id;
SELECT Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
FROM AP_APINV_APPROVERS
WHERE Invoice_ID = p_invoice_id
AND NOTIFICATION_STATUS = 'SENT'
GROUP BY Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
ORDER BY Notification_Key;
SELECT Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
FROM AP_APINV_APPROVERS
WHERE Invoice_ID = p_invoice_id
AND Line_Number = p_line_number
AND NOTIFICATION_STATUS = 'SENT'
GROUP BY Item_Class, Item_Id, Role_Name, Invoice_Key, Notification_Key
ORDER BY Notification_Key;
select approval_iteration
into l_approval_iteration
from ap_invoices
where invoice_id=p_invoice_id;
SELECT end_date
INTO l_end_date
FROM wf_items
WHERE item_type = 'APINVLDP'
AND item_key = l_invoice_key;
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.nullStatus,
approverNameIn => l_name,
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id);
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.nullStatus,
approverNameIn => l_name,
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id);
SELECT Invoice_Key, Notification_Key, Invoice_ID, Notification_status
FROM AP_APINV_APPROVERS
GROUP BY Invoice_Key, Notification_Key, Invoice_Id, Notification_Status
ORDER BY Notification_Key;
--we only need to update at the header level once
IF l_invoice_key <> nvl(l_old_inv_key, 'dummy') THEN
WF_Engine.abortProcess(
itemType => 'APINVLDP',
itemKey => l_invoice_key,
process => 'APPROVAL_STAGING');
DELETE FROM AP_APINV_APPROVERS;
UPDATE ap_invoice_lines_all
SET wfapproval_status = 'NOT REQUIRED'
WHERE wfapproval_status in ('INITIATED','REQUIRED','REJECTED',
'NEEDS REAPPROVAL','STOPPED');
UPDATE ap_invoices_all
SET wfapproval_status = 'NOT REQUIRED'
WHERE wfapproval_status in ('INITIATED','REQUIRED','REJECTED',
'NEEDS REAPPROVAL','STOPPED');
SELECT dist_code_combination_id,set_of_books_id
INTO l_ccid,l_sob
FROM ap_invoice_distributions_all
WHERE invoice_distribution_id=p_dist_id;
SELECT chart_of_accounts_id
INTO l_chart_of_accounts_id
FROM gl_sets_of_books
WHERE set_of_books_id = l_sob;
SELECT organization
INTO l_return_val
FROM PA_EXP_ORGS_IT
WHERE organization_id=(SELECT expenditure_organization_id
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id);
SELECT count(invoice_distribution_id)
INTO l_count_pa_rel
FROM ap_invoice_distributions_all
WHERE invoice_id = p_invoice_id
AND project_id is not null;
SELECT sum(decode(po_header_id, null, 0, 1)),
count(line_number)
INTO l_sum_matched, l_item_count
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code = 'ITEM';
SELECT sum(decode(tax_already_calculated_flag, 'Y',
1, 0)), count(line_number)
INTO l_sum_calc, l_line_count
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_type_lookup_code not in ('TAX','AWT');
SELECT employee_number
INTO l_return_val
FROM per_all_people_f pap
WHERE person_id = (SELECT ph.agent_id
FROM ap_invoice_distributions_all aid,
po_distributions_all pd,
po_headers_all ph
WHERE pd.po_distribution_id =
aid.po_distribution_id
AND pd.po_header_id = ph.po_header_id
AND aid.invoice_distribution_id =
p_sub_class_id
AND pd.creation_date >= pap.effective_start_date
AND pd.creation_date <=
nvl(pap.effective_end_date,sysdate));
SELECT employee_number
INTO l_return_val
FROM per_all_people_f pap
WHERE person_id = (
SELECT pd.deliver_to_person_id
FROM ap_invoice_distributions_all aid,
po_distributions_all pd
WHERE pd.po_distribution_id =
aid.po_distribution_id
AND aid.invoice_distribution_id =
p_sub_class_id
AND pd.creation_date >= pap.effective_start_date
AND pd.creation_date <=
nvl(pap.effective_end_date,sysdate));
SELECT decode(po_header_id, null, 'N', 'Y')
INTO l_return_val
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id
AND line_number = p_sub_class_id;
INSERT INTO AP_HOLDS_all(
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,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID,
RESPONSIBILITY_ID,
RCV_TRANSACTION_ID,
LINE_NUMBER)
select il.invoice_id invoice_id,
NULL,
hc.hold_lookup_code,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
hc.description description,
NULL,
NULL,
'S',
NULL,
sysdate,
fnd_global.user_id,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
fnd_global.org_id,
null,
null,
il.line_number line_number
from ap_invoice_lines_all il,
po_lines_all pl,
ap_hold_codes hc
where il.invoice_id = p_invoice_id
and il.po_line_location_id is not null
and pl.po_line_id = il.po_line_id
and pl.unit_price <> il.unit_price
and hc.hold_lookup_code = 'PRICE'
and il.line_type_lookup_code = 'ITEM'
UNION ALL
select il.invoice_id invoice_id,
NULL,
hc.hold_lookup_code,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
hc.description description,
NULL,
NULL,
'S',
NULL,
sysdate,
fnd_global.user_id,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
fnd_global.org_id,
null,
null,
il.line_number line_number
from ap_invoice_lines_all il,
po_line_locations_all ll,
ap_hold_codes hc
where il.invoice_id = p_invoice_id
and il.po_line_location_id = ll.line_location_id
and il.quantity_invoiced > ll.quantity_received
and hc.hold_lookup_code = 'QTY REC'
and il.line_type_lookup_code = 'ITEM'
UNION ALL
select il.invoice_id invoice_id,
NULL,
hc.hold_lookup_code,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
hc.description description,
NULL,
NULL,
'S',
NULL,
sysdate,
fnd_global.user_id,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
fnd_global.org_id,
null,
null,
il.line_number line_number
from ap_invoice_lines_all il,
po_line_locations_all ll,
ap_hold_codes hc
where il.invoice_id = p_invoice_id
and il.po_line_location_id = ll.line_location_id
and il.quantity_invoiced > ll.quantity
and hc.hold_lookup_code = 'QTY ORD'
and il.line_type_lookup_code = 'ITEM'
UNION ALL
select il.invoice_id invoice_id,
NULL,
hc.hold_lookup_code,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
hc.description description,
NULL,
NULL,
'S',
NULL,
sysdate,
fnd_global.user_id,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
fnd_global.org_id,
null,
null,
il.line_number line_number
from ap_invoice_lines_all il,
po_line_locations_all ll,
ap_hold_codes hc
where il.invoice_id = p_invoice_id
and il.po_line_location_id = ll.line_location_id
and il.amount > ll.amount
and hc.hold_lookup_code = 'AMT ORD'
and il.line_type_lookup_code = 'ITEM'
UNION ALL
select il.invoice_id invoice_id,
NULL,
hc.hold_lookup_code,
sysdate,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
hc.description description,
NULL,
NULL,
'S',
NULL,
sysdate,
fnd_global.user_id,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
fnd_global.org_id,
null,
null,
il.line_number line_number
from ap_invoice_lines_all il,
po_line_locations_all ll,
ap_hold_codes hc
where il.invoice_id = p_invoice_id
and il.po_line_location_id = ll.line_location_id
and il.amount > ll.amount_received
and hc.hold_lookup_code = 'AMT REC'
and il.line_type_lookup_code = 'ITEM';
update ap_invoice_lines_all il
set disputable_flag = 'Y'
where il.invoice_id = l_invoice_id
and il.org_id = l_org_id
and il.line_type_lookup_code = 'ITEM'
-- bug 4611844
-- non-po matched or
-- po_matched: driven by line_location_id
and ( il.po_line_location_id is null
or (exists
(select h.line_location_id
from ap_holds_all h
where h.invoice_id = l_invoice_id
and h.org_id = l_org_id
and il.po_line_location_id = h.line_location_id
and h.status_flag = 'S'
and h.hold_lookup_code in ('PRICE', 'QTY ORD', 'QTY REC', 'AMT ORD', 'AMT REC'))));
select count(*)
into l_num
from ap_invoice_lines_all
where invoice_id = l_invoice_id
and org_id = l_org_id
and disputable_flag = 'Y';
select count(*)
into l_num
from ap_holds_all
where invoice_id = l_invoice_id
and org_id = l_org_id
and hold_lookup_code in ('QTY REC', 'AMT REC');
select line_number
from ap_invoice_lines_all
where invoice_id = p_invoice_id
and line_type_lookup_code = 'ITEM'
and nvl(disputable_flag, 'N' ) = 'Y';
PROCEDURE insert_approver_rec(p_item_key IN VARCHAR2,
p_invoice_id IN NUMBER,
p_invoice_iteration IN NUMBER,
p_mapping_tbl IN tLineApprovers,
p_invoice_source IN VARCHAR2,
p_ext_user_name IN VARCHAR2) as
l_org_id ap_invoices_all.org_id%TYPE;
l_api_name CONSTANT VARCHAR2(200) := 'insert_approver_rec';
insert into ap_apinv_approvers (
invoice_id,
invoice_iteration,
invoice_key,
line_number,
notification_iteration,
notification_key,
notification_status,
role_name,
orig_system,
orig_system_id,
external_role_name,
approval_status,
access_control_flag,
source,
last_updated_by,
last_update_date,
created_by,
creation_date,
program_application_id,
program_id,
program_update_date,
request_id)
VALUES (
p_invoice_id,
p_invoice_iteration,
p_item_key,
p_mapping_tbl(i).line_number,
l_notif_iter,
l_notif_key,
'PEND',
p_mapping_tbl(i).role_name,
'PER',
p_mapping_tbl(i).approver_id,
decode(p_invoice_source,'ISP', p_ext_user_name, null),
'NEGOTIATE',
'I',
p_invoice_source,
nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')), -1),
sysdate,
nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')), -1),
sysdate,
200,
0,
sysdate,
0);
end insert_approver_rec;
select ail.line_number, pll.work_approver_id, wfr.name
from ap_invoice_lines_all ail,
po_line_locations_all pll,
ap_invoices_all ai,
wf_local_roles wfr
where ai.invoice_id = l_invoice_id
and ai.invoice_id = ail.invoice_id
and ai.invoice_type_lookup_code in ('STANDARD', 'CREDIT', 'PREPAYMENT')
and ail.line_type_lookup_code = 'ITEM'
and nvl(ail.disputable_flag, 'N') = 'Y'
and pll.line_location_id = ail.po_line_location_id
and pll.work_approver_id = wfr.orig_system_id
and wfr.orig_system = 'PER';
select ph.agent_id, ail.line_number, wfr.name
from ap_invoice_lines_all ail,
po_headers_all ph,
ap_invoices_all ai,
wf_local_roles wfr
where ai.invoice_id = l_invoice_id
and ai.invoice_id = ail.invoice_id
and ai.invoice_type_lookup_code in ('STANDARD', 'CREDIT', 'PREPAYMENT')
and ail.line_type_lookup_code = 'ITEM'
and nvl(ail.disputable_flag, 'N') = 'Y'
and ail.po_header_id = ph.po_header_id
and ph.agent_id = wfr.orig_system_id
and wfr.orig_system = 'PER';
select ail.requester_id, ail.line_number, wfr.name
from ap_invoice_lines_all ail,
ap_invoices_all ai,
wf_local_roles wfr
where ai.invoice_id = l_invoice_id
and ai.invoice_id = ail.invoice_id
and ai.invoice_type_lookup_code in ('STANDARD', 'CREDIT', 'PREPAYMENT')
and ail.line_type_lookup_code = 'ITEM'
and nvl(ail.disputable_flag, 'N') = 'Y'
and ail.requester_id = wfr.orig_system_id
and wfr.orig_system = 'PER';
select pt.task_manager_person_id, ail.line_number, wfr.name
from ap_invoice_lines_all ail,
ap_invoices_all ai,
pa_tasks pt,
wf_local_roles wfr
where ai.invoice_id = l_invoice_id
and ai.invoice_id = ail.invoice_id
and ai.invoice_type_lookup_code in ('STANDARD', 'CREDIT', 'PREPAYMENT')
and ail.line_type_lookup_code = 'ITEM'
and nvl(ail.disputable_flag, 'N') = 'Y'
and ail.project_id = pt.project_id
and ail.task_id = pt.task_id
and pt.task_manager_person_id = wfr.orig_system_id
and wfr.orig_system = 'PER';
select ai.invoice_type_lookup_code, ai.internal_contact_email,
ai.source, u.user_name
into l_invoice_type, l_internal_contact_email,
l_source, l_ext_user_name
from ap_invoices_all ai,
fnd_user u
where invoice_id = l_invoice_id
and u.user_id = ai.created_by
and trunc(sysdate) between trunc(u.start_date)
and trunc(nvl(u.end_date, sysdate+1));
select count(*)
into l_adhoc_role_count
from wf_local_roles
where name = l_adhoc_role_name
and display_name = l_adhoc_display_name;
insert_approver_rec(itemkey, l_invoice_id, l_invoice_iteration,
l_line_appr_tbl, l_source, l_ext_user_name);
update ap_invoice_lines_all
set line_owner_role = l_line_appr_tbl(i).role_name
where invoice_id = l_invoice_id
and line_number = l_line_appr_tbl(i).line_number
and line_type_lookup_code = 'ITEM'
and line_owner_role is null
and disputable_flag = 'Y';
select distinct il.line_owner_role, i.source, u.user_name
from ap_invoice_lines_all il,
ap_invoices_all i,
fnd_user u
where il.line_owner_role is not null
and il.line_type_lookup_code = 'ITEM'
and i.invoice_id = l_invoice_id
and i.org_id = l_org_id
and i.invoice_id = il.invoice_id
and il.org_id = l_org_id
and u.user_id = i.created_by;
insert into ap_apinv_approvers
( invoice_id,
invoice_key,
notification_key,
role_name,
external_role_name,
approval_status,
access_control_flag,
source)
values
( l_invoice_id,
itemKey,
l_notif_key,
l_rec.line_owner_role,
decode(l_rec.source,'ISP', l_rec.user_name, null),
'NEGOTIATE',
'I',
l_rec.source);
select count(*)
into l_num
from ap_invoice_lines_all
where org_id = l_org_id
and invoice_id = l_invoice_id
and line_owner_role is null
and line_type_lookup_code = 'ITEM';
update ap_invoice_lines_all
set line_owner_role = l_role
where invoice_id = l_invoice_id
and org_id = l_org_id
and line_type_lookup_code = 'ITEM'
and line_owner_role is null
and disputable_flag = 'Y';
select count(*)
into l_num
from ap_apinv_approvers
where invoice_id = l_invoice_id
and invoice_key = itemKey
and notification_status is NULL;
select 'X'
into l_f
from dual
where exists( select 'e' from ap_invoices_all
where invoice_id = l_invoice_id and
org_id = l_org_id and wfapproval_status = 'REJECTED'
union all
select 'e' from ap_invoice_lines_all
where invoice_id = l_invoice_id and
org_id = l_org_id and wfapproval_status = 'REJECTED');
select rowid, notification_key
from ap_apinv_approvers
where notification_status is null
and invoice_id = l_invoice_id
and invoice_key = itemKey
for update;
select distinct role_name, notification_key
from ap_apinv_approvers
-- for dispute child process, we use role_name for grouping
-- notification_key is not necessary
where notification_status = 'PEND'
and invoice_id = l_invoice_id
and invoice_key = itemKey;
SELECT
PV.vendor_name,
AI.invoice_num,
AI.invoice_date,
AI.description
INTO
l_invoice_supplier_name,
l_invoice_number,
l_invoice_date,
l_invoice_description
FROM
ap_invoices_all AI,
po_vendors PV,
po_vendor_sites_all PVS
WHERE
AI.invoice_id = l_invoice_id AND
AI.vendor_id = PV.vendor_id AND
AI.vendor_site_id = PVS.vendor_site_id(+);
update ap_apinv_approvers
set notification_status = 'SENT'
where invoice_id = l_invoice_id
and invoice_key = itemkey
and role_name = l_role_name;
select rowid
from ap_apinv_approvers
where invoice_id = l_invoice_id
and notification_key = itemkey;
update ap_apinv_approvers
set access_control_flag =
decode(access_control_flag, 'I', 'E',
'E', 'I',
'I')
where rowid = l_rowid;
delete from ap_apinv_approvers
where invoice_id = l_invoice_id
and invoice_key = itemkey;
select decode(access_control_flag,
'E',EXTERNAL_ROLE_NAME, ROLE_NAME)
from ap_apinv_approvers
where invoice_id = l_invoice_id
and notification_key = itemkey;
l_last_updated_by number;
l_last_update_login number;
l_last_update_date date;
select gl_date,
last_updated_by,
last_update_login
from ap_invoices_all
where invoice_id = l_invoice_id
and org_id = l_org_id;
fetch invoice into l_accounting_date, l_last_updated_by, l_last_update_login;
l_last_updated_by,
l_last_update_login,
l_accounting_date,
l_message_name,
l_invoice_amount,
l_base_amount,
l_temp_cancelled_amount,
l_cancelled_by,
l_cancelled_amount,
l_cancelled_date,
l_last_update_date,
l_original_prepayment_amount,
l_pay_curr_invoice_amount,
l_token,
null);
update ap_invoice_lines_all
set disputable_flag = 'N'
where invoice_id = l_invoice_id
and org_id = l_org_id
and line_owner_role =(
select ROLE_NAME
from ap_apinv_approvers
where invoice_id = l_invoice_id
and notification_key = itemkey);
select invoice_key
from ap_apinv_approvers
where invoice_id = l_invoice_id
and notification_key = itemkey;
select count(*)
into l_num
from ap_invoice_lines_all
where invoice_id = l_invoice_id
and org_id = l_org_id
and disputable_flag = 'Y'
and line_type_lookup_code = 'ITEM';
PROCEDURE is_invoice_updated( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) as
l_num number;
select count(*)
into l_num
from ap_invoice_lines_all
where invoice_id = l_invoice_id
and org_id = l_org_id
and creation_date <> last_update_date
and line_type_lookup_code = 'ITEM';
select access_control_flag
from ap_apinv_approvers
where invoice_id = l_invoice_id
and notification_key = itemkey;
select invoice_type_lookup_code
into l_type
from ap_invoices_all
where invoice_id = l_invoice_id
and org_id = l_org_id;
PROCEDURE update_to_invoice( itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) as
l_invoice_id number;
update ap_invoices_all
set invoice_type_lookup_code =
decode(invoice_type_lookup_code,
'INVOICE REQUEST', 'STANDARD',
'CREDIT MEMO REQUEST', 'CREDIT', 'STANDARD')
where invoice_id = l_invoice_id;
select rowid, notification_key
from ap_apinv_approvers
where notification_status is null
and invoice_id = l_invoice_id
and invoice_key = itemKey
and rownum = 1
for update;
SELECT
PV.vendor_name,
AI.invoice_num,
AI.invoice_date,
AI.description
INTO
l_invoice_supplier_name,
l_invoice_number,
l_invoice_date,
l_invoice_description
FROM
ap_invoices_all AI,
po_vendors PV,
po_vendor_sites_all PVS
WHERE
AI.invoice_id = l_invoice_id AND
AI.vendor_id = PV.vendor_id AND
AI.vendor_site_id = PVS.vendor_site_id(+);
update ap_apinv_approvers
set notification_status = 'STARTED'
where rowid = l_rowid;
select vendor_id, set_of_books_id
into l_vendor_id, l_set_of_books_id
from ap_invoices_all
where invoice_id = l_invoice_id;
UPDATE ap_holds_all
SET release_lookup_code = l_release_lookup_code,
release_reason = (SELECT description
FROM ap_lookup_codes
WHERE lookup_code = l_release_lookup_code
AND lookup_type = 'HOLD CODE'),
last_update_date = sysdate,
last_updated_by = 5,
status_flag = 'R'
WHERE invoice_id = p_invoice_id
-- AND nvl(line_location_id, -1) = nvl(p_line_location_id, -1)
-- AND nvl(rcv_transaction_id, -1) = nvl(rcv_transaction_id, -1)
AND hold_lookup_code = p_hold_lookup_code
AND nvl(status_flag, 'x') <> 'x';