eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PQP_NL_PENSION_EXTRACTS dependencies on HR_ORGANIZATION_INFORMATION

Line 2719: hr_organization_information hoi

2715: -- If a valid version is not found then do nothing.
2716: CURSOR c_get_org_hierarchy IS
2717: SELECT pos.org_structure_version_id
2718: FROM per_org_structure_versions_v pos,
2719: hr_organization_information hoi
2720: WHERE hoi.organization_id = p_business_group_id
2721: AND To_Number(hoi.org_information1) = pos.organization_structure_id
2722: AND Trunc(p_effective_date) BETWEEN date_from
2723: AND Nvl(date_to,Hr_Api.g_eot)

Line 2740: FROM hr_organization_information

2736:
2737: --Cursor to check whether oganization is tax organization or not
2738: CURSOR csr_tax_org(c_org_id NUMBER) IS
2739: SELECT 'x'
2740: FROM hr_organization_information
2741: WHERE organization_id = c_org_id
2742: AND org_information_context = 'NL_ORG_INFORMATION'
2743: AND org_information3 IS NOT NULL
2744: AND org_information4 IS NOT NULL;

Line 5532: hr_organization_information hoi

5528: -- If a valid version is not found then do nothing.
5529: CURSOR c_get_org_hierarchy IS
5530: SELECT pos.org_structure_version_id
5531: FROM per_org_structure_versions_v pos,
5532: hr_organization_information hoi
5533: WHERE hoi.organization_id = p_business_group_id
5534: AND To_Number(hoi.org_information1) = pos.organization_structure_id
5535: AND Trunc(p_effective_date) BETWEEN date_from
5536: AND Nvl(date_to,Hr_Api.g_eot)

Line 5554: FROM hr_organization_information

5550: -- Cursor to check whether oganization is tax organization or not
5551: --
5552: CURSOR csr_tax_org(c_org_id NUMBER) IS
5553: SELECT 'x'
5554: FROM hr_organization_information
5555: WHERE organization_id = c_org_id
5556: AND org_information_context = 'NL_ORG_INFORMATION'
5557: AND org_information3 IS NOT NULL
5558: AND org_information4 IS NOT NULL;

Line 6522: FROM hr_organization_information

6518: -- Cursor to find the named hierarchy associated with the BG
6519: --
6520: CURSOR c_find_named_hierarchy IS
6521: SELECT org_information1
6522: FROM hr_organization_information
6523: WHERE organization_id = p_business_group_id
6524: AND org_information_context = 'NL_BG_INFO';
6525:
6526: --

Line 6562: FROM hr_organization_information

6558: --
6559: CURSOR csr_get_er_num(c_org_id IN Number) IS
6560: SELECT UPPER(nvl(lpad(org_information4,7,'0'),'0000000')) old_num
6561: ,SUBSTR(NVL(org_information2,'-1'),0,7) new_num
6562: FROM hr_organization_information
6563: WHERE org_information_context = 'PQP_ABP_PROVIDER'
6564: AND organization_id = c_org_id;
6565:
6566: --

Line 6577: l_new_er_num hr_organization_information.org_information2%TYPE := '-1';

6573: AND business_group_id = p_business_group_id
6574: ORDER BY effective_end_date DESC;
6575:
6576: l_old_er_num ben_ext_chg_evt_log.old_val1%TYPE := '-1';
6577: l_new_er_num hr_organization_information.org_information2%TYPE := '-1';
6578: l_org_info_id hr_organization_information.org_information_id%TYPE;
6579: l_version_id per_org_structure_versions_v.org_structure_version_id%TYPE DEFAULT NULL;
6580: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_ABP_ER_Num';
6581: l_ret_val NUMBER := -1;

Line 6578: l_org_info_id hr_organization_information.org_information_id%TYPE;

6574: ORDER BY effective_end_date DESC;
6575:
6576: l_old_er_num ben_ext_chg_evt_log.old_val1%TYPE := '-1';
6577: l_new_er_num hr_organization_information.org_information2%TYPE := '-1';
6578: l_org_info_id hr_organization_information.org_information_id%TYPE;
6579: l_version_id per_org_structure_versions_v.org_structure_version_id%TYPE DEFAULT NULL;
6580: l_proc_name VARCHAR2(150) := g_proc_name ||'Get_ABP_ER_Num';
6581: l_ret_val NUMBER := -1;
6582: l_org_id NUMBER;

Line 9013: FROM hr_organization_information

9009:
9010: --Cursor to find the named hierarchy associated with the BG
9011: CURSOR c_find_named_hierarchy IS
9012: SELECT org_information1
9013: FROM hr_organization_information
9014: WHERE organization_id = p_business_group_id
9015: AND org_information_context = 'NL_BG_INFO';
9016:
9017: --Cursor to find the valid version id for the particular named hierarchy

Line 9307: l_org_info_id hr_organization_information.org_information_id%TYPE;

9303: ) RETURN Number IS
9304:
9305: l_org_id hr_all_organization_units.organization_id%TYPE;
9306: l_ret_value Number := 0; --return
9307: l_org_info_id hr_organization_information.org_information_id%TYPE;
9308: l_named_hierarchy Number;
9309: l_version_id per_org_structure_versions_v.org_structure_version_id%TYPE DEFAULT NULL;
9310: l_loop_again Number;
9311: l_is_org_info_valid Varchar2(1);

Line 9324: FROM hr_organization_information

