Query to get Business Group, Legal Entity Name, Operating Unit Details

SELECT distinct hrl.country, hroutl_bg.NAME bg, hroutl_bg.organization_id,
       lep.legal_entity_id, lep.NAME legal_entity,
       hroutl_ou.NAME ou_name, hroutl_ou.organization_id org_id,
       hrl.location_id,
       hrl.location_code,
       glev.FLEX_SEGMENT_VALUE
  FROM xle_entity_profiles lep,
       xle_registrations reg,
       hr_locations_all hrl,
       hz_parties hzp,
       fnd_territories_vl ter,
       hr_operating_units hro,
       hr_all_organization_units_tl hroutl_bg,
       hr_all_organization_units_tl hroutl_ou,
       hr_organization_units gloperatingunitseo,
       gl_legal_entities_bsvs glev
 WHERE lep.transacting_entity_flag = 'Y'
   AND lep.party_id = hzp.party_id
   AND lep.legal_entity_id = reg.source_id
   AND reg.source_table = 'XLE_ENTITY_PROFILES'
   AND hrl.location_id = reg.location_id
   AND reg.identifying_flag = 'Y'
   AND ter.territory_code = hrl.country
   AND lep.legal_entity_id = hro.default_legal_context_id
   AND gloperatingunitseo.organization_id = hro.organization_id
   AND hroutl_bg.organization_id = hro.business_group_id
   AND hroutl_ou.organization_id = hro.organization_id
   AND glev.legal_entity_id = lep.legal_entity_id

ROI: Which Interface Tables are Used by ROI to Process Serial and Lot Controlled Items

Goal
 Which interface tables are used to populate Serial and Lot information for Receiving Open Interface (ROI)?

Executable:RVCTP - Receiving Transaction Processor

Solution

In addition to rcv_headers_interface and rcv_transactions_interface, the mtl_transaction_lots_interface and mtl_serial_numbers_interface tables must be populated for the Receiving Open Interface (ROI) to process Delivery, Correction and Return of Serial and Lot Controlled Items.

Significant software changes were made in 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher) to accommodate new ROI functionality.  Due to dependencies, the new ROI functionality will not be backported.

Example:
1) If Item is Serial Controlled only:


INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)  --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'sr000016',
'sr000016',
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 


2) If Item is Lot Controlled only:

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID) --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'lt2002',
1,
1,
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 


3) If Item is both Serial and Lot Controlled:


INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
FM_SERIAL_NUMBER,
TO_SERIAL_NUMBER,
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID)  --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'sr000016',
'sr000016',
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL); 

INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE (
TRANSACTION_INTERFACE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LOT_NUMBER,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SERIAL_TRANSACTION_TEMP_ID, --mtl_serial_numbers_interface.transaction_interface_id
PRODUCT_CODE,
PRODUCT_TRANSACTION_ID) --rcv_transactions_interface.interface_transaction_id
VALUES
(MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,
sysdate,
1,
sysdate,
1,
1,
'lt2002',
1,
1,
MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,
'RCV',
RCV_TRANSACTIONS_INTERFACE_S.CURRVAL);  --rcv_transactions_interface.interface_transaction_id



When transacting Items that are both Lot and Serial Controlled, if Quantity is greater than 1, populate mtl_serial_numbers_interface and mtl_transaction_lots_interface as follows:
1.  one record in mtl_serial_numbers_interface table for each Serial Number (or Serial Number range)
2.  for each of the mtl_serial_numbers_interface records, populate a corresponding record in mtl_transaction_lots_interface with the Lot Number associated with each mtl_serial_numbers_interface record (mtl_transaction_lots_interface.serial_transaction_temp_id=mtl_serial_numbers_interface.transaction_interface_id)

11.5.10 / R12 ROI How to Receive Intransit Shipment (Inter-org transfer) for Lot / Serial Controlled Items via Receiving Open Interface

Goal
 New functionality of 11.5.10/11i.SCM_PF.J/11i.PRC_PF.J (and higher)  supports for Inter-Org Transfers:
         . RECEIVE transaction
         . DELIVER to Inventory Transaction

How to enter a Receipt (Receive/Deliver Transactions) for Inter-Organization Shipments for Lot and Serial Controlled item?
 Solution

Following SAMPLE script is intended

To enter a Receive and Deliver to Inventory Transaction with Inventory destination type
for an Inter-Organization Transfer done between 2 inventory organizations with Direct
Receipt Routing through the Receiving Open Interface (ROI)
(ie Perform the RECEIVE and DELIVER transaction at the same time)


In order to be most comprehensive, the sample script has been tested for a Lot
and Serial Controlled Item.

The script will load records into the tables
          RCV_HEADERS_INTERFACE,
          RCV_TRANSACTIONS_INTERFACE,
          MTL_TRANSACTION_LOTS_INTERFACE
          MTL_SERIAL_NUMBERS_INTERFACE

If the item is only a standard item, only the records into
RCV_HEADERS_INTERFACE and RCV_TRANSACTIONS_INTERFACE tables have to be created.


          ------------------------SETUP------------------------

0) Ensure to apply the patches listed in Note 367396.1 Recommended Patches for Enhanced Receiving Validation after upgrade to or install of 11.5.10 (or 11i.SCM_PF.J) and For Release 12 see Note 423541.1


1) Inventory Setup/Organizations/Shipping Networks
from Sending Organization M1 to Destination Organization D2
Transfer Type=Intransit
Receipt Routing=Direct

2) Item  'Lot-Serial-Controlled-1' is a lot and serial controlled Item in
sending and Destination Organization

SQL> Select mp.organization_id Org_Id, mp.organization_code Org_Code,
msi.inventory_item_id, msi.segment1, decode(to_char(msi.lot_control_code),
  '2','Y',
  '1','N') LOT_CONTROL,
decode(to_char(msi.serial_number_control_code),
  '1','None',
  '2','Predefined',
  '5','Dynamic at INV receipt',
  '6','Dynamic at SO issue') SERIAL_CONTROL
  from mtl_system_items_b msi,mtl_parameters mp
where msi.segment1 like '&item' and msi.organization_id=mp.organization_id;

For both organizations, for item=Lot-Serial-Controlled-1 , inventory_item_id=169845
and LOT_CONTROL='Y' and SERIAL_CONTROL='Dynamic at INV receipt'


3) Inventory  / Transactions/ Inter Organization transfer
Create manual "Intransit Shipment" 'ROI-Lot-Serial-1' from M1 to D2 for a quantity of 10
- From Org= M1, To Org= D2
- Shipment: 'ROI-Lot-Serial-1' 
- Item= Lot-Serial-Controlled-1
- Lot=S00226
- From Subinventory=Stores
- To Subinventory=Staging1
- Quantity=10
- Start Serial Number=SM1_00001
- End Serial Number=SM1_00010


4) Run the following scripts so to find the necessary information to insert
into the RCV_TRANSACTIONS_INTERFACE table:

Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
        SHIPMENT_HEADER_ID=233534
        SHIPMENT_NUM= ROI-Lot-Serial-1
        RECEIPT_SOURCE_CODE=INVENTORY
        RECEIPT_NUM=null
        SHIP_TO_ORG_ID=210

Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
        SHIPMENT_HEADER_ID=233534
        SHIPMENT_LINE_ID=246486
        UNIT_OF_MEASURE=Each
        ITEM_ID=169845
        QUANTITY_SHIPPED=10
        QUANTITY_RECEIVED=0
        SHIPMENT_LINE_STATUS_CODE=EXPECTED
        SOURCE_DOCUMENT_CODE=INVENTORY
        ROUTING_HEADER_ID=3
        FROM_ORGANIZATION=207
        TO_ORGANIZATION_ID=210
        TO_SUBINVENTORY=Staging1


