eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IBE_ORD_ONECLICK_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 23


     it will update the oneclick settings row to be off and null for that address usage.

 ------------------------------------------------------------------------------
*/
procedure Get_Settings(
    p_api_version      IN     NUMBER,
    p_init_msg_list    IN    VARCHAR2 := FND_API.g_false,
    p_commit           IN    VARCHAR2 := FND_API.g_false,
    p_validation_level IN      NUMBER    := FND_API.g_valid_level_full,
    x_return_status    OUT NOCOPY    VARCHAR2,
    x_msg_count        OUT NOCOPY    NUMBER,
    x_msg_data         OUT NOCOPY    VARCHAR2,

    p_party_id         IN     NUMBER := NULL,
    p_acct_id          IN     NUMBER := NULL,

    x_OBJECT_VERSION_NUMBER    OUT NOCOPY    NUMBER,
    x_ONECLICK_ID              OUT NOCOPY    NUMBER,
    x_ENABLED_FLAG             OUT NOCOPY    VARCHAR2,
    x_FREIGHT_CODE             OUT NOCOPY    VARCHAR2,
    x_PAYMENT_ID               OUT NOCOPY    NUMBER,
    x_BILL_PTYSITE_ID          OUT NOCOPY    NUMBER,
    x_SHIP_PTYSITE_ID          OUT NOCOPY    NUMBER,
    x_LAST_UPDATE_DATE         OUT NOCOPY    DATE,
    x_EMAIL_ADDRESS            OUT NOCOPY    VARCHAR2
) is
    l_api_name    CONSTANT VARCHAR2(30)    := 'Get_Settings';

Line: 53


    SELECT     object_version_number,
        ord_oneclick_id,
        enabled_flag,
        freight_code,
        payment_id,
        bill_to_pty_site_id,
        ship_to_pty_site_id,
        last_update_date
    FROM IBE_ORD_ONECLICK
    WHERE
        party_id = c_party_id and
        cust_account_id = c_acct_id;

Line: 67


    IS select email_address
    from HZ_CONTACT_POINTS
    where
        contact_point_type = 'EMAIL' and
        owner_table_name = 'HZ_PARTIES' and
        status = 'A' and
        owner_table_id = owner_id and primary_flag = 'Y';

Line: 149


        x_LAST_UPDATE_DATE;

Line: 164


   ibe_util.debug('x_LAST_UPDATE_DATE      :'||to_char(x_LAST_UPDATE_DATE,'DD-MON-YYYY:HH24:MI:SS'));

Line: 282


        update IBE_ORD_ONECLICK
            set enabled_flag = x_ENABLED_FLAG,
            bill_to_pty_site_id = x_BILL_PTYSITE_ID,
            ship_to_pty_site_id = x_SHIP_PTYSITE_ID,
            object_version_number = x_OBJECT_VERSION_NUMBER + 1,
            last_updated_by = FND_GLOBAL.user_id,
            last_update_date = SYSDATE
        where
            ord_oneclick_id = x_ONECLICK_ID;

Line: 364


         - inserts/updates foreign keys into IBE_ORD_ONECLICK
         - no longer saves user's email address associated with IBE_ORD_ONECLICK
         - assumes address validation was done at the java layer before calling this api

 ------------------------------------------------------------------------------
*/
procedure Save_Settings(
    p_api_version      IN     NUMBER,
    p_init_msg_list    IN    VARCHAR2 := FND_API.g_false,
    p_commit           IN    VARCHAR2 := FND_API.g_false,
    p_validation_level IN      NUMBER    := FND_API.g_valid_level_full,
    x_return_status    OUT NOCOPY    VARCHAR2,
    x_msg_count        OUT NOCOPY    NUMBER,
    x_msg_data         OUT NOCOPY    VARCHAR2,

    p_party_id         IN     NUMBER := NULL,
    p_acct_id          IN     NUMBER := NULL,

    p_OBJECT_VERSION_NUMBER    IN    NUMBER := FND_API.G_MISS_NUM,
    p_ENABLED_FLAG             IN    VARCHAR2 :=  'N',
    p_FREIGHT_CODE             IN    VARCHAR2 :=  FND_API.G_MISS_CHAR,
    p_PAYMENT_ID               IN    NUMBER :=  FND_API.G_MISS_NUM,
    p_BILL_PTYSITE_ID          IN    NUMBER :=  FND_API.G_MISS_NUM,
    p_SHIP_PTYSITE_ID          IN    NUMBER :=  FND_API.G_MISS_NUM
) is
    l_api_name    CONSTANT VARCHAR2(30)    := 'Save_Settings';

Line: 395


    SELECT ord_oneclick_id
    from IBE_ORD_ONECLICK
    where party_id = c_party_id and cust_account_id = c_acct_id;

Line: 438


           ibe_util.debug('ibe_ord_oneclick_pvt.Save_Settings - inserting new row');

Line: 440


        insert into IBE_ORD_ONECLICK_ALL (
            object_version_number,
            ord_oneclick_id,
            party_id,
            cust_account_id,
            enabled_flag,
            freight_code,
            payment_id,
            bill_to_pty_site_id,
            ship_to_pty_site_id,
		  org_id,
            created_by,
            creation_date,
            last_updated_by,
            last_update_date)
        values (1,
            IBE_ORD_ONECLICK_S1.NEXTVAL,
            p_party_id,
            p_acct_id,
            p_ENABLED_FLAG,
            p_FREIGHT_CODE,
            p_PAYMENT_ID,
            p_BILL_PTYSITE_ID,
            p_SHIP_PTYSITE_ID,
		  MO_Global.get_current_org_id(),
            FND_GLOBAL.user_id,
            SYSDATE,
            FND_GLOBAL.user_id,
            SYSDATE)
        returning ord_oneclick_id into l_oneclick_id;

Line: 477


        update IBE_ORD_ONECLICK
        set
            object_version_number = p_OBJECT_VERSION_NUMBER + 1,
            enabled_flag = p_ENABLED_FLAG,
            freight_code = p_FREIGHT_CODE,
            payment_id = p_PAYMENT_ID,
            bill_to_pty_site_id = p_BILL_PTYSITE_ID,
            ship_to_pty_site_id = p_SHIP_PTYSITE_ID,
            last_updated_by = FND_GLOBAL.user_id,
            last_update_date = SYSDATE
        where
            ord_oneclick_id = l_oneclick_id and
            party_id = p_party_id    and
            cust_account_id = p_acct_id and
            object_version_number = p_OBJECT_VERSION_NUMBER;

Line: 567


        - return the cartid that was ultimately created/updated

 ------------------------------------------------------------------------------
*/

procedure Express_Buy_Order(
    p_api_version      IN          NUMBER,
    p_init_msg_list    IN          VARCHAR2 := FND_API.g_false,
    p_commit           IN          VARCHAR2 := FND_API.g_false,
    p_validation_level IN          NUMBER   := FND_API.g_valid_level_full,
    x_return_status    OUT NOCOPY  VARCHAR2,
    x_msg_count        OUT NOCOPY  NUMBER,
    x_msg_data         OUT NOCOPY  VARCHAR2,

    -- identification
    p_party_id         IN    NUMBER,
    p_acct_id          IN    NUMBER,
    p_retrieval_num    IN    NUMBER := FND_API.g_miss_num, -- optional, only if recipient is expressing a cart

    -- common pricing parameters
    p_currency_code     IN    VARCHAR2 := FND_API.g_miss_char,
    p_price_list_id     IN    NUMBER   := FND_API.g_miss_num,
    p_price_req_type    IN    VARCHAR2 := FND_API.g_miss_char,
    p_incart_event      IN    VARCHAR2 := FND_API.g_miss_char,
    p_incart_line_event IN    VARCHAR2 := FND_API.g_miss_char,

    -- flag to drive behavior
    -- (values: 'ITEMS', 'CART', 'LISTS', 'LIST_LINES')
    p_flag              IN     VARCHAR2 := FND_API.g_miss_char,

    -- for express checkout of a shopping cart
    p_cart_id           IN    NUMBER := FND_API.g_miss_num,
    p_minisite_id       IN    NUMBER := FND_API.g_miss_num, -- for stop sharing notification

    -- for express checkout of a list of shopping lists
    p_list_ids      IN    JTF_NUMBER_TABLE,
    p_list_ovns     IN    JTF_NUMBER_TABLE,

    -- for express checkout of a list of shopping list lines
    p_list_line_ids    IN    JTF_NUMBER_TABLE,
    p_list_line_ovns   IN    JTF_NUMBER_TABLE,

    -- for express checkout of a list of items (usually from catalog)
    p_item_ids     IN    JTF_NUMBER_TABLE,
    p_qtys         IN    JTF_NUMBER_TABLE,
    p_org_ids      IN    JTF_NUMBER_TABLE,
    p_uom_codes    IN    JTF_VARCHAR2_TABLE_100,

    -- return the quote header id
    x_new_cart_id    OUT NOCOPY    NUMBER,

    -- TimeStamp check
    p_last_update_date           IN DATE     := FND_API.G_MISS_DATE,
    x_last_update_date         OUT NOCOPY  DATE

) is
    l_api_name     CONSTANT VARCHAR2(30) := 'Express_Buy_Order';

Line: 663


    lx_last_update_date       DATE;

Line: 672


    SELECT quote_header_id, creation_date, currency_code
    FROM aso_quote_headers
    WHERE
    quote_source_code = 'IStore Oneclick' and
    party_id = c_party_id and
    cust_account_id = c_acct_id and
    quote_name is null and
    order_id   is null and
    nvl(trunc(quote_expiration_date), trunc(sysdate)+1) >= trunc(sysdate);

Line: 683


    SELECT last_update_date
    FROM ASO_QUOTE_HEADERS
    WHERE quote_header_id = c_qte_header_id;

Line: 724


        ibe_util.debug('Express_Buy_Order: Validate_user_update START');

Line: 730


      IBE_Quote_Misc_pvt.Validate_User_Update
        (p_init_msg_list          => p_Init_Msg_List
        ,p_quote_header_id        => p_cart_id
        ,p_party_id               => p_party_id
        ,p_cust_account_id        => p_acct_id
        ,p_quote_retrieval_number => p_retrieval_num
        ,p_validate_user          => FND_API.G_TRUE
        ,p_privilege_type_code    => 'A'
	,p_last_update_date       => p_last_update_date
        ,x_return_status          => x_return_status
        ,x_msg_count              => x_msg_count
        ,x_msg_data               => x_msg_data     );

Line: 760


    ibe_util.debug('Input p_last_update_date:  '||p_last_update_date);

Line: 812


        ibe_util.debug('Express_Buy_Order - oneclick update: no products to add!');

Line: 820


      FETCH c_quote_date INTO l_qte_header_rec.last_update_date;

Line: 823


    l_qte_header_rec.last_update_date   := p_last_update_date;

Line: 824


    l_control_rec.last_update_date := l_qte_header_rec.last_update_date;

Line: 836


    l_qte_header_rec.last_update_date := FND_API.G_MISS_DATE;

Line: 837


    l_control_rec.last_update_date    := FND_API.G_MISS_DATE;

Line: 867


      ibe_util.debug('     l_qte_header_rec.last_update_date : ' || to_char(l_qte_header_rec.last_update_date,'DD-MON-YYYY:HH24:MI:SS'));

Line: 884


            p_auto_update_active_quote => FND_API.g_false,
    --        p_combineSameItem    => 'Y', -- let api default from profile
            p_sharee_Number            => p_retrieval_num,
            p_sharee_party_id          => l_sharee_party_id,
            p_sharee_cust_account_id   => l_sharee_acct_id,
            p_minisite_id              => p_minisite_id,
            p_control_rec              => l_control_rec,
            p_qte_header_rec           => l_qte_header_rec,
            P_Qte_Line_Tbl             => l_qte_line_tbl,
            P_hd_Payment_Tbl           => l_hd_Payment_Tbl,
            P_hd_Shipment_Tbl          => l_hd_Shipment_Tbl,
            P_hd_Tax_Detail_Tbl        => l_hd_Tax_Detail_Tbl,

            x_quote_header_id          => lx_quote_header_id,
            x_last_update_date         => lx_last_update_date,
            X_Return_Status            => x_Return_Status,
            X_Msg_Count                => x_Msg_Count,
            X_Msg_Data                 => x_Msg_Data);

Line: 971


              p_delete_context  => 'IBE_SC_CART_ORDERED',
              P_minisite_id     => p_minisite_id        ,
              p_api_version     => p_api_version        ,
              p_init_msg_list   => fnd_api.g_false      ,
              p_commit          => fnd_api.g_false      ,
              x_return_status   => x_return_status      ,
              x_msg_count       => x_msg_count          ,
              x_msg_data        => x_msg_data           );

Line: 995


    l_qte_header_rec.last_update_date := lx_last_update_date;

Line: 996


    l_control_rec.last_update_date    := lx_last_update_date;

Line: 1124


	x_last_update_date := IBE_Quote_Misc_pvt.getQuoteLastUpdateDate(p_cart_id);

Line: 1173


SELECT quote_header_id, creation_date, currency_code
FROM aso_quote_headers
WHERE quote_source_code = 'IStore Oneclick'
and party_id        = c_party_id
and cust_account_id = c_acct_id
and quote_name      is null
and order_id        is null
and nvl(trunc(quote_expiration_date), trunc(sysdate)+1) >= trunc(sysdate);

Line: 1183


SELECT  object_version_number,
        ord_oneclick_id,
        enabled_flag,
        freight_code,
        payment_id,
        bill_to_pty_site_id,
        ship_to_pty_site_id,
        last_update_date

FROM IBE_ORD_ONECLICK
WHERE party_id        = c_party_id
and   cust_account_id = c_acct_id;

Line: 1197


SELECT last_update_date
FROM ASO_QUOTE_HEADERS
WHERE quote_header_id = c_qte_header_id;

Line: 1202


SELECT p.party_type
FROM HZ_PARTIES p, HZ_CUST_ACCOUNTS a
where p.party_id = a.party_id
and a.cust_account_id = c_acct_id;

Line: 1208


SELECT bank_account_num, inactive_date, bank_account_name
FROM AP_BANK_ACCOUNTS
WHERE bank_account_id = c_ba_id;

Line: 1213


SELECT payment_id
FROM ASO_PAYMENTS
WHERE quote_header_id = c_qtehdr_id
and quote_line_id is null;

Line: 1219


SELECT shipment_id
FROM ASO_SHIPMENTS
WHERE quote_header_id = c_qtehdr_id
and quote_line_id is null;

Line: 1225


    SELECT substr(party_name,1,50) from HZ_PARTIES
    WHERE party_type = 'PERSON' and party_id = c_party_id
    UNION
    SELECT substr(party_name,1,50) from HZ_PARTIES
    WHERE party_id = (SELECT subject_id from HZ_RELATIONSHIPS
                       WHERE party_id = c_party_id
                       and subject_type = 'PERSON'
                       and object_type = 'ORGANIZATION');

Line: 1272


lx_last_update_date      DATE;

Line: 1346


      FETCH c_quote_date INTO l_push_qte_header_rec.last_update_date;

Line: 1349


    l_control_rec.last_update_date := l_push_qte_header_rec.last_update_date;

Line: 1354


      ibe_util.debug('     l_qte_header_rec.last_update_date : ' || to_char(l_qte_header_rec.last_update_date,'DD-MON-YYYY:HH24:MI:SS'));

