[Home] [Help]
211: WHERE party_id in
212: ( SELECT child_id
213: from hz_hierarchy_nodes
214: where parent_object_type = 'ORGANIZATION'
215: and parent_table_name = 'HZ_PARTIES'
216: and child_object_type = 'ORGANIZATION'
217: and parent_id = p_party_id
218: and effective_start_date <= sysdate
219: and effective_end_date >= sysdate
433: WHERE party_id in
434: ( SELECT child_id
435: from hz_hierarchy_nodes
436: where parent_object_type = ''ORGANIZATION''
437: and parent_table_name = ''HZ_PARTIES''
438: and child_object_type = ''ORGANIZATION''
439: and parent_id = :3
440: and effective_start_date <= sysdate
441: and effective_end_date >= sysdate
515: WHERE party_id in
516: ( SELECT child_id
517: from hz_hierarchy_nodes
518: where parent_object_type = ''ORGANIZATION''
519: and parent_table_name = ''HZ_PARTIES''
520: and child_object_type = ''ORGANIZATION''
521: and parent_id = :3
522: and effective_start_date <= sysdate
523: and effective_end_date >= sysdate
704: WHERE party_id in
705: ( SELECT child_id
706: FROM hz_hierarchy_nodes
707: WHERE parent_object_type = 'ORGANIZATION'
708: AND parent_table_name = 'HZ_PARTIES'
709: AND child_object_type = 'ORGANIZATION'
710: AND parent_id = p_party_id
711: AND effective_start_date <= sysdate
712: AND effective_end_date >= sysdate
828: WHERE party_id in
829: ( SELECT child_id
830: from hz_hierarchy_nodes
831: where parent_object_type = 'ORGANIZATION'
832: and parent_table_name = 'HZ_PARTIES'
833: and child_object_type = 'ORGANIZATION'
834: and parent_id = p_party_id
835: and effective_start_date <= sysdate
836: and effective_end_date >= sysdate
861: WHERE party_id in
862: ( SELECT child_id
863: from hz_hierarchy_nodes
864: where parent_object_type = 'ORGANIZATION'
865: and parent_table_name = 'HZ_PARTIES'
866: and child_object_type = 'ORGANIZATION'
867: and parent_id = p_party_id
868: and effective_start_date <= sysdate
869: and effective_end_date >= sysdate
1079: WHERE party_id in
1080: ( SELECT child_id
1081: from hz_hierarchy_nodes
1082: where parent_object_type = 'ORGANIZATION'
1083: and parent_table_name = 'HZ_PARTIES'
1084: and child_object_type = 'ORGANIZATION'
1085: and parent_id = p_party_id
1086: and effective_start_date <= sysdate
1087: and effective_end_date >= sysdate
1819: SELECT trx_credit_limit, overall_credit_limit
1820: FROM hz_cust_profile_amts
1821: WHERE cust_acct_profile_amt_id = p_cust_acct_profile_amt_id;
1822:
1823: CURSOR c_hz_parties IS
1824: SELECT tax_name, year_established,
1825: sic_code, -- industrial code
1826: sic_code_type, -- industrial code type
1827: url,
1826: sic_code_type, -- industrial code type
1827: url,
1828: employees_total,
1829: duns_number
1830: FROM hz_parties
1831: WHERE party_id = p_party_id;
1832:
1833:
1834: CURSOR c_credit_requests IS
1891: p_resultout => l_resultout);
1892: exit;
1893: END LOOP;
1894:
1895: FOR c_hz_parties_rec IN c_hz_parties
1896: LOOP
1897:
1898: build_case_folder_details(
1899: p_case_folder_id => p_case_folder_id,
1897:
1898: build_case_folder_details(
1899: p_case_folder_id => p_case_folder_id,
1900: p_data_point_id => 89,
1901: p_data_point_value => c_hz_parties_rec.tax_name,
1902: p_mode => p_mode,
1903: p_error_msg => l_errmsg,
1904: p_resultout => l_resultout);
1905:
1905:
1906: build_case_folder_details(
1907: p_case_folder_id => p_case_folder_id,
1908: p_data_point_id => 93,
1909: p_data_point_value => c_hz_parties_rec.sic_code_type,
1910: p_mode => p_mode,
1911: p_error_msg => l_errmsg,
1912: p_resultout => l_resultout);
1913: build_case_folder_details(
1912: p_resultout => l_resultout);
1913: build_case_folder_details(
1914: p_case_folder_id => p_case_folder_id,
1915: p_data_point_id => 92,
1916: p_data_point_value => c_hz_parties_rec.sic_code,
1917: p_mode => p_mode,
1918: p_error_msg => l_errmsg,
1919: p_resultout => l_resultout);
1920: build_case_folder_details(
1919: p_resultout => l_resultout);
1920: build_case_folder_details(
1921: p_case_folder_id => p_case_folder_id,
1922: p_data_point_id => 100,
1923: p_data_point_value => c_hz_parties_rec.url,
1924: p_mode => p_mode,
1925: p_error_msg => l_errmsg,
1926: p_resultout => l_resultout);
1927: build_case_folder_details(
1926: p_resultout => l_resultout);
1927: build_case_folder_details(
1928: p_case_folder_id => p_case_folder_id,
1929: p_data_point_id => 101,
1930: p_data_point_value => c_hz_parties_rec.employees_total,
1931: p_mode => p_mode,
1932: p_error_msg => l_errmsg,
1933: p_resultout => l_resultout);
1934: build_case_folder_details(
1933: p_resultout => l_resultout);
1934: build_case_folder_details(
1935: p_case_folder_id => p_case_folder_id,
1936: p_data_point_id => 183,
1937: p_data_point_value => c_hz_parties_rec.duns_number,
1938: p_mode => p_mode,
1939: p_error_msg => l_errmsg,
1940: p_resultout => l_resultout);
1941: exit;
4262: l_relationship_id hz_relationships.relationship_id%TYPE;
4263: l_report_type hz_financial_reports.type_of_financial_report%TYPE := 'BALANCE_SHEET';
4264: l_credit_rating_id NUMBER;
4265: l_financial_report_id NUMBER;
4266: l_hq_branch_ind hz_parties.hq_branch_ind%type;
4267: l_balance_sheet_id NUMBER;
4268: l_income_statement_id NUMBER;
4269: l_tangible_net_worth_id NUMBER;
4270: l_annual_sales_volume_id NUMBER;
4277: 'DOMESTIC_ULTIMATE_OF','GLOBAL_ULTIMATE_OF')
4278: AND rel.actual_content_source = 'DNB'
4279: AND rel.start_date <= sysdate
4280: AND NVL(rel.end_date, to_date('12/31/4712','MM/DD/YYYY')) > sysdate
4281: AND rel.subject_table_name = 'HZ_PARTIES'
4282: AND rel.object_table_name = 'HZ_PARTIES';
4283:
4284: CURSOR c_contact_point IS
4285: SELECT contact_point_id, phone_line_type
4278: AND rel.actual_content_source = 'DNB'
4279: AND rel.start_date <= sysdate
4280: AND NVL(rel.end_date, to_date('12/31/4712','MM/DD/YYYY')) > sysdate
4281: AND rel.subject_table_name = 'HZ_PARTIES'
4282: AND rel.object_table_name = 'HZ_PARTIES';
4283:
4284: CURSOR c_contact_point IS
4285: SELECT contact_point_id, phone_line_type
4286: FROM hz_contact_points
4283:
4284: CURSOR c_contact_point IS
4285: SELECT contact_point_id, phone_line_type
4286: FROM hz_contact_points
4287: WHERE owner_table_name = 'HZ_PARTIES'
4288: AND owner_table_id = p_party_id
4289: AND contact_point_type = 'PHONE'
4290: AND actual_content_source = 'DNB'
4291: AND phone_line_type in ('FAX','GEN')
4333: -- Get information from hz_party
4334: BEGIN
4335: SELECT hq_branch_ind
4336: INTO l_hq_branch_ind
4337: FROM hz_parties
4338: WHERE party_id = p_party_id;
4339:
4340: populate_dnb_data(
4341: p_case_folder_id => p_case_folder_id,
4338: WHERE party_id = p_party_id;
4339:
4340: populate_dnb_data(
4341: p_case_folder_id => p_case_folder_id,
4342: p_source_table_name => 'HZ_PARTIES',
4343: p_source_key => p_party_id,
4344: p_source_key_column_name => 'PARTY_ID',
4345: p_mode => p_mode);
4346: EXCEPTION
4347: WHEN NO_DATA_FOUND THEN
4348: NULL;
4349: WHEN OTHERS THEN
4350: p_resultout := 1;
4351: p_error_msg := 'SqlError While retrieving Data from HZ_PARTIES '||sqlerrm;
4352: ar_cmgt_util.wf_debug(p_case_folder_id, p_error_msg);
4353: return;
4354: END;
4355:
4641: l_deno_dso number := 1;
4642: l_deno_ddso number := 1;
4643: l_numerator_dso number;
4644: l_numerator_ddso number;
4645: l_party_number hz_parties.party_number%type;
4646: l_score number;
4647:
4648:
4649:
4791: WHERE party_id in
4792: ( SELECT child_id
4793: from hz_hierarchy_nodes
4794: where parent_object_type = 'ORGANIZATION'
4795: and parent_table_name = 'HZ_PARTIES'
4796: and child_object_type = 'ORGANIZATION'
4797: and parent_id = p_party_id
4798: and effective_start_date <= sysdate
4799: and effective_end_date >= sysdate
5193: WHERE party_id in
5194: ( SELECT child_id
5195: from hz_hierarchy_nodes
5196: where parent_object_type = 'ORGANIZATION'
5197: and parent_table_name = 'HZ_PARTIES'
5198: and child_object_type = 'ORGANIZATION'
5199: and parent_id = p_party_id
5200: and effective_start_date <= sysdate
5201: and effective_end_date >= sysdate
5447: WHERE party_id in
5448: ( SELECT child_id
5449: from hz_hierarchy_nodes
5450: where parent_object_type = 'ORGANIZATION'
5451: and parent_table_name = 'HZ_PARTIES'
5452: and child_object_type = 'ORGANIZATION'
5453: and parent_id = p_party_id
5454: and effective_start_date <= sysdate
5455: and effective_end_date >= sysdate
5551: WHERE party_id in
5552: ( SELECT child_id
5553: from hz_hierarchy_nodes
5554: where parent_object_type = 'ORGANIZATION'
5555: and parent_table_name = 'HZ_PARTIES'
5556: and child_object_type = 'ORGANIZATION'
5557: and parent_id = p_party_id
5558: and effective_start_date <= sysdate
5559: and effective_end_date >= sysdate