[Home] [Help]
2180: || ' WHERE IT.inventory_item_id = CII.inventory_item_id '
2181: || ' AND IT.serviceable_product_flag = ''Y'''
2182: || ' AND IT.organization_id = :l_organization_id '
2183: || ' AND CII.owner_party_account_id in (select cust_account_id '
2184: ||' from hz_cust_accounts '
2185: ||' where party_id = :p_party_id ) '
2186: || ' And CIS.instance_status_id = CII.instance_status_id '
2187: || ' And CIS.service_order_allowed_flag = ''Y'''
2188: || ' And sysdate between Nvl(CIS.start_date_active, sysdate) and Nvl(CIS.end_date_active, sysdate) '
2204: || ' WHERE IT.inventory_item_id = CII.inventory_item_id '
2205: || ' AND IT.serviceable_product_flag = ''Y'''
2206: || ' AND IT.organization_id = :l_organization_id '
2207: || ' AND CII.owner_party_account_id in (select cust_account_id '
2208: ||' from HZ_CUST_ACCOUNTS '
2209: ||' where party_id = :p_party_id '
2210: ||' UNION ALL'
2211: ||' select A.RELATED_CUST_ACCOUNT_ID '
2212: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
2209: ||' where party_id = :p_party_id '
2210: ||' UNION ALL'
2211: ||' select A.RELATED_CUST_ACCOUNT_ID '
2212: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
2213: ||' HZ_CUST_ACCOUNTS B '
2214: ||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
2215: ||' AND B.party_id = :p_party_id '
2216: ||' AND B.status = ''A'''
2217: ||' AND A.status = ''A'''
2238: || ' AND IT.organization_id = :l_organization_id '
2239: || ' AND CII.owner_party_account_id in '
2240: ||' (select A.RELATED_CUST_ACCOUNT_ID '
2241: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
2242: ||' HZ_CUST_ACCOUNTS B '
2243: ||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
2244: ||' AND B.party_id = :p_party_id '
2245: ||' AND B.status = ''A'''
2246: ||' AND A.status = ''A'''
2434: ||' AND IT.organization_id = :l_organization_id '
2435: ||' AND CII.install_location_id = SI.id1 '
2436: ||' AND CII.owner_party_account_id in '
2437: ||'(select cust_account_id '
2438: ||' from hz_cust_accounts '
2439: ||' where party_id = :p_party_id ) '
2440: ||' And CIS.instance_Status_id = CII.instance_status_id '
2441: ||' And CIS.service_order_allowed_flag = ''Y'''
2442: ||' And sysdate between Nvl(CIS.start_date_active, sysdate) and '
2463: ||' AND CII.install_location_id = SI.id1 '
2464: ||' AND CII.owner_party_account_id in '
2465: ||' (select A.RELATED_CUST_ACCOUNT_ID '
2466: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
2467: ||' HZ_CUST_ACCOUNTS B '
2468: ||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
2469: ||' AND B.party_id = :l_party_id '
2470: ||' AND B.status = ''A'''
2471: ||' AND A.status = ''A'''
2496: ||' AND IT.organization_id = :l_organization_id '
2497: ||' AND CII.install_location_id = SI.id1 '
2498: ||' AND CII.owner_party_account_id in '
2499: ||' (select cust_account_id '
2500: ||' from hz_cust_accounts '
2501: ||' where party_id = :p_party_id '
2502: ||' UNION '
2503: ||' select A.RELATED_CUST_ACCOUNT_ID '
2504: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
2501: ||' where party_id = :p_party_id '
2502: ||' UNION '
2503: ||' select A.RELATED_CUST_ACCOUNT_ID '
2504: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
2505: ||' HZ_CUST_ACCOUNTS B '
2506: ||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
2507: ||' AND B.party_id = :p_party_id '
2508: ||' AND B.status = ''A'''
2509: ||' AND A.status = ''A'''
6236:
6237: CURSOR l_Get_party_id(p_cust_acct_id IN NUMBER)
6238: IS
6239: SELECT party_id
6240: FROM HZ_CUST_ACCOUNTS
6241: WHERE cust_account_id = p_cust_acct_id;
6242:
6243:
6244: /*** Fetch inventory revision code **/