5)  Run the following scripts to identify the Lot/Serial Information
related to the SHIPMENT_LINE_ID=246486 of the Inter-Org Shipment with SHIPMENT_HEADER_ID=233534

Select * from RCV_LOTS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
         SHIPMENT_LINE_ID=246486
         SUPPLY_TYPE_CODE=SHIPMENT
         LOT_NUM=S00226
         QUANTITY=10

Select * from RCV_SERIALS_SUPPLY where SHIPMENT_LINE_ID=&Shipment_Line_id;
10 records with
         SHIPMENT_LINE_ID=246486
         SUPPLY_TYPE_CODE=SHIPMENT
         SERIAL_NUM= serial numbers from SM1_00001 to SM1_00010
         LOT_NUM=S00226



RECEIVE/ DELIVER to INVENTORY Transaction for INTER-ORG TRANSFER SHIPMENT Example

1) Insert via ROI a Direct DELIVER Receipt for Inter Organization Shipment Number
'ROI-Lot-Serial-1' (SHIPMENT_HEADER_ID=233534)
of 2 items in destination organization
with LOT_NUM=S00226 and serial numbers SM1_00001 to SM1_00002

Insert
. 1 record in RCV_HEADERS_INTERFACE table for the receipt header information
with SHIPMENT_NUM='ROI-Lot-Serial-1'
and VALIDATION_FLAG='Y'

. 1 record in RCV_TRANSACTIONS_INTERFACE table for SHIPMENT_LINE_ID=246486
with TRANSACTION_TYPE='RECEIVE' and AUTO_TRANSACT_CODE='DELIVER'
for a quantity =2 indicating
DESTINATION_TYPE_CODE='INVENTORY'
RECEIPT_SOURCE_CODE='INVENTORY' and SOURCE_DOCUMENT_CODE='INVENTORY'
VALIDATION_FLAG='Y'

. 1 record in MTL_TRANSACTION_LOTS_INTERFACE for a quantity=2
on lot number S00226

. 1 record in MTL_SERIAL_NUMBERS_INTERFACE , indicating
FM_SERIAL_NUMBER='SM1_00001'and TO_SERIAL_NUMBER='SM1_00002'


INSERT INTO RCV_HEADERS_INTERFACE
            (HEADER_INTERFACE_ID,
             GROUP_ID,
             PROCESSING_STATUS_CODE,
             RECEIPT_SOURCE_CODE,
             TRANSACTION_TYPE,
             AUTO_TRANSACT_CODE,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             LAST_UPDATE_LOGIN,
             CREATION_DATE,
             CREATED_BY,
             SHIPMENT_NUM,
             SHIP_TO_ORGANIZATION_ID,
             EXPECTED_RECEIPT_DATE,
             VALIDATION_FLAG
              )
            VALUES
             (rcv_headers_interface_s.nextval , --Header_Interface_Id
             rcv_interface_groups_s.nextval,   --Group_Id
             'PENDING', --Processing_Status_Code
             'INVENTORY', --Receipt_Source_Code
             'NEW', --Transaction_Type
             'DELIVER',  --Auto_Transact_Code
             SYSDATE, --Last_Update_Date
             0,  --Last_Updated_By
             0,  --Last_Update_Login
             SYSDATE, --Creation_Date
             0, --Created_By
             'ROI-Lot-Serial-1',   --Shipment_Num
             210,            --Ship_To_Organization_Id,
             SYSDATE,        --Expected_Receipt_Date
             'Y'             --Validation_Flag
             );
   
INSERT INTO RCV_TRANSACTIONS_INTERFACE
            (INTERFACE_TRANSACTION_ID,
             GROUP_ID,
             LAST_UPDATE_DATE,
             LAST_UPDATED_BY,
             CREATION_DATE,
             CREATED_BY,
             LAST_UPDATE_LOGIN,
             TRANSACTION_TYPE,
             TRANSACTION_DATE,
             PROCESSING_STATUS_CODE,
             PROCESSING_MODE_CODE,
             TRANSACTION_STATUS_CODE,
             QUANTITY,
             UNIT_OF_MEASURE,
             INTERFACE_SOURCE_CODE,
             ITEM_ID,
             EMPLOYEE_ID,
             AUTO_TRANSACT_CODE,
             SHIPMENT_HEADER_ID,
             SHIPMENT_LINE_ID,
             RECEIPT_SOURCE_CODE,
             TO_ORGANIZATION_ID,
             SOURCE_DOCUMENT_CODE,
             DESTINATION_TYPE_CODE,
             SUBINVENTORY,
             SHIPMENT_NUM,
             EXPECTED_RECEIPT_DATE,
             HEADER_INTERFACE_ID,
             VALIDATION_FLAG
             )
             VALUES
             ( rcv_transactions_interface_s.nextval, -- INTERFACE_TRANSACTION_ID             
             rcv_interface_groups_s.currval,        --GROUP_ID
             SYSDATE,               --LAST_UPDATE_DATE
             0,                              --LAST_UPDATED_BY
             SYSDATE,               --CREATION_DATE
             0,                              --CREATED_BY
             0,                              --LAST_UPDATE_LOGIN
             'RECEIVE',               --TRANSACTION_TYPE
             SYSDATE,               --TRANSACTION_DATE
             'PENDING',              --PROCESSING_STATUS_CODE
             'BATCH',                  --PROCESSING_MODE_CODE
             'PENDING',              --TRANSACTION_STATUS_CODE
             2,                              --QUANTITY
             'Each',                       --UNIT_OF_MEASURE
             'RCV',                       --INTERFACE_SOURCE_CODE
             169845,                    --ITEM_ID
             13706,                      --EMPLOYEE_ID
             'DELIVER',              --AUTO_TRANSACT_CODE
             233534,                    --SHIPMENT_HEADER_ID
             246486,                    --SHIPMENT_LINE_ID
             'INVENTORY',        --RECEIPT_SOURCE_CODE
             210,                          --TO_ORGANIZATION_ID
             'INVENTORY',        --SOURCE_DOCUMENT_CODE
             'INVENTORY',        --DESTINATION_TYPE_CODE
             'Staging1',                 --SUBINVENTORY
             'ROI-Lot-Serial-1',    --SHIPMENT_NUM
             SYSDATE,               --EXPECTED_RECEIPT_DATE,
             rcv_headers_interface_s.currval,  --HEADER_INTERFACE_ID
             'Y'                            --VALIDATION_FLAG
             );


INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
            ( TRANSACTION_INTERFACE_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            LOT_NUMBER,
            TRANSACTION_QUANTITY,
            PRIMARY_QUANTITY,
            SERIAL_TRANSACTION_TEMP_ID,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID
            )
            VALUES
            ( MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE, --LAST_UPDATE_DATE
            0,                --LAST_UPDATED_BY
            SYSDATE, --CREATION_DATE
            0,                --CREATED_BY
            0,                --LAST_UPDATE_LOGIN
            'S00226',    --LOT_NUMBER
            2,                --TRANSACTION_QUANTITY
            2,                --PRIMARY_QUANTITY
            MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL, --SERIAL_TRANSACTION_TEMP_ID
            'RCV',         --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );

INSERT INTO MTL_SERIAL_NUMBERS_INTERFACE
            ( TRANSACTION_INTERFACE_ID,
            LAST_UPDATE_DATE,
            LAST_UPDATED_BY,
            CREATION_DATE,
            CREATED_BY,
            LAST_UPDATE_LOGIN,
            FM_SERIAL_NUMBER,
            TO_SERIAL_NUMBER,
            PRODUCT_CODE,
            PRODUCT_TRANSACTION_ID)
            VALUES
            (MTL_MATERIAL_TRANSACTIONS_S.CURRVAL,--TRANSACTION_INTERFACE_ID
            SYSDATE,  --LAST_UPDATE_DATE
            0,                 --LAST_UPDATED_BY
            SYSDATE,  --CREATION_DATE
            0,                 --CREATED_BY
            0,                 --LAST_UPDATE_LOGIN
            'SM1_00001', --FM_SERIAL_NUMBER
            'SM1_00002', --TO_SERIAL_NUMBER
            'RCV',         --PRODUCT_CODE
            RCV_TRANSACTIONS_INTERFACE_S.CURRVAL  --PRODUCT_TRANSACTION_ID
            );
Commit;

Note: On R12 instance, RHI.org_id or RTI.org_id should also be populated.


2) Run the following scripts to check data have been correctly inserted

SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y

SQL> Select * from RCV_TRANSACTIONS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
INTERFACE_TRANSACTION_ID=238839
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
TRANSACTION_TYPE=RECEIVE
AUTO_TRANSACT_CODE=DELIVER
RECEIPT_SOURCE_CODE=INVENTORY
TO_ORGANIZATION_ID=210
SOURCE_DOCUMENT_CODE=INVENTORY
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y

SQL> Select * from MTL_TRANSACTION_LOTS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
LOT_NUMBER=S00226
SERIAL_TRANSACTION_TEMP_ID=11305732
PRODUCT_CODE=RCV
PRODUCT_TRANSACTION_ID=238839

SQL> Select * from MTL_SERIAL_NUMBERS_INTERFACE
where PRODUCT_TRANSACTION_ID=&INTERFACE_TRANSACTION_ID;
TRANSACTION_INTERFACE_ID=11305732
FM_SERIAL_NUMBER=SM1_00001
TO_SERIAL_NUMBER=SM1_00002
PRODUCT_TRANSACTION_ID=238839


3) In Purchasing Responsibility, Change to receiving organization and
run the Receiving Transaction Processor for the given group_id used in RHI and RTI (GROUP_ID=25168)


4) Navigate to Receiving / Receiving Transactions Summary form
For Shipment Number ROI-Lot-Serial-1, Receipt Number 5012 has Receive and Deliver transactions.


5) Check how the following application tables have been populated/updated

SQL> Select * from RCV_HEADERS_INTERFACE where SHIPMENT_NUM like '&Shipment_Num';
GROUP_ID=25168
HEADER_INTERFACE_ID=107193
PROCESSING_STATUS_CODE=SUCCESS
SHIPMENT_NUM=ROI-Lot-Serial-1
VALIDATION_FLAG=Y
             
SQL> Select * from RCV_SHIPMENT_HEADERS where SHIPMENT_NUM like '&Shipment_Num';
SHIPMENT_HEADER_ID=233534
SHIPMENT_NUM= ROI-Lot-Serial-1
RECEIPT_NUM=5012

SQL> Select * from RCV_SHIPMENT_LINES where SHIPMENT_HEADER_ID=&Shipment_Header_id;
SHIPMENT_HEADER_ID=233534
SHIPMENT_LINE_ID=246486
UNIT_OF_MEASURE=Each
ITEM_ID=169845
QUANTITY_SHIPPED=10
QUANTITY_RECEIVED=2
SHIPMENT_LINE_STATUS_CODE=PARTIALLY RECEIVED

SQL> Select * from RCV_TRANSACTIONS where SHIPMENT_HEADER_ID=233534
It returns 2 records

For TRANSACTION_TYPE=RECEIVE
    TRANSACTION_ID=307969
    DESTINATION_TYPE_CODE=RECEIVING
    PARENT_TRANSACTION_ID=-1
    QUANTITY=2

For TRANSACTION_TYPE=DELIVER
    TRANSACTION_ID=307970
    DESTINATION_TYPE_CODE=INVENTORY
    PARENT_TRANSACTION_ID=307969
    QUANTITY=2

Technical details Payment Process Request (PPR) processing in Payments in R12

Technical details Payment Process Request (PPR) processing in Payments in R12
 
Overview

Under Funds disbursement page, users can submit Payment Process Requests (PPR) to generate payments. There is an option to submit a single Payment Process Request or schedule Payment Process Requests.

There are four steps in the processing of a PPR.
a) Document selection
b) Build Payments
c) Format Payments
d) Confirm Payments

Document selection and Confirm Payments are handled by Payables (AP) code while Build Payments and Format payments are handled by Payments (IBY) code.

Submitting a Single Payment Process Request

Mandatory Fields - Payment process request name, pay through date
Under Payment Attributes tab – Payment Date, Payment Exchange rate type.
Payment Process Profile and Disbursement bank account are optional fields.

Under Processing tab, options are available to stop the process after document selection / payment and also how to create Payment Instruction.

Under Validation Failure Results tab, choose option that best suits the business needs regarding how to handle validation failure on document(s) or payment(s).

Click on Submit to submit the Payment process request.



Document Selection – Payables

Code: AP_AUTOSELECT_PKG

When a Payment Process request is submitted, a record is created in AP_INV_SELECTION_CRITERIA_ALL with a checkrun_name which is the same as the payment process request name.

Payment Profile and Internal Bank Account from which payments have to be made do not have to be specified during invoice selection. User who submits the PPR does not need know this information. These values can be provided by at a later stage by a Payments Manager or Administrator.

Selection:
Invoices are then selected based on due date, discount date, paygroup and other criteria provided by the user while submitting the PPR. The selection process is handled by the calling product

The table AP_SELECTED_INVOICES_ALL is populated with selected invoices.
AP_UNSELECTED_INVOICES_ALL is populated with unselected invoices.

Locking:
After selecting the documents, the invoices are locked to prevent other check runs from selecting the same invoices.
AP_PAYMENT_SCHEDULES_ALL.checkrun_id is populated on the selected documents.

Review:
If the Payment Process Request has been setup to ‘Stop Process for Review After Scheduled Payment Selection’, the process stops for user review. The status of the PPR is set to Invoices Pending Review.

If the ‘Stop Process for Review After Scheduled Payment Selection’ was not enabled, at the end of invoice selection, build program is submitted automatically.

If no invoices met the selection criteria and no payment schedules selected for payment, the PPR is cancelled automatically and the status of the PPR is set to “Cancelled - No Invoices Selected”

If user review required, after the user reviews the selected payment schedules and clicks on Submit, AP calls the IBYBUILD program.

Valid Statuses and actions
At the end of this step, the valid statuses are
a) Invoices Pending Review or
b) Cancelled - No Invoices Selected or
c) Other statuses from missing information such as Missing Exchange rates

If PPR status is Cancelled-No Invoices Selected, there are no valid actions available.
For others, the actions available are
a) Terminate the PPR or
b) Modify / proceed to submit the PPR and start the build process.




