PROCEDURE FUJ_CRT_TICK_APINVOICE
(ERRBUF out varchar2, RETCODE out varchar2
,P_PO_NUMBER VARCHAR2,P_GL_DATE VARCHAR2,P_PAYMENT_METHOD VARCHAR2
--,AMOUNT NUMBER
)
AS
L_VENDOR_ID NUMBER;
L_VENDOR_SITE_ID NUMBER;
L_AMOUNT NUMBER;
v_request_id NUMBER;
l_boolean boolean;
l_phase varchar2(200);
l_status varchar2(200);
l_dev_phase varchar2(200);
l_dev_status varchar2(200);
l_message varchar2(200);
BEGIN
SELECT VENDOR_ID, VENDOR_SITE_ID,POL.UNIT_PRICE
INTO L_VENDOR_ID , L_VENDOR_SITE_ID, L_AMOUNT
FROM PO_HEADERS_ALL POH,
PO_LINES_ALL POL
WHERE SEGMENT1 = P_PO_NUMBER --'3696'
AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
;
insert into AP_INVOICES_INTERFACE (
invoice_id,
invoice_num,
vendor_id,
vendor_site_id,
invoice_amount,
INVOICE_CURRENCY_CODE,
invoice_date,
DESCRIPTION,
--PAY_GROUP_LOOKUP_CODE,
source,
org_id
,po_NUMBER
,PAYMENT_METHOD_CODE
)
values (
ap_invoices_interface_s.NEXTVAL,
'TICK-'||ap_invoices_interface_s.CURRVAL,--P_PO_NUMBER
L_VENDOR_ID ,
L_VENDOR_SITE_ID ,
100,--L_AMOUNT,--
'AED',
fnd_conc_date.string_to_date(P_GL_DATE),
'This Invoice is created for ticket',
-- 'WUFS SUPPLIER',
'MANUAL INVOICE ENTRY',
102
,P_PO_NUMBER
,P_PAYMENT_METHOD
);
insert into AP_INVOICE_LINES_INTERFACE (
invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
DIST_CODE_COMBINATION_ID
)
values (
ap_invoices_interface_s.CURRVAL,
AP_INVOICE_LINES_INTERFACE_S.NEXTVAL,
1,
'ITEM',
100,
3362334
);
COMMIT;
begin
mo_global.init ('SQLAP');
MO_GLOBAL.set_policy_context('S',102);
fnd_global.apps_initialize (2605,50579,200,0,0);
FND_REQUEST.SET_ORG_ID(102);
v_request_id :=fnd_request.submit_request (APPLICATION => 'SQLAP',
PROGRAM => 'APXIIMPT',
DESCRIPTION => '',
START_TIME => NULL,
SUB_REQUEST => FALSE,
ARGUMENT1 => 102,
ARGUMENT2 =>'MANUAL INVOICE ENTRY',
ARGUMENT3 =>null,
ARGUMENT4 =>NULL,
ARGUMENT5 =>NULL,
ARGUMENT6 =>NULL,
ARGUMENT7 =>NULL,
ARGUMENT8 =>'N',
ARGUMENT9 =>'Y'
);
Commit;
IF v_request_id > 0
THEN
l_boolean :=
FND_CONCURRENT.WAIT_FOR_REQUEST (v_request_id --request_id IN number default NULL,
,20 --Interval IN number default 60, SECONDS
,0 --max_wait IN number default 0,
,l_phase --phase OUT varchar2,
,l_status --status OUT varchar2,
,l_dev_phase --dev_phase OUT varchar2,
,l_dev_status --dev_status OUT varchar2,,
,l_message --message OUT varchar2) return boolean
);
end if;
fnd_file.put_line(fnd_file.log,'Please see the output of Payables OPEN Invoice Import program request id :'|| v_request_id);
dbms_output.put_line('success');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error :'||sqlerrm);
dbms_output.put_line('Error :'||sqlerrm);
end;
fnd_file.put_line(fnd_file.log,'SUCCESS');
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,'Error :'||sqlerrm);
dbms_output.put_line('Error :'||sqlerrm);
END;
---------Validating the AP invoice from backend by submitting concurrent program.
declare
v_request_id number;
begin
mo_global.init ('SQLAP');
MO_GLOBAL.set_policy_context('S',443);
fnd_global.apps_initialize (26015,524410,200,0,0);
FND_REQUEST.SET_ORG_ID(443);
v_request_id :=fnd_request.submit_request (APPLICATION => 'SQLAP',
PROGRAM => 'APPRVL',
DESCRIPTION => '',
START_TIME => NULL,
SUB_REQUEST => FALSE,
ARGUMENT1 => 443,
ARGUMENT2 =>'All',
ARGUMENT3 =>null,
ARGUMENT4 =>TO_CHAR(SYSDATE,'YYYY-MM-DD'),
ARGUMENT5 =>TO_CHAR(SYSDATE,'YYYY-MM-DD'),
ARGUMENT6 =>NULL,
ARGUMENT7 =>NULL,
ARGUMENT8 =>NULL, --or pass invoice id here remove dates
ARGUMENT9 =>NULL ,
ARGUMENT10 =>'N',
ARGUMENT11 =>1000
);
Commit;
dbms_output.put_line(v_request_id);
end;
--checking invoice ap approval status --
apps.ap_invoices_pkg.get_approval_status (p_invoice_id,
NULL,
NULL,
NULL
);
--To approve AP Invoice from backend
create or replace procedure xxap_inv_auto_approval(p_invoice_id IN NUMBER)
IS
v_hist_id NUMBER;
v_user_id NUMBER;
v_login_id NUMBER;
v_user_name VARCHAR2 (150) := 'SYSTEM-USER';
v_validation_status VARCHAR2 (50);
v_stop_approval_result BOOLEAN;
v_hist_rec ap_inv_aprvl_hist_all%ROWTYPE;
v_org_id ap_invoices_all.org_id%TYPE;
v_invoice_amount ap_invoices_all.invoice_amount%TYPE;
v_validation_request_id ap_invoices_all.validation_request_id%TYPE;
v_wfapproval_status ap_invoices_all.wfapproval_status%TYPE;
BEGIN
BEGIN
SELECT org_id, invoice_amount, wfapproval_status
INTO v_org_id, v_invoice_amount, v_wfapproval_status
FROM ap_invoices_all
WHERE invoice_id = p_invoice_id;
SELECT user_id
INTO v_user_id
FROM fnd_user
WHERE user_name = v_user_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,'Exception in first block-' || SQLERRM
);
DBMS_OUTPUT.put_line ('Exception in first block-' || SQLERRM);
END;
BEGIN
v_validation_status :=
apps.ap_invoices_pkg.get_approval_status (p_invoice_id,
NULL,
NULL,
NULL
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Exception retrieving invoice validation status-'
|| SQLERRM
);
DBMS_OUTPUT.put_line
( 'Exception retrieving invoice validation status-'
|| SQLERRM
);
END;
IF (UPPER (v_validation_status) <> 'APPROVED')
THEN
fnd_file.put_line (fnd_file.LOG, 'Invoice is not validated');
DBMS_OUTPUT.put_line ('Invoice is not validated');
ELSE
IF (v_wfapproval_status = 'INITIATED')
THEN
v_stop_approval_result :=
ap_workflow_pkg.stop_approval
(p_invoice_id,
NULL,
'INV_SUM_ACTIONS_WFAPPROVE.do_force_approval'
);
--Bug5527190
IF v_stop_approval_result = TRUE
THEN
fnd_file.put_line
(fnd_file.LOG,
'Invoice approval workflow is forcibly stopped'
);
DBMS_OUTPUT.put_line
('Invoice approval workflow is forcibly stopped');
END IF;
END IF;
UPDATE ap_invoices_all
SET wfapproval_status = 'MANUALLY APPROVED'
WHERE invoice_id = p_invoice_id;
UPDATE ap_invoice_lines_all
SET wfapproval_status = 'MANUALLY APPROVED'
WHERE invoice_id = p_invoice_id;
--insert into the history table
v_hist_rec.history_type := 'DOCUMENTAPPROVAL';
v_hist_rec.invoice_id := p_invoice_id;
v_hist_rec.iteration := 0;
v_hist_rec.org_id := v_org_id;
v_hist_rec.approver_name := v_user_name;
v_hist_rec.amount_approved := v_invoice_amount;
v_hist_rec.created_by := v_user_id;
v_hist_rec.last_updated_by := v_user_id;
v_hist_rec.last_update_login := -1;
v_hist_rec.creation_date := SYSDATE;
v_hist_rec.last_update_date := SYSDATE;
v_hist_rec.response := 'MANUALLY APPROVED';
v_hist_rec.approver_comments := 'System Auto Approved';
ap_workflow_pkg.insert_history_table (v_hist_rec);
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Exception in auto approval main block-' || SQLERRM
);
DBMS_OUTPUT.put_line ('Exception in auto approval main block-' || SQLERRM);
END xxap_inv_auto_approval;