9320:
9321: --Cursor to find the named hierarchy associated with the BG
9322: CURSOR c_find_named_hierarchy IS
9323: SELECT org_information1
9324: FROM hr_organization_information
9325: WHERE organization_id = p_business_group_id
9326: AND org_information_context = 'NL_BG_INFO';
9327:
9328: --Cursor to find the valid version id for the particular named hierarchy

Line 9357: FROM hr_organization_information hoi

9353: --Cursor to find if there is any information record at the org level
9354: --if so return the org info id
9355: CURSOR c_get_valid_org_info(c_org_id IN hr_all_organization_units.organization_id%TYPE) IS
9356: SELECT hoi.org_information_id
9357: FROM hr_organization_information hoi
9358: WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
9359: AND hoi.org_information3 = To_Char(p_pension_type_id)
9360: AND NVL(hoi.org_information7,'Y') = 'Y'
9361: AND hoi.organization_id = c_org_id;

Line 9364: CURSOR c_get_org_info(c_org_id IN hr_organization_information.organization_id%TYPE) IS

9360: AND NVL(hoi.org_information7,'Y') = 'Y'
9361: AND hoi.organization_id = c_org_id;
9362:
9363: --Cursor to find the participation end date from org level information
9364: CURSOR c_get_org_info(c_org_id IN hr_organization_information.organization_id%TYPE) IS
9365: SELECT Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
9366: FROM hr_organization_information hoi
9367: WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
9368: AND hoi.org_information3 = To_Char(p_pension_type_id)

Line 9366: FROM hr_organization_information hoi

9362:
9363: --Cursor to find the participation end date from org level information
9364: CURSOR c_get_org_info(c_org_id IN hr_organization_information.organization_id%TYPE) IS
9365: SELECT Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
9366: FROM hr_organization_information hoi
9367: WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
9368: AND hoi.org_information3 = To_Char(p_pension_type_id)
9369: AND hoi.org_information6 = 'Y'
9370: AND NVL(hoi.org_information7,'Y') = 'Y'

Line 9539: FROM hr_organization_information

9535:
9536: --Cursor to find the named hierarchy associated with the BG
9537: CURSOR c_find_named_hierarchy IS
9538: SELECT org_information1
9539: FROM hr_organization_information
9540: WHERE organization_id = p_business_group_id
9541: AND org_information_context = 'NL_BG_INFO';
9542:
9543: --Cursor to find the valid version id for the particular named hierarchy

Line 9951: hr_organization_information hoi,

9947:
9948: CURSOR c_rr_cur IS
9949: SELECT 1
9950: FROM per_all_assignments_f paf,
9951: hr_organization_information hoi,
9952: pqp_pension_types_f pty,
9953: pay_all_payrolls_f ppf
9954: WHERE paf.assignment_id = p_assignment_id
9955: AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date

Line 9963: FROM hr_organization_information hoi1

9959: AND (paf.organization_id = hoi.organization_id
9960: OR
9961: (fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id
9962: AND NOT EXISTS (SELECT 1
9963: FROM hr_organization_information hoi1
9964: WHERE hoi1.org_information_context = 'PQP_NL_ABP_PT'
9965: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
9966: AND hoi1.organization_id = paf.organization_id
9967: AND ( NVL(hoi1.org_information6,'N')= 'N'

Line 10103: hr_organization_information hoi,

10099: ,'FPU_R','A'
10100: ,'FPU_S','S'
10101: ,'FPU_T' ,'B') fpu_code
10102: FROM per_all_assignments_f paf,
10103: hr_organization_information hoi,
10104: pqp_pension_types_f pty,
10105: pay_all_payrolls_f ppf
10106: WHERE paf.assignment_id = p_assignment_id
10107: AND p_effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date

Line 10115: FROM hr_organization_information hoi1

10111: AND (paf.organization_id = hoi.organization_id
10112: OR
10113: (fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id
10114: AND NOT EXISTS (SELECT 1
10115: FROM hr_organization_information hoi1
10116: WHERE hoi1.org_information_context = 'PQP_NL_ABP_PT'
10117: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
10118: AND hoi1.organization_id = paf.organization_id
10119: AND ( NVL(hoi1.org_information6,'N')= 'N'

Line 11899: FROM hr_organization_information

11895:
11896: --Cursor to find the named hierarchy associated with the BG
11897: CURSOR c_find_named_hierarchy IS
11898: SELECT org_information1
11899: FROM hr_organization_information
11900: WHERE organization_id = p_business_group_id
11901: AND org_information_context = 'NL_BG_INFO';
11902:
11903: --Cursor to find the valid version id for the particular named hierarchy

Line 16095: FROM hr_organization_information

16091: --
16092: CURSOR csr_get_new_er_num(c_org_id IN NUMBER
16093: ,c_effective_date IN DATE) IS
16094: SELECT SUBSTR(NVL(org_information2,'-1'),0,7)
16095: FROM hr_organization_information
16096: WHERE org_information_context = 'PQP_ABP_PROVIDER'
16097: AND organization_id = c_org_id;
16098:
16099: l_proc_name VARCHAR2(150) := g_proc_name ||'.Get_Header_EMR_Number';

Line 16131: FROM hr_organization_information

16127:
16128:
16129: CURSOR csr_get_submit_code IS
16130: SELECT Substr(org_information3,0,4)
16131: FROM hr_organization_information
16132: WHERE org_information_context = 'PQP_ABP_PROVIDER'
16133: AND organization_id = p_org_id;
16134:
16135: l_proc_name Varchar2(150) := g_proc_name ||'.Get_Header_Submit_Code';