Build Payments - Payments

Code: IBY_DISBURSE_SUBMIT_PUB_PKG

Build Payments creates record in IBY_PAY_SERVICE_REQUESTS with call_app_pay_service_req_code = checkrun_name.

Primary Key: PAYMENT_SEVICE_REQUEST_ID

Key Columns:
CALL_AP_PAY_SERVICE_REQ_CODE -> PPR name
CALLING_APP_ID
PAYMENT_SERVICE_REQUEST_STATUS
INTERNAL_BANK_ACCOUNT_ID
MAXIMUM_PAYMENT_AMOUNT
MINIMUM_PAYMENT_AMOUNT
DOCUMENT_REJECTION_LEVEL_CODE
PAYMENT_REJECTION_LEVEL_CODE
REQUIRE_PROP_PMTS_REVIEW_FLAG
CREATE_PMT_INSTRUCTIONS_FLAG

Note: The displayed status of the PPR is generated by ibyvutlb.pls
There is a get_psr_status function that derives the display sttaus of the PPR on the dashboard.

Some of the values for PAYMENT_SERVICE_REQUEST_STATUS in the table are
PAYMENT_SERVICE_REQUEST_STATUS
------------------------------
DOCUMENTS_VALIDATED
INFORMATION_REQUIRED
INSERTED
PAYMENTS_CREATED
PENDING_REVIEW
TERMINATED
VALIDATION_FAILED

The build program populates the IBY_DOCS_PAYABLE_ALL table with the payments. Link to the payment service request table is through PAYMENT_SERVICE_REQUEST_ID.

Key Columns:
Payment_service_request_id
Calling_app_doc_ref_number -> invoice_number
Document_payable_id
Document_status
Payment_currency_code
Payment_amount
Document_amount
Exclusive_payment_flag
Payment_method_code
Payment_id
Formatting_payment_id
Ext_payee_id
Payee_party_id
Payment_profile_id
Internal_bank_account_id
Calling_app_doc_unique_ref2 -> invoice_id
Calling_app_doc_unique_ref3 -> payment number


a) Internal Bank Account/Payment Process Profile Assignment:
Code: IBY_ASSIGN_PUB

If the payment process request has the internal bank account and payment profile assigned to it, the same is assigned to all the documents in the PPR.

If a default internal bank account and PPP were not provided when submitting the PPR, Oracle Payments attempts to default the values. If it cannot find a default value for all the documents, the PPR is set to INFORMATION REQUIRED status. The display status of the PPR is “Information Required - Pending Action”
User should complete the missing information and Run Payment Process to continue.

b) Document Validation
Code: IBY_VALIDATIONSETS_PUB

During this step, Oracle Payments validates all the documents using Payment Method based validations and then payment format based validations..

b.1 - If all the documents pass validation, all the documents are set to a status of VALIDATED and the request status is set to ‘Documents Validated’.

b.2 – If there are any validation failures, Oracle Payments uses the system option used while submitting the PPR to determine the next action.

The DOCUMENT_REJECTION_LEVEL_CODE of the PPR can have the following values which determine how the document processing will continue when there is a validation failure
REQUEST - Reject all documents in this PPR
DOCUMENT - Reject only the document in error
PAYEE - Reject all the documents related to the supplier
NONE - Stop the request for review

b.2.1 – REQUEST
The status of the payment process request is updated to ‘Failed Document Validation’. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request.

b.2.2 – DOCUMENT
Oracle Payments rejects all documents that failed validation. Oracle Payments then calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.

b.2.3 – PAYEE
Oracle Payments rejects all documents for the supplier that had one or more documents that failed validation. Oracle Payments calls the calling application and AP releases the rejected documents so they can be paid through another Payment process request. The rest of the documents are set to VALIDATED status and the ppr is set to ‘Documents Validated’ status.

c) Create Payments
Code: IBY_PAYGROUP_PUB

The validated documents are then grouped into proposed payments based on the grouping rules, both user defined and hard coded.

Example: If exclusive_payment_flag = Y on a document, its paid on a separate payment.
It then numbers the payments (internal identifier not the check numbering) and validates the created payments.

Records are inserted into IBY_PAYMENTS_ALL that holds the payment information for the selected documents.

The build program then updates the IBY_DOCS_PAYABLE_ALL table with the payment_id and formatting_payment_id values that corresponding to the payment that pays the document.

IBY_PAYMENTS_ALL links through payment_service_request_id.

Key Columns:
Payment_service_request_id
Payment_id
Payment_method_code
Payment_status
Payments_complete_flag
Payment_amount,
Dicount_amount_taken
Internal_bank_Account_id
Ext_payee_id
Payment_instruction_id
Payment_profile_id
Void_date

The PAYMENT_REJECTION_LEVEL_CODE can have the following values which determine how the payment processing will continue when there is a validation failure
REQUEST – Reject all payments in the request
PAYMENT – Reject only those payments in error
NONE – Stop the request for review

Request – Entire PPR is rejected. Oracle Payments raises a business event that calls AP to release the documents. The status of the payment process request and proposed payments is updated to ‘REJECTED’.

Payment – Payments that failed validation are rejected and AP releases the documents that belong to the payment that failed validation. The other payments are accepted. The accepted payments get a status of ‘CREATED’.

None – Payments that failed Validation are set to ‘Failed Validation’ and allows for user intervention. Status of the PPR is set to ‘PENDING REVIEW’

If in the PPR setup, ‘Stop Process for Review After Creation of Proposed Payments’ is enabled, the PPR status is set to ‘Pending Proposed Payment Review’. This status prevents further processing until user takes action. If this option to stop for review is not enabled, the status of the PPR is set to ‘Payments Created’. In this status, payment instruction can be created for the PPR.



Format Payments - Payments

Code: IBY_PAYINTSR_PUB, IBY_CHECKNUMBER_PUB

When a PPR is submitted, there are two options

The CREATE_PMT_INSTRUCTIONS_FLAG can be a Y or N
Y – Payment Instruction will be automatically created after payments are created.
N – Application waits for standard request submission for Payment Instruction.

IBY_PAYMENT_INSTRUCTIONS_ALL stores the payment instruction information.

If the PPR is setup to automatically submit instruction, the payment_service_request_id will be populated in iby_payment_instructions_all because the instruction will be specific to the PPR In this case, the instruction can be linked to the PPR using PAYMENT_SERVICE_REQUEST_ID

If the PPR processing is setup for the user to submit the instruction as a standard request, then when the instruction is submitted, then the instruction is linked to the PPR through the payments selected by the instruction.
The link in this case will be through iby_payments_all.payment_instruction_id


Key Columns in IBY_PAYMENT_INSTRUCTIONS_ALL
Payment_instruction_id
Payment_profile_id
Payment_instruction_status
Payments_complete_code
Payment_count
Print_instruction_immed_flag
Transmit_instr_immed_flag
Internal_bank_account_id
Payment_document_id
Payment_date
Payment_reason_code
Payment_currency_code

Format:
The following processing occurs during the format step.

a) Number the payments – Check Numbering
b) Create XML Extract message
c) Pass the extract to XML publisher
d) Oracle XML Publisher (BI publisher) applies the format template
e) BI publisher formats and stores the output
f) Oracle Payments then updates the status of the Payment Instruction and the Payments. If successful, the status of Payments and Instruction is ‘Formatted’.

