-- To scan if any open transaction (OM,IB,Service Contract,AR,Pricing,Sales Contract,Field Service) exists
-- for the given customer account
-- This is usefull if you are about to disable customer account and want to know if any transaction pending

-- Order Management query

SELECT /*+ parallel (oeh,6 ) */
oeh.order_number
,oeh.order_type_id
,oeh.flow_status_code hdr_flow_status_code
,oeh.sold_to_org_id hdr_sold_to_org_id
,oel.*
FROM oe_order_headers_all oeh
,oe_order_lines_all oel
,hz_cust_accounts_all hca1
WHERE oeh.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
AND oel.flow_status_code NOT IN ('CLOSED', 'CANCELLED')
AND oeh.open_flag = 'Y'
AND oeh.sold_to_org_id = hca1.cust_account_id
AND hca1.account_number = p_account_number
AND oel.header_id = oeh.header_id
AND (
EXISTS ( -- sold to org scan for order
SELECT *
FROM hz_cust_accounts_all hca
WHERE (oeh.sold_to_org_id = hca.cust_account_id)
AND hca.account_number = p_account_number)
OR EXISTS ( -- colled the ship to location for given cust account id and scan all related open orders
SELECT 1
FROM hz_cust_accounts_all hca
,hz_cust_acct_sites_all hcasa
,hz_cust_site_uses_all hcasua
WHERE hca.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id =
hcasua.cust_acct_site_id
AND hca.status = 'A'
AND hcasua.status = 'A'
AND hcasa.status = 'A'
AND hca.account_number = p_account_number
AND hca.cust_account_id = hca.cust_account_id
AND hcasua.site_use_code = 'SHIP_TO'
AND hcasua.site_use_id = oel.ship_to_org_id
AND ROWNUM < 2)
OR EXISTS ( -- colled the bill to , ship to site for given cust account id and scan all related open orders
SELECT 1
FROM hz_cust_accounts_all hca
,hz_cust_acct_sites_all hcasa
,hz_cust_site_uses_all hcasua
WHERE hca.cust_account_id = hcasa.cust_account_id
AND hcasa.cust_acct_site_id =
hcasua.cust_acct_site_id
AND hca.status = 'A'
AND hcasua.status = 'A'
AND hcasa.status = 'A'
AND hca.account_number = p_account_number
AND hca.cust_account_id = hca.cust_account_id
AND hcasua.site_use_code = 'BILL_TO'
AND (
hcasua.site_use_id = oel.invoice_to_org_id
OR hcasua.site_use_id = oeh.invoice_to_org_id
)
AND ROWNUM < 2)
);


-- Install base query

SELECT *
FROM csi_item_instances csi1
WHERE csi1.instance_id IN (
SELECT csi.instance_id
FROM csi_item_instances csi
,csi_i_parties pty
,hz_cust_accounts_all hza
WHERE csi.instance_id = pty.instance_id
AND relationship_type_code IN
('BILL_TO', 'OWNER', 'SHIP_TO', 'SOLD_TO')
AND pty.party_id = hza.party_id
AND TRUNC (SYSDATE)
BETWEEN NVL (TRUNC (csi.active_start_date)
,TRUNC (SYSDATE) )
AND NVL (TRUNC (csi.active_end_date)
,TRUNC (SYSDATE) )
AND hza.account_number = &p_account_number
UNION
SELECT csip.instance_id
FROM hz_cust_accounts_all hca
,hz_cust_acct_sites_all hcas
,hz_cust_site_uses_all hzsu
,csi_i_parties csip
,csi_ip_accounts csia
WHERE csia.bill_to_address = hzsu.site_use_id
AND hzsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND csip.instance_party_id = csia.instance_party_id
AND hca.status = 'A'
AND hcas.status = 'A'
AND hzsu.status = 'A'
AND csip.relationship_type_code = 'OWNER'
AND hzsu.site_use_code = 'BILL_TO'
AND hca.account_number = p_account_number
AND TRUNC (SYSDATE)
BETWEEN NVL (TRUNC (csip.active_start_date)
,TRUNC (SYSDATE) )
AND NVL (TRUNC (csip.active_end_date)
,TRUNC (SYSDATE) )
AND TRUNC (SYSDATE)
BETWEEN NVL (TRUNC (csia.active_start_date)
,TRUNC (SYSDATE) )
AND NVL (TRUNC (csia.active_end_date)
,TRUNC (SYSDATE) )
UNION
SELECT csip.instance_id
FROM hz_cust_accounts_all hca
,hz_cust_acct_sites_all hcas
,hz_cust_site_uses_all hzsu
,csi_i_parties csip
,csi_ip_accounts csia
WHERE csia.ship_to_address = hzsu.site_use_id
AND hzsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND csip.instance_party_id = csia.instance_party_id
AND hcas.status = 'A'
AND hzsu.status = 'A'
AND csip.relationship_type_code = 'OWNER'
AND hzsu.site_use_code = 'SHIP_TO'
AND hca.account_number = p_account_number
AND TRUNC (SYSDATE)
BETWEEN NVL (TRUNC (csip.active_start_date)
,TRUNC (SYSDATE) )
AND NVL (TRUNC (csip.active_end_date)
,TRUNC (SYSDATE) )
AND TRUNC (SYSDATE)
BETWEEN NVL (TRUNC (csia.active_start_date)
,TRUNC (SYSDATE) )
AND NVL (TRUNC (csia.active_end_date)
,TRUNC (SYSDATE) ) );


