[Home] [Help]
1154: -- But for B2C,it is always to him, the customer. No any contact will be present.
1155: -- This is the value to be populated in oe_order_headers_all.invoice_to_contact_id.
1156: -- How this value is retrieved ?
1157: -- This is hz_cust_account_roles.cust_account_role_id whose
1158: -- cust_acct_site_id = hz_cust_acct_sites_all.cust_acct_site_id and
1159: -- hz_cust_acct_sites_all.cust_acct_site_id = hz_cust_site_uses_all.cust_acct_site_id
1160: -- hz_cust_site_uses_all.site_use_code = 'BILL_TO' and site_use_id = <
1161: -- Also, If the address is defaulted from any of the valid lines,
1162: -- no need to populate the 'ContactId'.
1155: -- This is the value to be populated in oe_order_headers_all.invoice_to_contact_id.
1156: -- How this value is retrieved ?
1157: -- This is hz_cust_account_roles.cust_account_role_id whose
1158: -- cust_acct_site_id = hz_cust_acct_sites_all.cust_acct_site_id and
1159: -- hz_cust_acct_sites_all.cust_acct_site_id = hz_cust_site_uses_all.cust_acct_site_id
1160: -- hz_cust_site_uses_all.site_use_code = 'BILL_TO' and site_use_id = <
1161: -- Also, If the address is defaulted from any of the valid lines,
1162: -- no need to populate the 'ContactId'.
1163: -- Why?
1171: Declare
1172: cursor c_cust_acct_id (lin_custacct_siteuse_id number, lin_siteuse_type varchar2)
1173: is
1174: select hca.cust_acct_site_id, hca.cust_account_id,hps.party_id
1175: from hz_cust_acct_sites hca,hz_cust_site_uses hcu,hz_party_sites hps
1176: where
1177: hcu.site_use_id = lin_custacct_siteuse_id
1178: and hcu.site_use_code = lin_siteuse_type
1179: and hcu.cust_acct_site_id = hca.cust_acct_site_id
2095:
2096: if (p_site_use_type ='BILL_TO') then
2097: l_addr_validate_qry:='SELECT LINES.LINE_ID '||
2098: 'FROM HZ_CUST_SITE_USES SITE, '||
2099: 'HZ_CUST_ACCT_SITES ACCT_SITE, '||
2100: 'OE_ORDER_LINES_ALL LINES '||
2101: 'WHERE LINES.HEADER_ID = :InvChkHdrId '||
2102: 'AND SITE.SITE_USE_ID = LINES.invoice_to_org_id '||
2103: 'AND SITE.SITE_USE_CODE = ''BILL_TO'' '||
2117: elsif(p_site_use_type = 'SHIP_TO') then
2118:
2119: l_addr_validate_qry :='SELECT LINES.LINE_ID '||
2120: 'FROM HZ_CUST_SITE_USES SITE, '||
2121: 'HZ_CUST_ACCT_SITES ACCT_SITE, '||
2122: 'OE_ORDER_LINES_ALL LINES '||
2123: 'WHERE LINES.HEADER_ID = :shpChkHdrId '||
2124: 'AND SITE.SITE_USE_ID = LINES.ship_to_org_id '||
2125: 'AND SITE.SITE_USE_CODE = ''SHIP_TO'' '||
2142:
2143: if(p_site_use_type = 'BILL_TO') then
2144: l_addr_validate_qry := 'SELECT HDR.HEADER_ID '||
2145: 'FROM HZ_CUST_SITE_USES SITE, '||
2146: 'HZ_CUST_ACCT_SITES ACCT_SITE, '||
2147: 'OE_ORDER_HEADERS_ALL HDR '||
2148: 'WHERE HDR.HEADER_ID = :invChkHdrId '||
2149: 'AND SITE.SITE_USE_ID = HDR.invoice_to_org_id '||
2150: 'AND SITE.SITE_USE_CODE = ''BILL_TO'' '||
2164:
2165: elsif(p_site_use_type = 'SHIP_TO') then
2166: l_addr_validate_qry := 'SELECT HDR.HEADER_ID '||
2167: 'FROM HZ_CUST_SITE_USES SITE, '||
2168: 'HZ_CUST_ACCT_SITES ACCT_SITE, '||
2169: 'OE_ORDER_HEADERS_ALL HDR '||
2170: 'WHERE HDR.HEADER_ID = :shpChkHdrId '||
2171: 'AND SITE.SITE_USE_ID = HDR.ship_to_org_id '||
2172: 'AND SITE.SITE_USE_CODE = ''SHIP_TO'' '||
2274: l_contact_validate_qry:='SELECT LINES.LINE_ID '||
2275: 'FROM OE_ORDER_LINES_ALL LINES, '||
2276: 'HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, '||
2277: 'HZ_CUST_SITE_USES_ALL INV, '||
2278: 'HZ_CUST_ACCT_SITES_ALL ADDR '||
2279: 'WHERE LINES.HEADER_ID = :InvCntChkHdrId '||
2280: 'AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = LINES.invoice_to_contact_id '||
2281: 'AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID '||
2282: 'AND ACCT_ROLE.ROLE_TYPE = ''CONTACT'' '||
2290: l_contact_validate_qry:='SELECT LINES.LINE_ID '||
2291: 'FROM OE_ORDER_LINES_ALL LINES, '||
2292: 'HZ_CUST_ACCOUNT_ROLES ACCT_ROLE, '||
2293: 'HZ_CUST_SITE_USES_ALL INV, '||
2294: 'HZ_CUST_ACCT_SITES_ALL ADDR '||
2295: 'WHERE LINES.HEADER_ID = :InvCntChkHdrId '||
2296: 'AND ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = LINES.ship_to_contact_id '||
2297: 'AND ACCT_ROLE.CUST_ACCOUNT_ID = ADDR.CUST_ACCOUNT_ID '||
2298: 'AND ACCT_ROLE.ROLE_TYPE = ''CONTACT'' '||