Print Checks:
a) Users can load stationery into the printer and print checks at this stage.
b) Determine if the checks printed ok. If not reprint




Confirm Payments - Payables

Code: AP_PMT_CALLOUT_PKG

Record Print Status of the checks to confirm the payments. Oracle Payments calls ap_pmt_callout_pkg.payment_completed to confirm the payments.

This does the following:
a) Assigns sequence/values – Document sequencing.
b) Creates data in AP_CHECKS_ALL with appropriate data from IBY tables.
Checkrun_name = ppr name and checkrun_id = checkrun_id from IBY table.
c) Data inserted into AP_INVOICE_PAYMENTS_ALL for the corresponding checks.
d) AP_PAYMENT_SCHEDULES_ALL for the invoices are updated to indicate the payment details and status.
e) The documents paid in this PPR are released by setting the checkrun_id on the payment schedules to null.
f) AP_INVOICES_ALL is udpated to show payment status
g) Data is deleted from the AP_SELECTED_INVOICES_ALL
h) Data is deleted from AP_UNSELECTED_INVOICES_ALL

When clicking on the View Output button, the window opens up and disappear immediately

Problem Symptoms/Problem Statement:
Concurrent request completes normal.
Blank fade on the concurrent request window, which is a result of windows, opens and disappears automatically in
fraction of second after pressing the View Output button.
Report Completed Successfully but unable to view output.
When clicking on the View Output button, the window opens up and disappear immediately.

Steps To Reproduce:
Run the Report click on the View Output button, the window opens up and disappears.

Cause
It is due to Internet Explorer security Options.

Solution
To implement the solution, please execute the following steps:

Internet Explorer>Security Options>Tools>Internet Options> Security> Custome Level
1. Automatic Prompt for File Download- Should be Enable
2. File Download-Should be Enable.

Query to display Compentencies related to Employee

Query to display Compentencies related to Employee in Performance Appraisals

SELECT pap.person_id, pap.full_name appraiser, pc.NAME compentency,
       NVL2 (prl1.NAME,
             prl1.step_value || '-' || prl1.NAME,
             prl1.step_value
            ) AS emp_prof_level,
       pce.comments, prl1.step_value
  FROM per_competence_elements pce,
       per_rating_levels_vl prl1,
       per_rating_levels_vl prl2,
       per_rating_levels_vl prl3,
       per_all_people_f pap,
       per_competences pc
 WHERE pce.proficiency_level_id = prl1.rating_level_id(+)
   AND pce.rating_level_id = prl2.rating_level_id(+)
   AND pce.weighting_level_id = prl3.rating_level_id(+)
   AND object_id = pap.person_id
   AND pc.competence_id = pce.competence_id
   AND TRUNC (SYSDATE) BETWEEN pap.effective_start_date AND effective_end_date
   AND pce.object_name = 'ASSESSOR_ID'
   AND pce.object_id IN (
          SELECT perp.person_id
            FROM per_participants perp, per_assessments pass
           WHERE perp.participation_in_id = pass.appraisal_id
             AND perp.participation_in_table = 'PER_APPRAISALS'
             AND perp.participation_in_column = 'APPRAISAL_ID'
             AND pass.assessment_id = pce.assessment_id
             AND pass.appraisal_id =
                          74515
                               ---(Appraisal id receieved from previous query)
                               );

Updating Performance Management Plan End Date

The ability to reopen a PMP after it has been completed is available in R12.1.3, but not in the earlier releases.

Updating Performance Management Plan Appraisals After the Appraisal Task End Date (Doc ID 1207165.1)
Oracle Performance Management provides a new profile option
HR: Update PMP Appraisals
After the Appraisal Period (HR: PER_WPM_APPRAISAL_UPDATE_AFTER_PERIOD.). Using this new profile option, Enterprises can decide if they want their workers to complete PMP Appraisals after the Appraisal Task End Date and before the Plan Period End Date.
This profile option can be set at the Responsibility and User levels.
If the profile is set to Yes:
Then Managers and Workers can update PMP Appraisals after the Appraisal Task End Date.
If the profile option is set to No or is left blank:
Then Managers and Workers cannot update PMP Appraisals after the Appraisal Task End Date.
There is no default value for this profile option.
Note: This profile option applies to PMP Appraisals only.

This profile option is available from R12.1.3.

However, in lower versions there has been the request for the same thing. Since the functionality is not currently available within the lower version applications a datafix was required.

Need To Extend PMP Appraisal Dates Beyond The The Plan Dates (Doc ID 1314529.1)

The following SQL identified the invalid / incorrect data:
SELECT plan_id
FROM per_perf_mgmt_plans
WHERE plan_name like '&PLAN_NAME'; --replace &PLAN_NAME with your plan_name.

SELECT end_date
FROM per_perf_mgmt_plans
WHERE plan_id = &plan_id;

SELECT task_end_date
FROM per_appraisal_periods
WHERE plan_id = &plan_id;

=== Solution /Action Plan ===

To implement the solution, please execute the following steps on a TEST instance first:

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:
i) To update the PLAN Period (IF you need both Plan and Appraisal periods need to be updated, update the plan period first):
UPDATE per_perf_mgmt_plans
SET end_date = '' --example '17-APR-2013'
WHERE plan_id = '';

iii) Use the below DML to update the task end date for Appraisals ONLY:
UPDATE per_appraisal_periods
SET task_end_date = '' --example '17-APR-2013'
WHERE plan_id = ''
AND task_end_Date = ''; -- Example: 10-APR-2013 which is the current task end date


3. Once the scripts complete, confirm that the data is corrected.
You can use the following SQL to confirm:

select * from per_perf_mgmt_plans
where plan_id =''

4. If you are satisfied with the results, issue a commit.

5. Confirm that the data is corrected when viewed in the Oracle Applications by querying the Plan Details :
NAV: HR Professional v4.0 > Performance Management > Performance Management Plan > Query up the plan

6. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.

Is it Possible to Update Generate Employee Number Method From Automatic to Manual

Applies to:

Oracle Human Resources - Version: 11.5.10.0 to 12.1.1 - Release: 11.5 to 12.0

Information in this document applies to any platform.

Is it possible to move back to the manual employee numbering?

What is the impact for the persons already entered in the system?

Please note: IF Global Numbering has ever been enabled this note is no longer valid. Once Global Numbering has been enabled there is no way to back it out and change to Manual Employee numbering. Global Numbering uses a different set of tables for Number Generation and goes across multiple business groups.

Solution

The only implication would be to check/keep track of any duplication numbers.

Please note: IF Global Numbering has ever been enabled this note is no longer valid. Once Global Numbering has been enabled there is no way to back it out and change to Manual Employee numbering. Global Numbering uses a different set of tables for Number Generation and goes across multiple business groups.

Please find detailed below the steps to change the automatic to manual employee

number generation.

When creating an employee the system automatically inputs the employee number.

This value is always numeric. But some Business reasons could require that

the employee number to be alphanumeric or the employee number should be entered

manually.

1. In the standard Responsibility US HRMS Manager navigate to:

Work Structures -> Organization -> Description.

2. Query back the Business Group.

3. Click on the Organization Classification of business group.

4. Click on the [Others] button.

5. From the List of values (LOV) click on Business Group Information.

6. Click in the Flexfield box.

7. This will bring up a window called Business Group Information.

One of the fields is Employee Number Generation. This should currently say

Automatic.

8. Update this to say Manual.

9. Save the record.

To test the results.

1. In the standard responsibility US HRMS Manager navigate to:

People -> Enter and Maintain -> Create a new person.

2. Now the employee number is NOT generated automatically.

Fixed Assets Additions API Sample Script

Sample Script: Using the Additions API via Invoices

The following sample script shows how you can use the Additions API via invoices:

set serveroutput on

declare

   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;
   l_inv_rec                  FA_API_TYPES.inv_rec_type;

   l_return_status            VARCHAR2(1);          
   l_mesg_count               number;
   l_mesg                     varchar2(4000);

begin

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;

   -- desc info
   l_asset_desc_rec.description               := '&description';

   -- cat info
   l_asset_cat_rec.category_id                := &category_id

   --type info
   l_asset_type_rec.asset_type                := '&ASSET_TYPE';

   -- invoice info
   l_inv_rec.fixed_assets_cost                := 2500;
   l_inv_rec.deleted_flag                     := 'NO';
   l_inv_rec.description            := l_asset_desc_rec.description;
   l_inv_rec.unrevalued_cost                  := 5555;
   l_inv_rec.create_batch_id                  := 1000;
   l_inv_rec.payables_code_combination_id     := 13528;
   l_inv_rec.feeder_system_name               := 'ACK';
   l_inv_rec.payables_cost                    := 5555;
   l_inv_rec.payables_units                   := 1;
   l_inv_rec.po_vendor_id                     := 1;
   l_inv_rec.inv_indicator                    := 1;

   l_inv_tbl (1)                              := l_inv_rec;

   -- fin info
   l_asset_fin_rec.date_placed_in_service     := '&DPIS';
   l_asset_fin_rec.depreciate_flag            := 'YES';


   -- book / trans info
   l_asset_hdr_rec.book_type_code             := '&book';

   -- distribution info 
   l_asset_dist_rec.units_assigned            := 1;
   l_asset_dist_rec.expense_ccid              := &ccid
   l_asset_dist_rec.location_ccid             := &location_id
   l_asset_dist_rec.assigned_to               := null;
   l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                        := l_asset_dist_rec;

   -- call the api
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );
 
   dbms_output.put_line(l_return_status);

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then

      l_mesg := chr(10) || substr(fnd_msg_pub.get
                              (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);

      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);

         dbms_output.put_line(l_mesg);
      end loop;

      fnd_msg_pub.delete_msg();

   end if;


   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');
dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
   end if;

end;
/

Sample Script: Using the Additions API with No Invoices

The following sample script shows how you can use the Additions API to test a manual change, when no invoice information is used:

set serveroutput on

declare

   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;

   l_return_status            VARCHAR2(1);     
   l_mesg_count               number;
   l_mesg                     varchar2(4000);

begin

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;

   -- desc info
   l_asset_desc_rec.description                  := '&description';
   l_asset_desc_rec.asset_key_ccid               := null;

   -- cat info 
   l_asset_cat_rec.category_id                   := &category_id

   --type info
   l_asset_type_rec.asset_type                   := '&asset_type';

   -- fin info
   l_asset_fin_rec.cost                          := &cost
   l_asset_fin_rec.date_placed_in_service        := '&DPIS';
   l_asset_fin_rec.depreciate_flag               := 'YES';

   -- deprn info
   l_asset_deprn_rec.ytd_deprn                   := &ytd
   l_asset_deprn_rec.deprn_reserve               := &reserve
   l_asset_deprn_rec.bonus_ytd_deprn             := 0;
   l_asset_deprn_rec.bonus_deprn_reserve         := 0;

   -- book / trans info
   l_asset_hdr_rec.book_type_code                := '&book';

   -- distribution info
   l_asset_dist_rec.units_assigned               := 1;
   l_asset_dist_rec.expense_ccid                 := &ccid
   l_asset_dist_rec.location_ccid                := &location_id
   l_asset_dist_rec.assigned_to                  := null;
   l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                           := l_asset_dist_rec;

   -- call the api 
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then

      l_mesg := chr(10) || substr(fnd_msg_pub.get
                                (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);

      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);

         dbms_output.put_line(l_mesg);
      end loop;

      fnd_msg_pub.delete_msg();

   end if;

   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');

dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
   end if;

end;
/

Sample Script: Using the Additions API via Invoices with Alternative Ledger Currency

set serveroutput on

declare

   l_trans_rec                FA_API_TYPES.trans_rec_type;
   l_dist_trans_rec           FA_API_TYPES.trans_rec_type;
   l_asset_hdr_rec            FA_API_TYPES.asset_hdr_rec_type;
   l_asset_desc_rec           FA_API_TYPES.asset_desc_rec_type;
   l_asset_cat_rec            FA_API_TYPES.asset_cat_rec_type;
   l_asset_type_rec           FA_API_TYPES.asset_type_rec_type;
   l_asset_hierarchy_rec      FA_API_TYPES.asset_hierarchy_rec_type;
   l_asset_fin_rec            FA_API_TYPES.asset_fin_rec_type;
   l_asset_deprn_rec          FA_API_TYPES.asset_deprn_rec_type;
   l_asset_dist_rec           FA_API_TYPES.asset_dist_rec_type;
   l_asset_dist_tbl           FA_API_TYPES.asset_dist_tbl_type;
   l_inv_tbl                  FA_API_TYPES.inv_tbl_type;
   l_inv_rate_tbl             FA_API_TYPES.inv_rate_tbl_type;
   l_inv_rec                  FA_API_TYPES.inv_rec_type;

   l_return_status            VARCHAR2(1);          
   l_mesg_count               number;
   l_mesg                     varchar2(4000);