-- Service Contracts


SELECT okh.*
FROM okc_k_headers_b okh
,okc_k_party_roles_b okp
,hz_cust_accounts hca
WHERE okh.sts_code NOT IN
('EXPIRED', 'TERMINATED', 'CANCELLED', 'DELETED')
AND okh.scs_code IN ('SERVICE', 'WARRANTY')
AND okh.ID = okp.dnz_chr_id
AND okp.object1_id1 = TO_CHAR (hca.party_id)
AND hca.status = 'A'
AND hca.account_number = &p_account_number
AND (
EXISTS (
SELECT 1
FROM okc_k_party_roles_b okprb
WHERE okprb.rle_code LIKE 'CUSTOMER'
AND okprb.jtot_object1_code LIKE 'OKX_PARTY'
AND okprb.object1_id1 = TO_CHAR (hca.party_id)
AND okh.ID = okprb.dnz_chr_id)
OR EXISTS (
SELECT 1
FROM okc_rules_b orb
,okc_rule_groups_b orgb
,hz_cust_site_uses_all hcsu
,hz_cust_acct_sites_all hcas
WHERE orgb.ID = orb.rgp_id
AND orb.rule_information_category LIKE 'STO'
AND orgb.cle_id IS NULL
AND hcsu.status = 'A'
AND hcas.status = 'A'
AND orb.object1_id1 = TO_CHAR (hcsu.site_use_id)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND okh.ID = orgb.dnz_chr_id)
OR EXISTS (
SELECT 1
FROM okc_rules_b orb
,okc_rule_groups_b orgb
,hz_cust_site_uses_all hcsu
,hz_cust_acct_sites_all hcas
WHERE orgb.ID = orb.rgp_id
AND orb.rule_information_category LIKE 'BTO'
AND orgb.cle_id IS NULL
AND orb.object1_id1 = TO_CHAR (hcsu.site_use_id)
AND hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND hcas.cust_account_id = hca.cust_account_id
AND hcsu.status = 'A'
AND hcas.status = 'A'
AND okh.ID = orgb.dnz_chr_id)


--Accounts Receivable

SELECT rcta.trx_number --Transaction Number
,rcta.interface_header_attribute1 --Source
,rctt.NAME --Transaction Type
,rcta.interface_header_context
,rcta.ship_to_customer_id
,rcta.bill_to_customer_id
,rcta.sold_to_customer_id
,rcta.ship_to_site_use_id
,rcta.bill_to_site_use_id
,rcta.sold_to_site_use_id
FROM ra_customer_trx_all rcta
,ra_cust_trx_types_all rctt
,ar_payment_schedules_all arp
,hz_cust_accounts_all hza
WHERE arp.customer_id = hza.cust_account_id
AND rcta.customer_trx_id = arp.customer_trx_id
AND rcta.cust_trx_type_id = rctt.cust_trx_type_id
AND TRUNC (SYSDATE) BETWEEN rctt.start_date
AND NVL (rctt.end_date, TRUNC (SYSDATE) )
AND hza.status = 'A'
AND arp.status = 'OP'
AND hza.account_number = &p_account_number;


-- Field Service

SELECT cia.incident_number request_number
,cis.NAME status
,cii.serial_number
,okh.contract_number
,hp.party_name customer_name
,hca.account_number customer_number
,jrg.group_name
,jrg.group_number
FROM cs_incidents_all_b cia
,cs_incident_statuses_tl cis
,csi_item_instances cii
,okc_k_items oki
,okc_k_headers_b okh
,okc_k_party_roles_b okp
,hz_cust_accounts hca
,hz_parties hp
,jtf_rs_groups_vl jrg
WHERE cia.incident_status_id = cis.incident_status_id
AND cis.NAME NOT IN
(
'Closed'
,'Closed - Call Avoidance'
,'Closed - Hotline Call Avoided'
,'Closed - L1B'
,'Closed - Tech Call Avoidance'
,'Cancelled'
)
AND cia.customer_product_id = cii.instance_id
AND oki.object1_id1 = TO_CHAR (cii.instance_id)
AND oki.jtot_object1_code = 'OKX_CUSTPROD'
AND oki.dnz_chr_id = okh.ID
AND okh.sts_code NOT IN
('EXPIRED', 'TERMINATED', 'CANCELLED', 'DELETED')
AND okh.ID = okp.chr_id
AND okp.rle_code = 'CUSTOMER'
AND okp.object1_id1 = TO_CHAR (hca.party_id)
AND hca.party_id = hp.party_id
AND cia.owner_group_id = jrg.GROUP_ID
AND hca.status = 'A'
AND hp.status = 'A'
AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (okh.start_date)
,TRUNC (SYSDATE) )
AND NVL (okh.end_date, TRUNC (SYSDATE) )
AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (jrg.start_date_active)
,TRUNC (SYSDATE) )
AND NVL (jrg.end_date_active
,TRUNC (SYSDATE) )
AND hca.account_number = &p_account_number;


