1 DECLARE 2   l_element_name                VARCHAR2(240); 3   l_element_id                  NUMBER; 4   l_period_name                 VARCHAR2(240); 5   l_period_date                 DATE; 6   x_element_type_id             NUMBER; 7   x_effective_start_date        DATE; 8   x_effective_end_date          DATE; 9   x_object_version_number       NUMBER;10   x_comment_id                  NUMBER;11   x_processing_priority_warning BOOLEAN;12 BEGIN13   fnd_global.apps_initialize(user_id      => 1510,14                              resp_id      => 23991,15                              resp_appl_id => 800);16 17   l_element_name := ‘BS交通补贴12‘;18   l_period_name  := ‘9 2016 日历月份‘;19 20   BEGIN21     SELECT ptp.end_date22       INTO l_period_date23       FROM per_time_periods ptp24      WHERE ptp.period_name = l_period_name;25   EXCEPTION26     WHEN OTHERS THEN27       l_period_date := NULL;28   END;29 30   BEGIN31     SELECT petf.element_type_id32       INTO l_element_id33       FROM pay_element_types_f petf34      WHERE petf.element_name = l_element_name35        AND l_period_date BETWEEN effective_start_date AND effective_end_date;36   EXCEPTION37     WHEN OTHERS THEN38       l_element_id := NULL;39   END;40 41   dbms_output.put_line(‘l_period_date:=‘ || l_period_date);42   dbms_output.put_line(‘l_element_id:=‘ || l_element_id);43 44   IF (l_element_id IS NULL) THEN45     dbms_output.put_line(‘Create‘);46   47     pay_element_types_api.create_element_type(p_validate                     => FALSE,48                                               p_effective_date               => l_period_date,49                                               p_classification_id            => 118,50                                               p_element_name                 => l_element_name,51                                               p_input_currency_code          => ‘CNY‘,52                                               p_output_currency_code         => ‘CNY‘,53                                               p_multiple_entries_allowed_fla => ‘Y‘,54                                               p_processing_type              => ‘N‘,55                                               p_business_group_id            => 81,56                                               p_element_type_id              => x_element_type_id,57                                               p_effective_start_date         => x_effective_start_date,58                                               p_effective_end_date           => x_effective_end_date,59                                               p_object_version_number        => x_object_version_number,60                                               p_comment_id                   => x_comment_id,61                                               p_processing_priority_warning  => x_processing_priority_warning);62     -- ELSE63     --   pay_element_types_api.update_element_type();64   END IF;65 66   IF x_processing_priority_warning THEN67     dbms_output.put_line(‘x_processing_priority_warning‘);68   END IF;69 70   dbms_output.put_line(‘x_element_type_id:=‘ || x_element_type_id);71   dbms_output.put_line(‘x_effective_start_date:=‘ || x_effective_start_date);72   dbms_output.put_line(‘x_effective_end_date:=‘ || x_effective_end_date);73   dbms_output.put_line(‘x_element_type_id:=‘ || x_object_version_number);74   dbms_output.put_line(‘x_comment_id:=‘ || x_comment_id);75 EXCEPTION76   WHEN OTHERS THEN77     dbms_output.put_line(‘导入要素异常！‘);78 END;

 1  /* IF (l_element_link_id IS NULL) THEN 2      3       pay_element_link_api.create_element_link(p_validate                   => FALSE, 4                                                p_effective_date             => l_period_date, 5                                                p_element_type_id            => l_element_id, 6                                                p_business_group_id          => 81, 7                                                p_costable_type              => ‘F‘, 8                                                p_payroll_id                 => l_payroll_id, 9                                                p_people_group_id            => l_people_group_id,10                                                p_cost_concat_segments       => ‘01.000000.66010101.0000.00000000.0000.000000.00‘,11                                                p_balance_concat_segments    => ‘01.000000.22110101.0000.00000000.0000.000000.00‘,12                                                p_cost_allocation_keyflex_id => 63,13                                                p_balancing_keyflex_id       => 76,14                                                p_standard_link_flag         => ‘N‘,15                                                p_element_link_id            => x_element_link_id,16                                                p_comment_id                 => x_comment_id,17                                                p_object_version_number      => x_object_version_number,18                                                p_effective_start_date       => x_effective_start_date,19                                                p_effective_end_date         => x_effective_end_date);20     21       dbms_output.put_line(‘x_element_link_id:=‘ || x_element_link_id);22       dbms_output.put_line(‘x_comment_id:=‘ || x_comment_id);23       dbms_output.put_line(‘x_element_type_id:=‘ || x_object_version_number);24       dbms_output.put_line(‘x_effective_start_date:=‘ || x_effective_start_date);25       dbms_output.put_line(‘x_effective_end_date:=‘ || x_effective_end_date);26     END IF;*/

  1 DECLARE  2   l_element_name          VARCHAR2(240);  3   l_element_id            NUMBER;  4   l_period_name           VARCHAR2(240);  5   l_period_date           DATE;  6   l_assign_number         VARCHAR2(240);  7   l_assignment_id         NUMBER;  8   l_payroll_id            NUMBER;  9   l_payroll               VARCHAR2(240); 10   l_element_link_id       NUMBER; 11   l_element_entry_id      NUMBER; 12   l_people_group_id       NUMBER; 13   x_element_link_id       NUMBER; 14   x_effective_start_date  DATE; 15   x_effective_end_date    DATE; 16   x_object_version_number NUMBER; 17   x_comment_id            NUMBER; 18   x_element_entry_id      NUMBER; 19   x_create_warning        BOOLEAN; 20 BEGIN 21  22   fnd_global.apps_initialize(user_id      => 1510, 23                              resp_id      => 23991, 24                              resp_appl_id => 800); 25  26   l_element_name  := ‘BS_FJJ_非计件日工‘; 27   l_period_name   := ‘9 2016 日历月份‘; 28   l_assign_number := ‘00251‘; 29  30   BEGIN 31     SELECT ptp.end_date 32       INTO l_period_date 33       FROM per_time_periods ptp 34      WHERE ptp.period_name = l_period_name; 35   EXCEPTION 36     WHEN OTHERS THEN 37       l_period_date := NULL; 38   END; 39  40   BEGIN 41     SELECT petf.element_type_id 42       INTO l_element_id 43       FROM pay_element_types_f petf 44      WHERE petf.element_name = l_element_name 45        AND l_period_date BETWEEN effective_start_date AND effective_end_date; 46   EXCEPTION 47     WHEN OTHERS THEN 48       l_element_id := NULL; 49   END; 50  51   dbms_output.put_line(‘l_period_date:=‘ || l_period_date); 52   dbms_output.put_line(‘l_element_id:=‘ || l_element_id); 53  54   BEGIN 55     SELECT paaf.payroll_id, 56            paaf.assignment_id, 57            paaf.people_group_id 58       INTO l_payroll_id, 59            l_assignment_id, 60            l_people_group_id 61       FROM per_all_assignments_f paaf 62      WHERE paaf.assignment_number = l_assign_number 63        AND l_period_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date; 64   EXCEPTION 65     WHEN no_data_found THEN 66       dbms_output.put_line(‘没有分配信息‘); 67     WHEN too_many_rows THEN 68       dbms_output.put_line(‘存在多条分配信息‘); 69   END; 70  71   dbms_output.put_line(‘l_payroll_id:=‘ || l_payroll_id); 72   dbms_output.put_line(‘l_assignment_id:=‘ || l_assignment_id); 73   dbms_output.put_line(‘l_people_group_id:=‘ || l_people_group_id); 74  75   IF l_payroll_id IS NOT NULL THEN 76     BEGIN 77       SELECT pap.payroll_name 78         INTO l_payroll 79         FROM pay_all_payrolls_f pap 80        WHERE pap.payroll_id = l_payroll_id 81          AND l_period_date BETWEEN pap.effective_start_date AND pap.effective_end_date; 82     EXCEPTION 83       WHEN no_data_found THEN 84         dbms_output.put_line(‘没有有效工资单‘); 85       WHEN too_many_rows THEN 86         dbms_output.put_line(‘存在多工资单‘); 87     END; 88    89     dbms_output.put_line(‘l_payroll:=‘ || l_payroll); 90   END IF; --IF l_payroll_id IS NULL THEN 91  92   IF (l_element_id IS NOT NULL) THEN 93    94     l_element_link_id := NULL; 95     BEGIN 96       SELECT pelf.element_link_id 97         INTO l_element_link_id 98         FROM pay_element_links_f pelf 99        WHERE pelf.element_type_id = l_element_id100          AND pelf.payroll_id = l_payroll_id101             --AND pelf.people_group_id = l_people_group_id102          AND l_period_date BETWEEN pelf.effective_start_date AND pelf.effective_end_date103          AND cuxhrpkg.show_record(‘PAY_PEOPLE_GROUPS‘,104                                   NULL,105                                   pelf.business_group_id,106                                   pelf.people_group_id,107                                   to_char(l_period_date,108                                           ‘YYYYMMDD‘),109                                   ‘‘) = ‘TRUE‘110          AND cuxhrpkg.show_ass(l_assignment_id,111                                pelf.people_group_id,112                                l_period_date) = ‘TRUE‘;113     114     EXCEPTION115       WHEN OTHERS THEN116         l_element_link_id := NULL;117     END;118   119     dbms_output.put_line(‘l_element_link_id:=‘ || l_element_link_id);120   121     IF (l_element_link_id IS NOT NULL) THEN122       BEGIN123         SELECT ee.element_entry_id124         --ee.object_version_number125           INTO l_element_entry_id126         -- l_object_version_number127           FROM pay_element_entries_f ee,128                pay_element_types_f   et,129                pay_element_links_f   el130          WHERE el.element_link_id = ee.element_link_id131            AND el.element_link_id = l_element_link_id132            AND el.element_type_id = et.element_type_id133            AND ee.assignment_id = l_assignment_id134            AND l_period_date BETWEEN ee.effective_start_date AND ee.effective_end_date135            AND l_period_date BETWEEN el.effective_start_date AND el.effective_end_date136            AND l_period_date BETWEEN et.effective_start_date AND et.effective_end_date137            AND et.multiple_entries_allowed_flag = ‘N‘138            AND ee.entry_type = ‘E‘;139       EXCEPTION140         WHEN OTHERS THEN141           l_element_entry_id := NULL;142       END;143     144       dbms_output.put_line(‘l_element_entry_id:=‘ || l_element_entry_id);145     END IF;146   END IF; --    IF (l_element_id IS NOT NULL) THEN147   IF (l_element_entry_id IS NULL) THEN148     pay_element_entry_api.create_element_entry(p_effective_date             => l_period_date,149                                                p_business_group_id          => 81,150                                                p_assignment_id              => l_assignment_id,151                                                p_element_link_id            => l_element_link_id,152                                                p_entry_type                 => ‘E‘,153                                                p_creator_type               => ‘F‘,154                                                p_cost_allocation_keyflex_id => NULL,155                                                -- p_input_value_id1            => l_input_id,156                                                157                                                p_effective_start_date  => x_effective_start_date,158                                                p_effective_end_date    => x_effective_end_date,159                                                p_element_entry_id      => x_element_entry_id,160                                                p_object_version_number => x_object_version_number,161                                                p_create_warning        => x_create_warning);162   163     dbms_output.put_line(‘x_effective_start_date:=‘ || x_effective_start_date);164     dbms_output.put_line(‘x_effective_end_date:=‘ || x_effective_end_date);165     dbms_output.put_line(‘x_element_entry_id:=‘ || x_element_entry_id);166     dbms_output.put_line(‘x_object_version_number:=‘ || x_object_version_number);167   168   END IF;169   --dbms_output.put_line(‘l_payroll:=‘ || l_payroll);170 EXCEPTION171   WHEN OTHERS THEN172     dbms_output.put_line(‘导入要素异常！‘);173 END;