begin

   dbms_output.enable(10000000);

   FA_SRVR_MSG.Init_Server_Message;

   -- desc info
   l_asset_desc_rec.description               := '&description';

   -- cat info
   l_asset_cat_rec.category_id                := &category_id

   --type info
   l_asset_type_rec.asset_type                := '&ASSET_TYPE';

   -- invoice info
   l_inv_rec.fixed_assets_cost                := 2500;
   l_inv_rec.deleted_flag                     := 'NO';
   l_inv_rec.description             := l_asset_desc_rec.description;
   l_inv_rec.unrevalued_cost                  := 5555;
   l_inv_rec.create_batch_id                  := 1000;
   l_inv_rec.payables_code_combination_id     := 13528;
   l_inv_rec.feeder_system_name               := 'ACK';
   l_inv_rec.payables_cost                    := 5555;
   l_inv_rec.payables_units                   := 1;
   l_inv_rec.po_vendor_id                     := 1;
   l_inv_rec.inv_indicator                    := 1;

   l_inv_tbl (1)                              := l_inv_rec;

   -- rate info
   l_inv_rec.inv_rate_tbl(1).set_of_books_id  := 102;
   l_inv_rec.inv_rate_tbl(1).exchange_rate    := 2;
   l_inv_rec.inv_rate_tbl(1).cost             := 2001;

   -- fin info
   l_asset_fin_rec.date_placed_in_service     := '&DPIS';
   l_asset_fin_rec.depreciate_flag            := 'YES';


   -- book / trans info
   l_asset_hdr_rec.book_type_code             := '&book';

   -- distribution info 
   l_asset_dist_rec.units_assigned            := 1;
   l_asset_dist_rec.expense_ccid              := &ccid
   l_asset_dist_rec.location_ccid             := &location_id
   l_asset_dist_rec.assigned_to               := null;
   l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
   l_asset_dist_tbl(1)                        := l_asset_dist_rec;

   -- call the api
   fa_addition_pub.do_addition(
           -- std parameters
           p_api_version             => 1.0,
           p_init_msg_list           => FND_API.G_FALSE,
           p_commit                  => FND_API.G_FALSE,
           p_validation_level        => FND_API.G_VALID_LEVEL_FULL,
           p_calling_fn              => null,
           x_return_status           => l_return_status,
           x_msg_count               => l_mesg_count,
           x_msg_data                => l_mesg,
           -- api parameters
           px_trans_rec              => l_trans_rec,
           px_dist_trans_rec         => l_dist_trans_rec,
           px_asset_hdr_rec          => l_asset_hdr_rec,
           px_asset_desc_rec         => l_asset_desc_rec,
           px_asset_type_rec         => l_asset_type_rec,
           px_asset_cat_rec          => l_asset_cat_rec,
           px_asset_hierarchy_rec    => l_asset_hierarchy_rec,
           px_asset_fin_rec          => l_asset_fin_rec,
           px_asset_deprn_rec        => l_asset_deprn_rec,
           px_asset_dist_tbl         => l_asset_dist_tbl,
           px_inv_tbl                => l_inv_tbl
          );
 
   dbms_output.put_line(l_return_status);

   --dump messages
   l_mesg_count := fnd_msg_pub.count_msg;

   if l_mesg_count > 0 then

      l_mesg := chr(10) || substr(fnd_msg_pub.get
                                    (fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
                                     1, 250);
      dbms_output.put_line(l_mesg);

      for i in 1..(l_mesg_count - 1) loop
         l_mesg :=
                     substr(fnd_msg_pub.get
                            (fnd_msg_pub.G_NEXT,
                             fnd_api.G_FALSE), 1, 250);

         dbms_output.put_line(l_mesg);
      end loop;

      fnd_msg_pub.delete_msg();

   end if;


   if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
     dbms_output.put_line('FAILURE');
   else
     dbms_output.put_line('SUCCESS');
dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
     dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
     dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
   end if;

end;
/

APP-AST-207069: This User ID Has Not Been Set Up As a Sales Force

Accessing the Collection Form Errors - APP-AST-207069: This User ID Has Not Been Set Up As a Sales Force

Symptoms
Attempting to access the Collection form and receive the following error:

ERROR:
APP-AST-207069: This user ID has not been set up as a sales force. Please contact the System Administrator

Steps to Reproduce:
Responsibility:  Receivables Manager
Navigation:  Collections > Collections

Cause
The issue is caused by not having defined a Collection Role for the Resource or the USER_ID has not been manually assigned to the employee resource tied to the USER_ID.

Solution
Please setup your Collections User correctly using the following steps from , Step #2:

A. To create an account:
Responsibility:  System Administrator
Navigation:  Security > User > Define
Enter a Username and Password

Assign the following responsibilities:
 •System Administrator
 •Application Developer
 •Receivables Manager
 •Collections Agent
 •Collections Forms Administrator
 •CRM Resource Manager
 •Collections HTML Admin
 •Telesales Agent
 •Human Resources Manager
 •CRM Administrator

B. In your HR Responsibility:
 •Setup employee
 •People>Enter & Maintain
 •Enter name, birthday, gender, SSN and Save
 •Note the employee number

C. In System Administrator Responsibility:
 •Security > User > Define
 •Query your Username
 •Enter the Full Name as it appeared in the HR setup screen above
 •Save

D. Using the CRM Resource Manager Responsibility
 •Menu: Maintain Resource
 •Create a Group  ◦-Name/Start Date (you can use anything)
   Assign Usages  Collections Application

Sales & Telesales Application
 Roles
Collections Agent
 Telesales Agent

•Import Resource ◦Resource Cat = Employee
 ◦Enter Employee # that was noted previously
 ◦Click search/select employee
 ◦Click create resource

•Default Value window ◦select start date of the resource

•Close the window
 •Select Resources click Save
 •Click Details
 •From the Resource Management Form, enter the Username
 •Click on Roles Tab (Security & Access) ◦Add the following roles:
 ◦Collections Role Type
 ◦Collections Agent Role
 ◦Telesales Role Type
 ◦Telesales Agent Role

•Click on the Group Tab
 •Query Group you created previously
 •Add as an employee to the Group
 •Close the form.

How to Query Position Hierarchy Chain of Command For A User

How to query position hierarchy chain of command for a user?

This is helpful when troubleshooting issues like 'Approval List Could Not Be Generated' when using position hierarchy.

Solution
 1.  Use the following query to determine the hierarchies to which a particular User belongs, and to identify those persons and users above the starting point User within each relevant hierarchy for this user.

QUERY1

SELECT
 peha.position_structure_id, peha.EMPLOYEE_ID,
fndu.user_id, ppos.POSITION_ID,
pps.name Hierarchy, fndu.USER_NAME UserName,
papf.FULL_NAME Person, ppos.NAME Position,
 peha.SUPERIOR_LEVEL SuperiorPositionLevel,
ppos2.NAME SuperiorPosition, papf2.FULL_NAME SuperiorPerson,
fndu2.USER_NAME SuperiorUsername, peha.SUPERIOR_ID SuperiorPersonId,
fndu2.user_id SuperiorUserId, ppos2.POSITION_ID SuperiorPosId
FROM
 PO_EMPLOYEE_HIERARCHIES_ALL peha, PER_POSITIONS ppos,
 PER_POSITIONS ppos2, per_all_people_f papf, per_all_people_f papf2,
 fnd_user fndu, fnd_user fndu2, per_position_structures pps
WHERE
 pps.business_group_id = peha.business_group_id AND
 pps.position_structure_id = peha.position_structure_id AND
 fndu2.EMPLOYEE_ID = papf2.PERSON_ID and papf2.PERSON_ID = peha.SUPERIOR_ID and
 papf2.EFFECTIVE_END_DATE > sysdate and papf.PERSON_ID = peha.employee_id and
 papf.EFFECTIVE_END_DATE > sysdate and ppos2.POSITION_ID = peha.SUPERIOR_POSITION_ID and
 ppos.position_id = peha.EMPLOYEE_POSITION_ID
and peha.superior_level > 0 and
 peha.employee_id = fndu.EMPLOYEE_ID
and fndu.USER_NAME = upper('&StartingUsername')
 ORDER BY peha.position_structure_id, peha.superior_level, papf2.full_name

The above query may return multiple names for a certain level.  This is because position hierarchy allows multiple persons to hold the same position.  When routing purchasing documents for approval the first person alphabetically (based on full_name) for that level position will be used for routing the document up the hierarchy.

2. Use the following query to find the Hierarchy value specified in the Purchasing document types form. 

This will show which position_structure_id to focus on in the above query.  Change the org_id value, document_type_code, and document_subtype as required, depending on the document and organization you are troubleshooting. 

This query will only return a result if the document type is currently setup for hierarchy routing.

QUERY2

select pdt.document_subtype, pdt.document_type_code,
pps.name Hierarchy, pdt.default_approval_path_id
 from po_document_types_all_b pdt, per_position_structures pps
 where
pps.position_structure_id = pdt.default_approval_path_id
 and pdt.org_id = 204
 and pdt.document_type_code = 'REQUISITION'
and pdt.document_subtype = 'PURCHASE'

How To Query Supervisor Chain Of Command For A User

How to query for supervisors hierarchy above an approver user when using employee supervisor chain of command?

Solution
Substitute the username in place of DCROCKETT in the following query.  This is useful when troubleshooting approval list issues with employee supervisor relationships for requisitions and purchasing documents

select fndu.user_name, pecx.full_name
From FND_USER fndu, per_employees_current_x pecx,
(
SELECT PERA.SUPERVISOR_ID --, fndu.user_name, pecx.full_name
FROM
PER_ASSIGNMENTS_F PERA
WHERE EXISTS
(SELECT '1' FROM PER_PEOPLE_F PERF,
PER_ASSIGNMENTS_F PERA1 WHERE TRUNC(SYSDATE) BETWEEN
PERF.EFFECTIVE_START_DATE AND PERF.EFFECTIVE_END_DATE AND PERF.PERSON_ID =
PERA.SUPERVISOR_ID AND PERA1.PERSON_ID = PERF.PERSON_ID AND TRUNC(SYSDATE)
BETWEEN PERA1.EFFECTIVE_START_DATE AND PERA1.EFFECTIVE_END_DATE AND
PERA1.PRIMARY_FLAG = 'Y' AND PERA1.ASSIGNMENT_TYPE = 'E' AND EXISTS (SELECT
'1' FROM PER_PERSON_TYPES PPT WHERE PPT.SYSTEM_PERSON_TYPE IN ('EMP',
'EMP_APL') AND PPT.PERSON_TYPE_ID = PERF.PERSON_TYPE_ID)) START WITH
PERA.PERSON_ID =
(select employee_id from fnd_user
where user_name = 'DCROCKETT') -- ** Replace DCROCKETT with your username
AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE
AND PERA.EFFECTIVE_END_DATE AND PERA.PRIMARY_FLAG = 'Y' AND
PERA.ASSIGNMENT_TYPE = 'E'
CONNECT BY PRIOR PERA.SUPERVISOR_ID =
PERA.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PERA.EFFECTIVE_START_DATE AND
PERA.EFFECTIVE_END_DATE AND PERA.PRIMARY_FLAG = 'Y' AND
PERA.ASSIGNMENT_TYPE = 'E'
) c
where fndu.employee_id = c.supervisor_id and pecx.employee_id = c.supervisor_id

How To Query Approval Group and Approval Assignment Details For iProcurement And Purchasing Users

How can Approval Group and Approval Assignment details be queried from the database for iProcurement and Purchasing Users?

Solution
Use the Job Based query below when Employee Supervisor job based approvals are used, or Use the Position
Based query below when Position Hierarchy position based approvals are used.  The query results will show the approval list / approver list based on the job / position of the employees.

Note1:  Enter the operating unit org_id value, and specify either the User Names or the Employee IDs (comma separated). 
Note2:  The query is currently setup for Approve Purchase Requisitions; change the
pcf.CONTROL_FUNCTION_NAME value to run the query for other approval types;

JobBased
- Shows the Approval Group / Assignment rule details for specified approvers based on their JOB in HR


select fndu.user_name, papf.full_name, paaf.person_id, pcr.control_group_id, pcr.control_rule_id,
pcr.last_update_date, pcr.rule_type_code, pcr.object_code, pcr.amount_limit,
pcr.segment1_low low1, pcr.segment2_low low2, pcr.segment3_low low3, pcr.segment4_low low4,
pcr.segment5_low low5, pcr.segment6_low low6, pcr.segment7_low low7, pcr.segment8_low low8,
pcr.segment1_high high1, pcr.segment2_high high2, pcr.segment3_high high3, pcr.segment4_high high4,
pcr.segment5_high high5, pcr.segment6_high high6, pcr.segment7_high high7, pcr.segment8_high high8
from po_control_rules pcr, po_position_controls_all ppca, po_control_functions pcf,
per_all_assignments_f paaf, per_all_people_f papf, fnd_user fndu
where pcr.control_group_id = ppca.control_group_id
and ppca.org_id = &MyOrgId
and ppca.control_function_id = pcf.control_function_id
and pcf.CONTROL_FUNCTION_NAME = 'Approve Purchase Requisitions' ---- THIS MAKES THE QUERY FOR PURCHASE REQUISITION APPROVALS
and ppca.job_id = paaf.job_id --- THIS MAKES THE QUERY FOR JOB BASED APPROVALS
and paaf.effective_end_date >= sysdate
and papf.effective_end_date >= sysdate
and paaf.person_id = papf.person_id
and concat(concat(paaf.person_id,'-'),NVL(paaf.object_version_number,0)) in
(select concat(concat(person_id, '-'), NVL(max(object_version_number),0))
from per_all_assignments_f
where person_id in (select employee_id from fnd_user where
user_name in ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
OR
employee_id in (&EmployeeIds) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
)
group by person_id)
and paaf.person_id = fndu.employee_id
and
(
fndu.user_name in ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
OR
fndu.employee_id in (&EmployeeIds) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
)
order by
pcr.control_group_id, pcr.control_rule_id, fndu.user_name

PositionBased
- Shows the Approval Group / Assignment rule details for specified approvers based on their POSITION in HR
select fndu.user_name, papf.full_name, paaf.person_id, pcr.control_group_id, pcr.control_rule_id,
pcr.last_update_date, pcr.rule_type_code, pcr.object_code, pcr.amount_limit,
pcr.segment1_low low1, pcr.segment2_low low2, pcr.segment3_low low3, pcr.segment4_low low4,
pcr.segment5_low low5, pcr.segment6_low low6, pcr.segment7_low low7, pcr.segment8_low low8,
pcr.segment1_high high1, pcr.segment2_high high2, pcr.segment3_high high3, pcr.segment4_high high4,
pcr.segment5_high high5, pcr.segment6_high high6, pcr.segment7_high high7, pcr.segment8_high high8
from po_control_rules pcr, po_position_controls_all ppca, po_control_functions pcf,
per_all_assignments_f paaf, per_all_people_f papf, fnd_user fndu
where pcr.control_group_id = ppca.control_group_id
and ppca.org_id = &MyOrgId
and ppca.control_function_id = pcf.control_function_id
and pcf.CONTROL_FUNCTION_NAME = 'Approve Purchase Requisitions' ---- THIS MAKES THE QUERY FOR PURCHASE REQUISITION APPROVALS
and ppca.position_id = paaf.position_id ---- THIS MAKES THE QUERY FOR POSITION BASED APPROVALS
and paaf.effective_end_date >= sysdate
and papf.effective_end_date >= sysdate
and paaf.person_id = papf.person_id
and concat(concat(paaf.person_id,'-'),NVL(paaf.object_version_number,0)) in
(select concat(concat(person_id, '-'), NVL(max(object_version_number),0))
from per_all_assignments_f
where person_id in (select employee_id from fnd_user where
user_name in ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
OR
employee_id in (&EmployeeIds) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
)
group by person_id)
and paaf.person_id = fndu.employee_id
and
(
fndu.user_name in ('&UserNames') ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
OR
fndu.employee_id in (&EmployeeIds) ---- SPECIFY THE USER NAMES OR THE EMPLOYEE IDs
)
order by
pcr.control_group_id, pcr.control_rule_id, fndu.user_name