AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE:Is_Invoice_Cancellable is a Function in the AP_CANCEL_PKG package that checks that an Invoice is cancellable or not when an Invoice Cancellation process starts.It follows the following steps and returns a Boolean value depending on the result.
- If invoice contains distribution that does not have open GL period return FALSE.
- If invoice has an effective payment, return FALSE.
- If invoice is selected for payment, return FALSE.
- If invoice is already cancelled, return FALSE.
- If invoice is credited invoice, return FALSE.
- If invoices have been applied against this invoice, return FALSE.
- If invoice is matched to Finally Closed PO’s, return FALSE.
- If project related invoices have pending adjustments, return FALSE.
- If cancelling will cause qty_billed or amount_billed to less than 0, return FALSE.
- If none of above, invoice is cancellable return True.
Here is a small procedure to check if an Invoice is cancellable or not.
create or replace procedure XX_INV_CANCELLABLE (p_inv_id IN NUMBER)
is
v_boolean BOOLEAN;
v_error_code VARCHAR2(100);
v_debug_info VARCHAR2(1000);
begin
v_boolean :=AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE(
P_invoice_id => p_inv_id,
P_error_code => v_error_code,
P_debug_info => v_debug_info,
P_calling_sequence => NULL);
IF v_boolean=TRUE
THEN
DBMS_OUTPUT.put_line (‘Invoice ‘||p_inv_id|| ‘ is cancellable’ );
ELSE
DBMS_OUTPUT.put_line (‘Invoice ‘||p_inv_id|| ‘ is not cancellable :’|| v_error_code );
END IF;
End XX_INV_CANCELLABLE;
Execute XX_INV_CANCELLABLE(12960);
AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE:
AP_CANCEL_SINGLE_INVOICE is a Function in the AP_CANCEL_PKG package that cancels one invoice by executing the following sequence of steps, returning TRUE if successful and FALSE otherwise.
1] Check if the invoice is cancellable. if yes, proceed otherwise return false.
2] If invoice has tax withheld, undo withholding.
3] Clear out all payment schedules.
4] Cancel all the non-discard lines.
a. reverse matching
b. fetch the maximum distribution line number
c. Set encumbered flags to ‘N’
d. Accounting event generation
e. reverse the distributions
f. update Line level Cancelled information
5] Zero out the Invoice.
6] Run AutoApproval for this invoice.
7] Check posting holds remain on this cancelled invoice.
a. if NOT exist – complete the cancellation by updating header level information set return value to TRUE.
b. if exist – no update, set the return values to FALSE, NO DATA rollback.
8] Commit the Data.
9] Populate the out parameters.
Here is a small procedure to cancel a single invoice.
create or replace procedure XX_INV_CANCEL(
P_xx_invoice_id IN NUMBER,
P_xx_last_updated_by IN NUMBER,
P_xx_last_update_login IN NUMBER,
P_xx_accounting_date IN DATE)
is
v_boolean BOOLEAN;
v_message_name VARCHAR2(1000);
v_invoice_amount NUMBER;
v_base_amount NUMBER;
v_temp_cancelled_amount NUMBER;
v_cancelled_by VARCHAR2(1000);
v_cancelled_amount NUMBER;
v_cancelled_date DATE;
v_last_update_date DATE;
v_orig_prepay_amt NUMBER;
v_pay_cur_inv_amt NUMBER;
v_token VARCHAR2(100);
begin
v_boolean := AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE
(p_invoice_id => P_xx_invoice_id,
p_last_updated_by => P_xx_last_updated_by,
p_last_update_login => P_xx_last_update_login,
p_accounting_date => P_xx_accounting_date,
p_message_name => v_message_name,
p_invoice_amount => v_invoice_amount,
p_base_amount => v_base_amount,
p_temp_cancelled_amount => v_temp_cancelled_amount,
p_cancelled_by => v_cancelled_by,
p_cancelled_amount => v_cancelled_amount,
p_cancelled_date => v_cancelled_date,
p_last_update_date => v_last_update_date,
p_original_prepayment_amount => v_orig_prepay_amt,
p_pay_curr_invoice_amount => v_pay_cur_inv_amt,
P_Token => v_token,
p_calling_sequence => NULL
);
IF v_boolean
THEN
DBMS_OUTPUT.put_line (‘Successfully Cancelled the Invoice’ );
COMMIT;
ELSE
DBMS_OUTPUT.put_line (‘Failed to Cancel the Invoice’ );
ROLLBACK;
END IF;
end XX_INV_CANCEL;
Execute XX_INV_CANCEL(120573,2325,-1,SYSDATE);
Calling Stored procedure through Forms Personalization
2. Create an action of type “BuiltIn”
3. BuiltIn Type for Action should be “Execute a Procedure”
v_field_value VARCHAR2(200) ;
begin
plsql_package.procedurenameHere ;
end’
v_field_value VARCHAR2(200) ;
begin
XX_PRC(”’||${item.PO_CONTROL_RULES.OBJECT_CODE_DISPLAYED_VALUE.value}||”’);
end’
Also, there is no semi colon after “end”
”’||${item.BLOCKNAME.FIELDNAME.value}||”’
Share this:
API – Allocate and Transact Orders (Transact Move Orders)
create or replace procedure XX_ALOCATE_TRANSACT_MOVE_ORDER (P_REQUEST_NUMBER VARCHAR2 ,P_ORGANIZATION_ID NUMBER,P_TRANSACTION_DATE varchar2)as
----------------- ALLOCATE MOVE ORDER API REQUIREMENTS -------------------------
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
l_return_values VARCHAR2 (2) := fnd_api.g_false;
l_commit VARCHAR2 (2) := fnd_api.g_false;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (255);
l_user_id NUMBER ;
l_resp_id NUMBER ;
l_appl_id NUMBER ;
l_row_cnt NUMBER := 1;
l_trohdr_rec inv_move_order_pub.trohdr_rec_type;
l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
x_trohdr_rec inv_move_order_pub.trohdr_rec_type;
x_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
l_validation_flag VARCHAR2 (2) := inv_move_order_pub.g_validation_yes;
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
x_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
x_number_of_rows NUMBER ;
x_transfer_to_location NUMBER ;
x_expiration_date DATE;
x_transaction_temp_id NUMBER ;
----------------- TRANSACT MOVE ORDER API REQUIREMENTS -------------------------
a_l_api_version NUMBER := 1.0;
a_l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
a_l_commit VARCHAR2 (2) := fnd_api.g_false;
a_x_return_status VARCHAR2 (2);
a_x_msg_count NUMBER := 0;
a_x_msg_data VARCHAR2 (255);
a_l_move_order_type NUMBER := 3;
a_l_transaction_mode NUMBER := 1;
a_l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
a_l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
a_x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
a_x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
a_l_transaction_date DATE := SYSDATE;
a_l_user_id NUMBER;
a_l_resp_id NUMBER;
a_l_appl_id NUMBER;
v_transaction_date date;
CURSOR c_mo_details IS
SELECT mtrh.header_id,
mtrh.request_number,
mtrh.move_order_type,
mtrh.organization_id,
mtrl.line_id,
mtrl.line_number,
mtrl.inventory_item_id,
mtrl.lot_number,
mtrl.quantity,
revision,mtrl.from_locator_id,
(select distinct operating_unit from org_organization_definitions
where organization_id = mtrh.organization_id) org_id
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrh.header_id = mtrl.header_id
AND (mtrh.request_number = P_REQUEST_NUMBER OR P_REQUEST_NUMBER IS NULL )--'8007'
AND (mtrh.organization_id = P_ORGANIZATION_ID OR P_ORGANIZATION_ID IS NULL ); -- 755;
BEGIN
FND_GLOBAL.APPS_INITIALIZE(1130, 50632, 401);
FOR i IN c_mo_details
LOOP
mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (NVL(i.organization_id,P_ORGANIZATION_ID));
-- mo_global.init ('INV');
SELECT COUNT (*)
INTO x_number_of_rows
FROM mtl_txn_request_lines
WHERE header_id = i.header_id;
DBMS_OUTPUT.put_line ('Calling INV_REPLENISH_DETAIL_PUB to Allocate MO');
-- Allocate each line of the Move Order
inv_replenish_detail_pub.line_details_pub(
p_line_id => i.line_id,
x_number_of_rows => x_number_of_rows,
x_detailed_qty => i.quantity,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_revision => i.revision,
x_locator_id => i.from_locator_id,
x_transfer_to_location => x_transfer_to_location,
x_lot_number => i.lot_number,
x_expiration_date => x_expiration_date,
x_transaction_temp_id => x_transaction_temp_id,
p_transaction_header_id => NULL,
p_transaction_mode => NULL,
p_move_order_type => i.move_order_type,
p_serial_flag => fnd_api.g_false,
p_plan_tasks => FALSE,
p_auto_pick_confirm => FALSE,
p_commit => FALSE
);
DBMS_OUTPUT.put_line('==========================================================');
DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line (x_msg_count);
IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
DBMS_OUTPUT.put_line (x_msg_data);
END IF;
IF (x_return_status = fnd_api.g_ret_sts_success) THEN
DBMS_OUTPUT.put_line ('ALLOCATE API SUCCESSFUL WITH - Trx temp ID: ');
DBMS_OUTPUT.put_line (x_transaction_temp_id);
END IF;
DBMS_OUTPUT.put_line('==========================================================');
IF x_transaction_temp_id IS NOT NULL OR x_transaction_temp_id <> 0 THEN
mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init ('INV');
a_l_trolin_tbl (1).line_id := i.line_id;
select to_date (P_TRANSACTION_DATE) --('31-AUG-2010')
into v_transaction_date
from dual;
FND_GLOBAL.APPS_INITIALIZE(1130, 50632, 401);
inv_pick_wave_pick_confirm_pub.pick_confirm
(p_api_version_number => a_l_api_version,
p_init_msg_list => a_l_init_msg_list,
p_commit => a_l_commit,
x_return_status => a_x_return_status,
x_msg_count => a_x_msg_count,
x_msg_data => a_x_msg_data,
p_move_order_type => i.move_order_type,
p_transaction_mode => a_l_transaction_mode,
p_trolin_tbl => a_l_trolin_tbl,
p_mold_tbl => a_l_mold_tbl,
x_mmtt_tbl => a_x_mmtt_tbl,
x_trolin_tbl => a_x_trolin_tbl,
p_transaction_date => v_transaction_date -- l_transaction_date
);
DBMS_OUTPUT.put_line('=======================================================');
DBMS_OUTPUT.put_line ('Return Status - '||a_x_return_status||' '||sqlerrm);
DBMS_OUTPUT.put_line ('Return Message - '||a_x_msg_data);
DBMS_OUTPUT.put_line (a_x_msg_count);
IF (a_x_return_status <> fnd_api.g_ret_sts_success) THEN
DBMS_OUTPUT.put_line (a_x_msg_data);
END IF;
IF (a_x_return_status = fnd_api.g_ret_sts_success) THEN
DBMS_OUTPUT.put_line ('TRANSACT API SUCCESSFULLY PROCESSED');
END IF;
DBMS_OUTPUT.put_line('=======================================================');
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('OUTER Exception Occured :');
DBMS_OUTPUT.put_line (SQLCODE||':'||SQLERRM);
DBMS_OUTPUT.put_line ('=======================================================');
END;
END;
begin
XX_ALOCATE_TRANSACT_MOVE_ORDER('',763,'31-AUG-2010');
end;
Share this:
API to cancel AP Invoice
Is_Invoice_Cancellable is a Function in the AP_CANCEL_PKG package that checks that an Invoice is cancellable or not when an Invoice Cancellation process starts.
It follows the following steps and returns a Boolean value depending on the result.
Here is a small procedure to check if an Invoice is cancellable or not.
create or replace procedure XX_INV_CANCELLABLE (p_inv_id IN NUMBER)
is
v_boolean BOOLEAN;
v_error_code VARCHAR2(100);
v_debug_info VARCHAR2(1000);
begin
v_boolean :=AP_CANCEL_PKG.IS_INVOICE_CANCELLABLE(
P_invoice_id => p_inv_id,
P_error_code => v_error_code,
P_debug_info => v_debug_info,
P_calling_sequence => NULL);
IF v_boolean=TRUE
THEN
DBMS_OUTPUT.put_line (‘Invoice ‘||p_inv_id|| ‘ is cancellable’ );
ELSE
DBMS_OUTPUT.put_line (‘Invoice ‘||p_inv_id|| ‘ is not cancellable :’|| v_error_code );
END IF;
End XX_INV_CANCELLABLE;
Execute XX_INV_CANCELLABLE(12960);
AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE:
AP_CANCEL_SINGLE_INVOICE is a Function in the AP_CANCEL_PKG package that cancels one invoice by executing the following sequence of steps, returning TRUE if successful and FALSE otherwise.
1] Check if the invoice is cancellable. if yes, proceed otherwise return false.
2] If invoice has tax withheld, undo withholding.
3] Clear out all payment schedules.
4] Cancel all the non-discard lines.
5] Zero out the Invoice.
6] Run AutoApproval for this invoice.
7] Check posting holds remain on this cancelled invoice.
8] Commit the Data.
9] Populate the out parameters.
Here is a small procedure to cancel a single invoice.
create or replace procedure XX_INV_CANCEL(
P_xx_invoice_id IN NUMBER,
P_xx_last_updated_by IN NUMBER,
P_xx_last_update_login IN NUMBER,
P_xx_accounting_date IN DATE)
is
v_boolean BOOLEAN;
v_message_name VARCHAR2(1000);
v_invoice_amount NUMBER;
v_base_amount NUMBER;
v_temp_cancelled_amount NUMBER;
v_cancelled_by VARCHAR2(1000);
v_cancelled_amount NUMBER;
v_cancelled_date DATE;
v_last_update_date DATE;
v_orig_prepay_amt NUMBER;
v_pay_cur_inv_amt NUMBER;
v_token VARCHAR2(100);
begin
v_boolean := AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE
(p_invoice_id => P_xx_invoice_id,
p_last_updated_by => P_xx_last_updated_by,
p_last_update_login => P_xx_last_update_login,
p_accounting_date => P_xx_accounting_date,
p_message_name => v_message_name,
p_invoice_amount => v_invoice_amount,
p_base_amount => v_base_amount,
p_temp_cancelled_amount => v_temp_cancelled_amount,
p_cancelled_by => v_cancelled_by,
p_cancelled_amount => v_cancelled_amount,
p_cancelled_date => v_cancelled_date,
p_last_update_date => v_last_update_date,
p_original_prepayment_amount => v_orig_prepay_amt,
p_pay_curr_invoice_amount => v_pay_cur_inv_amt,
P_Token => v_token,
p_calling_sequence => NULL
);
IF v_boolean
THEN
DBMS_OUTPUT.put_line (‘Successfully Cancelled the Invoice’ );
COMMIT;
ELSE
DBMS_OUTPUT.put_line (‘Failed to Cancel the Invoice’ );
ROLLBACK;
END IF;
end XX_INV_CANCEL;
Execute XX_INV_CANCEL(120573,2325,-1,SYSDATE);
Share this:
Flexfields avilable for your Oracle Application Version
select faa.APPLICATION_NAME “Application Name”, fif.id_flex_name “Flexfield Name”, faa.description “Application Description”
from FND_ID_FLEXS fif, FND_APPLICATION_ALL_VIEW faa
WHERE fif.APPLICATION_ID = faa.APPLICATION_ID
order by faa.APPLICATION_NAME;
Share this:
Submit the Concurrent Program from Backend
Note:- This is the Concurrent Program, not the Request Set. To Submit the Request Set from the backend, We have different API.
I have already document, for submitting the request set from the backend in the Following URL.
http://oracleerpappsguide.blogspot.com/2011/09/registering-executable-concurrent.html
DECLARE
l_success NUMBER;
BEGIN
BEGIN
fnd_global.apps_initialize( user_id => 2572694, resp_id => 50407, resp_appl_id => 20003);
— If you are directly running from the database using the TOAD, SQL-NAVIGATOR or –SQL*PLUS etc. Then you need to Initialize the Apps. In this case use the above API to –Initialize the APPS. If you are using same code in some procedure and running directly
–from application then you don’t need to initalize.
–Then you can comment the above API.
l_success :=
fnd_request.submit_request
(‘XXAPP’, — Application Short name of the Concurrent Program.
‘XXPRO_RPT’, — Program Short Name.
‘Program For testing the backend Report’, — Description of the Program.
SYSDATE, — Submitted date. Always give the SYSDATE.
FALSE, — Always give the FLASE.
‘1234’ — Passing the Value to the First Parameter of the report.
);
COMMIT;
— Note:- In the above request Run, I have created the Report, which has one parameter.
IF l_success = 0
THEN
— fnd_file.put_line (fnd_file.LOG, ‘Request submission For this store FAILED’ );
DBMS_OUTPUT.PUT_LINE( ‘Request submission For this store FAILED’ );
ELSE
— fnd_file.put_line (fnd_file.LOG, ‘Request submission for this store SUCCESSFUL’);
DBMS_OUTPUT.PUT_LINE( ‘Request submission For this store SUCCESSFUL’ );
END IF;
—Note:- If you are running directly from database, use DBMS API to display. If you are
— Running directly from Application, then Use the fnd_file API to write the message
— in the log file.
END;
Share this: