[Home] [Help]
1652: || ' WHERE IT.inventory_item_id = CII.inventory_item_id '
1653: || ' AND IT.serviceable_product_flag = ''Y'''
1654: || ' AND IT.organization_id = :l_organization_id '
1655: || ' AND CII.owner_party_account_id in (select cust_account_id '
1656: ||' from hz_cust_accounts '
1657: ||' where party_id = :p_party_id ) '
1658: || ' And CIS.instance_status_id = CII.instance_status_id '
1659: || ' And CIS.service_order_allowed_flag = ''Y'''
1660: || ' And sysdate between Nvl(CIS.start_date_active, sysdate) and Nvl(CIS.end_date_active, sysdate) '
1676: || ' WHERE IT.inventory_item_id = CII.inventory_item_id '
1677: || ' AND IT.serviceable_product_flag = ''Y'''
1678: || ' AND IT.organization_id = :l_organization_id '
1679: || ' AND CII.owner_party_account_id in (select cust_account_id '
1680: ||' from HZ_CUST_ACCOUNTS '
1681: ||' where party_id = :p_party_id '
1682: ||' UNION ALL'
1683: ||' select A.RELATED_CUST_ACCOUNT_ID '
1684: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
1681: ||' where party_id = :p_party_id '
1682: ||' UNION ALL'
1683: ||' select A.RELATED_CUST_ACCOUNT_ID '
1684: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
1685: ||' HZ_CUST_ACCOUNTS B '
1686: ||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
1687: ||' AND B.party_id = :p_party_id '
1688: ||' AND B.status = ''A'''
1689: ||' AND A.status = ''A'''
1710: || ' AND IT.organization_id = :l_organization_id '
1711: || ' AND CII.owner_party_account_id in '
1712: ||' (select A.RELATED_CUST_ACCOUNT_ID '
1713: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
1714: ||' HZ_CUST_ACCOUNTS B '
1715: ||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
1716: ||' AND B.party_id = :p_party_id '
1717: ||' AND B.status = ''A'''
1718: ||' AND A.status = ''A'''
1906: ||' AND IT.organization_id = :l_organization_id '
1907: ||' AND CII.install_location_id = SI.id1 '
1908: ||' AND CII.owner_party_account_id in '
1909: ||'(select cust_account_id '
1910: ||' from hz_cust_accounts '
1911: ||' where party_id = :p_party_id ) '
1912: ||' And CIS.instance_Status_id = CII.instance_status_id '
1913: ||' And CIS.service_order_allowed_flag = ''Y'''
1914: ||' And sysdate between Nvl(CIS.start_date_active, sysdate) and '
1935: ||' AND CII.install_location_id = SI.id1 '
1936: ||' AND CII.owner_party_account_id in '
1937: ||' (select A.RELATED_CUST_ACCOUNT_ID '
1938: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
1939: ||' HZ_CUST_ACCOUNTS B '
1940: ||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
1941: ||' AND B.party_id = :l_party_id '
1942: ||' AND B.status = ''A'''
1943: ||' AND A.status = ''A'''
1968: ||' AND IT.organization_id = :l_organization_id '
1969: ||' AND CII.install_location_id = SI.id1 '
1970: ||' AND CII.owner_party_account_id in '
1971: ||' (select cust_account_id '
1972: ||' from hz_cust_accounts '
1973: ||' where party_id = :p_party_id '
1974: ||' UNION '
1975: ||' select A.RELATED_CUST_ACCOUNT_ID '
1976: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
1973: ||' where party_id = :p_party_id '
1974: ||' UNION '
1975: ||' select A.RELATED_CUST_ACCOUNT_ID '
1976: ||' FROM HZ_CUST_ACCT_RELATE_ALL A, '
1977: ||' HZ_CUST_ACCOUNTS B '
1978: ||' WHERE B.cust_account_id = A.CUST_ACCOUNT_ID '
1979: ||' AND B.party_id = :p_party_id '
1980: ||' AND B.status = ''A'''
1981: ||' AND A.status = ''A'''
5622:
5623: CURSOR l_Get_party_id(p_cust_acct_id IN NUMBER)
5624: IS
5625: SELECT party_id
5626: FROM HZ_CUST_ACCOUNTS
5627: WHERE cust_account_id = p_cust_acct_id;
5628:
5629:
5630: /*** Fetch inventory revision code **/