[Home] [Help]
540: ,hr_organization_units hou
541: WHERE hoi.organization_id = g_gre_id
542: AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
543: AND hou.organization_id = hoi.organization_id
544: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
545: AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
546:
547: FOR i IN 1.. g_count_org - 1
548: LOOP
616: ,per_periods_of_service pos
617: WHERE asg.person_id = pos.person_id
618: AND asg.assignment_id = p_assignment_id
619: AND asg.business_group_id = pos.business_group_id
620: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
621: AND NVL(pos.actual_termination_date,(TO_DATE('31-12-4712','DD-MM-YYYY')))
622: BETWEEN asg.effective_start_date AND asg.effective_end_date
623: ORDER BY 1 DESC;
624:
1043: FROM hr_organization_units hou
1044: ,hr_organization_information hoi
1045: ,hr_organization_information bank
1046: WHERE hoi.organization_id = hou.organization_id
1047: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1048: AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1049: AND hoi.org_information1 = g_tax_year
1050: AND bank.org_information_context = 'PER_IN_CHALLAN_BANK'
1051: and bank.organization_id = hoi.organization_id
1056: SELECT 'BOOK - '||hoi.org_information3 ||' - ' ||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR') org_information3
1057: FROM hr_organization_units hou
1058: ,hr_organization_information hoi
1059: WHERE hoi.organization_id = hou.organization_id
1060: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1061: AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1062: AND hoi.org_information1 = g_tax_year
1063: AND hoi.org_information13 = g_quarter
1064: AND hoi.org_information5 IS NULL
1083: FROM hr_organization_units hou
1084: ,hr_organization_information hoi
1085: ,hr_organization_information bank
1086: WHERE hoi.organization_id = hou.organization_id
1087: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1088: AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1089: AND hoi.org_information1 = g_tax_year
1090: AND bank.org_information_context = 'PER_IN_CHALLAN_BANK'
1091: and bank.organization_id = hoi.organization_id
1096: SELECT 'BOOK - '||hoi.org_information3 ||' - ' ||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR') org_information3
1097: FROM hr_organization_units hou
1098: ,hr_organization_information hoi
1099: WHERE hoi.organization_id = hou.organization_id
1100: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1101: AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1102: AND hoi.org_information1 = g_tax_year
1103: AND hoi.org_information13 = g_quarter
1104: AND hoi.org_information5 IS NULL
1130: FROM hr_organization_units hou
1131: ,hr_organization_information hoi
1132: ,hr_organization_information bank
1133: WHERE hoi.organization_id = hou.organization_id
1134: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1135: AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1136: AND hoi.org_information1 = g_tax_year
1137: AND bank.org_information_context = 'PER_IN_CHALLAN_BANK'
1138: and bank.organization_id = hoi.organization_id
1143: SELECT 'BOOK - '||hoi.org_information3 ||' - ' ||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR') org_information3
1144: FROM hr_organization_units hou
1145: ,hr_organization_information hoi
1146: WHERE hoi.organization_id = hou.organization_id
1147: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1148: AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1149: AND hoi.org_information1 = g_tax_year
1150: AND hoi.org_information13 = g_quarter
1151: AND hoi.org_information5 IS NULL
1179: AND paa.payroll_action_id = p_payroll_action_id
1180: AND asg.assignment_id = pai.assignment_id
1181: AND asg.person_id = pep.person_id
1182: AND asg.business_group_id = pep.business_group_id
1183: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1184: AND (
1185: (
1186: (NVL(pai.action_information13,'0') <> (SELECT NVL(paei.aei_information2,'0')
1187: FROM per_assignment_extra_info paei
1287: AND pai.action_context_id = p_assignment_action_id
1288: AND asg.assignment_id = pai.assignment_id
1289: AND asg.person_id = pep.person_id
1290: AND asg.business_group_id = pep.business_group_id
1291: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1292: AND (
1293: (NVL(pai.action_information18,'0') <> (SELECT NVL(paei.aei_information2,'0')
1294: FROM per_assignment_extra_info paei
1295: ,per_assignments_f paa
1344: AND pai.action_context_id = p_assignment_action_id
1345: AND asg.assignment_id = pai.assignment_id
1346: AND asg.person_id = pep.person_id
1347: AND asg.business_group_id = pep.business_group_id
1348: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1349: AND (
1350: (NVL(pai.action_information18,'0') = (SELECT NVL(paei.aei_information2,'0')
1351: FROM per_assignment_extra_info paei
1352: ,per_assignments_f paa
1471: CURSOR c_bg_check(p_assignment_id NUMBER)
1472: IS
1473: SELECT 1
1474: FROM per_assignments_f
1475: WHERE business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
1476: AND assignment_id = p_assignment_id;
1477:
1478: l_procedure VARCHAR2(100);
1479: l_action_id NUMBER;
2067: AND paa.payroll_action_id = p_payroll_action_id
2068: AND asg.assignment_id = pai.assignment_id
2069: AND asg.person_id = pep.person_id
2070: AND asg.business_group_id = pep.business_group_id
2071: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2072: AND pai.source_id IN
2073: (
2074: SELECT
2075: paa.assignment_action_id run_asg_action_id
2247: AND pai.action_context_id = p_assignment_action_id
2248: AND asg.assignment_id = pai.assignment_id
2249: AND asg.person_id = pep.person_id
2250: AND asg.business_group_id = pep.business_group_id
2251: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2252: AND pai.source_id IN
2253: (
2254: SELECT
2255: paa.assignment_action_id run_asg_action_id
2378: AND pai.action_context_id = p_assignment_action_id
2379: AND asg.assignment_id = pai.assignment_id
2380: AND asg.person_id = pep.person_id
2381: AND asg.business_group_id = pep.business_group_id
2382: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2383: AND (
2384: (FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Salary Under Section 17',pai.action_context_id,pai.source_id,1)))
2385: = FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Salary Under Section 17','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
2386: AND
2565: CURSOR c_bg_check(p_assignment_id NUMBER)
2566: IS
2567: SELECT 1
2568: FROM per_assignments_f
2569: WHERE business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2570: AND assignment_id = p_assignment_id;
2571:
2572: l_procedure VARCHAR2(100);
2573: l_action_id NUMBER;
2850: ,hr_organization_units hou
2851: WHERE hoi.organization_id = p_gre_id
2852: AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
2853: AND hou.organization_id = hoi.organization_id
2854: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2855: AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2856:
2857: CURSOR c_reg_org_details(p_reg_org_id NUMBER)
2858: IS
2862: ,hr_organization_units hou
2863: WHERE hoi.organization_id = p_reg_org_id
2864: AND hoi.org_information_context = 'PER_IN_COMPANY_DF'
2865: AND hou.organization_id = hoi.organization_id
2866: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2867: AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2868:
2869: CURSOR c_pos(p_person_id NUMBER)
2870: IS
2875: WHERE asg.position_id=pos.position_id(+)
2876: AND asg.job_id=job.job_id(+)
2877: AND asg.person_id = p_person_id
2878: AND asg.primary_flag = 'Y'
2879: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2880: AND g_qr_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
2881: AND g_qr_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
2882: AND g_qr_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
2883:
2888: FROM per_assignments_f asg
2889: ,hr_organization_units hou
2890: WHERE asg.person_id = p_person_id
2891: AND asg.primary_flag = 'Y'
2892: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2893: AND hou.organization_id = asg.organization_id
2894: AND hou.business_group_id = asg.business_group_id
2895: AND g_qr_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
2896: AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
2905: ,per_people_f pep
2906: WHERE hoi.org_information_context = 'PER_IN_INCOME_TAX_REP_DF'
2907: AND hoi.organization_id = p_gre_id
2908: AND hou.organization_id = hoi.organization_id
2909: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
2910: AND pep.person_id = hoi.org_information1
2911: AND pep.business_group_id = hou.business_group_id
2912: AND g_qr_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
2913: AND g_qr_end_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
3531: FROM hr_organization_information hoi
3532: ,hr_organization_units hou
3533: WHERE hoi.organization_id = p_gre_id
3534: AND hoi.organization_id = hou.organization_id
3535: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
3536: AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
3537: )
3538: AND hoi.org_information13 = g_quarter
3539: AND hoi.org_information1 = p_tax_year
3573: FROM hr_organization_information hoi
3574: ,hr_organization_units hou
3575: WHERE hoi.organization_id = p_gre_id
3576: AND hoi.organization_id = hou.organization_id
3577: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
3578: AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
3579: )
3580: AND hoi.org_information13 = g_quarter
3581: AND hoi.org_information1 = p_tax_year
4210: FROM per_assignments_f asg
4211: ,per_people_f pep
4212: WHERE asg.assignment_id = p_assignment_id
4213: AND pep.person_id = asg.person_id
4214: AND pep.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4215: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4216: AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
4217: AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
4218: ORDER BY 5 DESC;
4211: ,per_people_f pep
4212: WHERE asg.assignment_id = p_assignment_id
4213: AND pep.person_id = asg.person_id
4214: AND pep.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4215: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4216: AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
4217: AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
4218: ORDER BY 5 DESC;
4219:
4628: FROM per_assignments_f asg
4629: ,per_people_f pep
4630: WHERE asg.assignment_id = p_assignment_id
4631: AND pep.person_id = asg.person_id
4632: AND pep.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4633: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4634: AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
4635: AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date;
4636:
4629: ,per_people_f pep
4630: WHERE asg.assignment_id = p_assignment_id
4631: AND pep.person_id = asg.person_id
4632: AND pep.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4633: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4634: AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
4635: AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date;
4636:
4637: CURSOR c_24qc_source_id(p_action_context_id NUMBER,
4703: AND scl.segment1 LIKE g_gre_id
4704: AND ( asg.effective_start_date BETWEEN g_fin_start_date AND g_end_date
4705: OR g_fin_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
4706: )
4707: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4708: ORDER BY 1 ;
4709:
4710: CURSOR get_eoy_archival_details(p_start_date DATE
4711: ,p_end_date DATE
4760: ,per_periods_of_service pos
4761: WHERE asg.person_id = pos.person_id
4762: AND asg.assignment_id = p_assignment_id
4763: AND asg.business_group_id = pos.business_group_id
4764: AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
4765: AND NVL(pos.actual_termination_date,(to_date('31-12-4712','DD-MM-YYYY')))
4766: BETWEEN asg.effective_start_date AND asg.effective_end_date
4767: ORDER BY 1 DESC;
4768:
7246: FROM hr_organization_information hoi
7247: ,hr_organization_units hou
7248: WHERE hoi.organization_id = g_gre_id
7249: AND hoi.organization_id = hou.organization_id
7250: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
7251: AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
7252: )
7253: AND hoi.org_information13 = g_quarter
7254: AND hoi.org_information1 = g_tax_year;
7440: FROM hr_organization_information hoi
7441: ,hr_organization_units hou
7442: WHERE hoi.organization_id = g_gre_id
7443: AND hoi.organization_id = hou.organization_id
7444: AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
7445: AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
7446: )
7447: AND hoi.org_information13 = g_quarter
7448: AND hoi.org_information1 = g_tax_year