, ,

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;
/by
,

API to cancel AP Invoice

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);
 

/by
, ,

Initialize Apps,

To run or execute the Oracle API from the back-end, we need to Initialize the Apps first.

We have API to Initialize the Apps.

To initialize apps using the API, we need to pass few IN parameter values.

The following is the script prepared to initialize apps based on the
Application, User-name and Responsibility name given.

Example:-

DECLARE
   l_appl_id             NUMBER;
   l_appl_name           VARCHAR2 (100) := 'PA';
   l_user_id             NUMBER;
   l_user_name           VARCHAR2 (100) := 'OPERATIONS';
   l_responsibility_id   NUMBER;
   l_resp_name           VARCHAR2 (200)
                                       := 'Projects, Vision Operations (USA)';
BEGIN

-- To get the Application ID of given Application.
   SELECT application_id
     INTO l_appl_id
     FROM fnd_application
    WHERE application_short_name = l_appl_name;

-- To get the User ID information of  given user
   SELECT user_id
     INTO l_user_id
     FROM fnd_user
    WHERE user_name = l_user_name;

-- To get the Resp ID information of the given responsibility.
   SELECT responsibility_id
     INTO l_responsibility_id
     FROM fnd_responsibility_tl
    WHERE responsibility_name = l_resp_name AND application_id = l_appl_id;

--Initialixze the Application to use the API. 
   fnd_global.apps_initialize (l_user_id, l_responsibility_id, l_appl_id);
END;

I Hope the above script would help understanding about the Initializing the Apps API.
/by
,

Credit Card Number (of different format) from specific column

Following query can be used to get the Credit Card Number (of different format) from specific column.

Column would have credit card Number in the text date. Date is not in any fixed format. And Credit Card Number would also not in any specific Format. In the following query, we have considered few credit card formats. In can include other formats accordingly as your requirement.

SELECT jtf_note_id, creation_date, LANGUAGE, notes,
CASE
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘99999999999999999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘99999999999999999’
 ),
LENGTH (‘99999999999999999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘999999999999999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘999999999999999’
 ),
LENGTH (‘999999999999999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘9999 9999 9999 9999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘9999 9999 9999 9999’
 ),
LENGTH (‘9999 9999 9999 9999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘9999 999999 99999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘9999 999999 99999’
 ),
LENGTH (‘9999 999999 99999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘9999-9999-9999-9999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘9999-9999-9999-9999’
 ),
LENGTH (‘9999-9999-9999-9999’)
)
WHEN INSTR (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’),
‘9999-999999-99999’
) > 0
THEN SUBSTR (notes,
INSTR (TRANSLATE (UPPER (notes),
 ‘0123456789’,
 ‘9999999999’
 ),
 ‘9999-999999-99999’
 ),
LENGTH (‘9999-999999-99999’)
)
ELSE ‘No Credit card Number’
END “Credit card Number”
FROM jtf_notes_tl
WHERE 1 = 1
AND (TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%999999999999999 %’) ) — 15 digit—
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999999999999999 %’)
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999 9999 9999 9999 %’)
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999 999999 99999 %’)
— below are different formats with ‘-‘ instead of ‘ ”
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999-9999-9999-9999 %’)
OR TRANSLATE (UPPER (notes), ‘0123456789’, ‘9999999999’) LIKE (‘%9999-999999-99999 %’)

I hope the above information would be helpful to you.
/by
, ,

Release 12.0, the “Import Standard Purchase Orders” concurrent program fails with the following error: ERROR ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT. Procedure init_sys_parameters.0

On Release 12.0, the “Import Standard Purchase Orders” concurrent program fails with the following error:

ERROR
ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT.
Procedure init_sys_parameters.0
ORA-01422: exact fetch returns more than requested number of rows in Package
po.plsql.PO_PDOI_PVT. Procedure init_startup_values.10
ORA-01422: exact fetch returns more than requested number of rows in Package po.plsql.PO_PDOI_PVT.
Procedure start_process.50
User-Defined Exception in Package po.plsql.PO_PDOI_Concurrent. Procedure POXPDOI.30

Steps To Reproduce:
1. Populate the interface table with the PO details.
2. Navigate to Requests -> Run -> Single request.
3. Select ‘Import Standard Purchase Orders’.

Cause

The multi_org_category flag was not set correctly.

Verify by running the following script:

Select multi_org_category
from fnd_concurrent_programs
where concurrent_program_name=’POXPOPDOI’;

Ideally, the above script should return a value ‘S’, which indicates it is set to single org. In problematic case it returns no rows.

Solution

To implement the solution, please execute one of the following set of steps:

1. Ensure that you have taken a backup of your system before applying the recommended solution.

2. Run the following scripts in a TEST environment first:

Update fnd_concurrent_programs
set multi_org_category = ‘S’
where concurrent_program_name=’POXPOPDOI’;

3. Commit the transaction using ‘commit’ command.

4. Once the scripts complete, confirm that the data is corrected.
You can use the following SQL to confirm:
Select multi_org_category from fnd_concurrent_programs where concurrent_program_name=’POXPOPDOI’;
— should return a value ‘S’.

5. Confirm that the data is corrected, run the “Import Standard Purchase Orders” concurrent program.

6. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.
*** Alternately, this can be achieved via the application with the following steps:
1. Assign yourself the responsibility System Administration. (note it is not system administrator).

2. Navigate to responsibility System Administration – Concurrent Programs form
– Search on POXPOPDOI as short name
– Choose Update
– Move to Request tab
– Off to the right it shows – Operating Unit Mode
– Ensure this is single

Make sure it is S – using this sql –

Select multi_org_category from fnd_concurrent_programs where concurrent_program_name=’POXPOPDOI’;

After saving.

3. Retest the import and confirm if that has properly corrected the problem.

/by