[Home] [Help]
2369: FROM csi_t_txn_line_details tld,
2370: csi_t_party_details tp,
2371: csi_t_party_accounts ta,
2372: hz_parties party,
2373: hz_cust_accounts account
2374: WHERE tld.txn_line_detail_id = tp.txn_line_detail_id
2375: AND tld.transaction_line_id = p_transaction_line_id
2376: AND tld.INSTANCE_ID IS NULL
2377: AND tp.txn_party_detail_id = ta.txn_party_detail_id
2381: AND tp.party_source_table = 'HZ_PARTIES';
2382: -- Bug 7350165
2383: -- CURSOR mu_customer_id_csr (p_party_id IN NUMBER, p_account_number IN NUMBER) IS
2384: -- SELECT CUST_ACCOUNT_ID CUSTOMER_ID
2385: -- FROM HZ_CUST_ACCOUNTS
2386: -- WHERE PARTY_ID = p_party_id
2387: -- AND ACCOUNT_NUMBER = p_account_number;
2388:
2389: CURSOR mu_system_csr (p_system_id IN NUMBER) IS
2849:
2850: -- Bug 7350165
2851: --CURSOR SYS_CUST_CSR (p_party_id IN NUMBER, p_account_number IN NUMBER) IS
2852: -- SELECT CUST_ACCOUNT_ID CUSTOMER_ID
2853: -- FROM HZ_CUST_ACCOUNTS
2854: -- WHERE PARTY_ID = p_party_id
2855: -- AND ACCOUNT_NUMBER = p_account_number;
2856:
2857: -- l_sys_cust_rec SYS_CUST_CSR%ROWTYPE; bug 7350165
4909: SELECT system_id
4910: INTO l_cust_sys_id
4911: FROM
4912: ( SELECT sys.system_id
4913: FROM csi_systems_vl sys, HZ_PARTIES hzp, HZ_CUST_ACCOUNTS acct,
4914: CSI_LOOKUPS LK
4915: WHERE sys.CUSTOMER_ID = acct.CUST_ACCOUNT_ID
4916: and acct.PARTY_ID = hzp.PARTY_ID
4917: and acct.account_number = l_instance.OWNER_ACCOUNT_NUMBER
4919: and LK.LOOKUP_TYPE (+) = 'CSI_SYSTEM_TYPE'
4920: and sys.name = l_instance.SYSTEM_NAME
4921: UNION
4922: SELECT sys.system_id
4923: FROM csi_systems_vl sys, HZ_PARTIES hzp, HZ_CUST_ACCOUNTS acct,
4924: HZ_CUST_ACCT_RELATE_ALL acctall, CSI_LOOKUPS LK
4925: WHERE sys.CUSTOMER_ID = acctall.RELATED_CUST_ACCOUNT_ID
4926: and sys.CUSTOMER_ID = acct.CUST_ACCOUNT_ID
4927: and acct.PARTY_ID = hzp.PARTY_ID
6372: --OWNER_ACCOUNT_ID
6373: BEGIN
6374: UPDATE csi_webadi_staging cws SET OWNER_ACCOUNT_ID =
6375: (
6376: SELECT CUST_ACCOUNT_ID FROM hz_cust_accounts
6377: WHERE status = 'A'
6378: AND sysdate between nvl(ACCOUNT_ACTIVATION_DATE,sysdate) and nvl(ACCOUNT_TERMINATION_DATE ,sysdate)
6379: AND PARTY_ID = cws.OWNER_ID
6380: AND ACCOUNT_NUMBER = cws.OWNER_ACCOUNT_NUMBER
6392: --ASSOCIATE_BILL_TO_ACCT_ID
6393: BEGIN
6394: UPDATE csi_webadi_staging cws SET ASSOCIATE_BILL_TO_ACCT_ID =
6395: (
6396: SELECT CUST_ACCOUNT_ID FROM hz_cust_accounts
6397: WHERE status = 'A'
6398: AND sysdate between nvl(ACCOUNT_ACTIVATION_DATE,sysdate) and nvl(ACCOUNT_TERMINATION_DATE ,sysdate)
6399: AND PARTY_ID = cws.ASSOCIATE_BILL_TO_PARTY_ID
6400: AND ACCOUNT_NUMBER = cws.ASSOCIATE_BILL_TO_ACCT_NUM
6412: -- ASSOCIATE_SHIP_TO_ACCT_ID
6413: BEGIN
6414: UPDATE csi_webadi_staging cws SET ASSOCIATE_SHIP_TO_ACCT_ID =
6415: (
6416: SELECT CUST_ACCOUNT_ID FROM hz_cust_accounts
6417: WHERE status = 'A'
6418: AND sysdate between nvl(ACCOUNT_ACTIVATION_DATE,sysdate) and nvl(ACCOUNT_TERMINATION_DATE ,sysdate)
6419: AND PARTY_ID = cws.ASSOCIATE_SHIP_TO_PARTY_ID
6420: AND ACCOUNT_NUMBER = cws.ASSOCIATE_SHIP_TO_ACCT_NUM
6440: AND okch.id = k_party.chr_id AND k_party.jtot_object1_code IN ('OKX_PARTY')
6441: AND contract_number = cws.ASSOCIATE_CONTRACT_NUMBER
6442: AND Nvl(contract_number_modifier, fnd_api.g_miss_char)
6443: = Nvl(cws.ASSOCIATE_CONTRACT_MODIFIER, fnd_api.g_miss_char)
6444: AND EXISTS (SELECT 1 FROM HZ_CUST_ACCOUNTS CA1
6445: WHERE CA1.party_id = k_party.object1_id1 AND CA1.status = 'A'
6446: AND CA1.account_number = cws.OWNER_ACCOUNT_NUMBER
6447: UNION ALL
6448: SELECT 1 FROM HZ_CUST_ACCOUNTS CA2, HZ_CUST_ACCT_RELATE_ALL REL
6444: AND EXISTS (SELECT 1 FROM HZ_CUST_ACCOUNTS CA1
6445: WHERE CA1.party_id = k_party.object1_id1 AND CA1.status = 'A'
6446: AND CA1.account_number = cws.OWNER_ACCOUNT_NUMBER
6447: UNION ALL
6448: SELECT 1 FROM HZ_CUST_ACCOUNTS CA2, HZ_CUST_ACCT_RELATE_ALL REL
6449: WHERE CA2.party_id = k_party.object1_id1
6450: AND REL.cust_account_id = CA2.CUST_ACCOUNT_ID
6451: AND REL.org_id = okch.AUTHORING_ORG_ID AND REL.status = 'A'
6452: AND CA2.status = 'A'