[Home] [Help]
1850: -- Get the Organization details ...
1851:
1852: CURSOR get_susp_org_cur(P_ORG_ID IN VARCHAR2) IS
1853: SELECT hou.organization_id, hou.name, poa.gl_code_combination_id
1854: FROM hr_all_organization_units hou, psp_organization_accounts poa
1855: WHERE hou.organization_id = poa.organization_id
1856: AND poa.business_group_id = p_business_group_id
1857: AND poa.set_of_books_id = p_set_of_books_id
1858: AND poa.organization_account_id = p_org_id;
1858: AND poa.organization_account_id = p_org_id;
1859:
1860: CURSOR get_org_id_cur(P_LINE_ID IN NUMBER) IS
1861: SELECT hou.organization_id, hou.name
1862: FROM hr_all_organization_units hou,
1863: per_assignments_f paf,
1864: psp_payroll_lines ppl,
1865: psp_payroll_sub_lines ppsl,
1866: psp_distribution_lines pdl
1873: AND pdl.distribution_date between
1874: hou.date_from and nvl(hou.date_to,pdl.distribution_date)
1875: UNION
1876: SELECT hou.organization_id, hou.name
1877: FROM hr_all_organization_units hou,
1878: per_assignments_f paf,
1879: psp_pre_gen_dist_lines ppgd
1880: WHERE ppgd.pre_gen_dist_line_id = p_line_id
1881: AND ppgd.assignment_id = paf.assignment_id
1881: AND ppgd.assignment_id = paf.assignment_id
1882: AND ppgd.distribution_date BETWEEN paf.effective_start_date AND paf.effective_end_date
1883: AND paf.organization_id = hou.organization_id;
1884:
1885: l_orig_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
1886: l_orig_org_id number;
1887:
1888: -- End of Get org id cursor Ravindra
1889:
1922: AND p_distribution_date BETWEEN poa.start_date_active AND
1923: nvl(poa.end_date_active,p_distribution_date); Bug 2056877.*/
1924: organization_account_id = p_organization_account_id; --Added for bug 2056877.
1925:
1926: l_organization_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
1927: l_organization_id NUMBER(15);
1928: l_rowid ROWID;
1929: l_assignment_id NUMBER(15);
1930: l_distribution_date DATE;
1952: l_dr_summary_amount NUMBER := 0;
1953: l_cr_summary_amount NUMBER := 0;
1954: l_dr_cr_flag VARCHAR2(1);
1955: l_effective_date DATE;
1956: x_susp_failed_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
1957: x_susp_failed_status VARCHAR2(50);
1958: x_susp_failed_date DATE;
1959: x_lines_glccid NUMBER(15);
1960: x_susp_nf_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
1956: x_susp_failed_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
1957: x_susp_failed_status VARCHAR2(50);
1958: x_susp_failed_date DATE;
1959: x_lines_glccid NUMBER(15);
1960: x_susp_nf_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
1961: x_susp_nf_date DATE;
1962: l_return_status VARCHAR2(10);
1963: x_line_id NUMBER;
1964: l_no_run number;
1980: l_assignment_number varchar2(100);
1981: l_element_type varchar2(200);
1982: l_person_name varchar2(300);
1983: l_account varchar2(1000);
1984: l_auto_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
1985:
1986: l_pay_action_type psp_payroll_lines.payroll_action_type%TYPE; -- Bug 7040943
1987:
1988: cursor get_element_type is
2009: hou.name
2010: from per_all_people_f ppf,
2011: per_all_assignments_f paf,
2012: pay_element_types_f pet,
2013: hr_all_organization_units hou
2014: where ppf.person_id = l_person_id
2015: and l_distribution_date between ppf.effective_start_date and ppf.effective_end_date
2016: and paf.assignment_id = l_assignment_id
2017: and l_distribution_date between paf.effective_start_date and paf.effective_end_date
3711: gms_interface_rec gms_interface_cursor%ROWTYPE;
3712: l_transaction_source VARCHAR2(30);
3713: l_expenditure_comment VARCHAR2(240);
3714: l_employee_number VARCHAR2(30);
3715: l_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
3716: l_segment1 VARCHAR2(25);
3717: l_task_number VARCHAR2(300); -- Bug 16391366 (27/03/2013)
3718: l_gms_batch_name VARCHAR2(10);
3719: l_expenditure_ending_date DATE;
3913: BEGIN
3914: -- SELECT substr(name,1,60) Commented for bug fix 2447912
3915: SELECT name -- Removed SUBSTR for bug fix 2447912
3916: INTO l_org_name
3917: FROM hr_all_organization_units hou
3918: WHERE organization_id = gms_interface_rec.expenditure_organization_id;
3919: EXCEPTION
3920: WHEN NO_DATA_FOUND THEN
3921: l_value := 'Organization Id = '||to_char(gms_interface_rec.expenditure_organization_id);
4344: WHERE ppgd.summary_line_id = p_summary_line_id;
4345:
4346: CURSOR get_susp_org_cur1 (P_ORG_ID IN VARCHAR2) IS
4347: SELECT hou.organization_id, hou.name
4348: FROM hr_all_organization_units hou, psp_organization_accounts poa
4349: WHERE hou.organization_id = poa.organization_id
4350: AND poa.business_group_id = p_business_group_id
4351: AND poa.set_of_books_id = p_set_of_books_id
4352: AND poa.organization_account_id = p_org_id;
4352: AND poa.organization_account_id = p_org_id;
4353:
4354: CURSOR get_org_id_cur1 (P_LINE_ID IN NUMBER) IS
4355: SELECT hou.organization_id, hou.name
4356: FROM hr_all_organization_units hou,
4357: per_assignments_f paf,
4358: psp_payroll_lines ppl,
4359: psp_payroll_sub_lines ppsl,
4360: psp_distribution_lines pdl
4365: AND ppl.payroll_line_id = ppsl.payroll_line_id
4366: AND pdl.distribution_date between paf.effective_start_date and paf.effective_end_date
4367: UNION
4368: SELECT hou.organization_id, hou.name
4369: FROM hr_all_organization_units hou,
4370: per_assignments_f paf,
4371: psp_pre_gen_dist_lines ppgdl
4372: WHERE paf.assignment_id = ppgdl.assignment_id
4373: AND hou.organization_id = paf.organization_id
4373: AND hou.organization_id = paf.organization_id
4374: AND ppgdl.pre_gen_dist_line_id = p_line_id
4375: AND ppgdl.distribution_date between paf.effective_start_date and paf.effective_end_date;
4376:
4377: l_orig_org_name1 hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
4378: l_orig_org_id1 number;
4379:
4380: CURSOR org_susp_ac_cur(P_ORGANIZATION_ID IN NUMBER,
4381: P_DISTRIBUTION_DATE IN DATE) IS
4412: AND p_distribution_date BETWEEN poa.start_date_active AND
4413: nvl(poa.end_date_active,p_distribution_date);Bug 2056877 */
4414: organization_account_id = p_organization_account_id; --Added for bug 2056877.
4415:
4416: l_organization_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
4417: l_organization_id NUMBER(15);
4418: l_rowid ROWID;
4419: l_assignment_id NUMBER(15);
4420: l_distribution_date DATE;
4441: l_trx_reject_code VARCHAR2(30);
4442: l_orig_trx_reference VARCHAR2(30);
4443: l_effective_date DATE;
4444:
4445: x_susp_failed_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
4446: x_susp_failed_reject_code VARCHAR2(30);
4447: x_susp_failed_date DATE;
4448: x_susp_nf_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
4449: x_susp_nf_date DATE;
4444:
4445: x_susp_failed_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
4446: x_susp_failed_reject_code VARCHAR2(30);
4447: x_susp_failed_date DATE;
4448: x_susp_nf_org_name hr_all_organization_units_tl.name%TYPE; -- Bug 2447912: Modified declaration
4449: x_susp_nf_date DATE;
4450: l_return_status VARCHAR2(10);
4451: x_line_id NUMBER;
4452: l_no_run NUMBER;
4475: l_assignment_number varchar2(100);
4476: l_element_type varchar2(200);
4477: l_person_name varchar2(300);
4478: l_account varchar2(1000);
4479: l_auto_org_name hr_all_organization_units_tl.name%TYPE;
4480: l_pay_action_type psp_payroll_lines.payroll_action_type%TYPE; -- Bug 7040943
4481:
4482: cursor get_element_type is
4483: select ppl.element_type_id,
4503: hou.name
4504: from per_all_people_f ppf,
4505: per_all_assignments_f paf,
4506: pay_element_types_f pet,
4507: hr_all_organization_units hou
4508: where ppf.person_id = l_person_id
4509: and l_distribution_date between ppf.effective_start_date and ppf.effective_end_date
4510: and paf.assignment_id = l_assignment_id
4511: and l_distribution_date between paf.effective_start_date and paf.effective_end_date