[Home] [Help]
463: --
464: -- cursor csr_assignment_action_id
465: -- is
466: -- SELECT ASSACT.assignment_action_id
467: -- FROM pay_payroll_actions PACT,
468: -- pay_assignment_actions ASSACT
469: -- WHERE ASSACT.assignment_id = P_ASSIGNMENT_ID
470: -- AND PACT.payroll_action_id = ASSACT.payroll_action_id
471: -- AND PACT.date_earned >= P_FROM_DATE
481: USE_NL(FEED)
482: INDEX(FEED PAY_BALANCE_FEEDS_F_N50) */
483: NVL(SUM(FND_NUMBER.CANONICAL_TO_NUMBER(TARGET.result_value) * FEED.scale),0)
484: FROM pay_assignment_actions ASSACT,
485: pay_payroll_actions PACT,
486: pay_run_results RR,
487: pay_run_result_values TARGET,
488: pay_balance_feeds_f FEED
489: WHERE ASSACT.assignment_id = P_ASSIGNMENT_ID
539: --
540: -- cursor csr_assignment_action_id
541: -- is
542: -- SELECT ASSACT.assignment_action_id
543: -- from pay_payroll_actions PACT,
544: -- pay_assignment_actions ASSACT
545: -- WHERE ASSACT.assignment_id = p_assignment_id
546: -- AND ASSACT.action_sequence <= NVL(p_action_sequence, ASSACT.action_sequence)
547: -- AND PACT.payroll_action_id = ASSACT.payroll_action_id
549: -- AND PACT.effective_date >= p_from_date
550: -- AND PACT.effective_date <= p_to_date
551: -- AND NOT EXISTS(
552: -- SELECT NULL
553: -- FROM pay_payroll_actions RPACT,
554: -- pay_assignment_actions RASSACT,
555: -- pay_action_interlocks RINTL
556: -- WHERE RINTL.locked_action_id = ASSACT.assignment_action_id
557: -- AND RASSACT.assignment_action_id = RINTL.locking_action_id
568: FROM ( select /*+ ORDERED */
569: ASSACT.assignment_action_id,
570: PACT.effective_date
571: from pay_assignment_actions ASSACT,
572: pay_payroll_actions PACT
573: WHERE ASSACT.assignment_id = p_assignment_id
574: AND ASSACT.action_sequence <= NVL(p_action_sequence, ASSACT.action_sequence)
575: AND PACT.payroll_action_id = ASSACT.payroll_action_id
576: AND PACT.action_type <> 'V'
577: AND PACT.effective_date >= p_from_date
578: AND PACT.effective_date <= p_to_date
579: AND NOT EXISTS(
580: SELECT NULL
581: FROM pay_payroll_actions RPACT,
582: pay_assignment_actions RASSACT,
583: pay_action_interlocks RINTL
584: WHERE RINTL.locked_action_id = ASSACT.assignment_action_id
585: AND RASSACT.assignment_action_id = RINTL.locking_action_id
641: -- months_between( BACT.date_earned,
642: -- nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')))
643: -- / 12) * 12) start_date
644: -- from pay_assignment_actions BAL_ASSACT,
645: -- pay_payroll_actions BACT,
646: -- hr_organization_information HROG
647: -- where BAL_ASSACT.assignment_action_id = assact_id
648: -- and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
649: -- and HROG.organization_id = BACT.business_group_id
648: -- and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
649: -- and HROG.organization_id = BACT.business_group_id
650: -- and HROG.org_information_context = 'Business Group Information') V1,
651: -- pay_assignment_actions ASSACT,
652: -- pay_payroll_actions PACT
653: -- WHERE ASSACT.assignment_id = V1.assignment_id
654: -- AND PACT.payroll_action_id = ASSACT.payroll_action_id
655: -- AND PACT.date_earned >= V1.start_date
656: -- AND ( ( PACT.date_earned < V1.date_earned)
679: months_between( BACT.date_earned,
680: nvl(fnd_date.canonical_to_date(HROG.org_information11), trunc(BACT.date_earned, 'YYYY')))
681: / 12) * 12) start_date
682: from hr_organization_information HROG,
683: pay_payroll_actions BACT,
684: pay_assignment_actions BAL_ASSACT
685: where BAL_ASSACT.assignment_action_id = assact_id
686: and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
687: and HROG.organization_id = BACT.business_group_id
686: and BACT.payroll_action_id = BAL_ASSACT.payroll_action_id
687: and HROG.organization_id = BACT.business_group_id
688: and HROG.org_information_context = 'Business Group Information') V1,
689: pay_assignment_actions ASSACT,
690: pay_payroll_actions PACT,
691: pay_run_results RR,
692: pay_run_result_values TARGET,
693: pay_balance_feeds_f FEED
694: WHERE ASSACT.assignment_id = V1.assignment_id