[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_history_table(p_hist_rec IN AP_INV_APRVL_HIST%ROWTYPE) IS
l_api_name CONSTANT VARCHAR2(200) := 'insert_history_table';
SELECT AP_INV_APRVL_HIST_S.nextval
INTO l_hist_id
FROM dual;
INSERT INTO AP_INV_APRVL_HIST_ALL
(APPROVAL_HISTORY_ID
,HISTORY_TYPE
,INVOICE_ID
,ITERATION
,RESPONSE
,APPROVER_ID
,APPROVER_NAME
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID
,AMOUNT_APPROVED
,HOLD_ID
,LINE_NUMBER
,APPROVER_COMMENTS
,NOTIFICATION_ORDER)
VALUES (
l_hist_id
,p_hist_rec.HISTORY_TYPE
,p_hist_rec.INVOICE_ID
,p_hist_rec.ITERATION
,p_hist_rec.RESPONSE
,p_hist_rec.APPROVER_ID
,p_hist_rec.APPROVER_NAME
,p_hist_rec.CREATED_BY
,p_hist_rec.CREATION_DATE
,p_hist_rec.LAST_UPDATE_DATE
,p_hist_rec.LAST_UPDATED_BY
,p_hist_rec.LAST_UPDATE_LOGIN
,p_hist_rec.ORG_ID
,p_hist_rec.AMOUNT_APPROVED
,p_hist_rec.HOLD_ID
,p_hist_rec.LINE_NUMBER
,p_hist_rec.APPROVER_COMMENTS
,p_hist_rec.NOTIFICATION_ORDER);
l_debug_info := 'After Insert into AP_INV_APRVL_HIST_ALL';
END insert_history_table;
FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name,'AP_ISP_UTILITIES_PKG.update_invoice_header2(+)');
l_debug_info := 'Step 1. update invoice amount: invoice_id = ';
SELECT SUM(DECODE(line_type_lookup_code,'ITEM',NVL(amount, 0) - NVL(included_tax_amount, 0) ,0)) ITEM_SUM,
SUM(DECODE(line_type_lookup_code,'TAX',amount,0)) + SUM(NVL(included_tax_amount, 0)) TAX_SUM,
SUM(DECODE(line_type_lookup_code,'MISCELLANEOUS',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) MISC_SUM,
SUM(DECODE(line_type_lookup_code,'FREIGHT',NVL(amount, 0) - NVL(included_tax_amount, 0),0)) FREIGHT_SUM,
sum(decode(line_type_lookup_code, 'ITEM', NVL(retained_amount, 0), 0)) RETAINAGE_SUM
INTO l_item_sum, l_tax_sum, l_misc_sum, l_frt_sum, l_retained_sum
FROM ap_invoice_lines_all
WHERE invoice_id = p_invoice_id;
update ap_invoices_all
set invoice_amount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
amount_applicable_to_discount = l_item_sum + l_tax_sum + l_misc_sum + l_frt_sum + l_retained_sum,
net_of_retainage_flag = DECODE(l_retained_sum, 0, 'N', 'Y')
where invoice_id = p_invoice_id;
AP_INVOICES_POST_PROCESS_PKG.insert_children (
X_invoice_id => p_invoice_id,
X_Payment_Priority => 99,
X_Hold_count => l_hold_count,
X_Line_count => l_line_count,
X_Line_Total => l_line_total,
X_calling_sequence => l_curr_calling_sequence,
X_Sched_Hold_count => l_Sched_Hold_count);
update ap_holds_all
set wf_status = 'TERMINATED'
where hold_id = l_hold_id ;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'Before select';
select count(1)
into l_num
from ap_invoice_lines_all ail
where ail.invoice_id = l_invoice_id
and ail.org_id = l_org_id
and ail.line_type_lookup_code = 'ITEM'
and 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 h.hold_id = l_hold_id
and ail.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')));
l_debug_info := 'After select, reultout : ' || resultout;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
l_debug_info := 'Before insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'Before AME_API.updateApprovalStatus2';
AME_API.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_hold_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverPersonIdIn => l_approver_id,
approverUserIdIn => NULL,
transactionTypeIn => 'APHLD');
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
l_debug_info := 'Before insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'Before AME_API.updateApprovalStatus2';
AME_API.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_hold_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverPersonIdIn => l_approver_id,
approverUserIdIn => NULL,
transactionTypeIn => 'APHLD');
UPDATE ap_holds_all
SET wf_status = 'RELEASED'
WHERE hold_id = l_hold_id;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
l_debug_info := 'Before insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'Before AME_API.updateApprovalStatus2';
AME_API.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_hold_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverPersonIdIn => l_approver_id,
approverUserIdIn => NULL,
transactionTypeIn => 'APHLD');
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
l_debug_info := 'Before insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'Before AME_API.updateApprovalStatus2';
AME_API.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_hold_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverPersonIdIn => l_approver_id,
approverUserIdIn => NULL,
transactionTypeIn => 'APHLD');
UPDATE ap_holds_all
SET wf_status = 'RELEASED'
WHERE hold_id = l_hold_id;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
l_debug_info := 'Before insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'Before AME_API.updateApprovalStatus2';
AME_API.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_hold_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverPersonIdIn => l_internal_approver_id,
approverUserIdIn => NULL,
transactionTypeIn => 'APHLD');
UPDATE ap_holds_all
SET wf_status = 'RELEASED'
WHERE hold_id = l_hold_id;
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;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
fetch invoice into l_accounting_date, l_last_updated_by, l_last_update_login;
l_last_updated_by,
l_last_update_login,
sysdate, -- 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);
l_debug_info := 'Before insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'Before AME_API.updateApprovalStatus2';
AME_API.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_hold_id),
approvalStatusIn => AME_UTIL.rejectStatus,
approverPersonIdIn => l_internal_approver_id,
approverUserIdIn => NULL,
transactionTypeIn => 'APHLD');
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
l_debug_info := 'Before insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'Before AME_API.updateApprovalStatus2';
AME_API.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_hold_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverPersonIdIn => l_approver_id,
approverUserIdIn => NULL,
transactionTypeIn => 'APHLD');
UPDATE ap_holds_all
SET wf_status = 'RELEASED'
WHERE hold_id = l_hold_id;
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_result(i)
and lookup_type = 'NLS TRANSLATION';
l_hist_rec.last_update_date := sysdate;
l_hist_rec.last_updated_by :=
FND_PROFILE.VALUE('AP_IAW_USER');
l_hist_rec.last_update_login := -1;
insert_history_table(p_hist_rec => l_hist_rec);
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 displayed_field
into l_tr_reason
FROM ap_lookup_codes
WHERE lookup_code = l_result(i)
and lookup_type = 'NLS TRANSLATION';
l_hist_rec.last_update_date := sysdate;
l_hist_rec.last_updated_by :=
FND_PROFILE.VALUE('AP_IAW_USER');
l_hist_rec.last_update_login := -1;
insert_history_table(p_hist_rec => l_hist_rec);
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_result(i)
and lookup_type = 'NLS TRANSLATION';
l_debug_info := 'Open Lines Cursor to Update History Tables';
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_history_table(
p_hist_rec => l_l_hist);
l_debug_info := 'Update Lines Approval Status';
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 count(*)
INTO l_lines_require_approval
FROM ap_invoice_lines_all
WHERE invoice_id = l_invoice_id
AND WFApproval_Status = 'INITIATED';
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.notifiedStatus,
approverNameIn => l_next_approver.NAME,
transactionTypeIn => 'APINV',
itemClassIn => l_next_approver.item_class,
itemIdIn => l_next_approver.item_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);
/* AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.notifiedStatus,
approverNameIn => l_next_approver.NAME,
transactionTypeIn => 'APINV',
itemClassIn => l_class(l_rec),
itemIdIn => l_ids(l_rec)); */
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';
l_debug_info := 'Before calling insert_history_table';
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'After calling insert_history_table';
/*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;
/* AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.notifiedStatus,
approverNameIn => l_next_approver.NAME,
transactionTypeIn => 'APINV',
itemClassIn => l_next_approver.item_class,
itemIdIn => l_next_approver.item_id); */
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);
l_debug_info := 'Before calling insert_history_table';
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USE
R_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LO
GIN_ID')),-1);
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'After calling insert_history_table';
SELECT distinct role_name
FROM ap_apinv_approvers
WHERE notification_status = 'PEND'
AND invoice_key = itemkey
AND line_number IS NOT NULL;
SELECT nvl(max(notification_iteration),0) + 1
INTO l_not_iteration
FROM ap_apinv_approvers
WHERE invoice_key = itemkey;
l_debug_info := 'Update Iteration Value in ap_apinv_approvers';
UPDATE ap_apinv_approvers
SET notification_iteration = l_not_iteration,
notification_key = itemkey || '_' || l_not_iteration,
child_process_item_key = itemkey || '_' || l_not_iteration,
child_process_item_type = 'APINVAPR',
item_key = itemkey || '_' || l_not_iteration,
item_type = 'APINVAPR'
WHERE role_Name = Group_Approvers_Rec.role_name
AND invoice_key = itemkey
AND line_number IS NOT NULL
AND notification_status = 'PEND';
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;
SELECT invoice_type_lookup_code
INTO l_invoice_type_lookup_code
FROM ap_invoices_all
WHERE invoice_id = l_invoice_id;
UPDATE AP_INVOICE_LINES_ALL
SET WFApproval_Status = DECODE(l_invoice_type_lookup_code
,'INVOICE REQUEST','REJECTED'
,'CREDIT MEMO REQUEST','REJECTED'
,'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_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
AND NOTIFICATION_STATUS = 'COMP');
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 distinct role_name
FROM ap_apinv_approvers
WHERE notification_status = 'PEND'
AND invoice_key = itemKey
AND line_number is NOT NULL
AND notification_key IS NOT NULL;
SELECT vendor_name,
vendor_site_code,
invoice_num,
invoice_date,
description,
invoice_amount,
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
(SELECT
PV.vendor_name vendor_name,
PVS.vendor_site_code vendor_site_code,
AI.invoice_num,
AI.invoice_date,
AI.description,
NVL(AI.invoice_amount, 0) invoice_amount,
AI.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.invoice_type_lookup_code <> 'PAYMENT REQUEST' AND
AI.vendor_site_id = PVS.vendor_site_id(+)
UNION ALL
SELECT
HZP.party_name vendor_name,
HZPS.party_site_name vendor_site_code,
AI.invoice_num,
AI.invoice_date,
AI.description,
NVL(AI.invoice_amount, 0) invoice_amount,
AI.invoice_currency_code
FROM
ap_invoices_all AI,
hz_parties HZP,
hz_party_sites HZPS
WHERE
AI.invoice_id = l_invoice_id AND
AI.party_id = HZP.party_id AND
AI.invoice_type_lookup_code = 'PAYMENT REQUEST' and
AI.party_site_id = HZPS.party_site_id(+))
;
SELECT notification_key,
invoice_iteration,
notification_iteration,
orig_system,
orig_system_id
INTO l_notification_key,
l_invoice_iteration,
l_notification_iteration,
l_orig_system,
l_orig_system_id
FROM ap_apinv_approvers
WHERE role_name = Notif_Process_Rec.role_name
AND notification_status = 'PEND'
AND invoice_key = itemKey
AND rownum = 1
AND notification_key is not null; --bug 16414516
l_debug_info := 'Select the Role and Approver information';
l_debug_info := 'Update the ap_apinv_approvers notification status';
UPDATE ap_apinv_approvers
SET notification_status = 'SENT'
WHERE notification_key = l_notification_key;
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.rejectStatus,
approverNameIn => l_role,
transactionTypeIn => 'APINV',
itemClassIn => ame_util.headerItemClassName,
itemIdIn => to_char(l_invoice_id));
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
l_debug_info := 'Before calling insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
l_debug_info := 'Before UPDATE AP_INVOICES_ALL';
UPDATE AP_INVOICES_ALL
SET wfapproval_status = 'REJECTED'
WHERE invoice_id = l_invoice_id;
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverNameIn => l_role,
transactionTypeIn => 'APINV',
itemClassIn => ame_util.headerItemClassName,
itemIdIn => to_char(l_invoice_id));
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
l_debug_info := 'Before calling insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
invap.Invoice_Key, al.line_number, al.amount
FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
WHERE invap.Notification_Key = itemkey
AND al.line_number = invap.line_number
AND al.invoice_id = invap.invoice_id;
l_debug_info := 'Before Update Approvers table';
UPDATE AP_APINV_APPROVERS
SET Notification_status = 'COMP'
WHERE Notification_Key = itemkey;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
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);
l_debug_info := 'Before calling insert_history_table for Line'
|| l_line_number;
insert_history_table(p_hist_rec => l_hist_rec);
UPDATE AP_INVOICE_LINES_ALL -- Bug 9388962
SET wfapproval_status = 'REJECTED'
,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);
SELECT invoice_key
INTO l_invoice_key
FROM AP_APINV_APPROVERS
WHERE Notification_Key = itemkey
AND rownum = 1;
l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
invap.Invoice_Key, al.line_number, al.amount
FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
WHERE invap.Notification_Key = itemkey
AND al.line_number = invap.line_number
AND al.invoice_id = invap.invoice_id;
l_debug_info := 'Before Update Approvers table';
UPDATE AP_APINV_APPROVERS
SET Notification_status = 'COMP'
WHERE Notification_Key = itemkey;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverNameIn => l_role, --bug 13919019
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id);
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverNameIn => l_role, --bug 13919019
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id); */
l_debug_info := 'Before calling insert_history_table for Line'
|| l_line_number;
insert_history_table(p_hist_rec => l_hist_rec);
SELECT invoice_key
INTO l_invoice_key
FROM AP_APINV_APPROVERS
WHERE Notification_Key = itemkey
AND rownum = 1;
l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.approvedStatus,
approverNameIn => l_role_name,
transactionTypeIn => 'APINV',
itemClassIn => ame_util.headerItemClassName,
itemIdIn => to_char(l_invoice_id));
l_debug_info := 'Before calling insert_history_table for Line'
|| l_line_number;
insert_history_table(p_hist_rec => l_hist_rec);
SELECT invoice_type_lookup_code
INTO l_invoice_type_lookup_code
FROM AP_INVOICES_ALL
WHERE invoice_id = l_invoice_id;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
SELECT invoice_type_lookup_code
INTO l_invoice_type_lookup_code
FROM AP_INVOICES_ALL
WHERE invoice_id = l_invoice_id;
SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
invap.Invoice_Key, al.line_number, al.amount
FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
WHERE invap.child_process_item_key = itemkey
AND al.line_number = invap.line_number
AND al.invoice_id = invap.invoice_id;
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;
l_debug_info := 'Before Update Approvers table';
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.rejectStatus,
approverNameIn => l_role_name,
transactionTypeIn => 'APINV',
itemClassIn => ame_util.headerItemClassName,
itemIdIn => to_char(l_invoice_id));
l_debug_info := 'Before calling insert_history_table for Line'
|| l_line_number;
insert_history_table(p_hist_rec => l_hist_rec);
fetch invoice into l_accounting_date, l_last_updated_by, l_last_update_login;
l_last_updated_by,
l_last_update_login,
sysdate, -- 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);
SELECT PV.vendor_name,
AI.invoice_num,
AI.invoice_date,
AI.description,
AI.org_id,
AI.invoice_id,
AI.approval_iteration,
nvl(p_ext_contact_id, AI.vendor_contact_id),
NVL(AI.invoice_amount, 0),
ahc.hold_instruction
FROM ap_holds_all ah,
ap_invoices_all AI,
po_vendors PV,
ap_hold_codes ahc
WHERE ah.hold_id = p_hold_id
AND AI.invoice_id = ah.invoice_id
AND AI.vendor_id = PV.vendor_id
AND AH.hold_lookup_code = AHC.hold_lookup_code;
SELECT AP_NEGOTIATION_HIST_S.nextval
INTO l_new_child_ItemKey
FROM dual;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
insert_history_table(p_hist_rec => l_hist_rec);
UPDATE ap_holds_all
SET wf_status = 'NEGOTIATE'
WHERE hold_id = p_hold_id;
SELECT PV.vendor_name,
AI.invoice_num,
AI.invoice_date,
AI.description,
AI.org_id,
AI.invoice_id,
alk.displayed_field,
NVL(AI.invoice_amount, 0),
ahc.hold_instruction
FROM ap_holds_all ah,
ap_invoices_all AI,
po_vendors PV,
ap_lookup_codes alk,
ap_hold_codes ahc
WHERE ah.hold_id = p_hold_id
AND alk.lookup_code = ah.hold_lookup_code
AND AI.invoice_id = ah.invoice_id
AND AI.vendor_id = PV.vendor_id
AND AH.hold_lookup_code = AHC.hold_lookup_code;
UPDATE ap_holds_all
SET wf_status = 'STARTED'
WHERE hold_id = p_hold_id;
SELECT invoice_type_lookup_code
,gl_date
,ap_utilities_pkg.get_gl_period_name(gl_date
,org_id)
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
in Initiated status need to be updated to Required. */
l_invoice_id := substr(itemkey, 1, instr(itemkey,'_')-1);
SELECT count(*)
into l_count_rejects
FROM ap_invoice_lines_all
WHERE invoice_id = l_invoice_id
AND wfapproval_status = 'REJECTED';
select '&'
into l_ampersand
from dual
where 1 like 1 escape '&';
SELECT count(*)
INTO l_count
FROM ap_inv_aprvl_hist
WHERE invoice_id = l_invoice_id
AND iteration = l_iteration
AND RESPONSE <> 'MANUALLY APPROVED'
AND history_type = 'DOCUMENTAPPROVAL';
UPDATE AP_INVOICES_ALL
SET wfapproval_status = 'WFAPPROVED'
WHERE invoice_id = l_invoice_id
AND wfapproval_status <> 'MANUALLY APPROVED';
UPDATE AP_INVOICES_ALL
SET wfapproval_status = decode(invoice_type_lookup_code,
'INVOICE REQUEST','REJECTED',
'CREDIT MEMO REQUEST','REJECTED',
'NOT REQUIRED')
WHERE invoice_id = l_invoice_id
AND wfapproval_status <> 'MANUALLY APPROVED';
UPDATE AP_INVOICE_LINES_ALL
SET wfapproval_status = 'NOT REQUIRED'
WHERE invoice_id = l_invoice_id
AND wfapproval_status <> 'MANUALLY APPROVED';
UPDATE AP_INVOICES_ALL
SET invoice_type_lookup_code =
DECODE(invoice_type_lookup_code
,'INVOICE REQUEST','STANDARD'
,'CREDIT MEMO REQUEST', 'CREDIT'
,invoice_type_lookup_code),
terms_id = l_terms_id,
terms_date = l_terms_date
WHERE invoice_id = l_invoice_id;
UPDATE AP_INVOICES_ALL
SET gl_date = l_new_gl_date
WHERE invoice_id = l_invoice_id;
UPDATE ap_invoice_lines_all
SET accounting_date = l_new_gl_date
,period_name = l_new_period_name
WHERE invoice_id = l_invoice_id;
UPDATE ap_invoice_distributions_all
SET accounting_date = l_new_gl_date
,period_name = l_new_period_name
WHERE invoice_id = l_invoice_id;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
insert_history_table(p_hist_rec => l_hist_rec);
/* Should never go through following update since all lines
should be either Rejected or approved by this time */
UPDATE AP_INVOICE_LINES_ALL
SET wfapproval_status = 'REQUIRED'
WHERE invoice_id = l_invoice_id
AND wfapproval_status = 'INITIATED';
UPDATE ap_invoices_all
SET wfapproval_status = 'NEEDS WFREAPPROVAL'
WHERE invoice_id = l_invoice_id;
SELECT supervisor_id
INTO l_esc_approver_id
FROM per_employees_current_x
WHERE employee_id = l_approver_id;
/*SELECT user_id
INTO l_esc_approver_id
FROM fnd_user
WHERE employee_id =
(SELECT supervisor_id
FROM per_employees_current_x
WHERE employee_id = l_approver_id);
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;
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);
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
l_debug_info := 'Before insert_history_table';
insert_history_table(p_hist_rec => l_hist_rec);
SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
invap.Invoice_Key, al.line_number, al.amount
FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
WHERE invap.Notification_Key = itemkey
AND al.line_number = invap.line_number
AND al.invoice_id = invap.invoice_id;
SELECT supervisor_id
INTO l_esc_approver_id
FROM per_employees_current_x
WHERE employee_id = l_approver_id;
SELECT user_id
INTO l_esc_approver_id
FROM fnd_user
WHERE employee_id =
(SELECT supervisor_id
FROM per_employees_current_x
WHERE employee_id = l_approver_id);
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;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
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);
l_debug_info := 'Before calling insert_history_table for Line'
|| l_line_number;
insert_history_table(p_hist_rec => l_hist_rec);
SELECT invoice_key
INTO l_invoice_key
FROM AP_APINV_APPROVERS
WHERE Notification_Key = itemkey
AND rownum = 1;
l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
SELECT PV.vendor_name,
AI.invoice_num,
AI.invoice_date,
AI.description,
AI.org_id,
AI.invoice_id,
AI.approval_iteration,
AI.vendor_contact_id,
NVL(AI.invoice_amount, 0)
FROM ap_invoices_all AI,
po_vendors PV
WHERE AI.invoice_id = p_invoice_id
AND AI.vendor_id = PV.vendor_id;
SELECT AP_NEGOTIATION_HIST_S.nextval
INTO l_new_child_ItemKey
FROM dual;
SELECT PV.vendor_name,
AI.invoice_num,
AI.invoice_date,
AI.description,
AI.org_id,
AI.invoice_id,
NVL(AI.invoice_amount, 0)
FROM ap_invoices_all AI,
ap_suppliers PV
WHERE AI.invoice_id = p_invoice_id
AND AI.invoice_type_lookup_code <> 'PAYMENT REQUEST'
AND AI.vendor_id = PV.vendor_id
UNION ALL
SELECT HZP.party_name,
AI.invoice_num,
AI.invoice_date,
AI.description,
AI.org_id,
AI.invoice_id,
NVL(AI.invoice_amount, 0)
FROM ap_invoices_all AI,
hz_parties HZP
WHERE AI.invoice_id = p_invoice_id
AND AI.invoice_type_lookup_code = 'PAYMENT REQUEST'
AND AI.party_id = HZP.party_id;
l_debug_info := 'Before UPDATE AP_INVOICES_ALL';
UPDATE AP_INVOICES_ALL
SET WFAPPROVAL_STATUS = 'INITIATED'
WHERE invoice_id = p_invoice_id;
UPDATE AP_INVOICE_LINES_ALL
SET WFAPPROVAL_STATUS = 'INITIATED'
where invoice_id = p_invoice_id;
l_debug_info := 'Before UPDATE AP_INVOICE_LINES_ALL';
SELECT COUNT(*)
INTO l_num
FROM ap_invoice_lines_all
WHERE po_header_id IS NOT NULL
AND invoice_id = l_invoice_id
and nvl(discarded_flag,'N')='N' /*bug 14748974 */
;
UPDATE AP_INVOICE_LINES_ALL
SET WFAPPROVAL_STATUS = 'INITIATED'
where invoice_id = p_invoice_id
and po_header_id IS NOT NULL
and nvl(discarded_flag,'N')='N';
UPDATE AP_INVOICE_LINES_ALL
SET WFAPPROVAL_STATUS = 'NOT REQUIRED'
where invoice_id = p_invoice_id
and (po_header_id IS NULL or
(po_header_id IS NOT NULL and nvl(discarded_flag,'N')='Y'));
UPDATE AP_INVOICE_LINES_ALL
SET WFAPPROVAL_STATUS = 'NOT REQUIRED'
where invoice_id = p_invoice_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'
AND invoice_iteration = c_iteration --bug 13441717
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'
AND invoice_iteration = c_iteration --bug 13441717
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 = 'APINVAPR'
AND item_key = l_invoice_key;
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(p_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(p_invoice_id),
approvalStatusIn => AME_UTIL.nullStatus,
approverNameIn => l_name,
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id);
SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
invap.Invoice_Key, al.line_number, al.amount
FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
WHERE invap.child_process_item_key = p_itemkey
AND invap.line_number = p_line_number
AND invap.invoice_id = p_invoice_id
AND al.line_number = invap.line_number
AND al.invoice_id = invap.invoice_id;
l_debug_info := 'Before Update Approvers table';
UPDATE AP_APINV_APPROVERS
SET Notification_status = 'COMP'
WHERE CHILD_PROCESS_ITEM_KEY = p_itemkey
AND INVOICE_ID = p_invoice_id
AND LINE_NUMBER = p_line_number;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
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);
l_debug_info := 'Before calling insert_history_table for Line'
|| l_line_number;
insert_history_table(p_hist_rec => l_hist_rec);
UPDATE AP_INVOICE_LINES_ALL
SET wfapproval_status = 'REJECTED'
,Last_Update_Date = sysdate
,Last_Updated_By = l_user_id
,Last_Update_Login = l_login_id
WHERE invoice_id = p_invoice_id
AND wfapproval_status <> 'MANUALLY APPROVED'
AND line_number = p_line_number;
SELECT invoice_key
INTO l_invoice_key
FROM AP_APINV_APPROVERS
WHERE invoice_id = p_invoice_id
AND line_number = p_line_number
AND child_process_item_key = p_itemkey
AND rownum = 1;
l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
SELECT count(*)
INTO l_sent
FROM AP_APINV_APPROVERS
WHERE Notification_status = 'SENT'
AND child_process_item_key = p_itemkey
AND INVOICE_ID = p_invoice_id
AND LINE_NUMBER = p_line_number;
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'
and nvl(discarded_flag,'N')='N'; --bug 12846778
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;
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;
l_debug_info := 'Before select';
select aha.release_lookup_code
into l_hold_release_code
from ap_holds_all aha,
ap_hold_codes ahc
where aha.invoice_id = l_invoice_id
and aha.org_id = l_org_id
and aha.hold_lookup_code = ahc.hold_lookup_code
and aha.hold_id = l_hold_id;
l_debug_info := 'After select, reultout : ' || resultout;
l_debug_info := 'Before select';
select nvl(ahc.wait_before_notify_days,0) -- Bug 10090981 *24*60
into l_wait_time
from ap_holds_all aha,
ap_hold_codes ahc
where aha.invoice_id = l_invoice_id
and aha.org_id = l_org_id
and aha.hold_lookup_code = ahc.hold_lookup_code
and aha.hold_id = l_hold_id;
l_debug_info := 'After select, reultout : ' || resultout;
l_debug_info := 'Before select';
select aha.release_lookup_code,
nvl(ahc.reminder_days,0) -- Bug 10090981 *24*60 --Bug8839774
into l_hold_release_code,
l_wait_time
from ap_holds_all aha,
ap_hold_codes ahc
where aha.invoice_id = l_invoice_id
and aha.org_id = l_org_id
and aha.hold_lookup_code = ahc.hold_lookup_code
and aha.hold_id = l_hold_id;
l_debug_info := 'After select, reultout : ' || resultout;
UPDATE ap_holds_all
SET wf_status = 'MANUALLYRELEASED'
WHERE hold_id = p_hold_id;
l_debug_info := 'Before select for l_num_holds_under_neg';
SELECT count(*)
INTO l_num_holds_under_neg
FROM ap_holds_all aha
WHERE aha.invoice_id = p_invoice_id
AND aha.org_id = p_org_id
AND aha.wf_status = 'NEGOTIATE';
l_debug_info := 'After select for l_num_holds_under_neg, l_num_holds_under_neg = ' || l_num_holds_under_neg;
l_debug_info := 'Before select for l_num_lines_under_neg';
SELECT COUNT(*)
INTO l_num_lines_under_neg
FROM ap_invoice_lines_all ail, ap_apinv_approvers aaa
WHERE ail.invoice_id = p_invoice_id
AND ail.org_id = p_org_id
AND aaa.invoice_id = ail.invoice_id
AND aaa.line_number = ail.line_number
AND aaa.approval_status = 'NEGOTIATE';
l_debug_info := 'After select for l_num_lines_under_neg, l_num_lines_under_neg = ' || l_num_lines_under_neg;
SELECT NVL(wfapproval_status, 'NOT REQUIRED')
INTO l_wfapproval_status
FROM ap_invoices_all
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 INTO AP_APPROVERS_LIST_GT(
TRANSACTION_ID, -- Bug 5624200
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(
p_invoice_id,
decode( l_class(l_rec),
'header',null,
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 INTO AP_APPROVERS_LIST_GT(
TRANSACTION_ID, -- Bug 5624200
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(
p_invoice_id,
decode(l_next_approver.item_class,
'header',null,
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 item_key, item_type,root_activity,
SUBSTR(item_key,1,INSTR(item_key,'_')-1) invoice_id
FROM wf_items
WHERE item_type IN ('APINVAPR','APINVNEG')
AND end_date is NULL -- Bug No 10172485
ORDER BY decode(root_activity,'APPROVAL_MAIN','3'
,'APPROVAL_INVOICE_LINES','2'
,'APPROVAL_NEGOTIATION', '1') asc;
SELECT item_key, item_type,root_activity,
ai.invoice_id
FROM wf_items wf,
ap_invoices_all ai
WHERE wf.item_type IN ('APINVAPR','APINVNEG')
AND wf.end_date is NULL -- Bug No 10172485
and ai.invoice_id||'_'||ai.approval_iteration = wf.item_key
and ai.org_id=p_org_id
ORDER BY decode(root_activity,'APPROVAL_MAIN','3'
,'APPROVAL_INVOICE_LINES','2'
,'APPROVAL_NEGOTIATION', '1') asc;
DELETE FROM AP_APINV_APPROVERS WHERE invoice_id in
(select invoice_id from ap_invoices_all ai
where ai.org_id=p_org_id
and wfapproval_status in ('INITIATED','REQUIRED'
,'REJECTED','NEEDS WFREAPPROVAL'
,'STOPPED')
);
UPDATE ap_invoice_lines_all
SET wfapproval_status = 'NOT REQUIRED'
WHERE wfapproval_status in ('INITIATED','REQUIRED'
,'REJECTED','NEEDS WFREAPPROVAL'
,'STOPPED');
UPDATE ap_invoices_all
SET wfapproval_status = 'NOT REQUIRED'
WHERE wfapproval_status in ('INITIATED','REQUIRED'
,'REJECTED','NEEDS WFREAPPROVAL'
,'STOPPED');
UPDATE ap_invoice_lines_all ail
SET ail.wfapproval_status = 'NOT REQUIRED'
WHERE ail.invoice_id in
( select /*+unnest*/ invoice_id
from ap_invoices_all ai
where ai.org_id=p_org_id
and ai.wfapproval_status in ('INITIATED','REQUIRED'
,'REJECTED','NEEDS WFREAPPROVAL','STOPPED')
)
AND wfapproval_status in ('INITIATED','REQUIRED'
,'REJECTED','NEEDS WFREAPPROVAL'
,'STOPPED');
UPDATE ap_invoices_all
SET wfapproval_status = 'NOT REQUIRED'
WHERE org_id = p_org_id
AND wfapproval_status in ('INITIATED','REQUIRED'
,'REJECTED','NEEDS WFREAPPROVAL'
,'STOPPED');
SELECT count(*)
INTO l_sent
FROM AP_APINV_APPROVERS
WHERE Notification_status = 'SENT'
AND child_process_item_key = p_itemkey
AND invoice_id = p_invoice_id;
SELECT invoice_key
INTO l_invoice_key
FROM AP_APINV_APPROVERS
WHERE Notification_Key = p_itemkey
AND invoice_id = p_invoice_id
AND rownum = 1;
l_debug_info := 'No Data Found in SELECT from AP_APINV_APPROVERS' ;
update ap_invoice_lines_all ail
set generate_dists = decode(ail.generate_dists, 'Y', 'D', ail.generate_dists)
where ail.invoice_id = l_invoice_id
and exists( select 1 from ap_invoice_distributions_all aid
where aid.invoice_id = l_invoice_id
and aid.invoice_line_number = ail.line_number) ;
l_debug_info := 'Exception during update.';
l_debug_info := 'Inserting wf_notes for approval from product page';
SELECT ai.invoice_type_lookup_code
INTO l_inv_type
FROM ap_invoices_all ai
WHERE invoice_id = l_invoice_id;
SELECT user_id,employee_id
FROM FND_USER
WHERE USER_NAME=p_rec_role;
Select response
From ap_inv_aprvl_hist
WHERE approval_history_id=(select max(approval_history_id)from ap_inv_aprvl_hist
where invoice_id= p_invoice_id);
/*insert_history( l_invoice_id,
l_iteration,
l_org_id,
l_status);*/
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
insert_history_table(p_hist_rec => l_hist_rec);
/*--Bug6406901, added the approver_name to the update cmd to populate correct value in AP_INV_APRVL_HLIST
UPDATE AP_INV_APRVL_HIST
set APPROVER_COMMENTS = l_comments, approver_name = l_display_name
WHERE APPROVAL_HISTORY_ID =(select max(APPROVAL_HISTORY_ID) from AP_INV_APRVL_HIST
where invoice_id=l_invoice_id);*/
Select response into l_response
From ap_inv_aprvl_hist
WHERE approval_history_id=(select max(approval_history_id)from ap_inv_aprvl_hist
where invoice_id=l_invoice_id);
insert_history(l_invoice_id,
l_iteration,
l_org_id,
l_result);
SELECT invoice_id
INTO l_temp_invoice_id
FROM ap_invoices
WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1)
AND wfapproval_status <> 'MANUALLY APPROVED'
FOR UPDATE NOWAIT;
UPDATE AP_INVOICES
SET wfapproval_status = 'REJECTED'
WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1)
AND wfapproval_status <> 'MANUALLY APPROVED'; */
WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey,
to_char(actid), funcmode);
WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey,
to_char(actid), funcmode);
WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey,
to_char(actid), funcmode);
SELECT user_id,employee_id
FROM FND_USER
WHERE USER_NAME=p_rec_role;
Select response
From ap_inv_aprvl_hist
WHERE approval_history_id=(select max(approval_history_id)from ap_inv_aprvl_hist
where invoice_id= p_invoice_id);
SELECT al.line_number, al.amount
FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
WHERE invap.Notification_Key = itemkey
AND al.line_number = invap.line_number
AND al.invoice_id = invap.invoice_id;
/*insert_history( l_invoice_id,
l_iteration,
l_org_id,
l_status);*/
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
insert_history_table(p_hist_rec => l_hist_rec);*/
insert_history_table(p_hist_rec => l_hist_rec);
/*--Bug6406901, added the approver_name to the update cmd to populate correct value in AP_INV_APRVL_HLIST
UPDATE AP_INV_APRVL_HIST
set APPROVER_COMMENTS = l_comments, approver_name = l_display_name
WHERE APPROVAL_HISTORY_ID =(select max(APPROVAL_HISTORY_ID) from AP_INV_APRVL_HIST
where invoice_id=l_invoice_id);*/
Select response into l_response
From ap_inv_aprvl_hist
WHERE approval_history_id=(select max(approval_history_id)from ap_inv_aprvl_hist
where invoice_id=l_invoice_id);
insert_history(l_invoice_id,
l_iteration,
l_org_id,
l_result);
SELECT invoice_id
INTO l_temp_invoice_id
FROM ap_invoices
WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1)
AND wfapproval_status <> 'MANUALLY APPROVED'
FOR UPDATE NOWAIT;
UPDATE AP_INVOICES
SET wfapproval_status = 'REJECTED'
WHERE invoice_id = substr(itemkey, 1, instr(itemkey,'_')-1)
AND wfapproval_status <> 'MANUALLY APPROVED'; */
WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey,
to_char(actid), funcmode);
WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey,
to_char(actid), funcmode);
WF_CORE.CONTEXT('APINV','SELECT_APPROVER',itemtype, itemkey,
to_char(actid), funcmode);
SELECT COUNT( name )
INTO l_user_role_count
FROM wf_roles
WHERE UPPER( email_address ) = UPPER( SUBSTR( WF_ENGINE.context_user, 7 ) )
AND orig_system = 'PER'
AND status = 'ACTIVE' ;
SELECT name
INTO l_user_name
FROM wf_roles
WHERE UPPER( email_address ) = UPPER( SUBSTR( WF_ENGINE.context_user, 7 ) )
AND orig_system = 'PER'
AND status = 'ACTIVE' ;
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
update ap_invoices
set validation_request_id = P_conc_id
where invoice_id = l_inv_id;
SELECT invap.Item_Class, invap.Item_Id, invap.Role_Name,
invap.Invoice_Key, al.line_number, al.amount
FROM AP_APINV_APPROVERS invap, AP_INVOICE_LINES_ALL al
WHERE invap.Notification_Key = itemkey
AND al.line_number = invap.line_number
AND al.invoice_id = invap.invoice_id;
SELECT user_id,employee_id
FROM FND_USER
WHERE USER_NAME=p_rec_role;
l_hist_rec.LAST_UPDATE_DATE := sysdate;
l_hist_rec.LAST_UPDATED_BY := nvl(TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),-1);
l_hist_rec.LAST_UPDATE_LOGIN := nvl(TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),-1);
ame_api2.updateApprovalStatus(
applicationIdIn => 200 ,
transactionTypeIn => 'APINV' ,
transactionIdIn => TO_CHAR(l_invoice_id) ,
approverIn => NxtApprover_rec ,
Forwardeein => Fwd_Rec );
AP_WORKFLOW_PKG.insert_history_table(p_hist_rec => l_hist_rec);
AME_API2.updateApprovalStatus2(applicationIdIn => 200,
transactionIdIn => to_char(l_invoice_id),
approvalStatusIn => AME_UTIL.forwardStatus,
approverNameIn => l_name,
transactionTypeIn => 'APINV',
itemClassIn => l_item_class,
itemIdIn => l_item_id,
forwardeeIn => fwd_rec,
updateItemIn => true);
AP_WORKFLOW_PKG.insert_history_table(p_hist_rec => l_hist_rec);