1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Custom Defaulting Logic of Requisitions

Discussion in 'Oracle Apps Technical' started by yowancristo, Dec 2, 2014.

  1. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Introduction

    This article explains how to customize defaulting logic of Requisitions/Lines and Distributions.

    Background

    For defaulting any column values of requisitions, requisition lines or requisition distributions during the time or creation, a specific package is provided by Oracle. This can be used for custom business validation and to set default values.

    Details

    There could be requirements raised to default any column value of requisition, requisition line or requisition distributions during the time of creation. To write custom logic and set default values to any of the columns related to requisition, the package POR_CUSTOM_PKG is provided by Oracle for customization.

    Multiple procedures are available in this package for customization. And these are invoked automatically whenever a requisition header/line/distribution is created or validated. Attribute ids are passed as IN OUT NOCOPY parameters to these procedures and can be modified to reflect any custom default logic.

    Here let's see an example - Default Buyer Id to certain value automatically.

    Buyer Id is populated at Requisition Line level, hence we have to customize the procedure CUSTOM_DEFAULT_REQ_LINE.

    Instead of hard coding the buyer id directly, we will store the Buyer Id to be defaulted (Employee Id of the person who is set up as a Buyer) in a custom profile option (or use similar mechanism such as a lookup etc), and the same will be derived and set as default buyer in POR_CUSTOM_PKG.CUSTOM_DEFAULT_REQ_LINE

    As we are not touching any other procedures for this logic, Only the procedure CUSTOM_DEFAULT_REQ_LINE of POR_CUSTOM_PKG is shown below.

    Code (SQL):


    PROCEDURE CUSTOM_DEFAULT_REQ_LINE (
    -- READ ONLY data
        req_header_id            IN NUMBER, -- 1
        req_line_id              IN NUMBER, -- 2
        old_req_line_id          IN NUMBER,   -- 3
        line_num                 IN NUMBER, -- 4

    -- header data
        preparer_id                IN NUMBER, -- 5
        header_attribute_1         IN VARCHAR2, -- 6
        header_attribute_2         IN VARCHAR2, -- 7
        header_attribute_3         IN VARCHAR2, -- 8
        header_attribute_4         IN VARCHAR2, -- 9
        header_attribute_5         IN VARCHAR2, -- 10
        header_attribute_6         IN VARCHAR2, -- 11
        header_attribute_7         IN VARCHAR2, -- 12
        header_attribute_8         IN VARCHAR2, -- 13
        header_attribute_9         IN VARCHAR2, -- 14
        header_attribute_10        IN VARCHAR2, -- 15
        header_attribute_11        IN VARCHAR2, -- 16
        header_attribute_12        IN VARCHAR2, -- 17
        header_attribute_13        IN VARCHAR2, -- 18
        header_attribute_14        IN VARCHAR2, -- 19
        header_attribute_15        IN VARCHAR2, -- 20

    -- line data: update any of the following parameters as default for line
        x_line_type_id             IN OUT NOCOPY  NUMBER, -- 21
        x_item_id                  IN OUT NOCOPY  NUMBER, -- 22
        x_item_revision            IN OUT NOCOPY  VARCHAR2, -- 23
        x_category_id              IN OUT NOCOPY  NUMBER, -- 24
        x_catalog_source           IN OUT NOCOPY  VARCHAR2, -- 25
        x_catalog_type             IN OUT NOCOPY  VARCHAR2, -- 26
        x_currency_code            IN OUT NOCOPY  VARCHAR2, -- 27
        x_currency_unit_price      IN OUT NOCOPY  NUMBER, -- 28
        x_manufacturer_name        IN OUT NOCOPY  VARCHAR2, -- 29
        x_manufacturer_part_num    IN OUT NOCOPY  VARCHAR2, -- 30
        x_deliver_to_loc_id        IN OUT NOCOPY  NUMBER, -- 31
        x_deliver_to_org_id        IN OUT NOCOPY  NUMBER, -- 32
        x_deliver_to_subinv        IN OUT NOCOPY  VARCHAR2, -- 33
        x_destination_type_code    IN OUT NOCOPY  VARCHAR2, -- 34
        x_requester_id             IN OUT NOCOPY  NUMBER, -- 35
        x_encumbered_flag          IN OUT NOCOPY  VARCHAR2, -- 36
        x_hazard_class_id          IN OUT NOCOPY  NUMBER, -- 37
        x_modified_by_buyer        IN OUT NOCOPY  VARCHAR2, -- 38
        x_need_by_date             IN OUT NOCOPY  DATE, -- 39
        x_new_supplier_flag        IN OUT NOCOPY  VARCHAR2, -- 40
        x_on_rfq_flag              IN OUT NOCOPY  VARCHAR2, -- 41
        x_org_id                   IN OUT NOCOPY  NUMBER, -- 42
        x_parent_req_line_id       IN OUT NOCOPY  NUMBER, -- 43
        x_po_line_loc_id           IN OUT NOCOPY  NUMBER, -- 44
        x_qty_cancelled            IN OUT NOCOPY  NUMBER, -- 45
        x_qty_delivered            IN OUT NOCOPY  NUMBER, -- 46
        x_qty_ordered              IN OUT NOCOPY  NUMBER, -- 47
        x_qty_received             IN OUT NOCOPY  NUMBER, -- 48
        x_rate                     IN OUT NOCOPY  NUMBER, -- 49
        x_rate_date                IN OUT NOCOPY  DATE, -- 50
        x_rate_type                IN OUT NOCOPY  VARCHAR2, -- 51
        x_rfq_required             IN OUT NOCOPY  VARCHAR2, -- 52
        x_source_type_code         IN OUT NOCOPY  VARCHAR2, -- 53
        x_spsc_code                IN OUT NOCOPY  VARCHAR2, -- 54
        x_other_category_code      IN OUT NOCOPY  VARCHAR2, -- 55
        x_suggested_buyer_id       IN OUT NOCOPY  NUMBER, -- 56
        x_source_doc_header_id     IN OUT NOCOPY  NUMBER, -- 57
        x_source_doc_line_num      IN OUT NOCOPY  NUMBER, -- 58
        x_source_doc_type_code     IN OUT NOCOPY  VARCHAR2, -- 59
        x_supplier_duns            IN OUT NOCOPY  VARCHAR2, -- 60
        x_supplier_item_num        IN OUT NOCOPY  VARCHAR2, -- 61
        x_taxable_status           IN OUT NOCOPY  VARCHAR2, -- 62
        x_unit_of_measure          IN OUT NOCOPY  VARCHAR2, -- 63
        x_unit_price               IN OUT NOCOPY  NUMBER, -- 64
        x_urgent                   IN OUT NOCOPY  VARCHAR2, -- 65
        x_supplier_contact_id      IN OUT NOCOPY  NUMBER, -- 66
        x_supplier_id              IN OUT NOCOPY  NUMBER, -- 67
        x_supplier_site_id         IN OUT NOCOPY  NUMBER, -- 68
        x_cancel_date              IN OUT NOCOPY  DATE, -- 69
        x_cancel_flag              IN OUT NOCOPY  VARCHAR2, -- 70
        x_closed_code              IN OUT NOCOPY  VARCHAR2, -- 71
        x_closed_date              IN OUT NOCOPY  DATE, -- 72
        x_auto_receive_flag        IN OUT NOCOPY  VARCHAR2, -- 73
        x_pcard_flag               IN OUT NOCOPY  VARCHAR2, -- 74
        x_attribute1               IN OUT NOCOPY  VARCHAR2, -- 75
        x_attribute2               IN OUT NOCOPY  VARCHAR2, -- 76
        x_attribute3               IN OUT NOCOPY  VARCHAR2, -- 77
        x_attribute4               IN OUT NOCOPY  VARCHAR2, -- 78
        x_attribute5               IN OUT NOCOPY  VARCHAR2, -- 79
        x_attribute6               IN OUT NOCOPY  VARCHAR2, -- 80
        x_attribute7               IN OUT NOCOPY  VARCHAR2, -- 81
        x_attribute8               IN OUT NOCOPY  VARCHAR2, -- 82
        x_attribute9               IN OUT NOCOPY  VARCHAR2, -- 83
        x_attribute10              IN OUT NOCOPY  VARCHAR2, -- 84
        x_attribute11              IN OUT NOCOPY  VARCHAR2, -- 85
        x_attribute12              IN OUT NOCOPY  VARCHAR2, -- 86
        x_attribute13              IN OUT NOCOPY  VARCHAR2, -- 87
        x_attribute14              IN OUT NOCOPY  VARCHAR2, -- 88
        x_attribute15              IN OUT NOCOPY  VARCHAR2, -- 89
        X_return_code                OUT NOCOPY NUMBER, -- 90
        X_error_msg                  OUT NOCOPY VARCHAR2, -- 91
        x_supplierContact          IN OUT NOCOPY  VARCHAR2, -- 92
        x_supplierContactPhone     IN OUT NOCOPY  VARCHAR2, -- 93
        x_supplier             IN OUT NOCOPY  VARCHAR2, -- 94
        x_supplierSite         IN OUT NOCOPY  VARCHAR2, -- 95
        x_taxCodeId        IN OUT NOCOPY  NUMBER, -- 96
        x_source_org_id    IN OUT NOCOPY  NUMBER -- 97

      )
      IS
        l_default_buyer     NUMBER;         -- Added by Yowan - 9-Aug-13

    BEGIN


       -- Below block is added to fetch default buyer from custom profile option XXC_DEFAULT_BUYER
        BEGIN
            SELECT  FPOV.profile_option_value
            INTO    l_default_buyer
            FROM    FND_PROFILE_OPTIONS         FPO,
                    FND_PROFILE_OPTION_VALUES   FPOV
            WHERE   FPO.profile_option_id   = FPOV.profile_option_id
            AND     FPO.profile_option_name = 'XXC_DEFAULT_BUYER'
            AND     TRUNC(NVL(FPO.end_date_active,sysdate)) >= TRUNC(SYSDATE);
        EXCEPTION
                WHEN    OTHERS  THEN
                    X_ERROR_MSG := 'Error in getting value for Default Buyer '||SQLCODE ;
                    l_default_buyer := 24449 ;   --hard coding the default buyer id in case of exception
        END;

        x_suggested_buyer_id := l_default_buyer ;
       
       
       X_RETURN_CODE:=0;
       X_ERROR_MSG:='';
    END;


     
    This will ensure that whenever a requisition is created, the buyer will be defaulted to person id stored in the custom profile option XXC_DEFAULT_BUYER.

    Thanks,
    Yowan Cristo


    References

    Oracle Note: (Doc ID 1336746.1)