-- Oracle Sales Contract

SELECT ros.contract_number
,ros.contract_id
,oat.NAME agreement_name
,qlh.NAME price_list_name
FROM ( SELECT hca.cust_account_id customer_account_id
FROM okc_k_party_roles_v okpr
,hz_cust_accounts hca
,hz_parties hp
WHERE okpr.jtot_object1_code = 'OKX_PARTY'
AND okpr.rle_code = 'CUSTOMER'
AND hp.party_id = TO_NUMBER (okpr.object1_id1)
AND hp.party_id = hca.party_id
AND okpr.dnz_chr_id = c_msc_rec.contract_id
AND hp.status = 'A'
AND hca.status = 'A'
union
SELECT hca.cust_account_id customer_account_id
FROM okc_k_party_roles_v okpr
,hz_cust_accounts hca
,hz_parties hp
WHERE okpr.jtot_object1_code = 'OKX_PARTY'
AND okpr.rle_code = 'DEALER'
AND hp.party_id = TO_NUMBER (okpr.object1_id1)
AND hp.party_id = hca.party_id
AND okpr.dnz_chr_id = c_msc_rec.contract_id
AND hp.status = 'A'
AND hca.status = 'A' )ros
,hz_cust_accounts hca
,oe_agreements_b agb
,oe_agreements_tl oat
,qp_list_headers qlh
WHERE hca.cust_account_id = ros.customer_account_id
AND ros.price_list_id = agb.agreement_id
AND agb.agreement_id = oat.agreement_id
AND agb.price_list_id = qlh.list_header_id
AND contract_status NOT IN
('EXPIRED', 'TERMINATED', 'CANCELLED', 'DELETED')
AND hca.status = 'A'
AND NVL (qlh.active_flag, 'Y') = 'Y'
AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (agb.start_date_active)
,TRUNC (SYSDATE) )
AND NVL (TRUNC (agb.end_date_active)
,TRUNC (SYSDATE) )
AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (qlh.start_date_active)
,TRUNC (SYSDATE) )
AND NVL (TRUNC (qlh.end_date_active)
,TRUNC (SYSDATE) )
AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (ros.start_date)
,TRUNC (SYSDATE) )
AND NVL (TRUNC (ros.end_date)
,TRUNC (SYSDATE) )
AND hca.account_number = p_account_number;

-- Oracle Advance pricing, we will assume only qualifiers the customer is used,if it is used in
-- attributes then we need to scan there as well

SELECT qlh.NAME
,hp.party_name
,hca.account_number
FROM qp_qualifiers_v qqv
,qp_list_headers qlh
,hz_parties hp
,hz_cust_accounts hca
WHERE qualifier_context = 'CUSTOMER'
AND qlh.list_header_id = qqv.list_header_id
AND qqv.qualifier_attr_value = TO_CHAR (hca.cust_account_id)
AND hca.party_id = hp.party_id
AND NVL (qlh.active_flag, 'Y') = 'Y'
AND hp.status = 'A'
AND hca.status = 'A'
AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (qqv.start_date_active)
,TRUNC (SYSDATE) )
AND NVL (TRUNC (qqv.end_date_active)
,TRUNC (SYSDATE) )
AND hca.account_number = &p_account_number;


-- To find give account releated to any other active customers by AR account relationship


SELECT rc.customer_name
,rc.customer_number
,rcra.relationship_type
,rcra.customer_reciprocal_flag
,rcra.status
,rcra.bill_to_flag
,rcra.ship_to_flag
,hca.account_number
,rcra.related_customer_id
FROM ra_customers rc
,ra_customer_relationships_all rcra
,hz_cust_accounts_all hca
WHERE rc.party_id = hca.party_id
AND rc.customer_id = rcra.customer_id
AND rcra.status = 'A'
AND hca.status = 'A'
AND rc.status = 'A'
AND hca.account_number = &p_account_number;


-- To find give account releated to any other active customers by AR Party relationship

SELECT hp.party_name --Customer Name
,hca.account_number --Customer Number
,hzr.relationship_type --Relationship Type
,hzr.relationship_code --Relation
,hzr.object_id
FROM hz_parties hp
,hz_relationships hzr
,hz_cust_accounts_all hca
WHERE hp.party_id = hca.party_id
AND hp.party_id = hzr.subject_id
AND hp.status = 'A'
AND hca.status = 'A'
AND TRUNC (SYSDATE) BETWEEN hzr.start_date
AND NVL (hzr.end_date, TRUNC (SYSDATE) )
AND hca.account_number = &p_account_number;