Line: 1360


       p_auto_update_active_quote => FND_API.g_false,
       --p_combineSameItem    => 'Y', -- let api default from profile
       p_control_rec              => l_control_rec,
       p_qte_header_rec           => l_push_qte_header_rec,
       p_save_type                => UPDATE_EXPRESSORDER,
       x_quote_header_id          => lx_quote_header_id,
       x_last_update_date         => lx_last_update_date,
       X_Return_Status            => lx_Return_Status,
       X_Msg_Count                => lx_Msg_Count,
       X_Msg_Data                 => lx_Msg_Data);

Line: 1433


    select count(*) into l_count_tax
    from ASO_TAX_DETAILS
    where quote_header_id = l_curr_cart_id
    and   quote_line_id is null;

Line: 1469


      ibe_util.debug('Express_Buy_Order - ***setting to create or update payment rec***');

Line: 1508


        l_payment_rec.operation_code := 'UPDATE';

Line: 1530


      ibe_util.debug('Get_express_items_settings - ***setting to create or update shipment rec***');

Line: 1532


    l_shipment_rec.operation_code := 'UPDATE';

Line: 1602


        Update_Settings
         - Updates the Express checkout settings
         - Called when a credit card is deleted, to check if the deleted
           credit card is the one selected for Express Checkout. If so,
           disables the Express Checkout

 ------------------------------------------------------------------------------
*/
Procedure Update_Settings(
    p_api_version      IN     NUMBER,
    p_init_msg_list    IN    VARCHAR2 := FND_API.g_false,
    p_commit           IN    VARCHAR2 := FND_API.g_false,
    x_return_status    OUT NOCOPY    VARCHAR2,
    x_msg_count        OUT NOCOPY    NUMBER,
    x_msg_data         OUT NOCOPY    VARCHAR2,
    p_party_id         IN     NUMBER := NULL,
    p_acct_id          IN     NUMBER := NULL,
    p_assignment_id    IN     NUMBER := NULL)
IS

    l_api_name      CONSTANT   VARCHAR2(30)  := 'Update_Settings';

Line: 1636


   select ord_oneclick_id, object_version_number, enabled_flag,  freight_code,
          payment_id, bill_to_pty_site_id, ship_to_pty_site_id
   from ibe_ord_oneclick
   where party_id = c_party_id and cust_account_id = c_acct_id;

Line: 1643


    SAVEPOINT     Update_Settings;

Line: 1646


      ibe_util.debug('------- Input to ibe_ord_oneclick_pvt.Update_Settings: -----------------');

Line: 1669


       ibe_util.debug('------- Retrieved From TABLES: Update_Settings-----------------');

Line: 1685


           ibe_util.debug('Update_Settings : Deleted CC is the one selected for Express checkout');

Line: 1686


           ibe_util.debug('Update_Settings : Disabling the Express checkout - Calling ibe_ord_oneclick_pvt.save_settings');

Line: 1707


           ibe_util.debug('Update_Settings : After Calling ibe_ord_oneclick_pvt.save_settings');

Line: 1708


           ibe_util.debug('Update_Settings : x_return_status - '||x_return_status);

Line: 1713


             ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings - call to ibe_ord_oneclick_pvt.save_settings failed' || x_return_status);

Line: 1727


           ibe_util.debug('Update_Settings : Success - Express Checkout disabled');

Line: 1728


           ibe_util.debug('Update_Settings : End');

Line: 1732


           ibe_util.debug('Update_Settings : Express Checkout disabled - No need for update');

Line: 1733


           ibe_util.debug('Update_Settings : End');

Line: 1747


           ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings: EXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));

Line: 1749


        ROLLBACK TO Update_Settings;

Line: 1759


           ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings: UNEXPECTED ERROR EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));

Line: 1761


        ROLLBACK TO Update_Settings;

Line: 1771


           ibe_util.debug('ibe_ord_oneclick_pvt.Update_Settings: OTHER EXCEPTION ' || to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS'));

Line: 1773


        ROLLBACK TO Update_Settings;

Line: 1787


End Update_Settings;